| Information on Statistics generation for Partition Tables [message #65007] |
Sun, 28 March 2004 22:41  |
sabir
Messages: 18
Registered: April 2003
|
Junior Member |
|
|
Hello Everyone,
I need your help on below mentioned scenario.In our Dataware house environment, we have a table that holds approx 55 Million records.For every append load we create a new partition based on month.Load for this table could be triggered by 7 dependencies.If anyone dependency is met then the table gets loaded.
My query is related to use of analyze command for this table.plz reply to following questions
1) If we first run Analyze on a specific partition and later run Analyze on table.Then will it reduce the time Oracle takes to gather statistics on table.Plz provide cases, that will be better
2) If we have partitioned table and the statistics are generated on Table as well as Partition level, but the queries are not written to make use of partitions.Then what difference (magnitude of performance difference) it makes for Oracle if the query is explicitly making use of Partitions and when not using.
Thanx in Advance to you All for your precious time,
Sabir
|
|
|
|
| Re: Information on Statistics generation for Partition Tables [message #65034 is a reply to message #65007] |
Thu, 01 April 2004 12:26  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ok,let see..
-- Create a List partitioned table
SQL> create table t partition by list(owner)
2 (Partition SYS_P Values('SYS'),Partition System_P Values('SYSTEM'),Partition Thiru_P values('THIRU'),Partition Other_P values(Default)) as select * from all_objects;
Table created.
-- these are the partitions
SQL> select table_name,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
T SYS_P
'SYS'
T SYSTEM_P
'SYSTEM'
T THIRU_P
'THIRU'
T OTHER_P
Default
-- lets insert some more rows
SQL> insert into t select * from t;
30465 rows created.
SQL> /
/
60930 rows created.
SQL>
121860 rows created.
SQL> SQL>
SQL> commit;
Commit complete.
SQL> set timing on
-- Lets have a baseline. A Compute statistics on the table + partitions
SQL> execute dbms_stats.gather_table_stats('THIRU','T');
PL/SQL procedure successfully completed.
<B>Elapsed: 00:00:44.36</B>
-- just confirming we have the statistics
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS,BLOCKS from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE NUM_ROWS BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T SYS_P
'SYS' 111200 1516
T SYSTEM_P
'SYSTEM' 5456 83
T THIRU_P
'THIRU' 200 8
T OTHER_P
Default 126864 1768
-- just checking the number of rows in SYS_P partition
SQL> select count(*) from t partition(SYS_P);
COUNT(*)
----------
111200
Elapsed: 00:00:00.07
-- Now lets delete the statistics
SQL> execute dbms_stats.delete_table_stats('THIRU','T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
-- Confirming the statistics are gone
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS,BLOCKS from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE NUM_ROWS BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T SYS_P
'SYS'
T SYSTEM_P
'SYSTEM'
T THIRU_P
'THIRU'
T OTHER_P
Default
Elapsed: 00:00:00.02
-- Now lets gather statistics on just one partition. the biggest one SYS_P
SQL> execute dbms_stats.gather_table_stats(ownname=>'THIRU',tabname=>'T',partname=>'SYS_P');
PL/SQL procedure successfully completed.
<B>Elapsed: 00:00:32.77 </B>
-- this constitutes the biggest chunk of overall time
-- Confirming we have the statistics for SYS_P partition
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS,BLOCKS from user_tab_partitions where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE NUM_ROWS BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
T SYS_P
'SYS' 111200 1516
T SYSTEM_P
'SYSTEM'
T THIRU_P
'THIRU'
T OTHER_P
Default
Elapsed: 00:00:00.01
-- Now lets gather statistics for the table again
SQL> execute dbms_stats.gather_table_stats('THIRU','T');
PL/SQL procedure successfully completed.
<B>Elapsed: 00:00:42.51</B>
-- Almost same as the baseline, although we had statistics for the biggest partition SYS_P. It just updated the statistics for everything.
Now,onto your 2nd question.
-- Query that doesnt explicitly specify partition
SQL> select count(*) from t where owner='THIRU';
COUNT(*)
----------
200
Elapsed: 00:00:00.20
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=200 Bytes=1200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- Query that specifies partition explicitly
SQL> select count(*) from t partition(thiru_p);
COUNT(*)
----------
200
Elapsed: 00:00:00.20
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> explain plan for select count(*) from t partition(thiru_p);
Explained.
Elapsed: 00:00:00.00
SQL> set timing off
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL | T | 200 | | 2 | 3 | 3
------------------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
-- only partition 3 is scanned here
SQL> explain plan for select count(*) from t where owner='THIRU';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 | |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS FULL | T | 200 | 1200 | 2 | 3 | 3
------------------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
-- again partition 3 only is scanned here.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
