467,893 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Table Partition Performance analysis

Table Partition Performance analysis
============================================

Collection of Statistics for Cost-Based Optimization/DBMS_STATS vs. ANALYZE

The cost-based approach relies on statistics and if the cost-based
Approach is used , then statistics should be gernerated for all tables, clusters, and all types of indexes accessed by SQL statements. If the size and data distribution of your tables change frequently, then generate statistics regularly to ensure the statistics accurately represent the data in the tables.

Select * from test partition;
This uses global statistics but no predicate
select * from test s where s.amount_of_shift > 1000;
This uses a predicate for more than one partition and may use global statistics
select * from test partition (sep2009) s where s.amount_of_shift > 1000;
This uses global statistics and predicate to one partition.

Gathering global statistics with the DBMS_STATS package is more useful because ANALYZE always runs serially. DBMS_STATS can run in serial or parallel. Whenever possible, DBMS_STATS calls a parallel query to gather statistics with the specified degree of parallelism; otherwise, it calls a serial query or the ANALYZE statement. Index statistics are not gathered in parallel.

ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics. DBMS_STATS can gather separate statistics for each partition as well as global statistics for the entire table or index. Depending on the SQL statement being optimized, the optimizer may choose to use either the partition (or subpartition) statistics or the global statistics.

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE PARTTAB(
  2.     ordid        NUMBER,
  3.     PARTCOL    DATE,        
  4.     DETAILS     NUMBER,
  5.     AMOUNT    NUMBER)
  6. PARTITION BY RANGE(PARTCOL)
  7. SUBPARTITION BY HASH(DETAILS) SUBPARTITIONS 2 
  8. (PARTITION q1 VALUES LESS THAN(TO_DATE('01-04-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE1,
  9.  PARTITION q2 VALUES LESS THAN(TO_DATE('01-07-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE2,
  10.  PARTITION q3 VALUES LESS THAN(TO_DATE('01-10-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE3,
  11.  PARTITION q4 VALUES LESS THAN(TO_DATE('01-12-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE4
  12. );
  13.  
  14. A local non prefixed index will be associated with it:
  15.  
  16. CREATE INDEX IDX_PARTTAB ON PARTTAB (ordid) LOCAL;
  17.  
  18. The PARTTAB table has been populated before to start the following examples.
  19.  
  20.  
  21.  
  22.  GATHER_TABLE_STATS
  23. -------------------
  24.  
  25.  Collects table, column, and index statistics.
  26.  
  27. Compute, serial mode, without histograms, Default granularity.
  28. ============================================================
  29.  
  30. SQL> execute dbms_stats.gather_table_stats(-
  31. >ownname => 'test',-
  32. >tabname => 'PARTTAB',-
  33. >partname => null,-                --> Gather stats on all partitions.
  34. >estimate_percent => null,-        --> Compute mode
  35. >block_sample => false,-            --> Default value. No Sense in Compute mode
  36. >method_opt => 'FOR ALL COLUMNS SIZE 1',-    --> Table and columns statistics. No histogram generated
  37. >degree => null,-                --> default parallel degree based on DOP set on PARTTAB.
  38. >granularity => 'default',-        --> Gather Global and Partition statistics
  39. >cascade => true ,-                --> with index stats generated
  40. >stattab => null,-                --> The statistics will be stored in the dictionary.
  41. >statid => null,-
  42. >statown => null);
  43.  
  44. PL/SQL procedure successfully completed.
  45.  
  46.  
  47.  Index Statistics won't be calculated by default if  CASCADE=>TRUE .
  48.  
  49.  
  50. SQL> select table_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN
  51.     , GLOBAL_STATS, USER_STATS, sample_size from user_tables
  52.     where table_name = 'PARTTAB';
  53.  
  54. TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
  55. ---------- -------- ------ ------------ --------- ----------- ------------ ---------- -----------
  56. PARTTAB          400      8            0         0          11 YES          NO                 400
  57.  
  58. Now that the statistics have been updated. 
  59. The column GLOBAL_STATS has been also initialized. 
  60.  
  61. SQL> select partition_name "Partition", NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN,
  62.  SAMPLE_SIZE, global_stats, user_stats
  63.  from user_tab_partitions
  64.  where table_name = 'PARTTAB'
  65.  order by partition_position
  66. /
  67.  
  68. Partition  NUM_ROWS BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
  69. ---------- -------- ------ ------------ ---------- ----------- ------------ ---------- -----------
  70. Q1              100      2            0          0          11 YES          NO                 100
  71. Q2              100      2            0          0          11 YES          NO                 100
  72. Q3              100      2            0          0          11 YES          NO                 100
  73. Q4              100      2            0          0          11 YES          NO                 100
  74.  
  75.  
  76. The statistics are again obtained at the table level with the GLOBAL_STATS .
  77.  
  78.  
  79. SQL> select partition_name "Partition", subpartition_name "Subpartition", NUM_ROWS, BLOCKS, EMPTY_BLOCKS
  80.     SAMPLE_SIZE, global_stats, user_stats
  81.     from user_tab_subpartitions
  82.     where table_name = 'PARTTAB'
  83.     order by partition_name, subpartition_position
  84. /
  85.  
  86. Partition  Subpartition  NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE GLOBAL_STATS USER_STATS
  87. ---------- ------------- -------- ------ ------------ --------- ----------- ----------- ------------ ----------
  88. Q1         SYS_SUBP10365                                                                          NO         NO
  89. Q1         SYS_SUBP10366                                                                          NO         NO
  90. Q2         SYS_SUBP10367                                                                          NO         NO
  91. Q2         SYS_SUBP10368                                                                          NO         NO
  92. Q3         SYS_SUBP10369                                                                          NO         NO
  93. Q3         SYS_SUBP10370                                                                          NO         NO
  94. Q4         SYS_SUBP10371                                                                          NO         NO
  95. Q4         SYS_SUBP10372                                                                          NO         NO
  96.  
  97. The statistics aren't computed at the subpartition level which is in phase 
  98. with the 'DEFAULT' granularity.
  99.  
  100.  
  101. SQL>select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from 
  102.  user_tab_col_statistics where table_name = 'PARTTAB'
  103. /
  104.  
  105. COLUMN_NAME     NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED
  106. --------------- ------------ ---------- ---------- ----------- -------------
  107. ORDID                      0          0        400           1 12-DEC-02
  108. PARTCOL                  4        .25          0           1 12-DEC-02
  109. DETAILS                100        .01          0           1 12-DEC-02
  110. AMOUNT                   0          0        400           1 12-DEC-02
  111.  
  112. The NUM_BUCKETS is set to 1 as there is no histogram generation. but, the column 
  113. statistics are well initialized
  114.  
  115. The same result is showed below on each partition columns:
  116.  
  117. SQL>select partition_name, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from user_part_col_statistics
  118.   where table_name = 'PARTTAB'
  119. /
  120.  
  121. PARTITION_ COLUMN_NAME     NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED
  122. ---------- --------------- ------------ ---------- ---------- ----------- -------------
  123. Q1         ORDID                      0          0        100           1 12-DEC-07
  124. Q1         PARTCOL                  1          1          0           1 12-DEC-07
  125. Q1         DETAILS                100        .01          0           1 12-DEC-07
  126. Q1         AMOUNT                   0          0        100           1 12-DEC-07
  127. Q2         ORDID                      0          0        100           1 12-DEC-07
  128. Q2         PARTCOL                  1          1          0           1 12-DEC-07
  129. Q2         DETAILS                100        .01          0           1 12-DEC-07
  130. Q2         AMOUNT                   0          0        100           1 12-DEC-07
  131. Q3         ORDID                      0          0        100           1 12-DEC-07
  132. Q3         PARTCOL                  1          1          0           1 12-DEC-07
  133. Q3         DETAILS                100        .01          0           1 12-DEC-07
  134. Q3         AMOUNT                   0          0        100           1 12-DEC-07
  135. Q4         ORDID                      0          0        100           1 12-DEC-07
  136. Q4         PARTCOL                  1          1          0           1 12-DEC-07
  137. Q4         DETAILS                100        .01          0           1 12-DEC-07
  138. Q4         AMOUNT                   0          0        100           1 12-DEC-07
  139.  
  140.  
  141. the statistics loaded for subpartitions of the PARTTAB table are displayed below:
  142.  
  143. SQL> select subpartition_name "Subpartition", COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, 
  144.   NUM_BUCKETS from dba_subpart_col_statistics where table_name = 'PARTTAB'
  145.   order by column_name
  146. /
  147.  
  148. Subpartition    COLUMN_NAME     NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS
  149. --------------- --------------- ------------ ---------- ---------- -----------
  150. SYS_SUBP10365   PARTCOL
  151. SYS_SUBP10365   ORDID
  152. SYS_SUBP10365   DETAILS
  153. SYS_SUBP10365   AMOUNT
  154. SYS_SUBP10366   PARTCOL
  155. SYS_SUBP10366   ORDID
  156. SYS_SUBP10366   DETAILS
  157. SYS_SUBP10366   AMOUNT
  158. SYS_SUBP10367   PARTCOL
  159. SYS_SUBP10367   ORDID
  160. SYS_SUBP10367   DETAILS
  161. SYS_SUBP10367   AMOUNT
  162. SYS_SUBP10368   PARTCOL
  163. SYS_SUBP10368   ORDID
  164. SYS_SUBP10368   DETAILS
  165. SYS_SUBP10368   AMOUNT
  166. SYS_SUBP10369   PARTCOL
  167. SYS_SUBP10369   ORDID
  168. SYS_SUBP10369   DETAILS
  169. SYS_SUBP10369   AMOUNT
  170. SYS_SUBP10370   PARTCOL
  171. SYS_SUBP10370   ORDID
  172. SYS_SUBP10370   DETAILS
  173. SYS_SUBP10370   AMOUNT
  174. SYS_SUBP10371   PARTCOL
  175. SYS_SUBP10371   ORDID
  176. SYS_SUBP10371   DETAILS
  177. SYS_SUBP10371   AMOUNT
  178. SYS_SUBP10372   PARTCOL
  179. SYS_SUBP10372   ORDID
  180. SYS_SUBP10372   DETAILS
  181. SYS_SUBP10372   AMOUNT
No statistics were loaded on subpartition's columns.

Partitioned objects can contain more than one sets of statistics. This is because statistics can be generated for the entire object, partition, or subpartition.


Thanks & Regards,
Vinod Sadanandan
Oracle DBA
Dec 7 '07 #1
  • viewed: 11898
Share:

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Eric | last post: by
10 posts views Thread by Bing Wu | last post: by
1 post views Thread by Mats Kling | last post: by
13 posts views Thread by James Conrad St.John Foreman | last post: by
1 post views Thread by Mark Dengler | last post: by
4 posts views Thread by Hemant Shah | last post: by
15 posts views Thread by Piero 'Giops' Giorgi | last post: by
reply views Thread by Damir | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.