ITPub博客

首页 > 数据库 > Oracle > [20140121]with+提示Materialize.txt

[20140121]with+提示Materialize.txt

原创 Oracle 作者:lfree 时间:2014-01-21 11:53:05 0 删除 编辑

 

昨天看别人的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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2630
  • 访问量
    6393920