ANALYZE的介紹
使用ANALYZE可以收集或洗掉物件的統計資訊、驗證物件的結構、標識表或cluster中的行遷移/行鏈接資訊等，官方檔案關于ANALYZE功能介紹如下：
· Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
· Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
· Identify migrated and chained rows of a table or cluster.
·
ANALYZE的使用
ANALYZE TABLE可以指定分析： 表、所有欄位、所有索引欄位、所有索引， 若不指定對應物件則表示全部都分析
#完全分析，采樣100%
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES;
注意：上面兩者是等價的，
#抽樣分析，采樣40%
ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 40 PERCENT;
對于大表而言，收集統計資訊相當于全表掃描，相當耗費時間和資源，可以用抽樣估演算法來收集統計資訊，這樣就可以節省時間和資源，如果不是要求要有精確資料的話，盡量采用抽樣分析法，可以指定采樣比例，
分析表統計資訊
#僅僅生成表的statistics，不生成列的直方圖資訊
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR TABLE;
分析索引欄位統計資訊
#分析了表，并僅對表上的索引列產生histograms
ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
分析指定列統計資訊
ANALYZE TABLE TEST COMPUTE STATISTICS FOR COLUMNS COLUMN_NAME1, COLUMN_NAME2;
分析所有列統計資訊
#分析表，同時生成所有列的histograms
ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL COLUMNS;
分析索引統計資訊
#指定索引分析
SQL> ANALYZE INDEX INDEX_NAME COMPUTE STATISTICS ;
#分析所有索引
SQL> ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR ALL INDEXES;
洗掉統計資訊
#洗掉統計分析資料
ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS;
#洗掉索引對應的統計資訊
ANALYZE INDEX <INDEX_NAME> DELETE STATISTICS;
#注意：下面這種語法是錯誤的，見過有些文章有介紹這種方法，
ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR TABLE;
ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR ALL INDEXES;
ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR ALL INDEXEXED COLUMNS;
#注意：TRUNCATE命令不會修改資料的統計資訊,也就是如果我們想讓CBO利用合理利用資料的統計資訊的時候,需要我們及時的使用ANALYZE命令或者dbms_stats重新統計資料的統計資訊
下面我們以具體的例子來看看ANALYZE命令會分析、收集那些統計資訊：
SQL> CREATE TABLE TEST
2 AS3 SELECT * FROM DBA_OBJECTS;
SQL> SQL> CREATE INDEX IX_TEST_N1 ON TEST( OBJECT_NAME);
執行sosi.sql檢查統計資訊情況如下所示，只有索引有相關統計資訊，
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------TEST NO NOColumn Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------OWNER VARCHAR2(30) NO NOOBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ -------- ----- ----------IX_TEST_N1 NONUNIQUE 2 359 44,089 72,408 1 1 37,058 NO NO 72,408 07-30-2018Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------IX_TEST_N1 OBJECT_NAME 1 VARCHAR2(128)***************
Partition Level***************
***************
SubPartition Level***************
SQL> ANALYZE TABLE TEST DELETE STATISTICS;
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR TABLE;
SQL>
如下截圖所示，洗掉統計資訊后，然后只分析、收集表的統計資訊(注意和后續截圖對比)
分析索引列的統計資訊
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
分析所有列的統計資訊
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL COLUMNS;
注意：對所有列都生成直方圖是沒有意義的，只有對WHERE陳述句中用到的列生成直方圖是有意義的
另外，之前在一篇博客中看到過：新執行的ANALYZE命令會抹掉之前ANALYZE的結果，但是個人測驗并沒有發現這種情況，不清楚是否跟版本有關系，個人也沒有為此大量去測驗過不同環境，
分析索引的統計資訊
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXES;
驗證索引結構
SQL> ANALYZE INDEX IX_TEST_N1 VALIDATE STRUCTURE;
Index analyzed.
驗證表結構
SQL> ANALYZE TABLE TEST VALIDATE STRUCTURE CASCADE;
Table analyzed.
ANALYZE使用注意事項
When analyzing a table, the database skips all domain indexes marked LOADING or FAILED
在收集表的統計資訊時，會跳過標記為Loading 或Failed 的域索引（全文索引），
ANALYZE的優勢
Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:
To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks
ANALYZE可以分析、收集行遷移、行鏈接的數量，以及freelist blocks數量
當某個索引處于monitoring usage的時候，如果使用DBMS_STATS去分析表并且同時分析索引，會將該索引的v$object_usage.USED 設定為TRUE，導致監控了N天的可疑索引前功近棄，如果使用ANALYZE，索引的狀態不會被設定為USE = TRUE，具體參考博客Oracle DBMS_STATS 包 和 Analyze 命令的區別
ANALYZE的限制
Analyzing tables is subject to the following restrictions:
You cannot use ANALYZE to collect statistics on data dictionary tables.
不能分析、收集資料字典表統計資訊
You cannot use ANALYZE to collect statistics on an external table. Instead, you must use the DBMS_STATS package.
不能收集、分析外部表統計資訊，只能用DBMS_STATS分析、收集外部表統計資訊
You cannot use ANALYZE to collect default statistics on a temporary table. However, if you have already created an association between one or more columns of a temporary table and a user-defined statistics type, then you can use ANALYZE to collect the user-defined statistics on the temporary table.
不能使用ANLYZE分析、收集臨時表的默認統計資訊
You cannot compute or estimate statistics for the following column types: REF column types, varrays, nested tables, LOB column types (LOB column types are not analyzed, they are skipped), LONG column types, or object types. However, if a statistics type is associated with such a column, then Oracle Database collects user-defined statistics.
不能計算或者估算如下型別列的統計資訊：REF column types, varrays, nested tables, LOB column types (LOB column types are not analyzed, they are skipped), LONG column types, or object types
You cannot analyze a domain index that is marked IN_PROGRESS or FAILED.
不能分析標記為失敗或IN_PROGRESS的域索引
ANALYZE命令不能正確地收集磁區表的統計資訊，而DBMS_STATS包卻可以，ANALYZE命令只會收集最低層次物件的統計資訊，然后推導和匯總出高一級的統計資訊，比如對于有子磁區的磁區表而言，它只會先收集子磁區統計資訊，然后再匯總，推匯出磁區或表級的統計資訊，有的統計資訊是可以從當前物件的下一級物件進行匯總后得到的，比如表的總行數，可以由各磁區的行數相加得到，但有的統計資訊則不能從下一級物件得到，比如列上的distinct值數量NUM_DISTINCT以及DESNSITY等，
不能并行收集統計資訊，
其實了解過ANALYZE命令的限制后，基本上就清楚了ANALYZE與DBMS_STATS的區別，官方也是推薦使用DBMS_STATS包來收集統計資訊，因為DBMS_STATS包收集的更廣，并且更準確，另外，自從Oracle 8i以后ANALYZE具備”VALIDATE”驗證功能，并且和DBMS_STATS包在功能上進行了劃分，ANALYZE主要負責驗證表和索引的結構以及鏈式行(chained and migrated rows)資訊，DBMS_STATS包主要負責統計資訊的管理，
參考資料：
https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ANALYZE.html#GUID-535CE98E-2359-4147-839F-DCB3772C1B0E
https://blog.csdn.net/tianlesoftware/article/details/7055233
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/18425.html
標籤：Oracle