Re: dbms_stats.set_table_stats
Date: Wed, 29 Jul 2009 08:13:28 +0900
Message-ID: <43c2e3d60907281613p1975421bne1f043cfd4a91b06_at_mail.gmail.com>
- Try dbms_stats.set_table_stats(..., no_invalidate=>false); http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#i997763
- If my assumption serves me right, your 10046 trace would not show library
cache miss. That means that the second query reuses the existing plan.
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english) http://ukja.tistory.com (korean) http://dioncho.blogspot.com (japanese) http://ask.ex-em.com (q&a) ================================
2009/7/28 Karl Arao <karlarao_at_gmail.com>
> Hi Guys,
>
> I was playing with dbms_stats.set_table_stats earlier. The reason is,
> I'd like to see plan changes let's say FTS to index scan (& vice
> versa) by explicitly setting the numrows and numblocks for the object.
> Then I'd like to see if there will be changes on the underlying "stat"
> value of 10046 trace, if it would also change on plan or any change on
> the number of blocks scanned or rows filtered.
>
> So here it goes...
>
> -- I created a table MYOBJECTS from dba_objects, created index on
> object_type
> -- then gathered stats
>
>
> hr_at_IVRS> execute dbms_stats.gather_table_stats(ownname => 'HR',
> tabname => 'MYOBJECTS', estimate_percent =>
> dbms_stats.auto_sample_size, method_opt =>'for all columns size auto',
> degree=> dbms_stats.default_degree, cascade => true);
> PL/SQL procedure successfully completed.
>
>
> -- Get the current # of rows, # of blocks, avg row len
>
>
> hr_at_IVRS> hr_at_IVRS> set serveroutput on
> DECLARE
> numr NUMBER;
> numb NUMBER;
> avgr NUMBER;
> BEGIN
> dbms_stats.get_table_stats(ownname => 'HR',tabname => 'MYOBJECTS',
> numrows=>numr, numblks =>numb, avgrlen=>avgr);
> dbms_output.put_line('# of rows: ' || TO_CHAR(numr));
> dbms_output.put_line('# of blocks: ' || TO_CHAR(numb));
> dbms_output.put_line('Avg row len: ' || TO_CHAR(avgr) || ' bytes');
> END;
> /
> # of rows: 102865
> # of blocks: 1524
> Avg row len: 93 bytes
>
>
> -- enabled 10046 then executed the SQL
>
> select owner, object_type, object_name, status from myobjects
> where object_type in ('TABLE','INDEX')
> order by 1,2,3,4
> /
>
>
> -- from select * from table(dbms_xplan.display);
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 3477389678
>
>
> ---------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost
> (%CPU)| Time |
>
> ---------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 7125 |
> 320K| |
> 419 (1)| 00:00:06 |
> | 1 | SORT ORDER BY | | 7125 |
> 320K| 856K|
> 419 (1)| 00:00:06 |
> | 2 | INLIST ITERATOR | | |
> | |
> | |
> | 3 | TABLE ACCESS BY INDEX ROWID| MYOBJECTS | 7125 |
> 320K| | 334 (1)| 00:00:05 |
> |* 4 | INDEX RANGE SCAN | MYOBJECTS_OBJTYPE_IDX | 7125 |
> | | 21 (5)| 00:00:01 |
>
> ---------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE')
>
>
> -- from the 10046 trace... you can see that it used the index
> STAT #2 id=1 cnt=7699 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=546 pr=0
> pw=0 time=460656 us)'
> STAT #2 id=2 cnt=7699 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=546
> pr=0 pw=0 time=2957809 us)'
> STAT #2 id=3 cnt=7699 pid=2 pos=1 obj=54226 op='TABLE ACCESS BY INDEX
> ROWID MYOBJECTS (cr=546 pr=0 pw=0 time=1996121 us)'
> STAT #2 id=4 cnt=7699 pid=3 pos=1 obj=54231 op='INDEX RANGE SCAN
> MYOBJECTS_OBJTYPE_IDX (cr=22 pr=0 pw=0 time=724692 us)'
>
>
>
> #####################################################
>
>
> -- then from another session, set table stats
>
> exec dbms_stats.set_table_stats( ownname => 'HR', tabname =>
> 'MYOBJECTS', numrows => 300000, numblks => 4000 );
>
>
> -- enabled 10046 then executed the SQL
>
>
> -- from select * from table(dbms_xplan.display); .... so it's now full
> table scan...
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 3289836943
>
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
>
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 20781 | 933K| | 1125
> (1)| 00:00:14 |
> | 1 | SORT ORDER BY | | 20781 | 933K| 2456K| 1125
> (1)| 00:00:14 |
> |* 2 | TABLE ACCESS FULL| MYOBJECTS | 20781 | 933K| |
> 882 (1)| 00:00:11 |
>
> ----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE')
>
>
>
> -- but from the 10046 trace... it's different... I'm getting an index
> access...
>
> STAT #1 id=1 cnt=7699 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=546 pr=0
> pw=0 time=622323 us)'
> STAT #1 id=2 cnt=7699 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=546
> pr=0 pw=0 time=1648679 us)'
> STAT #1 id=3 cnt=7699 pid=2 pos=1 obj=54226 op='TABLE ACCESS BY INDEX
> ROWID MYOBJECTS (cr=546 pr=0 pw=0 time=1056790 us)'
> STAT #1 id=4 cnt=7699 pid=3 pos=1 obj=54231 op='INDEX RANGE SCAN
> MYOBJECTS_OBJTYPE_IDX (cr=22 pr=0 pw=0 time=494570 us)'
>
>
>
>
> Is this the expected behavior? I'm faking the stats, which in turn
> giving me a fake plan :) when I look in my 10046 trace it's still
> using the index scan.. :)
>
>
>
>
>
>
> - Karl Arao
> http://karlarao.wordpress.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 28 2009 - 18:13:28 CDT