[20221130]with+materialize会产生日志吗.txt

--//测试看看这样的写法是否会产生日志.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试脚本:
$ cat cc1.txt
with mysid as ( select /*+ materialize */ userenv('SID') n from dual )        
select upper(nvl(program, 'null')),
      upper(module),
      type,
      decode(nvl(instr(process, ':'), 0),
             0,
             nvl(process, 1234),
             substr(process, 1, instr(process, ':') - 1)),
      osuser,
      machine,
      SCHEMANAME,
      USERNAME,
      SERVICE_NAME,
      sid,
      serial#
 from sys.v_$session,mysid         
where  sid = mysid.n;

--//其执行计划如下:
Plan hash value: 2230424401
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |        |       |     4 (100)|          |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION  |                             |        |       |            |          |       |       |          |
|   2 |   LOAD AS SELECT            |                             |        |       |            |          |   270K|   270K|  270K (0)|
|   3 |    FAST DUAL                |                             |      1 |       |     2   (0)| 00:00:01 |       |       |          |
|   4 |   NESTED LOOPS              |                             |      1 |   310 |     2   (0)| 00:00:01 |       |       |          |
|   5 |    NESTED LOOPS             |                             |      1 |   297 |     2   (0)| 00:00:01 |       |       |          |
|   6 |     NESTED LOOPS            |                             |      1 |   271 |     2   (0)| 00:00:01 |       |       |          |
|   7 |      VIEW                   |                             |      1 |    13 |     2   (0)| 00:00:01 |       |       |          |
|   8 |       TABLE ACCESS FULL     | SYS_TEMP_0FD9D662B_22489899 |      1 |    13 |     2   (0)| 00:00:01 |       |       |          |
|*  9 |      FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)             |      1 |   258 |     0   (0)|          |       |       |          |
|* 10 |     FIXED TABLE FIXED INDEX | X$KSLWT (ind:1)             |      1 |    26 |     0   (0)|          |       |       |          |
|* 11 |    FIXED TABLE FIXED INDEX  | X$KSLED (ind:2)             |      1 |    13 |     0   (0)|          |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$71D7A081
   2 - SEL$1
   3 - SEL$1        / DUAL@SEL$1
   7 - SEL$D67CB2D2 / MYSID@SEL$2
   8 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
   9 - SEL$71D7A081 / S@SEL$5
  10 - SEL$71D7A081 / W@SEL$5
  11 - SEL$71D7A081 / E@SEL$5
Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND
              "S"."INDX"="MYSID"."N"))
  10 - filter("S"."INDX"="W"."KSLWTSID")
  11 - filter("W"."KSLWTEVT"="E"."INDX")
--//建立一张临时表SYS_TEMP_0FD9D662B_22489899.

3.测试:
--//session 1:
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        53       3047 57253                    DEDICATED 57254       27         37 alter system kill session '53,3047' immediate;

--//session 2:
SYS@book> @ses 53 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
        53 redo size                                      1656
        53 redo size for lost write detection                0
        53 redo size for direct writes                       0
--//session 1:
@ cc1.txt
--//输出略.

--//session 2:
SYS@book> @ses 53 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
        53 redo size                                      2540
        53 redo size for lost write detection                0
        53 redo size for direct writes                       0
--//可以看出这样写法的缺点是产生redo.

--//session 1:
@ cc1.txt
--//输出略.

--//session 2:
SYS@book> @ses 53 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
        53 redo size                                      3424
        53 redo size for lost write detection                0
        53 redo size for direct writes                       0

--//3424-2540 = 884
--//2540-1656 = 884
--//相当于1K的redo.可以发现这样写加上程序频繁调用产生的redo不可小看.

--//注测试中使用ses2脚本来之tpt ses.sql.脚本的主要内容如下:
select
    ses.sid,
    sn.name,
    ses.value
from
    v$sesstat ses,
    v$statname sn
where
    sn.statistic# = ses.statistic#
and ses.sid in (&1)
and lower(sn.name) like lower('%&2%')
/

3.补充19c的情况:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> show parameter temp_undo_enabled
PARAMETER_NAME    TYPE    VALUE
----------------- ------- -----
temp_undo_enabled boolean FALSE

--//测试过程略.
SYS@192.168.100.235:1521/orcl> @ses 3403 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      3403 redo size                                         0
      3403 redo size for lost write detection                0
      3403 redo size for direct writes                       0

SYS@192.168.100.235:1521/orcl> @ses 3403 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      3403 redo size                                       612
      3403 redo size for lost write detection                0
      3403 redo size for direct writes                       0

SYS@192.168.100.235:1521/orcl> @ses 3403 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      3403 redo size                                      1148
      3403 redo size for lost write detection                0
      3403 redo size for direct writes                       0

--//1148-612 = 536
--//612-0 = 612

--//修改temp_undo_enabled=true看看.
SYS@192.168.100.235:1521/orcl> alter session set temp_undo_enabled=true;
Session altered.

SYS@192.168.100.235:1521/orcl> @ hide temp_undo_enabled
NAME              DESCRIPTION               DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------- ------------------------- ------------- ------------- ------------ ----- ---------
temp_undo_enabled is temporary undo enabled TRUE          TRUE          FALSE        TRUE  IMMEDIATE

SYS@192.168.100.235:1521/orcl> @ pr
==============================
NAME                          : temp_undo_enabled
DESCRIPTION                   : is temporary undo enabled
DEFAULT_VALUE                 : TRUE
SESSION_VALUE                 : TRUE
SYSTEM_VALUE                  : FALSE
ISSES_MODIFIABLE              : TRUE
ISSYS_MODIFIABLE              : IMMEDIATE
PL/SQL procedure successfully completed.

--//测试过程略.
SYS@192.168.100.235:1521/orcl> @ses 1725 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      1725 redo size                                         0
      1725 redo size for lost write detection                0
      1725 redo size for direct writes                       0

SYS@192.168.100.235:1521/orcl> @ses 1725 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      1725 redo size                                       612
      1725 redo size for lost write detection                0
      1725 redo size for direct writes                       0

SYS@192.168.100.235:1521/orcl> @ses 1725 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
      1725 redo size                                      1148
      1725 redo size for lost write detection                0
      1725 redo size for direct writes                       0
--//一样产生日志.

--//如果在dg备库上测试,不会产生日志:
SYS@192.168.100.237:1521/orcldg> @ ses 406 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
       406 redo size                                         0
       406 redo size for lost write detection                0
       406 redo size for direct writes                       0

SYS@192.168.100.237:1521/orcldg> @ ses 406 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
       406 redo size                                         0
       406 redo size for lost write detection                0
       406 redo size for direct writes                       0

SYS@192.168.100.237:1521/orcldg> @ ses 406 'redo size'
       SID NAME                                          VALUE
---------- ---------------------------------------- ----------
       406 redo size                                         0
       406 redo size for lost write detection                0
       406 redo size for direct writes                       0