Re: dbms_stats.set_table_stats

Hi Dion, Niall,


The flushing of shared pool & buffer cache helped just to have a fresh
ground for the test case.
I was able to force a full table scan (from index scan).. I used the
GATHER_PLAN_STATISTICS hint to give me more details on the execution.
Then compare the results of explain plan from

dbms_xplan.display
xmsh script of tanel poder
10046 raw trace STAT lines
orasrp statement plan

By having these info, I could conclude that yes you can affect the
join order by faking the statistics. But as a warning, be aware of the
"rows" column of dbms_xplan.display output it could really go big and
you may be faked by it because those are not real rows. Better run it
with GATHER_PLAN_STATISTICS or have a 10046 trace to get the "real #
of rows" and "logical reads".



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

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



sys@IVRS> alter system flush shared_pool;

System altered.

sys@IVRS> alter system flush buffer_cache;

System altered.



alter session set events '10046 trace name context forever, level 8';



select /*+ GATHER_PLAN_STATISTICS */ owner, object_type, object_name,
status from myobjects
where object_type = 'TABLE'
order by 1,2,3,4
/


-- from select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 594239673

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes 
|TempSpc| Cost
(%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |  3866 |   173K|  
     |   229
(1)| 00:00:03 |
|   1 |  SORT ORDER BY               |                       |  3866 |   173K|  
 472K|   229
(1)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYOBJECTS             |  3866 |   173K|  
    |    181   (0)| 00:00:03 |
|*  3 |    INDEX RANGE SCAN          | MYOBJECTS_OBJTYPE_IDX |  3866 |  
|            |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_TYPE"='TABLE')

15 rows selected.



hr@IVRS> @xmsh 4136267286 %

SQL hash value:      4136267286    Cursor address:  2FC5ADEC    |
Statement first parsed at: 2009-07-19/11:44:57  |  10 seconds ago

Pr   Op
Objcect                          ms spent in     Estimated Real #rows
 Op. ite-    Logical    Logical    Optimizer
ed   ID Operation                                               Name
                            operation   output rows   returned
rations      reads     writes         Cost
-- ---- -------------------------------------------------------
------------------------------ ------------- ------------- ----------
---------- ---------- ---------- ------------
      0 SELECT STATEMENT

                                  229
      1  SORT ORDER BY
                               233.93          3866       3688
 1        280          0          229
      2   TABLE ACCESS BY INDEX ROWID
MYOBJECTS                             404.69          3866       3688
        1        280          0          181
A     3    INDEX RANGE SCAN
MYOBJECTS_OBJTYPE_IDX                  24.32          3866       3688
        1         11          0           11



Op
ID     Predicate Information (identified by operation id):
------ 
----------------------------------------------------------------------------------------------------
    3  - access("OBJECT_TYPE"='TABLE')





-- from the 10046 trace
STAT #4 id=1 cnt=3688 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=280
pr=280 pw=0 time=233929 us)'
STAT #4 id=2 cnt=3688 pid=1 pos=1 obj=54226 op='TABLE ACCESS BY INDEX
ROWID MYOBJECTS (cr=280 pr=280 pw=0 time=404689 us)'
STAT #4 id=3 cnt=3688 pid=2 pos=1 obj=54231 op='INDEX RANGE SCAN
MYOBJECTS_OBJTYPE_IDX (cr=11 pr=11 pw=0 time=24316 us)'


-- from orasrp
Statement Plan
--------------
met 1 time
Rows        Row Source Operation [Object Id]
----------  --------------------------------
     3,688  SORT ORDER BY (cr=280 pr=280 pw=0 time=0.2339s)
     3,688    TABLE ACCESS BY INDEX ROWID MYOBJECTS (cr=280 pr=280
pw=0 time=0.4047s) [54226]
     3,688      INDEX RANGE SCAN MYOBJECTS_OBJTYPE_IDX (cr=11 pr=11
pw=0 time=0.0243s) [54231]

Statement Flat Profile
----------------------

      ----------- Time Per Call ---------
Event Name                                  % Time       Seconds
Calls          Avg         Min         Max
----------------------------------------  --------  ------------
---------  ----------- ----------- -----------
SQL*Net message from client                  43.7%       0.2572s
 246      0.0010s     0.0007s     0.0026s
FETCH calls [CPU]                            30.7%       0.1810s
 247      0.0007s     0.0000s     0.1610s
db file sequential read                      20.8%       0.1225s
 280      0.0004s     0.0000s     0.0020s
PARSE calls [CPU]                             4.6%       0.0270s
   1      0.0270s     0.0270s     0.0270s
SQL*Net message to client                     0.1%       0.0009s
 247      0.0000s     0.0000s     0.0000s
EXEC calls [CPU]                              0.0%       0.0000s
   1      0.0000s     0.0000s     0.0000s
----------------------------------------  --------  ------------
---------  ----------- ----------- -----------
Total                                       100.0%       0.5886s




######################


sys@IVRS> alter system flush shared_pool;

System altered.

sys@IVRS> alter system flush buffer_cache;

System altered.


exec dbms_stats.set_table_stats( ownname => 'HR', tabname =>
'MYOBJECTS', numrows => 30000000, numblks => 4000 ,
no_invalidate=>false);

# of rows: 30000000
# of blocks: 4000
Avg row len: 93 bytes


alter session set events '10046 trace name context forever, level 8';



select /*+ GATHER_PLAN_STATISTICS */ owner, object_type, object_name,
status from myobjects
where object_type = 'TABLE'
order by 1,2,3,4
/




-- from select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3289836943

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost
(%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1127K|    49M|       | 14291  
(4)| 00:02:52 |
|   1 |  SORT ORDER BY     |           |  1127K|    49M|   129M| 14291  
(4)| 00:02:52 |
|*  2 |   TABLE ACCESS FULL| MYOBJECTS |  1127K|    49M|       |
1257    (31)| 00:00:16 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE')

14 rows selected.




hr@IVRS> @xmsh 4136267286 %

SQL hash value:      4136267286    Cursor address:  2FC5ADEC    |
Statement first parsed at: 2009-07-19/11:45:52  |  12 seconds ago

Pr   Op
Objcect                          ms spent in     Estimated Real #rows
 Op. ite-    Logical    Logical    Optimizer
ed   ID Operation                                               Name
                            operation   output rows   returned
rations      reads     writes         Cost
-- ---- -------------------------------------------------------
------------------------------ ------------- ------------- ----------
---------- ---------- ---------- ------------
      0 SELECT STATEMENT

                                14291
      1  SORT ORDER BY
                               446.39       1127396       3688
 1       1451          0        14291
 F    2   TABLE ACCESS FULL
MYOBJECTS                              50.88       1127396       3688
        1       1451          0         1257



Op
ID     Predicate Information (identified by operation id):
------ 
----------------------------------------------------------------------------------------------------
    2  - filter("OBJECT_TYPE"='TABLE')




-- from the 10046 raw trace
STAT #7 id=1 cnt=3688 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1451
pr=1447 pw=0 time=446391 us)'
STAT #7 id=2 cnt=3688 pid=1 pos=1 obj=54226 op='TABLE ACCESS FULL
MYOBJECTS (cr=1451 pr=1447 pw=0 time=50879 us)'


-- from orasrp
Statement Plan
--------------
met 1 time
Rows        Row Source Operation [Object Id]
----------  --------------------------------
     3,688  SORT ORDER BY (cr=1,451 pr=1,447 pw=0 time=0.4464s)
     3,688    TABLE ACCESS FULL MYOBJECTS (cr=1,451 pr=1,447 pw=0
time=0.0509s) [54226]

Statement Flat Profile
----------------------

      ----------- Time Per Call ---------
Event Name                                  % Time       Seconds
Calls          Avg         Min         Max
----------------------------------------  --------  ------------
---------  ----------- ----------- -----------
SQL*Net message from client                  49.2%       0.3373s
 246      0.0014s     0.0006s     0.0193s
FETCH calls [CPU]                            28.4%       0.1950s
 247      0.0008s     0.0000s     0.1530s
db file scattered read                       19.8%       0.1359s
 101      0.0013s     0.0004s     0.0026s
PARSE calls [CPU]                             1.7%       0.0120s
   1      0.0120s     0.0120s     0.0120s
db file sequential read                       0.5%       0.0034s
   3      0.0011s     0.0008s     0.0014s
SQL*Net message to client                     0.3%       0.0021s
 247      0.0000s     0.0000s     0.0004s
EXEC calls [CPU]                              0.0%       0.0000s
   1      0.0000s     0.0000s     0.0000s
----------------------------------------  --------  ------------
---------  ----------- ----------- -----------
Total                                       100.0%       0.6856s





- Karl Arao
http://karlarao.wordpress.com
--
http://www.freelists.org/webpage/oracle-l


Other related posts: