昨天看别人的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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1073468/
扫一扫
1566
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
