dbms_stats.set_table_stats

From: Karl Arao <karlarao_at_gmail.com>
Date: Tue, 28 Jul 2009 21:09:50 +0800
Message-ID: <12ee65600907280609v256f2168odd314f2ff419d841_at_mail.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

Received on Tue Jul 28 2009 - 08:09:50 CDT