1. Try dbms_stats.set_table_stats(..., no_invalidate=>false); http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#i997763 2. 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@xxxxxxxxx> > 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@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@IVRS> hr@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 > -- > //www.freelists.org/webpage/oracle-l > > >