I did a few tests and it appears that setting optimizer_dynamic_sampling to
4 alone doesn't cause the CBO to sample the tables at parse time:
test 1 - optimizer_dynamic_samplig=4, all tables in the query analyzed:
OPTIMIZER_DYNAMIC_SAMPLING = 4
_OPTIMIZER_DYN_SMP_BLKS = 32
...
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.639
** Performing dynamic sampling initial checks. **
- Dynamic sampling initial checks returning FALSE.
...
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.641
- Performing dynamic sampling initial checks. **
- Dynamic sampling initial checks returning FALSE.
..
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.647
- Performing dynamic sampling initial checks. **
Column: PAY_CONFIR Col#: 18 Table: PS_PAY_CALENDAR Alias: SYS_ALIAS_3
NDV: 2 NULLS: 0 DENS: 5.0000e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: RUN_ID Col#: 4 Table: PS_PAY_CALENDAR Alias: SYS_ALIAS_3
NDV: 493 NULLS: 0 DENS: 2.0284e-03
NO HISTOGRAM: #BKT: 1 #VAL: 2
- Dynamic sampling initial checks returning TRUE (level = 4).
*** 2004-08-07 00:02:47.647
- Generated dynamic sampling query:
query text :
SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("SYS_ALIAS_3") */ 1 AS C1,
CASE WHEN "SYS_ALIAS_3"."RUN_ID"='PD2' AND
"SYS_ALIAS_3"."PAY_CONFIRM_RUN"='N' THEN 1 ELSE 0 END AS C2 FROM
"PS_PAY_CALENDAR" SAMPLE BLOCK (5.201342) "SYS_ALIAS_3") SAMPLESUB
PARSING IN CURSOR #3 len=323 dep=1 uid=23 oct=3 lid=23 tim=1066271648094494
hv=3448857413 ad='586a352c'
SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("SYS_ALIAS_3") */ 1 AS C1,
CASE WHEN "SYS_ALIAS_3"."RUN_ID"='PD2' AND
"SYS_ALIAS_3"."PAY_CONFIRM_RUN"='N' THEN 1 ELSE 0 END AS C2 FROM
"PS_PAY_CALENDAR" SAMPLE BLOCK (5.201342) "SYS_ALIAS_3") SAMPLESUB
END OF STMT
PARSE #3:c=10000,e=1077,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1066271648094480
EXEC #3:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1066271648094677
FETCH #3:c=0,e=3684,p=47,cr=20,cu=0,mis=0,r=1,dep=1,og=1,tim=1066271648098390
*** 2004-08-07 00:02:47.652
- Executed dynamic sampling query:
level : 4
sample pct. : 5.201342
actual sample size : 1082
filtered sample card. : 0
orig. card. : 18486
block cnt. : 596
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.0010
- Not using dynamic sampling.
...
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.654
- Performing dynamic sampling initial checks. **
- Dynamic sampling initial checks returning FALSE.
...
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.654
- Performing dynamic sampling initial checks. **
Column: RETROPAY_L Col#: 19 Table: WB_RETROPAY_EARNS Alias: C
NDV: 2 NULLS: 0 DENS: 5.0000e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: RETROPAY_P Col#: 17 Table: WB_RETROPAY_EARNS Alias: C
NDV: 3 NULLS: 0 DENS: 3.3333e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
- Dynamic sampling initial checks returning TRUE (level = 4).
*** 2004-08-07 00:02:47.654
- Generated dynamic sampling query:
query text :
SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("C") */ 1 AS C1, CASE WHEN
"C"."RETROPAY_PRCS_FLAG"='C' AND "C"."RETROPAY_LOAD_SW"='Y' THEN 1 ELSE 0
END AS C2 FROM "WB_RETROPAY_EARNS" SAMPLE BLOCK (0.460077) "C") SAMPLESUB
PARSING IN CURSOR #3 len=296 dep=1 uid=23 oct=3 lid=23 tim=1066271648101368
hv=1633843630 ad='5869fff0'
SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("C") */ 1 AS C1, CASE WHEN
"C"."RETROPAY_PRCS_FLAG"='C' AND "C"."RETROPAY_LOAD_SW"='Y' THEN 1 ELSE 0
END AS C2 FROM "WB_RETROPAY_EARNS" SAMPLE BLOCK (0.460077) "C") SAMPLESUB
END OF STMT
PARSE #3:c=0,e=1069,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1066271648101355
EXEC #3:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1066271648101549
FETCH
#3:c=10000,e=3132,p=29,cr=38,cu=0,mis=0,r=1,dep=1,og=1,tim=1066271648104710
*** 2004-08-07 00:02:47.659
- Executed dynamic sampling query:
level : 4
sample pct. : 0.460077
actual sample size : 888
filtered sample card. : 649
orig. card. : 164733
block cnt. : 6738
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.1667
Aah - finally the CBO DID sample AND use the result
...
*** 2004-08-07 00:02:47.660
** Performing dynamic sampling initial checks. **
- Dynamic sampling initial checks returning FALSE.
TABLE: PS_RETROPAY_RQST ORIG CDN: 13679 ROUNDED CDN: 13679 CMPTD
CDN: 13679
Access path: tsc Resc: 32 Resp: 32
BEST_CST: 32.00 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
*** 2004-08-07 00:02:47.661
- Performing dynamic sampling initial checks. **
- Dynamic sampling initial checks returning FALSE.
OK - 1 out of 5 tables in the query got successfully sampled.
test 2 - deleting the statistics from one of the tables:
Now the CBO samples - and uses - statistics for 3 out of the 5 tables,
including the one without statistics.
At 08:23 PM 8/6/2004, you wrote:
>In theory, setting dynamic sampling to 4 should cause Oracle 9 to sample
>statistics at parse time. Do you have any experience that it actually
>works? I have tried on occasion to set dynamic sampling at the session
>level, but unless the tables in the sql do not have statistics (in which
>case you run the risk that the CBO is bypassed for the RBO if you have
>optimizer_mode=choose => note to self to test if dynamic_sampling > 1
>changes that "rule"), I have the strong impression that the CBO in many
>cases does NOT sample statistics but simply uses the existing statistics.
>Unless you jack up the level to much higher than 4, and even then not
>always. I have to do more tests to be more certain.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Aug 07 2004 - 01:31:41 CDT