eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Dataguard配置Step by Step | Blog首页 | 如何获得创建控制文件的脚本并重建控制文件 »

关于PUSH_SUBQ提示的说明

PUSH_SUBQ 可以用来控制子查询的执行 这个是PUSH_SUBQ 的本意

我那个例子的意思是说:
PUSH_SUBQ 本质上是个CBO的hints(当然RBO也提不上hints)
由于PUSH_SUBQ 的引入就是为了来解决unnesting的某些不足
所以在不同的版本上,这个hints发挥的作用也有所不同了.

8i上这个提示的作用更接近本原:


$ sqlplus "/ as sysdba"

SQL*Plus: Release 8.1.5.0.0 - Production on Sun Sep 12 20:51:21 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> connect scott/tiger
Connected.
SQL> create table dept1 as select * from dept;

Table created.

SQL> set linesize 120
SQL> select a.*      
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER ---------------'最初push_subq的使命是为了消除/提高这个filter的效率的'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   5    4         INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
   6    1     TABLE ACCESS (FULL) OF 'DEPT1'




Statistics
----------------------------------------------------------
          0  recursive calls
         52  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       2715  bytes sent via SQL*Net to client
        751  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed


用于push_subq是个CBO hints,这里我们可以看到COST的出现:

 

 

SQL> select /*+ push_subq */ a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=122)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=122)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=2 Bytes=44)
   3    2       TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)--------'这里消除了之前的filter'
   4    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=21 Bytes=2100)




Statistics
----------------------------------------------------------
        204  recursive calls
         85  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
       2706  bytes sent via SQL*Net to client
        768  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         14  rows processed    

再看CBO下:

正常情况下,没什么好说的:

 

 


SQL> set autotrace traceonly
SQL> exec dbms_stats.gather_schema_stats('SCOTT')

PL/SQL procedure successfully completed.

SQL> select a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=2 Card=5 Bytes=275)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
   5    1     TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)




Statistics
----------------------------------------------------------
         39  recursive calls
         68  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       2708  bytes sent via SQL*Net to client
        751  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed       
       

在CBO下push_subq发挥了同样的作用:

 

SQL> select /*+ push_subq */ a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
   1    0   NESTED LOOPS (Cost=2 Card=5 Bytes=275)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
   3    2       TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)---'注意这里'
   4    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)




Statistics
----------------------------------------------------------
          0  recursive calls
         84  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       2709  bytes sent via SQL*Net to client
        768  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed       

而在Oracle9i之中:

 

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Sun Sep 12 21:42:57 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> connect scott/tiger
Connected.
SQL> set linesize 120
SQL> set autotrace traceonly
SQL> select a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   5    4         INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
   6    1     TABLE ACCESS (FULL) OF 'DEPT1'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
       1164  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> select /*+ push_subq */ a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9676)
   1    0   HASH JOIN (SEMI) (Cost=8 Card=82 Bytes=9676)
   2    1     HASH JOIN (Cost=5 Card=82 Bytes=8938)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   4    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
   5    1     TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=82 Bytes=738)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1195  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> exec dbms_stats.gather_schema_stats('scott')

PL/SQL procedure successfully completed.

SQL> select a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
   1    0   HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
   2    1     HASH JOIN (Cost=5 Card=11 Bytes=572)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
   5    1     TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)




Statistics
----------------------------------------------------------
        172  recursive calls
          0  db block gets
         60  consistent gets
          0  physical reads
          0  redo size
       1196  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> select /*+ push_subq */ a.*
  2  from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
   1    0   HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
   2    1     HASH JOIN (Cost=5 Card=11 Bytes=572)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
   5    1     TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1196  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>        

 

可以肯定的是push_subq从8i到9i的作用发生了变化
这个变化可能来自于CBO的更加优化

历史上的今天...
    >> 2010-10-15文章:
    >> 2008-10-15文章:
    >> 2006-10-15文章:

无觅

By eygle on 2004-10-15 21:07 | Comments (0) | SQL.PLSQL | 67 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com