Force dynamic sampling for Global Temporary Tables
上线前业务测试过程中，需要找出低效以及潜在的抵消语句，发现有一个语句是个潜伏的定时炸弹：
select distinct CATA_ID from PD_CATA_DICT where eff_datesysdate start With CATA_ID in
(select distinct a.CATA_ID from pd_cataitem_dict a, pd_prod_dict_tmp b where a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
(select distinct a.CATA_ID from pd_cataitem_dict a, pd_prod_dict_tmp b where a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
执行计划如下
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1232498413
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1232498413
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
看到没，三个表之间有相互的关联关系，但是由于PD_PROD_DICT_TMP 的cardinality是1，导致cbo采用没有直接关联关系的两个表直接走 CARTESIAN ，查看PD_PROD_DICT_TMP 表，是否统计值不准确，发现这个表没有统计值，而且是个临时表，于是我手工insert 这个临时表1万的数据之后发现执行计划没有改变，insert了100万数据之后依然没有变，可想而知如果这种语句如果上到生产之后带来的问题有多大。
如何来改变这种状况呢，想起了以前看过的hint DYNAMIC_SAMPLING，（数据库版本是10.2.0.4）
于是进行测试
SQL> set autot trace exp
SQL> select /*+ DYNAMIC_SAMPLING */* from pd_prod_dict_tmp;
SQL> select /*+ DYNAMIC_SAMPLING */* from pd_prod_dict_tmp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
没有生效，拿普通表试下看看是否生效
SQL> select /*+ DYNAMIC_SAMPLING */* from pd_prod_dict
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3211492975
----------------------------------------------------------
Plan hash value: 3211492975
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10528 | 2035K| 84 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT | 10528 | 2035K| 84 (2)| 00:00:02 |
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10528 | 2035K| 84 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT | 10528 | 2035K| 84 (2)| 00:00:02 |
----------------------------------------------------------------------------------
再拿没有统计值的表试下看看是否生效
SQL> create table test as select *from dba_tables;
Table created.
SQL> select /*+ DYNAMIC_SAMPLING */* from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13241 | 6297K| 90 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 13241 | 6297K| 90 (2)| 00:00:02 |
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13241 | 6297K| 90 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 13241 | 6297K| 90 (2)| 00:00:02 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
发现生效了，评估值为13241，看看是否准确，
SQL> set autot off
SQL> select count(*) from test;
SQL> select count(*) from test;
COUNT(*)
----------
12429
----------
12429
加大取样的级别，看是否能带来好处
SQL> set autot traceonly
SQL> select /*+ DYNAMIC_SAMPLING(10) */* from test;
SQL> select /*+ DYNAMIC_SAMPLING(10) */* from test;
12429 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12429 | 5911K| 90 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 12429 | 5911K| 90 (2)| 00:00:02 |
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12429 | 5911K| 90 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 12429 | 5911K| 90 (2)| 00:00:02 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
ok，很准确
-----
- dynamic sampling used for this statement
ok，很准确
通过以上看来，
1、有统计值以及临时表如果只通过DYNAMIC_SAMPLING hint无法实现动态采样，
2、采样的级别对评估值的准确性有影响
于是想到和动态采样相关的另一个hint dynamic_sampling_est_cdn
测试看看，是否生效
SQL> set autot trace exp
SQL> select /*+ dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
没有生效，同时使用两个hint看看是否生效
SQL> select /*+ dynamic_sampling dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL> select /*+ dynamic_sampling(b) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL> select /*+ dynamic_sampling(b 1) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
SQL> select /*+ dynamic_sampling(b 0) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL> select /*+ dynamic_sampling(b 9) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
SQL> select /*+ dynamic_sampling(b 1) */* from pd_prod_dict_tmp b
2 /
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
总算试出来了，只有同时使用两个hint，而且需要指定对应的表以及级别才可以启用临时表的动态采样，来测试下临时表的采样是否准确
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
总算试出来了，只有同时使用两个hint，而且需要指定对应的表以及级别才可以启用临时表的动态采样，来测试下临时表的采样是否准确
SQL> insert into pd_prod_dict_tmp select prod_id from pd_prod_dict;
10404 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 2851005187
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10528 | 94752 | 10 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_PD_PROD_DICT | 10528 | 94752 | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10528 | 94752 | 10 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_PD_PROD_DICT | 10528 | 94752 | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> /
10404 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 2851005187
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10528 | 94752 | 10 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_PD_PROD_DICT | 10528 | 94752 | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10528 | 94752 | 10 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_PD_PROD_DICT | 10528 | 94752 | 10 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select count(*) from pd_prod_dict_tmp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3080676641
----------------------------------------------------------
Plan hash value: 3080676641
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
SQL> set autot off
SQL> /
SQL> /
COUNT(*)
----------
20808
----------
20808
SQL> set autot trace exp
SQL> select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
SQL> select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) */* from pd_prod_dict_tmp b
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2556722750
----------------------------------------------------------
Plan hash value: 2556722750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20808 | 243K| 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 20808 | 243K| 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20808 | 243K| 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PD_PROD_DICT_TMP | 20808 | 243K| 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
ok，也很准确
-----
- dynamic sampling used for this statement
ok，也很准确
于是将hint代入语句测试看是否生效
SQL> select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) */distinct CATA_ID
2 from PD_CATA_DICT where eff_datesysdate
3 start With CATA_ID in (select distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
2 from PD_CATA_DICT where eff_date
3 start With CATA_ID in (select distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1232498413
----------------------------------------------------------
Plan hash value: 1232498413
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - filter("EFF_DATE"SYSDATE@!)
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
10 - access("CATA_ID"="A"."CATA_ID" AND "A"."CATA_ITEM_ID"="B"."PROD_ID")
filter("A"."CATA_ITEM_ID"="B"."PROD_ID")
14 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
10 - access("CATA_ID"="A"."CATA_ID" AND "A"."CATA_ITEM_ID"="B"."PROD_ID")
filter("A"."CATA_ITEM_ID"="B"."PROD_ID")
14 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
SQL> select /*+ dynamic_sampling(@v b 10) dynamic_sampling_est_cdn(@v b) */distinct CATA_ID
2 from PD_CATA_DICT where eff_datesysdate
3 start With CATA_ID in (select /*+ qb_name(v) */distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
2 from PD_CATA_DICT where eff_date
3 start With CATA_ID in (select /*+ qb_name(v) */distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1232498413
----------------------------------------------------------
Plan hash value: 1232498413
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
| 5 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 16 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 1 | 12 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 39 | 156 | 1 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_PD_CATA_DICT | 39 | 156 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_PD_CATAITEM_DICT | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | CONNECT BY PUMP | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - filter("EFF_DATE"SYSDATE@!)
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
10 - access("CATA_ID"="A"."CATA_ID" AND "A"."CATA_ITEM_ID"="B"."PROD_ID")
filter("A"."CATA_ITEM_ID"="B"."PROD_ID")
14 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
10 - access("CATA_ID"="A"."CATA_ID" AND "A"."CATA_ITEM_ID"="B"."PROD_ID")
filter("A"."CATA_ITEM_ID"="B"."PROD_ID")
14 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
SQL> select distinct CATA_ID
2 from PD_CATA_DICT where eff_datesysdate
3 start With CATA_ID in (select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) */ distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
2 from PD_CATA_DICT where eff_date
3 start With CATA_ID in (select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) */ distinct a.CATA_ID from
4 pd_cataitem_dict a, pd_prod_dict_tmp b where
5 a.cata_item_id=b.prod_id) connect by CATA_ID=PRIOR PAR_CATA_ID
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2040775482
----------------------------------------------------------
Plan hash value: 2040775482
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
|* 5 | HASH JOIN | | 20828 | 589K| 26 (8)| 00:00:01 |
| 6 | NESTED LOOPS | | 10472 | 173K| 15 (7)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | PK_PD_CATAITEM_DICT | 10472 | 132K| 14 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | 4 | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 20808 | 243K| 10 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | CONNECT BY PUMP | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (50)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 24 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PD_CATA_DICT | | | | |
|* 5 | HASH JOIN | | 20828 | 589K| 26 (8)| 00:00:01 |
| 6 | NESTED LOOPS | | 10472 | 173K| 15 (7)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | PK_PD_CATAITEM_DICT | 10472 | 132K| 14 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | 4 | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | PD_PROD_DICT_TMP | 20808 | 243K| 10 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | CONNECT BY PUMP | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| PD_CATA_DICT | 1 | 24 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_PD_CATA_DICT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - filter("EFF_DATE"SYSDATE@!)
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
5 - access("A"."CATA_ITEM_ID"="B"."PROD_ID")
8 - access("CATA_ID"="A"."CATA_ID")
13 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
3 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
5 - access("A"."CATA_ITEM_ID"="B"."PROD_ID")
8 - access("CATA_ID"="A"."CATA_ID")
13 - access("CATA_ID"=PRIOR "PAR_CATA_ID")
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
限制真多啊，只有在特定的query block内设置hint才能正事生效！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16158219/viewspace-629082/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 10.2和以上版本的pga动态工作区管理
- database records an archived log in the control file
- When RMAN Performs Control File Autobackups
- Planning an Incremental Backup Strategy
- Multiple Copies of RMAN Backups
- 关于flashback log
- maximum control file size？？
- Renaming a Datafile in the Primary Database
- Duplicate Database fails with Rman-5001 [ID 334588.1]
- Oracle backup fails with error RMAN-06056: could not access datafile
