AW: dbms_stats block_sample 11.2 ->12.1
Date: Tue, 14 Mar 2017 05:28:43 +0000
Message-ID: <AM3PR05MB4673A70974A2553D155092AF3240_at_AM3PR05MB467.eurprd05.prod.outlook.com>
Hallo Jonathan,
thank you for your suggestions. I have included deleting the stats.So the sequence of commands was
alter system flush buffer_cache;
exec dbms_stats.delete_table_stats('SYSTEM','PNO');
exec dbms_stats.gather_table_stats('SYSTEM','PNO',estimate_percent=>1, block_sample=>false);
alter system flush buffer_cache;
exec dbms_stats.delete_table_stats('SYSTEM','PNO');
exec dbms_stats.gather_table_stats('SYSTEM','PNO',estimate_percent=>1, block_sample=>true);
Table ware created under SYSTEM, but as CTAS from some user table in user tablespace, not SYSTEM tablespace. One table was created in 11g and with Datapump exported in 12c, the other in opposite direction. No chained rows in both tables. Column datatypes Number, Date, Varchar2 ( maximum was 2000). No temp table was used during calculation.
11g used 'direct path read' with block_sample=>false
WAIT #140573335891040: nam='direct path read' ela= 464 file number=69 first dba=2473283 block cnt=13 obj#=307912 tim=1489464300759589 WAIT #140573335891040: nam='direct path read' ela= 356 file number=69 first dba=2473313 block cnt=15 obj#=307912 tim=1489464300760142 WAIT #140573335891040: nam='direct path read' ela= 425 file number=68 first dba=2462977 block cnt=15 obj#=307912 tim=1489464300760778 WAIT #140573335891040: nam='direct path read' ela= 265 file number=68 first dba=2463009 block cnt=15 obj#=307912 tim=1489464300761219
and 'db file sequential read' with block_sample=>true
WAIT #140573341899224: nam='db file sequential read' ela= 177 file#=69 block#=2473332 blocks=1 obj#=307912 tim=1489464302897440 WAIT #140573341899224: nam='db file sequential read' ela= 220 file#=68 block#=2463033 blocks=1 obj#=307912 tim=1489464302897895 WAIT #140573341899224: nam='db file sequential read' ela= 158 file#=67 block#=2470179 blocks=1 obj#=307912 tim=1489464302898284 WAIT #140573341899224: nam='db file sequential read' ela= 139 file#=67 block#=2470231 blocks=1 obj#=307912 tim=1489464302898645
12c used also 'direct path read' with block_sample=>false
WAIT #139902614792496: nam='direct path read' ela= 340 file number=26 first dba=86403 block cnt=13 obj#=368576 tim=9127487087323 WAIT #139902614792496: nam='direct path read' ela= 324 file number=26 first dba=86433 block cnt=15 obj#=368576 tim=9127487088050 WAIT #139902614792496: nam='direct path read' ela= 319 file number=26 first dba=86465 block cnt=15 obj#=368576 tim=9127487088512 WAIT #139902614792496: nam='direct path read' ela= 340 file number=26 first dba=86497 block cnt=15 obj#=368576 tim=9127487089024
but with block_sample=>true used mix of 'direct path read' and 'db file sequential read'. The number of direct path reads was not reduced , only sequential reads 'added' (?)
WAIT #139902615007560: nam='direct path read' ela= 338 file number=26 first dba=86403 block cnt=13 obj#=368576 tim=9127489554602 WAIT #139902615007560: nam='db file sequential read' ela= 167 file#=26 block#=86445 blocks=1 obj#=368576 tim=9127489554792 WAIT #139902615007560: nam='direct path read' ela= 627 file number=26 first dba=86433 block cnt=15 obj#=368576 tim=9127489555911 WAIT #139902615007560: nam='db file sequential read' ela= 326 file#=26 block#=86674 blocks=1 obj#=368576 tim=9127489556284 WAIT #139902615007560: nam='direct path read' ela= 439 file number=26 first dba=86465 block cnt=15 obj#=368576 tim=9127489556943 WAIT #139902615007560: nam='db file sequential read' ela= 187 file#=26 block#=86702 blocks=1 obj#=368576 tim=9127489557144 WAIT #139902615007560: nam='direct path read' ela= 340 file number=26 first dba=86497 block cnt=15 obj#=368576 tim=9127489557749 WAIT #139902615007560: nam='db file sequential read' ela= 179 file#=26 block#=86824 blocks=1 obj#=368576 tim=9127489557945 WAIT #139902615007560: nam='direct path read' ela= 1322 file number=26 first dba=86530 block cnt=62 obj#=368576 tim=9127489559706 WAIT #139902615007560: nam='db file sequential read' ela= 186 file#=26 block#=86855 blocks=1 obj#=368576 tim=9127489559917
Aggregates for 2 runs
11g block_sample=>false
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 2 0.00 0.00 enq: KO - fast object checkpoint 4 0.00 0.00 direct path read 850 0.02 1.11
11g block_sample=>true
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 2 0.00 0.00 enq: KO - fast object checkpoint 4 0.00 0.00 direct path read 1 0.00 0.00 db file sequential read 1610 0.00 0.51 row cache lock 5 0.00 0.00
12c block_sample=>false
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enq: KO - fast object checkpoint 6 0.00 0.00 reliable message 2 0.00 0.00 direct path read 1258 0.01 1.54
12c block_sample=>true
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enq: KO - fast object checkpoint 6 0.00 0.00 reliable message 2 0.00 0.00 direct path read 1470 0.01 1.80 db file sequential read 1028 0.00 0.21
Best Regards,
Petr
Von: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> im Auftrag von Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Gesendet: Donnerstag, 9. März 2017 10:08 An: oracle-l_at_freelists.org
Betreff: Re: dbms_stats block_sample 11.2 ->12.1
Enable tracing (level 12) and repeat, then check the statements used to gather stats. There might have been small variations in the SQL (e.g. the sample() clause might have used different group size for blocks). There might have been significant variations in the SQL - did one of them gather some histograms or create temporary tables Did you delete stats or recreate the tables between the false and true tests ? The stats gathered for the first test might have affected the choice made by the optimizer in the second test.
Finally, someone might confirm this, I think there used to be a bug with block sampling that caused something silly to happen - but I don't remember what, which version, and whether it's been fixed.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Petr Novak <Petr.Novak_at_trivadis.com> Sent: 09 March 2017 08:57:14
Cc: ORACLE-L
Subject: dbms_stats block_sample 11.2 ->12.1
Hallo,
I made test with 2 tables about 700M in 11.2.0.4 and 12.1.0.2
dbms_stats.gather_table_stats(,,estimate_percent=>1, block_sample=>false) used on both DBs 'direct path read' dbms_stats.gather_table_stats(,,estimate_percent=>1, block_sample=>true) used on 11.2 'db file sequential read' , on 12.1 mix of 'direct path read' and 'db file sequential read'.
It is just pure coincidence or known change of implemention ?
Best Regards,
Petr
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 14 2017 - 06:28:43 CET