Database Tuning (MOSC)

MOSC Banner

Gathering statistics on partitioned and non-partitioned tables

edited Aug 30, 2011 10:40PM in Database Tuning (MOSC) 4 commentsAnswered ✓
Hi all,
My DB is 11.1

I find that gathering statistics on partitioned tables are really slow.

TABLE_NAME                       NUM_ROWS     BLOCKS SAMPLE_SIZE LAST_ANALYZED PARTITIONED COMPRESSION
------------------------------ ---------- ---------- ----------- ------------- ----------- -----------
O_FCT_BP1                        112123170     843140    11212317 8/30/2011 3:5    NO          DISABLED
LEON_123456                      112096060     521984    11209606 8/30/2011 4:2    NO          ENABLED
O_FCT                           115170000     486556      115170 8/29/2011 6:3     YES   
 
SQL> SELECT COUNT(*)  FROM user_tab_subpartitions
  2  WHERE table_name =O_FCT<font color="#000080">'</font>
  3  ;
 
  COUNT(*)
----------
       112


I used the following script:
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => user,
                                tabname          => O_FCT<font color="#000080">',</font>
                                method_opt       => <font color="#000080">'for all

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center