Re: dbms_stats.set_table_stats
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