long running JOB due to Dynamic Sampling-优快云博客

本文通过调整动态采样参数解决了SQL执行效率低下问题。详细分析了不同动态采样等级对SQL执行计划的影响，并最终确定最优参数配置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天遇到2个Long running 的JOB，一个跑了10多个小时没跑完，另外一个跑了2个多小时没跑完

数据库版本：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

4节点RAC环境，不过现在只启动了3个

SQL> select inst_id from gv$instance;

INST_ID
----------
1
4
2

ETL的人发邮件说

Hi All,

We are encountering long running job in CIT APAC daily of ODATE 0509 processing. KB7 job is running more than 9.45 hours in KA5 table. The long running job details are as follows,

Below is the long running job details,

1. ControlM Server: BDHP4270

2. ControlM Agent: BDHP4423

3. Table name: ADWGQGKA5@BDHP4423

4. Job name: ADWGQKB7

5. Order id: 0000ni43

6: Run date: 20110509

Please someone look into the issue and help us to resolve it. Thanks in advance.

跑10多个小时的SQL有300多行，这里我挑那个跑2个多小时的SQL来说明问题

SQL语句如下：

create table OPT_ACTVY_MTH_TFADS
parallel 2 TABLESPACE OPTIMA01M
nologging as SELECT ACTVY_SKID,
FUND_SKID,
ACCT_SKID,
BUS_UNIT_SKID,
DATE_SKID,
FY_DATE_SKID,
COST_TYPE_CODE,
ESTMT_VAR_MTH_COST,
ESTMT_FIX_MTH_COST,
ACTL_FIX_MTH_COST,
ACTL_VAR_MTH_COST,
COST_BOOK_AMT,
COST_CMMT_AMT,
COST_PLAN_AMT,
ESTMT_COST_OVRRD_AMT,
LA_TOT_BOOK_AMT,
MANUL_COST_OVRRD_AMT
FROM OPT_ACTVY_MTH_VW;

执行计划如下：

SQL> explain plan for create table OPT_ACTVY_MTH_TFADS
2 parallel 2 TABLESPACE OPTIMA01M
3 nologging as SELECT ACTVY_SKID,
4 FUND_SKID,
5 ACCT_SKID,
6 BUS_UNIT_SKID,
7 DATE_SKID,
8 FY_DATE_SKID,
9 COST_TYPE_CODE,
10 ESTMT_VAR_MTH_COST,
11 ESTMT_FIX_MTH_COST,
12 ACTL_FIX_MTH_COST,
13 ACTL_VAR_MTH_COST,
14 COST_BOOK_AMT,
15 COST_CMMT_AMT,
16 COST_PLAN_AMT,
17 ESTMT_COST_OVRRD_AMT,
18 LA_TOT_BOOK_AMT,
19 MANUL_COST_OVRRD_AMT
20 FROM OPT_ACTVY_MTH_VW;

Explained.

Elapsed: 00:00:01.75
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3581950603

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 220 | 506 (1)| 00:00:02 | | | | | |
|* 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 220 | 506 (1)| 00:00:02 | | | Q1,02 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | OPT_ACTVY_MTH_TFADS | | | | | | | Q1,02 | PCWP | |
| 4 | VIEW | OPT_ACTVY_MTH_VW | 1 | 220 | 506 (1)| 00:00:02 | | | Q1,02 | PCWP | |
| 5 | WINDOW BUFFER | | 1 | 135 | 505 (1)| 00:00:02 | | | Q1,02 | PCWP | |
| 6 | SORT GROUP BY | | 1 | 135 | 505 (1)| 00:00:02 | | | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | | | | | | | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | | | | | | | Q1,01 | P->P | HASH |
|* 9 | FILTER | | | | | | | | Q1,01 | PCWC | |
| 10 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 11 | NESTED LOOPS | | 1 | 135 | 505 (1)| 00:00:02 | | | Q1,01 | PCWP | |
| 12 | MERGE JOIN CARTESIAN | | 1 | 110 | 504 (1)| 00:00:02 | | | Q1,01 | PCWP | |
| 13 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 14 | PX RECEIVE | | | | | | | | Q1,01 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ10000 | | | | | | | Q1,00 | P->P | BROADCAST |
| 16 | NESTED LOOPS | | | | | | | | Q1,00 | PCWP | |
| 17 | NESTED LOOPS | | 1 | 60 | 456 (1)| 00:00:02 | | | Q1,00 | PCWP | |
| 18 | PX BLOCK ITERATOR | | 1 | 34 | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWC | |
|* 19 | TABLE ACCESS FULL | OPT_PRMTN_DIM | 1 | 34 | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWP | |
|* 20 | INDEX RANGE SCAN | OPT_CAL_MASTR_DIM_NX1 | 94 | | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 21 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1144 | 29744 | 29 (4)| 00:00:01 | | | Q1,00 | PCWP | |
| 22 | BUFFER SORT | | 9863 | 481K| 476 (1)| 00:00:02 | | | Q1,01 | PCWP | |
| 23 | PX BLOCK ITERATOR | | 9863 | 481K| 41 (3)| 00:00:01 | 1 | 29 | Q1,01 | PCWC | |
|* 24 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 9863 | 481K| 41 (3)| 00:00:01 | 1 | 29 | Q1,01 | PCWP | |
|* 25 | INDEX RANGE SCAN | OPT_ACTVY_DIM_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_ACTVY_DIM | 1 | 25 | 1 (0)| 00:00:01 | ROWID | ROWID | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))
9 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))
19 - filter("PRMTN"."SHPMT_END_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PRMTN"."SHPMT_START_DATE"<=ADD_MONTHS(SYSDATE@!,36))
20 - access("CAL"."DAY_DATE">="PRMTN"."SHPMT_START_DATE" AND "CAL"."DAY_DATE"<="PRMTN"."SHPMT_END_DATE")
filter("CAL"."DAY_DATE"<=ADD_MONTHS(SYSDATE@!,36) AND "CAL"."DAY_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
24 - filter("ACTVY_FCT"."ACTVY_SKID"<>0)
25 - access("ACTVY"."ACTVY_SKID"="ACTVY_FCT"."ACTVY_SKID")
filter("ACTVY"."ACTVY_SKID"<>0)
26 - filter("ACTVY"."PRMTN_ID"="PRMTN"."PRMTN_ID")

Note
-----
- dynamic sampling used for this statement

50 rows selected.

奶奶的最后居然有动态采样，我来检查一下统计信息是否过期

SQL> select owner, table_name name, object_type, stale_stats, last_analyzed
2 from dba_tab_statistics
3 where table_name in
4 (
5 'OPT_ACTVY_DIM',
6 'OPT_ACTVY_DIM',
7 'OPT_ACTVY_FCT',
8 'OPT_CAL_MASTR_DIM'
9 )
10 and owner='ADWGQ_OPTIMA_AP11'
11 and (stale_stats = 'YES' or last_analyzed is null);

no rows selected

统计信息是没过期的，那为什么还有动态采样呢？

SQL> show parameter dyn

NAME TYPE VALUE
------------------------------------ --------------------------------- ---------
optimizer_dynamic_sampling integer 4

原因在于动态采样设置了4，动态采样level设置为4不是我设置的哈，我这里是OLAP环境，设置动态采样还是有理由的

不过我不明白的是，统计信息没过期，为什么CBO还动态采样？？？以前的知识是只有统计信息过期或者没收集过统计信息才会进行动态采样的。恩这个有待于进一步调查。现在我讲动态采样关闭

SQL> alter session set optimizer_dynamic_sampling=0;

Session altered.

Elapsed: 00:00:00.61
SQL> explain plan for create table OPT_ACTVY_MTH_TFADS
2 parallel 2 TABLESPACE OPTIMA01M
3 nologging as SELECT ACTVY_SKID,
4 FUND_SKID,
5 ACCT_SKID,
6 BUS_UNIT_SKID,
7 DATE_SKID,
8 FY_DATE_SKID,
9 COST_TYPE_CODE,
10 ESTMT_VAR_MTH_COST,
11 ESTMT_FIX_MTH_COST,
12 ACTL_FIX_MTH_COST,
13 ACTL_VAR_MTH_COST,
14 COST_BOOK_AMT,
15 COST_CMMT_AMT,
16 COST_PLAN_AMT,
17 ESTMT_COST_OVRRD_AMT,
18 LA_TOT_BOOK_AMT,
19 MANUL_COST_OVRRD_AMT
20 FROM OPT_ACTVY_MTH_VW;

Explained.

Elapsed: 00:00:00.73
SQL> select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 9834K| 2063M| | 135K (3)| 00:08:46 | | | | | |
|* 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 9834K| 2063M| | 111K (3)| 00:07:13 | | | Q1,04 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | OPT_ACTVY_MTH_TFADS | | | | | | | | Q1,04 | PCWP | |
| 4 | VIEW | OPT_ACTVY_MTH_VW | 9834K| 2063M| | 111K (3)| 00:07:13 | | | Q1,04 | PCWP | |
| 5 | WINDOW BUFFER | | 9834K| 1266M| | 96384 (3)| 00:06:15 | | | Q1,04 | PCWP | |
| 6 | SORT GROUP BY | | 9834K| 1266M| 1396M| 96384 (3)| 00:06:15 | | | Q1,04 | PCWP | |
| 7 | PX RECEIVE | | 9834K| 1266M| | 1609 (18)| 00:00:07 | | | Q1,04 | PCWP | |
| 8 | PX SEND HASH | :TQ10003 | 9834K| 1266M| | 1609 (18)| 00:00:07 | | | Q1,03 | P->P | HASH |
|* 9 | FILTER | | | | | | | | | Q1,03 | PCWC | |
| 10 | MERGE JOIN | | 9834K| 1266M| | 1609 (18)| 00:00:07 | | | Q1,03 | PCWP | |
| 11 | SORT JOIN | | 8600 | 915K| 2416K| 899 (2)| 00:00:04 | | | Q1,03 | PCWP | |
|* 12 | HASH JOIN | | 8600 | 915K| | 897 (1)| 00:00:04 | | | Q1,03 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 9863 | 481K| | 41 (3)| 00:00:01 | 1 | 29 | Q1,03 | PCWC | |
|* 14 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 9863 | 481K| | 41 (3)| 00:00:01 | 1 | 29 | Q1,03 | PCWP | |
| 15 | PX RECEIVE | | 8600 | 495K| | 855 (1)| 00:00:04 | | | Q1,03 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10001 | 8600 | 495K| | 855 (1)| 00:00:04 | | | Q1,01 | P->P | BROADCAST |
|* 17 | HASH JOIN | | 8600 | 495K| | 855 (1)| 00:00:04 | | | Q1,01 | PCWP | |
| 18 | PX BLOCK ITERATOR | | 6981 | 231K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,01 | PCWC | |
|* 19 | TABLE ACCESS FULL | OPT_PRMTN_DIM | 6981 | 231K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,01 | PCWP | |
| 20 | BUFFER SORT | | | | | | | | | Q1,01 | PCWC | |
| 21 | PX RECEIVE | | 8603 | 210K| | 427 (1)| 00:00:02 | | | Q1,01 | PCWP | |
| 22 | PX SEND BROADCAST | :TQ10000 | 8603 | 210K| | 427 (1)| 00:00:02 | | | Q1,00 | P->P | BROADCAST |
| 23 | PX BLOCK ITERATOR | | 8603 | 210K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWC | |
|* 24 | TABLE ACCESS FULL| OPT_ACTVY_DIM | 8603 | 210K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWP | |
|* 25 | FILTER | | | | | | | | | Q1,03 | PCWP | |
|* 26 | SORT JOIN | | 5317 | 135K| | 453 (2)| 00:00:02 | | | Q1,03 | PCWP | |
| 27 | BUFFER SORT | | | | | | | | | Q1,03 | PCWC | |
| 28 | PX RECEIVE | | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,03 | PCWP | |
| 29 | PX SEND BROADCAST | :TQ10002 | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | P->P | BROADCAST |
| 30 | PX BLOCK ITERATOR | | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | PCWC | |
|* 31 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))
9 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))
12 - access("ACTVY"."ACTVY_SKID"="ACTVY_FCT"."ACTVY_SKID")
14 - filter("ACTVY_FCT"."ACTVY_SKID"<>0)
17 - access("ACTVY"."PRMTN_ID"="PRMTN"."PRMTN_ID")
19 - filter("PRMTN"."SHPMT_END_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"PRMTN"."SHPMT_START_DATE"<=ADD_MONTHS(SYSDATE@!,36))
24 - filter("ACTVY"."ACTVY_SKID"<>0)
25 - filter("CAL"."DAY_DATE"<="PRMTN"."SHPMT_END_DATE")
26 - access("CAL"."DAY_DATE">="PRMTN"."SHPMT_START_DATE")
filter("CAL"."DAY_DATE">="PRMTN"."SHPMT_START_DATE")
31 - filter("CAL"."DAY_DATE"<=ADD_MONTHS(SYSDATE@!,36) AND "CAL"."DAY_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

54 rows selected.

关闭动态采样之后，CBO选择了正确的执行计划，并且SQL跑了6秒就完成了。

SQL> create table OPT_ACTVY_MTH_TFADS

2 parallel 2 TABLESPACE OPTIMA01M

3 nologging as SELECT ACTVY_SKID,

4 FUND_SKID,

5 ACCT_SKID,

6 BUS_UNIT_SKID,

7 DATE_SKID,

8 FY_DATE_SKID,

9 COST_TYPE_CODE,

10 ESTMT_VAR_MTH_COST,

11 ESTMT_FIX_MTH_COST,

12 ACTL_FIX_MTH_COST,

13 ACTL_VAR_MTH_COST,

14 COST_BOOK_AMT,

15 COST_CMMT_AMT,

16 COST_PLAN_AMT,

17 ESTMT_COST_OVRRD_AMT,

18 LA_TOT_BOOK_AMT,

19 MANUL_COST_OVRRD_AMT

20 FROM OPT_ACTVY_MTH_VW;

Table created.

Elapsed: 00:00:06.82

当然了，设置采样率为6，也能达到效果

SQL> alter session set optimizer_dynamic_sampling=6;

Session altered.

Elapsed: 00:00:00.60

SQL> explain plan for create table OPT_ACTVY_MTH_TFADS

2 parallel 2 TABLESPACE OPTIMA01M

3 nologging as SELECT ACTVY_SKID,

4 FUND_SKID,

5 ACCT_SKID,

6 BUS_UNIT_SKID,

7 DATE_SKID,

8 FY_DATE_SKID,

9 COST_TYPE_CODE,

10 ESTMT_VAR_MTH_COST,

11 ESTMT_FIX_MTH_COST,

12 ACTL_FIX_MTH_COST,

13 ACTL_VAR_MTH_COST,

14 COST_BOOK_AMT,

15 COST_CMMT_AMT,

16 COST_PLAN_AMT,

17 ESTMT_COST_OVRRD_AMT,

18 LA_TOT_BOOK_AMT,

19 MANUL_COST_OVRRD_AMT

20 FROM OPT_ACTVY_MTH_VW;

Explained.

Elapsed: 00:00:00.79

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3013937184

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

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

| 0 | CREATE TABLE STATEMENT | | 6888K| 1445M| | 95147 (3)| 00:06:10 | | | | | |

|* 1 | PX COORDINATOR | | | | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10004 | 6888K| 1445M| | 78252 (3)| 00:05:05 | | | Q1,04 | P->S | QC (RAND) |

| 4 | VIEW | OPT_ACTVY_MTH_VW | 6888K| 1445M| | 78252 (3)| 00:05:05 | | | Q1,04 | PCWP | |

| 5 | WINDOW BUFFER | | 6888K| 886M| | 67892 (3)| 00:04:24 | | | Q1,04 | PCWP | |

| 6 | SORT GROUP BY | | 6888K| 886M| 978M| 67892 (3)| 00:04:24 | | | Q1,04 | PCWP | |

| 7 | PX RECEIVE | | 6888K| 886M| | 1532 (13)| 00:00:06 | | | Q1,04 | PCWP | |

| 8 | PX SEND HASH | :TQ10003 | 6888K| 886M| | 1532 (13)| 00:00:06 | | | Q1,03 | P->P | HASH |

|* 9 | FILTER | | | | | | | | | Q1,03 | PCWC | |

| 10 | MERGE JOIN | | 6888K| 886M| | 1532 (13)| 00:00:06 | | | Q1,03 | PCWP | |

| 11 | SORT JOIN | | 6024 | 641K| | 898 (1)| 00:00:04 | | | Q1,03 | PCWP | |

|* 12 | HASH JOIN | | 6024 | 641K| | 897 (1)| 00:00:04 | | | Q1,03 | PCWP | |

| 13 | PX BLOCK ITERATOR | | 9863 | 481K| | 41 (3)| 00:00:01 | 1 | 29 | Q1,03 | PCWC | |

|* 14 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 9863 | 481K| | 41 (3)| 00:00:01 | 1 | 29 | Q1,03 | PCWP | |

| 15 | PX RECEIVE | | 6024 | 347K| | 855 (1)| 00:00:04 | | | Q1,03 | PCWP | |

| 16 | PX SEND BROADCAST | :TQ10001 | 6024 | 347K| | 855 (1)| 00:00:04 | | | Q1,01 | P->P | BROADCAST |

|* 17 | HASH JOIN | | 6024 | 347K| | 855 (1)| 00:00:04 | | | Q1,01 | PCWP | |

| 18 | PX BLOCK ITERATOR | | 8603 | 210K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,01 | PCWC | |

|* 19 | TABLE ACCESS FULL | OPT_ACTVY_DIM | 8603 | 210K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,01 | PCWP | |

| 20 | BUFFER SORT | | | | | | | | | Q1,01 | PCWC | |

| 21 | PX RECEIVE | | 3822 | 126K| | 427 (1)| 00:00:02 | | | Q1,01 | PCWP | |

| 22 | PX SEND BROADCAST | :TQ10000 | 3822 | 126K| | 427 (1)| 00:00:02 | | | Q1,00 | P->P | BROADCAST |

| 23 | PX BLOCK ITERATOR | | 3822 | 126K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWC | |

|* 24 | TABLE ACCESS FULL| OPT_PRMTN_DIM | 3822 | 126K| | 427 (1)| 00:00:02 | 1 | 29 | Q1,00 | PCWP | |

|* 25 | FILTER | | | | | | | | | Q1,03 | PCWP | |

|* 26 | SORT JOIN | | 5317 | 135K| | 453 (2)| 00:00:02 | | | Q1,03 | PCWP | |

| 27 | BUFFER SORT | | | | | | | | | Q1,03 | PCWC | |

| 28 | PX RECEIVE | | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,03 | PCWP | |

| 29 | PX SEND BROADCAST | :TQ10002 | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | P->P | BROADCAST |

| 30 | PX BLOCK ITERATOR | | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | PCWC | |

|* 31 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 5317 | 135K| | 452 (2)| 00:00:02 | | | Q1,02 | PCWP | |

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

Predicate Information (identified by operation id):

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

1 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))

9 - filter(TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=ADD_MONTHS(SYSDATE@!,36))

12 - access("ACTVY"."ACTVY_SKID"="ACTVY_FCT"."ACTVY_SKID")

14 - filter("ACTVY_FCT"."ACTVY_SKID"<>0)

17 - access("ACTVY"."PRMTN_ID"="PRMTN"."PRMTN_ID")

19 - filter("ACTVY"."ACTVY_SKID"<>0)

24 - filter("PRMTN"."SHPMT_END_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"PRMTN"."SHPMT_START_DATE"<=ADD_MONTHS(SYSDATE@!,36))

25 - filter("CAL"."DAY_DATE"<="PRMTN"."SHPMT_END_DATE")

26 - access("CAL"."DAY_DATE">="PRMTN"."SHPMT_START_DATE")

filter("CAL"."DAY_DATE">="PRMTN"."SHPMT_START_DATE")

31 - filter("CAL"."DAY_DATE"<=ADD_MONTHS(SYSDATE@!,36) AND "CAL"."DAY_DATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note

-----

- dynamic sampling used for this statement

58 rows selected.

SQL> create table OPT_ACTVY_MTH_TFADS

2 parallel 2 TABLESPACE OPTIMA01M

3 nologging as SELECT ACTVY_SKID,

4 FUND_SKID,

5 ACCT_SKID,

6 BUS_UNIT_SKID,

7 DATE_SKID,

8 FY_DATE_SKID,

9 COST_TYPE_CODE,

10 ESTMT_VAR_MTH_COST,

11 ESTMT_FIX_MTH_COST,

12 ACTL_FIX_MTH_COST,

13 ACTL_VAR_MTH_COST,

14 COST_BOOK_AMT,

15 COST_CMMT_AMT,

16 COST_PLAN_AMT,

17 ESTMT_COST_OVRRD_AMT,

18 LA_TOT_BOOK_AMT,

19 MANUL_COST_OVRRD_AMT

20 FROM OPT_ACTVY_MTH_VW;

Table created.

Elapsed: 00:00:22.86

采样率为6，SQL要跑22秒，比之前的6秒慢了16秒。

恩，下来调查一下，为什么统计信息没过期，设置了动态采样奶奶的CBO乱搞。

---Update------

Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is twice the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

以前一直以为只要收集过统计信息，即使设置的动态采样，也不会采用动态采样，哎没好好读文档(其实以前读过，不过忘记了)。
根据文档的描述，只有动态采样的level<=2，才会在没有收集统计信息的时候去动态采样，如果动态采样级别高于2
CBO会根据情况去做动态采样，即使统计信息没过期。