|
 |
| |
|
|
How to Move from ANALYZE to DBMS_STATS on Partitioned Tables
|
|
park1q
|
2013-05-30 10:38:15, 조회 : 3,914, 추천 : 862 |
 |
|
 |
| 문서 ID: |
공지:237538.1 |
| 제목: |
How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples |
| 유형: |
BULLETIN |
| 상태: |
PUBLISHED | |
| 컨텐트 유형: |
TEXT/X-HTML |
| 생성 날짜: |
06-MAY-2003 |
| 마지막 갱신 날짜: |
15-NOV-2004 | | Purpose
This article provides statistic gathering examples using DBMS_STATS and the legacy ANALYZE commands.
Its purpose is to assist users of ANALYZE to move to DBMS_STATS for partitioned tables.
Note: This article is a part of a series explaining how to move to DBMS_STATS
from legacy statistic gathering routines such as ANALYZE and DBMS_UTILITY.
See the following article to view other notes in the series:
<Note:237293.1> How to Move from ANALYZE to DBMS_STATS - Introduction Scope and Application
This article is aimed at anyone wishing to gather statistics on Partitioned objects for use by the Cost Based Optimizer (CBO).
Gathering statistics on Partitioned Tables.
Statistics for Partitionned tables are stored in the following dictionary Views:
- Table Level
- USER_TABLES
- USER_TAB_COL_STATISTICS
- USER_HISTOGRAMS
This dictionary view is always populated at the same time as USER_TAB_COL_STATISTICS.
To determine how many buckets have been populated, look at the Num_Buckets column in USER_TAB_COL_STATISTICS.
- USER_INDEXES if any
- Partition Level
- USER_TAB_PARTITIONS
- USER_PART_COL_STATISTICS
- USER_PART_HISTOGRAMS
This dictionary view is always populated at the same time as USER_PART_COL_STATISTICS.
To determine how many buckets have been populated, look at the Num_Buckets column in USER_PART_COL_STATISTICS.
- USER_PART_INDEXES if any
The examples below provide comparable ANALYZE and DBMS_STATS commands. The idea is that these should facilitate transition from one to the other.
Key to the examples: For each case, there is :
- “Direct Translation” This translation produces statistics which are as close to those produced by the legacy ANALYZE command as possible .
- “Enhanced Translation” This translation adds global statistics to the Direct Translation to (hopefully) improve the accuracy of certain statistics.
Color Codes
- RED : Original statement
- ORANGE : Direct Translation
- GREEN : Enhanced Translation
The examples feature a Partitioned Table called T2Part.
Note that in the ANALYZE example, and the DBMS_STATS example with GRANULARITY => 'PARTITION', since only the Partition level is being gathered, any statistics above this level will be derived from these statistics.
In the DBMS_STATS example where GRANULARITY => 'ALL' or 'DEFAULT', global statistics are gathered for all levels of the object.
See <Note:236935.1> for more details on Global and derived statistics.
| 1.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS; |
| 1.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'PARTITION' ,CASCADE => TRUE); |
| 1.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'DEFAULT' ,CASCADE => TRUE); |
| 2.1 |
From ANALYZE TABLE T2Part ESTIMATE STATISTICS 15 PERCENT; |
| 2.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
ESTIMATE_PERCENT => 15, GRANULARITY => 'PARTITION' ,CASCADE => TRUE); |
| 2.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
ESTIMATE_PERCENT => 15, GRANULARITY => 'DEFAULT' ,CASCADE => TRUE ); |
| 3.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE; |
| 3.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => FALSE ,GRANULARITY => 'PARTITION',METHOD_OPT => 'FOR COLUMNS'); |
| 3.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => FALSE ,GRANULARITY => 'DEFAULT',METHOD_OPT => 'FOR COLUMNS'); |
| 4.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE;
+ ANALYZE INDEX IDX_T2Part COMPUTE STATISTICS; |
| 4.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => TRUE ,GRANULARITY => 'PARTITION' ,METHOD_OPT => 'FOR COLUMNS'); |
| 4.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => TRUE ,GRANULARITY => 'DEFAULT' ,METHOD_OPT => 'FOR COLUMNS'); |
| 5.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS; |
| 5.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'PARTITION' ,CASCADE => FALSE , -
METHOD_OPT => 'FOR ALL INDEXED COLUMNS'); |
| 5.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'DEFAULT' ,CASCADE => FALSE , -
METHOD_OPT => 'FOR ALL INDEXED COLUMNS'); |
Gathering statistics on Partitioned Table.
1 - Case 1
| 1.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS; |
| 1.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'PARTITION' ,CASCADE => TRUE); |
| 1.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'DEFAULT' ,CASCADE => TRUE); |
- Gather Statistics on T2Part, T2Part columns and T2Part indexes examining all the rows.
- Collect only the lowest level statistics (in this case, this is at the partition level)
- Partition Statistics :
- USER_TAB_PARTITIONS :
Gathered Table Partition Statistics.
Sample_Size is set to the number of rows
- USER_PART_COL_STATISTICS :
Gathered Column Partition statistics.
Sample_Size is set to the number of rows
- Derive upper level statistics by aggregation.
- Table Statistics :
- USER_TABLES :
Derived Table Statistics.
Sample_Size is set to 0
- USER_TAB_COL_STATISTICS :
Derived Columns statistics.
Sample_Size is set to NULL
- The number of distinct values for a column in USER_TAB_COL_STATISTICS is an aggregated value.
This value is prone to inaccuracy due to overlapping values between partitions.
See <Note:236935.1> for an example of this.
- Index Statistics
- USER_INDEXES :
Derived Index Statistics.
Sample_Size is set to 0 in USER_INDEXES
- USER_IND_PARTITIONS :
Gathered Index Partition Statistics.
Sample_Size is set to number of rows in USER_IND_PARTITIONS
- Global_Stats column is always set to NO with ANALYZE
1.2 DBMS_STATS.GATHER_TABLE_STATS statistics
In this example,
is replaced by
NULL, TABNAME => 'T2Part',GRANULARITY => 'PARTITION',CASCADE => TRUE);
- There is a difference in the Sample_Size column
- USER_TAB_PARTITIONS :
Gathered Table Partition Statistics.
Sample_Size is set the number of Rows
- USER_PART_COL_STATISTICS :
Gathered Columns Partition statistics.
Sample_Size is set to the number of Rows - number of Nulls
- Global_Stats Column
- at Partition Level is set to YES (Not like ANALYZE).
- at Table Level is set to NO (like ANALYZE).
- The number of distinct values for a column in USER_TAB_COL_STATISTICS is an aggregated value.
There could have some inaccuracy due to overlapping values between partitions.
See examples in <Note:236935.1>
DBMS_STATS is able to collect a more accurate picture of the statistics by gathering global statistics at table level. In this case,
is replaced with:
NULL, TABNAME => 'T2Part', GRANULARITY => 'ALL', CASCADE => TRUE);
> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', GRANULARITY => 'DEFAULT', CASCADE => TRUE);
- Granularity is set to 'DEFAULT', to gather statistics at Table and partition level.
- Some statistics like Num_Distinct can have different values between DBMS_STATS and ANALYZE. With DBMS_STATS there are no overlapping values between partitions because statistics are gathered on the table as a whole.
- Because Global statistics are gathered with DBMS_STATS as opposed to being aggregated, the time to collect the more accurate statistics can be longer.
2 - Case 2
| 2.1 |
From ANALYZE TABLE T2Part ESTIMATE STATISTICS 15 PERCENT; |
| 2.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
ESTIMATE_PERCENT => 15, GRANULARITY => 'PARTITION' ,CASCADE => TRUE); |
| 2.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
ESTIMATE_PERCENT => 15, GRANULARITY => 'DEFAULT' ,CASCADE => TRUE); |
- This example is similar to case 1 except for the sample size which has been changed from compute to a 15% sample.
- Sample_Size column
- Derived (the Table Level statistics are derived in this example):
See derived statistics in case 1
- Gathered (the partition Level statistics are gathered in this example): For an explanation of this see Example Case 2 in <Note:237537.1>
3 - Case 3
| 3.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE; |
| 3.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => FALSE ,GRANULARITY => 'PARTITION',METHOD_OPT => 'FOR COLUMNS'); |
| 3.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => FALSE ,GRANULARITY => 'DEFAULT',METHOD_OPT => 'FOR COLUMNS'); |
- This example gathers Statistics on T2Part, but not on T2Part Columns and not on T2Part indexes.
- Only collects statistics at the lowest level (in this case at partition level)
- Partition Statistics :
- USER_TAB_PARTITIONS : Gathered Table Partition Statistics.
- USER_PART_COL_STATISTICS : No statistics are collected for Columns.
Derive upper level statistics by aggregation.
- Table Statistics :
- USER_TABLES : Derived Table Statistics.
- USER_TAB_COL_STATISTICS : No statistics are collected for Columns.
No Index Statistics are collected
Global_Stats column is always set to NO with the ANALYZE command
3.2 DBMS_STATS.GATHER_TABLE_STATS
In this example,
is replaced with:
NULL, TABNAME => 'T2Part',CASCADE => FALSE, GRANULARITY => 'PARTITION', METHOD_OPT => 'FOR COLUMNS');
- Global_Stats Column
- at Partition Level, GLOBAL_STATS is set to YES (unlike ANALYZE).
- at Table Level, GLOBAL_STATS is set to NO (as with the ANALYZE example).
- Avg_Row_Len Statistics
- When statistics are gathered (in this case, at the partition level): Then the Avg_Row_Len Statistic is set as per Examples: Case 3, Case 6 and Remark (**) in <Note:237537.1>
- in USER_TAB_PARTITIONS is set to 100 (unlike ANALYZE)as there is no old value.
- in USER_TABLES is derived to 100 (unlike ANALYZE)as there is no old value.
In this example,
becomes
NULL, TABNAME => 'T2Part',CASCADE => FALSE,GRANULARITY => 'DEFAULT',METHOD_OPT => 'FOR COLUMNS')
> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part',CASCADE => FALSE,GRANULARITY => 'ALL',METHOD_OPT => 'FOR COLUMNS')
- Global_Stats Column
- at Partition Level is set to YES (unlike ANALYZE).
- at Table Level is set to YES (unlike ANALYZE).
- Avg_Row_Len Statistics
- When statistics are gathered (in this case at the partition level): Then the Avg_Row_Len Statistic is set as per Examples: Case 3, Case 6 and Remark (**) in <Note:237537.1>
- in USER_TAB_PARTITIONS is set to 100 (unlike ANALYZE).
- in USER_TABLES is derived to 100 (unlike ANALYZE).
4 - Case 4
| 4.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE;
+ ANALYZE INDEX IDX_T2Part COMPUTE STATISTICS; |
| 4.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => TRUE ,GRANULARITY => 'PARTITION' ,METHOD_OPT => 'FOR COLUMNS'); |
| 4.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
CASCADE => TRUE ,GRANULARITY => 'DEFAULT' ,METHOD_OPT => 'FOR COLUMNS'); |
- Gather statistics on T2Part and T2Part indexes but not on T2Part columns.
- The 1st Statement is hte same as in Case 3
- The 2nd statement gathers T2Part index statistics at Partition level
- Global_Stats Column is always set to NO with ANALYZE .
- The 2nd statement populates USER_INDEXES and USER_IND_PARTITIONS
- Index Statistics are collected in typical ANALYZE fashion:
o USER_INDEXES : Derived Index Statistics
o USER_IND_PARTITIONS : Gathered Index Statistics
4.2 DBMS_STATS.GATHER_TABLE_STATS
In this case,
becomes
NULL, TABNAME => 'T2Part',CASCADE => TRUE, GRANULARITY => 'PARTITION', METHOD_OPT => 'FOR COLUMNS');
- Index Statistics are set like ANALYZE :
o USER_INDEXES : Derived Index Statistics
o USER_IND_PARTITIONS : Gathered Index Statistics
- Global Statistics column is set as follows:
o USER_INDEXES.GLOBAL_STATS = NO
o USER_IND_PARTITIONS.GLOBAL_STATS = YES exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part',CASCADE => FALSE, GRANULARITY => 'PARTITION', METHOD_OPT => 'FOR COLUMNS');
+ exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => NULL, INDNAME => 'IDX_T2Part', GRANULARITY => 'PARTITION');
= exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part',CASCADE => TRUE, GRANULARITY => 'PARTITION', METHOD_OPT => 'FOR COLUMNS');
In this example,
becomes
NULL, TABNAME => 'T2Part',CASCADE => TRUE, GRANULARITY => 'DEFAULT', METHOD_OPT => 'FOR COLUMNS');
- Index Statistics are not set like ANALYZE :
o USER_INDEXES : Gathered Index Statistics
o USER_IND_PARTITIONS : Gathered Index Statistics
- Global Statistics is set as follows:
o USER_INDEXES.GLOBAL_STATS = YES
o USER_IND_PARTITIONS.GLOBAL_STATS = YES exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part',CASCADE => FALSE, GRANULARITY => 'DEFAULT', METHOD_OPT => 'FOR COLUMNS');
+ exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => NULL, INDNAME => 'IDX_T2Part', GRANULARITY => 'DEFAULT');
= exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part',CASCADE => TRUE, GRANULARITY => 'DEFAULT', METHOD_OPT => 'FOR COLUMNS');
5 - Case 5
| 5.1 |
From ANALYZE TABLE T2Part COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS; |
| 5.2.1 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'PARTITION' ,CASCADE => FALSE , -
METHOD_OPT => 'FOR ALL INDEXED COLUMNS'); |
| 5.2.2 |
to exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T2Part', -
GRANULARITY => 'DEFAULT' ,CASCADE => FALSE , -
METHOD_OPT => 'FOR ALL INDEXED COLUMNS'); |
- Gather Statistics on T2Part, and T2Part Indexed Columns
- Do not Gather Statistics on T2Part not Indexed Columns and T2Part indexes
- Populate USER_TABLES, USER_TAB_PARTITIONS
- Populate USER_TAB_COL_STATISTICS, USER_PART_COL_STATISTICS for the Indexed Columns.
- No Statistics value in USER_INDEXES, USER_IND_PARTITIONS for table T2Part
- Global_Stats Column is set to NO.
- Statistics Avg_Row_Len (at partition Level) is gathered.
- Statistics Avg_Row_Len (at table Level) is derived.
5.2 DBMS_STATS.GATHER_TABLE_STATS
NULL, TABNAME => 'T2Part', GRANULARITY =>'PARTITION', CASCADE =>FALSE , METHOD_OPT =>'FOR ALL INDEXED COLUMNS');
- Global_Stats Column
- at Partition Level is set to YES (unlike ANALYZE).
- at Table Level is set to NO (like ANALYZE).
- Avg_Row_Len Statistics
- When statistics are gathered (in this case at the partition level): Then the Avg_Row_Len Statistic is set as per Examples: Case 3, Case 6 and Remark (**) in <Note:237537.1>
- in USER_TAB_PARTITIONS is set to 100 (unlike ANALYZE), except if all columns are indexed.
- in USER_TABLES is derived to 100 (unlike ANALYZE), except if all columns are indexed.
NULL, TABNAME => 'T2Part', GRANULARITY =>'DEFAULT', CASCADE =>FALSE , METHOD_OPT =>'FOR ALL INDEXED COLUMNS');
- Global_Stats Column
- at Partition Level is set to YES (unlike ANALYZE).
- at Table Level is set to YES (unlike ANALYZE).
- Avg_Row_Len Statistics
- When statistics are gathered (in this case at the partition level): Then the Avg_Row_Len Statistic is set as per Examples: Case 3, Case 6 and Remark (**) in <Note:237537.1>
- in USER_TAB_PARTITIONS is set to 100 (Not like ANALYZE), except if all columns are indexed.
- in USER_TABLES is derived to 100 (Not like ANALYZE), except if all columns are indexed.
References
<Note:236935.1> Global statistics
<Note:237293.1> How to Move from ANALYZE to DBMS_STATS - Introduction
<Note:237901 .1> Gathering Schema or Database Statistics Automatically - Examples
<End_o <End_o . |
|
 |
Copyright © 2005, Oracle. All rights reserved. 법적 공지 및 사용 약관입니다. |
 | |
|
|
|
|
|
|
|
Technote
