Re: db file scattered read

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 07 Aug 2004 00:35:50 -0600
Message-Id: <6.1.0.6.2.20040806235010.02dea628@pop.centrexcc.com>

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