Re: dbms_stats.set_table_stats

  • From: Dion Cho <ukja.dion@xxxxxxxxx>
  • To: karlarao@xxxxxxxxx
  • Date: Wed, 29 Jul 2009 08:13:28 +0900

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
>
>
>

Other related posts: