ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Hint PRECOMPUTE_SUBQUERY 知多少

Hint PRECOMPUTE_SUBQUERY 知多少

原创 Linux操作系统 作者:flying_warrior 时间:2011-04-26 10:54:28 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 

SQL> set autotrace traceonly;

SQL>

SQL>  select a

  2   from   t1

  3   where  a in (select b from t2);

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

 

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     9 |   234 |     5  (20)|

|   1 |  HASH JOIN SEMI    |      |     9 |   234 |     5  (20)|

|   2 |   TABLE ACCESS FULL| T1   |     9 |   117 |     2   (0)|

|   3 |   TABLE ACCESS FULL| T2   |     9 |   117 |     2   (0)|

----------------------------------------------------------------

 

Note

-----

   - 'PLAN_TABLE' is old version

 

 

Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

         14  consistent gets

          0  physical reads

          0  redo size

        318  bytes sent via SQL*Net to client

        481  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

SQL> drop table PLAN_TABLE;

 

Table dropped.

 

SQL> @?/rdbms/admin/utlxplan

 

Table created.

 

SQL>   select a

  2    from   t1

  3    where  a in (select b from t2);

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1713220790

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     9 |   234 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN SEMI    |      |     9 |   234 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   |     9 |   117 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |     9 |   117 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("A"="B")

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

         14  consistent gets

          0  physical reads

          0  redo size

        318  bytes sent via SQL*Net to client

        481  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

SQL>

SQL>  select a

  2   from   t1

  3   where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16

              OR "A"=17 OR "A"=18 OR "A"=19)table2里的数据 直接返回给table A做过滤。我估计等于在SQL里写OR

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

         15  recursive calls

          0  db block gets

         10  consistent gets

          0  physical reads

          0  redo size

        318  bytes sent via SQL*Net to client

        481  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

Alter session set sql_trace=true;

PARSING IN CURSOR #1 len=97 dep=1 ……

 

SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

 

PARSING IN CURSOR #2 len=75 dep=0 ……
select a
from   t1
where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

 

根据depth 0 1 可以看出 这是一个递归SQL 先执行1 再执行0. 实际上就是分成了2条不同的SQL

 

 

如果是这样的话 那么如果我在T2中有很多记录呢?

delete t2;
insert into t2 select rownum from dual connect by level <= 1000;
 
SQL> select a from   t1 where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);
 
9 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   117 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     9 |   117 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"=1 OR "A"=2 OR "A"=3 OR "A"=4 OR "A"=5 OR "A"=6 OR
              "A"=7 OR "A"=8 OR "A"=9 OR "A"=10 OR "A"=11 OR "A"=12 OR "A"=13 OR
 
 
              "A"=14 OR "A"=15 OR "A"=16 OR "A"=17 OR "A"=18 OR "A"=19 OR "A"=20
 OR
 
              "A"=21 OR "A"=22 OR "A"=23 OR "A"=24 OR "A"=25 OR "A"=26 OR "A"=27
 OR
 
              "A"=28 OR "A"=29 OR "A"=30 OR "A"=31 OR "A"=32 OR "A"=33 OR "A"=34
 OR
 
              "A"=35 OR "A"=36 OR "A"=37 OR "A"=38 OR "A"=39 OR "A"=40 OR "A"=41
 OR
 
              "A"=42 OR "A"=43 OR "A"=44 OR "A"=45 OR "A"=46 OR "A"=47 OR "A"=48
 OR……

就拼接到1000……

 

SQL> select sharable_mem,last_load_time from v$sql WHERE sql_text LIKE '%PRECOMPUTE_SUBQUERY%'

  2  ;

 

SHARABLE_MEM LAST_LOAD_TIME

------------ ---------------------------------------------------------

       19151 2011-04-26/10:55:41

       14042 2011-04-26/10:52:50

       32151 2011-04-26/10:55:41

     2112412 2011-04-26/11:00:38

     2253940 2011-04-26/11:00:36

 

可以看到在后面测试的情况下 同一条SQL 对于共享内存的消耗极大。

 

后面直接补充一条  如果T2 里面超过10000万。这个hint 会失效,但是共享内存首先是尝试load一次 失败后 重走标准执行计划,可是内存是被占用了的。实验就不补了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21818314/viewspace-693725/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-21

  • 博文量
    49
  • 访问量
    78959