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

来源：这里教程网时间：2026-03-03 18:12:30 作者：

[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