Re: dbms_stats.set_table_stats

From: Karl Arao <karlarao_at_gmail.com>
Date: Thu, 30 Jul 2009 15:08:16 +0800
Message-ID: <12ee65600907300008g3b1f8d1djc71a58a5399bdbd3_at_mail.gmail.com>



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

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

sys_at_IVRS> alter system flush shared_pool;

System altered.

sys_at_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_at_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_at_IVRS> alter system flush shared_pool;

System altered.

sys_at_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_at_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
Received on Thu Jul 30 2009 - 02:08:16 CDT

Original text of this message