Home » RDBMS Server » Performance Tuning » Information on Statistics generation for Partition Tables
Information on Statistics generation for Partition Tables [message #65007] Sun, 28 March 2004 22:41 Go to next message
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 Go to previous message
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
Previous Topic: striping
Next Topic: Forcing optimizer to use full scan
Goto Forum:
  


Current Time: Sun Jan 17 13:42:08 CST 2021