[20221130]with+materialize會產生日誌嗎.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2926310/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- linux 不產生日誌了Linux
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- 通過關閉trace再次產生日誌檔案
- sqlldr載入會產生redo嗎？SQL
- [20221130]PLSQL的變數作用範圍(linux).txtSQL變數Linux
- 歡聚——圖靈生日會人物篇圖靈
- 產品經理需要會寫程式碼嗎？
- update操作會產生幾條mlog$日誌？
- [20221130]最佳化備庫dg遇到的問題2.txt
- 生日
- 蘋果公司會生產廉價的iPhone嗎？蘋果iPhone
- 網易數帆雲原生日誌平臺架構實踐架構
- CNCF 會重蹈 OpenStack 的覆轍嗎？｜航海日誌 Vol.25
- 網易X工行：雲原生日誌系統 Loggie 正式開源！
- [20221130]測試訪問檢視v$session幾種情況的效能差異.txtSession
- 生日隨想
- 谷歌眼鏡更名Project Aura：未來會有新產品嗎谷歌Project
- 物化檢視comlete重新整理會產生大量的日誌
- 關於delete還是update會產生更多日誌的問題delete
- IoT裝置網路會削減開支提高生產力嗎？
- Grafana 系列文章（九）：開源雲原生日誌解決方案 Loki 簡介GrafanaLoki
- ACM zb的生日ACM
- [20170412]分析重做日誌.txt
- PostgreSQL違反唯一約束的插入操作會產品HEAP垃圾嗎？SQL
- 冴羽答讀者問：如果有機會，你會選擇脫產學習深造嗎？
- PHP實現日誌寫入log.txtPHP
- 圖靈生日賀詞圖靈
- [Tkey] 生日禮物
- 超越夢想，追求卓越——圖靈五週年生日聚會圓滿成功圖靈
- 乾貨 | 這些產品主圖設計的色彩原理，你會用嗎？
- 新一代雲原生日誌架構 - Loggie的設計與實踐架構
- oracle 11g不同會話產生的事務會使用相同的undo segment嗎--undo系列之一Oracle會話
- Python會消亡嗎？Python
- 你會單例嗎？單例
- ChatGPT 會開源嗎？ChatGPT
- Kubernetes 會成為 AWS 對抗 Google 的武器嗎？｜航海日誌 Vol.23Go
- 執行alter system flush buffer_cache一定會產生檢查點嗎？
- TXT文字Log日誌分割工具(附工具連結)