[20140121]with+提示Materialize.txt
昨天看別人的awr報表,發現有一條不良的sql語句使用with+Materialize,開始我覺得得使用提示Materialize不好.
我記得第一次知道這個with命令實際上看jonathanlewis這本書,發現他很喜歡使用Materialize提示.
我一直不知道為什麼,我開始以為情況這樣,如果建立測試例子:
1.問題提出:
SCOTT@test> @ver
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> select b.name,a.* from v$mystat a,v$statname b where a.statistic#=b.statistic# and lower(b.name) like 'session%memory%';
NAME SID STATISTIC# VALUE
-------------------------------------------------- ---------- ---------- ----------
session uga memory 310 29 1297384
session uga memory max 310 30 1297384
session pga memory 310 35 2346728
session pga memory max 310 36 2346728
SCOTT@test> create table tx as select rownum id from dual connect by level<=3e6;
create table tx as select rownum id from dual connect by level<=3e6
*
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
SCOTT@test> select b.name,a.* from v$mystat a,v$statname b where a.statistic#=b.statistic# and lower(b.name) like 'session%memory%';
NAME SID STATISTIC# VALUE
-------------------------------------------------- ---------- ---------- ----------
session uga memory 310 29 1527488
session uga memory max 310 30 116685224
session pga memory 310 35 2255736
session pga memory max 310 36 118262856
--可以發現session uga memory,session pga memory 曾經達到接近116M.
--但是如果使用with+Materialize,建立幾個笛卡爾集基本沒有問題.
如果你執行如下:
SCOTT@test> with a as (select * from dept ) select * from a;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fzvcr3dk3d5mt, child number 0
-------------------------------------
with a as (select * from dept ) select * from a
Plan hash value: 3383998547
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
| 1 | TABLE ACCESS FULL| DEPT | 6 | 4 (0)|
--------------------------------------------------------
--可以發現僅僅是全表掃描dept,如果加入提示Materialize:
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6k0h3810rynfs, child number 0
-------------------------------------
with a as (select /*+ Materialize */ * from dept ) select * from a
Plan hash value: 1561387207
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | 269K| 269K| 269K (0)|
| 3 | TABLE ACCESS FULL | DEPT | 6 | 4 (0)| | | |
| 4 | VIEW | | 6 | 2 (0)| | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_C219A083 | 6 | 2 (0)| | | |
-------------------------------------------------------------------------------------------------------------------
--必須先建立一張臨時表SYS_TEMP_0FD9D6607_C219A083.我以為如果這個臨時表很大,效率一定不高.
2.建立跟蹤看看.
SCOTT@test> @10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@test> with a as (select /*+ Materialize */ * from dept ) select * from a;
..
SCOTT@test> @10046off
Session altered.
看跟蹤內容:
=====================
PARSING IN CURSOR #182926956744 len=201 dep=1 uid=0 oct=1 lid=0 tim=1390269209255550 hv=2138698115 ad='be8060e8' sqlid='c3f1vy9zrmwc3'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6608_C219A083" ("C0" NUMBER(2),"C1" VARCHAR2(14),"C2" VARCHAR2(13) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950920 ) NOPARALLEL
END OF STMT
PARSE #182926956744:c=0,e=771,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1390269209255548
BINDS #182926964064:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=2a974bacc0 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
kxsbbbfp=2a974bacd8 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D6608_C219A083"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=2a974bacf8 bln=22 avl=02 flg=01
value=1
EXEC #182926964064:c=0,e=216,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1390269209256002
FETCH #182926964064:c=0,e=52,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1390269209256078
CLOSE #182926964064:c=0,e=3,dep=2,type=3,tim=1390269209256125
=====================
--仔細看建立臨時表的命令非常特別,有一個 IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT 引數.
-- 4254950920 = 0xfd9d6608 ,正好與建立的臨時表SYS_TEMP_0FD9D6608_C219A083的0FD9D6608對上.另外可以注意一個細節,與前面的
--不同SYS_TEMP_0FD9D6607_C219A083,可能與我重新整理共享池有關.
=====================
PARSING IN CURSOR #182926965632 len=66 dep=0 uid=84 oct=3 lid=84 tim=1390269209266849 hv=1098863064 ad='b2ac4fd8' sqlid='6k0h3810rynfs'
with a as (select /*+ Materialize */ * from dept ) select * from a
END OF STMT
PARSE #182926965632:c=21997,e=42782,p=0,cr=33,cu=0,mis=1,r=0,dep=0,og=1,plh=1561387207,tim=1390269209266847
WAIT #182926965632: nam='Disk file operations I/O' ela= 363 FileOperation=2 fileno=3 filetype=2 obj#=-1 tim=1390269209267544
WAIT #182926965632: nam='Disk file operations I/O' ela= 269 FileOperation=2 fileno=2001 filetype=2 obj#=-1 tim=1390269209268284
WAIT #182926965632: nam='Disk file operations I/O' ela= 260 FileOperation=2 fileno=2001 filetype=2 obj#=-1 tim=1390269209268979
WAIT #182926965632: nam='direct path write temp' ela= 47 file number=2001 first dba=38913 block cnt=1 obj#=-1 tim=1390269209269066
WAIT #182926965632: nam='direct path sync' ela= 32453 File number=2001 Flags=0 p3=0 obj#=-1 tim=1390269209301584
EXEC #182926965632:c=2000,e=34925,p=0,cr=8,cu=8,mis=0,r=0,dep=0,og=1,plh=1561387207,tim=1390269209301869
=====================
SCOTT@test> show parameter db_files ;
NAME TYPE VALUE
--------- -------- ------
db_files integer 2000
--可以發現要往臨時檔案寫資訊.
EXEC #182926819144:c=1999,e=2507,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=0,tim=1390269209305214
WAIT #182926965632: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1390269209305394
WAIT #182926965632: nam='db file sequential read' ela= 45 file#=2001 block#=38913 blocks=1 obj#=-40016376 tim=1390269209305583
FETCH #182926965632:c=0,e=226,p=1,cr=4,cu=1,mis=0,r=1,dep=0,og=1,plh=1561387207,tim=1390269209305668
WAIT #182926965632: nam='log file sync' ela= 8860 buffer#=6541 sync scn=3256463333 p3=0 obj#=-40016376 tim=1390269209314564
WAIT #182926965632: nam='SQL*Net message from client' ela= 358 driver id=1650815232 #bytes=1 p3=0 obj#=-40016376 tim=1390269209315020
WAIT #182926965632: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-40016376 tim=1390269209315074
FETCH #182926965632:c=0,e=49,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=1561387207,tim=1390269209315108
STAT #182926965632 id=1 cnt=4 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION (cr=13 pr=1 pw=1 time=35100 us)'
STAT #182926965632 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT (cr=8 pr=0 pw=1 time=34701 us)'
STAT #182926965632 id=3 cnt=4 pid=2 pos=1 obj=73199 op='TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=91 us cost=4 size=108 card=6)'
STAT #182926965632 id=4 cnt=4 pid=1 pos=2 obj=0 op='VIEW (cr=5 pr=1 pw=0 time=217 us cost=2 size=180 card=6)'
STAT #182926965632 id=5 cnt=4 pid=4 pos=1 obj=4254950920 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6608_C219A083 (cr=5 pr=1 pw=0 time=208 us cost=2 size=108 card=6)'
--可以發現如果with裡面的資訊很大,往臨時表寫入的資訊越多,如果許多會話執行這樣的操作direct path write temp等待時間會很多.
--即使寫成這樣.
SCOTT@test> with a as (select /*+ Materialize */ * from dept ) select * from a where a.deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4tz5ywcn07kbz, child number 0
-------------------------------------
with a as (select /*+ Materialize */ * from dept ) select * from a
where a.deptno=10
Plan hash value: 1561387207
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | 269K| 269K| 269K (0)|
| 3 | TABLE ACCESS FULL | DEPT | 6 | 4 (0)| | | |
|* 4 | VIEW | | 6 | 2 (0)| | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_C219A083 | 6 | 2 (0)| | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."DEPTNO"=10)
--也不會使用dept的主鍵索引.而沒有Materialize提示,可以很好的使用索引.
SCOTT@test> with a as (select * from dept ) select * from a where a.deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9wk03u9hh1nwd, child number 0
-------------------------------------
with a as (select * from dept ) select * from a where a.deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 2 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=10)
Note
3.再來看看臨時表物件：
SCOTT@test> desc sys.SYS_TEMP_0FD9D6608_C219A083
Name Null? Type
----- -------- -------------
C0 NUMBER(2)
C1 VARCHAR2(14)
C2 VARCHAR2(13)
--與dept欄位對應。
SCOTT@test> select * from sys.SYS_TEMP_0FD9D6608_C219A083;
no rows selected
--其他會話也可以看到。如果執行 alter system flush shared_pool;
SCOTT@test> alter system flush shared_pool;
System altered.
SCOTT@test> select * from sys.SYS_TEMP_0FD9D6608_C219A083;
select * from sys.SYS_TEMP_0FD9D660C_C219A083
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@test> with a as (select /*+ Materialize */ * from dept ) select * from a ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7hwqnbncaycq0, child number 0
-------------------------------------
with a as (select /*+ Materialize */ * from dept ) select * from a
Plan hash value: 1561387207
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | | | | 269K| 269K| 269K (0)|
| 3 | TABLE ACCESS FULL | DEPT | 6 | 4 (0)| | | |
| 4 | VIEW | | 6 | 2 (0)| | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_C219A083 | 6 | 2 (0)| | | |
-------------------------------------------------------------------------------------------------------------------
--可以發現臨時表名字發生了變化SYS_TEMP_0FD9D660D_C219A083。
4.最後建議取消Materialize提示，在合適的地方建立索引，最佳化完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1073468/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Flutter Toast、彈出提示、輕提示FlutterAST
- pycharm 每日提示PyCharm
- 【sql hint 提示】SQL
- 開源提示
- 提示詞工程
- myeclipse中提示Hot Code Replace Failed提示窗錯誤EclipseAI
- tooltip提示框
- CSS 專家提示CSS
- MySQL SQL hint 提示MySql
- 【SQL 提示 之三】SQL
- sqlplus提示SQL
- 優化器提示優化
- Oracle使用提示(Hints)Oracle
- Jbuilder 每日提示 (轉)UI
- Prompt提示詞概念
- vue提示訊息Vue
- 根據提示操作
- Minstrel自動生成結構化提示，讓AI為AI寫提示詞的多代理提示生成框架AI框架
- Element 利用Tooltip提示框實現動態顯示文字提示
- Metasploit新增技巧提示功能
- Promise 的 9 個提示Promise
- pl devloper的智慧提示dev
- 自定義音量提示 viewView
- SVProgressHUD提示框IOSiOS
- 命令提示符
- 伺服器的提示！伺服器
- 釋出防扒提示，
- AndroidToast提示封裝AndroidAST封裝
- hints提示總結 zt
- 五個ChatGPT提示詞ChatGPT
- ALaunch 0.8 := 懸浮提示
- openai官網提示詞OpenAI
- nvm安裝node提示
- ChatGPT提示詞（Prompt）框架ChatGPT框架
- 含動畫的java氣泡提示效果，右下角訊息提示窗動畫Java
- 登入提示框與密碼提示框，生成驗證碼密碼
- win10系統下提示Outlook提示“收件人太多”如何解決Win10
- Win10系統提示Windows安全登入提示框如何解決Win10Windows