Oralce中分析表及索引
Sample :
exec dbms_stats.gather_schema_stats(ownname=>'PDM8',cascade=>True);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('TEST', CASCADE => TRUE)
SQL> exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'sxitismg',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);
SQL>execute dbms_stats.gather_schema_stats(ownname => ‘scott’,estimate_percent => 20,method_opt=>‘for all indexed columns size 10’, cascade=>true);
exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'scott',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);
GATHER_SCHEMA_STATS 下有個引數: cascade
,設定此值為true 時,oracle才對index進行分析;
dbms_stats.gather_table_stats
dbms_stats.gather_index_stats
如:
GATHER_SCHEMA_STATS 下有個引數: cascade
,設定此值為true 時,oracle才對index進行分析;
最好還是用dbms_stats.gather_index_stats來進行分析,不要一次對一個使用者下的物件進行分析,不好。寫個指令碼就行了
SQL> analyze table t3 compute statistics
for table
for all indexes
for all indexed columns size 254;
Automating histogram sampling with dbms_stats
May 14, 2003
Don Burleson
One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.
To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats. There are also important new options within the method_opt clause, namely skewonly, repeat and auto.
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
In practice, there is a specific order to use the different options of dbms_stats. See this article for details. Let’s take a close look at each method option.
The method_opt=’SKEWONLY’ dbms_stats Option
The first is the “skewonly” option which very time-intensive because it examines the distribution of values for every column within every index. If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access. For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows.
Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled. In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.
--*************************************************************
-- SKEWONLY option – Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/
The method_opt=’REPEAT’ dbms_stats Option
Following the one-time detailed analysis, the re-analyze task will be less resource intensive with the REPEAT option. Using the repeat option will only re-analyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will re-analyze you statistics on a regular basis.
--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/
The method_opt=’AUTO’ dbms_stats Option
The auto option is used when monitoring is implemented and creates histograms based upon data distribution and the manner in which the column is accessed by the application (e.g. the workload on the column as determined by monitoring, especially foreign keys to determine the cardinality of table join result sets). Using method_opt=>’auto’ is similar to using the gather auto in the option parameter of dbms_stats.
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
/
Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.
Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.
Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:
1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.
2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against
3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.
4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.
Periodically you will want to re-run the skewonly and auto option to identify any new columns that require histograms. Once located, the repeat option will ensure that they are refreshed with current values.
--------------------------------------------------------------------------------
If you like DBA internal tricks, check-out my new book Creating a Self-tuning Oracle Database by Rampant TechPress. This book is now available at this link:
Regards,
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84622/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 定期表及索引分析Oracle索引
- Oracle表與索引的分析及索引重建Oracle索引
- Oracle表與索引的分析及索引重建(轉)Oracle索引
- oracle 索引分析及索引重建Oracle索引
- Oracle表table與索引index的分析及索引重建及統計資訊匯入匯出Oracle索引Index
- oracle 表分析和索引Oracle索引
- Analyze分析表或者索引索引
- Oracle分割槽表及分割槽索引Oracle索引
- 表及索引 move tablespace 常用指令碼索引指令碼
- ORACLE分析表和索引的指令碼Oracle索引指令碼
- mongodb索引及查詢優化分析MongoDB索引優化
- 分割槽表中的區域性分割槽索引及全域性索引與執行計劃索引
- Oracle中存取資料掃描Table及索引的方式(全表掃描,索引掃描等)Oracle索引
- SQL Server中對比表數量,索引數量及procedure數量SQLServer索引
- 資料庫建表及索引規約資料庫索引
- 分割槽表及分割槽索引建立示例索引
- 分割槽表中全域性及本地分割槽索引什麼時候會失效及處理索引
- 只匯入表結構及索引的方法索引
- magento2 建立資料表及新增索引索引
- SAP中的資料庫表索引資料庫索引
- 索引在ORACLE中的應用分析索引Oracle
- oralce之 10046對Hash Join分析
- 資料庫表,索引(索引組織表,聚簇表,聚簇索引,)資料庫索引
- index_oracle索引梳理系列及分割槽表梳理IndexOracle索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(1)索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- 深入學習分割槽表及分割槽索引(1)索引
- 分割槽表中全域性及本地分割槽索引什麼時候會失效及處理[final]索引
- 索引分析索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oralce 10g中的segment type
- 關於oracle的索引重建問題及原因分析Oracle索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- 聯合索引和多個單列索引使用中的索引命中情況及索引建立原則索引
- 查詢表上的索引及對應的欄位索引
- update表中index索引列對原索引條目做什麼操作?Index索引
- PowerDesiner 15 在物理模型中建立表和索引模型索引