dbms_stats.set_table_stats

  • From: Karl Arao <karlarao@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 28 Jul 2009 21:09:50 +0800

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: