Re: dbms_stats.set_table_stats
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: Dion Cho <ukja.dion@xxxxxxxxx>, niall.litchfield@xxxxxxxxx
- Date: Thu, 30 Jul 2009 15:08:16 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
