Oracle中Analyze命令的詳細(xì)介紹

這篇文章主要講解了“Oracle中Analyze命令的詳細(xì)介紹”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Oracle中Analyze命令的詳細(xì)介紹”吧！

創(chuàng)新互聯(lián)是專業(yè)的天臺網(wǎng)站建設(shè)公司，天臺接單;提供網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站,網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行天臺網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!

使用DBMS_STATS 收集統(tǒng)計(jì)信息參考：

Oracle Statistic 統(tǒng)計(jì)信息 小結(jié)

http://blog.csdn.net/tianlesoftware/article/details/4668723

Oracle 判斷 并 手動收集 統(tǒng)計(jì)信息 腳本

http://blog.csdn.net/tianlesoftware/article/details/6445868

一.Purpose

Use the ANALYZE statement tocollect statistics, for example, to:

--使用analyze命令可以收集統(tǒng)計(jì)信息，如：

（1）Collect ordelete statistics about an index or index partition, table or table partition,index-organized table, cluster, or scalar object attribute.

--收集或刪除對象的統(tǒng)計(jì)信息

（2）Validate thestructure of an index or index partition, table or table partition, index-organizedtable, cluster, or object reference (REF).

--驗(yàn)證對象的結(jié)構(gòu)

（3）Identifymigrated and chained rows of a table or cluster.

--確定table 或cluster的migrated 和chained rows。

For thecollection of most statistics, use the DBMS_STATS package, which letsyou collect statistics in parallel, collect global statistics for partitionedobjects, and fine tune your statistics collection in other ways.

--在大多數(shù)情況下，使用DBMS_STATS 包來收集統(tǒng)計(jì)信息。

Usethe ANALYZE statement (rather than DBMS_STATS) for statisticscollection not related to the cost-based optimizer:

--在如下兩種情況下，使用analyze 命令要比dbms_stats 包好：

（1）To usethe VALIDATE or LIST CHAINED ROWS clauses

（2）To collectinformation on freelist blocks

二.Prerequisites

The schemaobject to be analyzed must be local, and it must be in your own schema or youmust have the ANALYZE ANY system privilege.

If you want tolist chained rows of a table or cluster into a list table, then the list tablemust be in your own schema, or you must have INSERT privilege on thelist table, or you must have INSERT ANY TABLE systemprivilege.

If you want tovalidate a partitioned table, then you must have the INSERT objectprivilege on the table into which you list analyzed rowids, or you must havethe INSERT ANY TABLE system privilege.

三. Syntax

 

詳細(xì)語法如下：

   ANALYZE TABLE tablenameCOMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options

   ANALYZE INDEX indexnameCOMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options

   ANALYZE CLUSTER clusternameCOMPUTE|ESTIMATE|DELETE STATISTICS options

ptnOption

   PARTITION (partion)

   SUBPARTITION (subpartition)

options

   VALIDATE STRUCTURE [CASCADE][INTO tablename]

   LIST CHAINED ROWS [INTOtablename]

   COMPUTE|ESTIMATE STATISTICSFOR TABLE

   COMPUTE|ESTIMATE STATISTICSFOR ALL COLUMNS

   COMPUTE|ESTIMATE STATISTICSFOR ALL INDEXED COLUMNS

   COMPUTE|ESTIMATE STATISTICSFOR COLUMNS [SIZE int] column [SIZE int]

   When Estimating statistics youcan optionally

    specify

    ... ESTIMATE STATISTICSSAMPLE n ROWS

    ... ESTIMATE STATISTICSSAMPLE n PERCENT

四. Semantics

4.1 schema

Specify theschema containing the table, index, or cluster. If you omit schema, thenOracle Database assumes the table, index, or cluster is in your own schema.

4.2 TABLE table

Specify a tableto be analyzed. When you analyze a table, the database collects statisticsabout expressions occurring in any function-based indexes as well. Therefore,be sure to create function-based indexes on the table before analyzing thetable. Refer to CREATEINDEX for more information about function-based indexes.

--當(dāng)使用analyze 收集表的統(tǒng)計(jì)信息時(shí)，也會自動收集函數(shù)索引的信息。

When analyzing atable, the database skips all domain indexesmarked LOADING or FAILED.

--在收集表的統(tǒng)計(jì)信息時(shí)，會跳過標(biāo)記為Loading 或Failed 的索引。

For anindex-organized table, the database also analyzes any mapping table andcalculates its PCT_ACCESSS_DIRECT statistics. These statisticsestimate the accuracy of guess data block addresses stored as part of the localrowids in the mapping table.

Oracle Databasecollects the following statistics for a table. Statistics marked with anasterisk are always computed exactly. Table statistics, including the status ofdomain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES,and DBA_TABLES in the columns shown in parentheses.

--Oracle analyze 收集表的如下統(tǒng)計(jì)信息，其中加星號的會準(zhǔn)確收集，收集的信息放在user_tables,all_tables 和 dba_tables 里的對應(yīng)字段，下面的括號內(nèi)是對應(yīng)的字段：

 （1）Number of rows (NUM_ROWS)

（2）* Number of data blocks below thehigh water mark—the number of data blocks that have been formatted to receivedata, regardless whether they currently contain data or are empty (BLOCKS)

（3）* Number of data blocks allocatedto the table that have never been used (EMPTY_BLOCKS)

（4）Average available free space in eachdata block in bytes (AVG_SPACE)

（5）Number of chained rows(CHAIN_COUNT)

（6）Average row length, including therow overhead, in bytes (AVG_ROW_LEN)

4.2.1 Restrictions on AnalyzingTables 

Analyzing tables is subject to thefollowing restrictions:

--analyze table 有如下限制：

（1）You cannotuse ANALYZE to collect statistics on data dictionary tables.

     --不能收集datadictionary tables

（2）You cannotuse ANALYZE to collect statistics on an external table. Instead, youmust use the DBMS_STATS package.

--不能收集external table， 如果要收集外部表的統(tǒng)計(jì)信息，需要使用DBMS_STATS包.

（3）You cannotuse ANALYZE to collect default statistics on a temporary table.However, if you have already created an association between one or more columnsof a temporary table and a user-defined statistics type, then you canuse ANALYZE to collect the user-defined statistics on the temporarytable.

     --不能收集臨時(shí)表的defaultstatistics.

（4）You cannotcompute or estimate statistics for the following column types:

--不用計(jì)算或者估算如下類型列的統(tǒng)計(jì)信息：

 REF columntypes, varrays, nested tables, LOB column types (LOB column types are notanalyzed, they are skipped), LONG column types, or object types.However, if a statistics type is associated with such a column, then OracleDatabase collects user-defined statistics.

4.2.2 partition_extension_clause

Specify thepartition or subpartition, or the partition or subpartition value, on which youwant statistics to be gathered. You cannot use this clause when analyzingclusters.

--可以在收集統(tǒng)計(jì)信息時(shí)指定分區(qū)，但是當(dāng)是cluster 時(shí)，則不能收集。

If youspecify PARTITION and table is composite-partitioned, thenOracle Database analyzes all the subpartitions within the specified partition.

--如果指定的分區(qū)是組合分區(qū)，那么會收集所有的子分區(qū)。

4.3 INDEX index

Specify an indexto be analyzed.

Oracle Databasecollects the following statistics for an index. Statistics marked with anasterisk are always computed exactly. For conventional indexes, when youcompute or estimate statistics, the statistics appear in the data dictionaryviews USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in thecolumns shown in parentheses.

--Analyze 收集索引的如下統(tǒng)計(jì)信息，加星號的準(zhǔn)確收集。 對于conventionalindex，收集的統(tǒng)計(jì)信息可以通過user_indexes,all_indexes或者 dba_indexes 來查看，具體對應(yīng)的字段查看括號里的內(nèi)容。

（1）* Depth of the index from its rootblock to its leaf blocks (BLEVEL)

（2）Number of leaf blocks (LEAF_BLOCKS)

（3）Number of distinct index values(DISTINCT_KEYS)

（4）Average number of leaf blocks foreach index value (AVG_LEAF_BLOCKS_PER_KEY)

（5）Average number of data blocks foreach index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)

（6）Clustering factor (how well orderedthe rows are about the indexed values) (CLUSTERING_FACTOR)

For domainindexes, this statement invokes the user-defined statistics collection functionspecified in the statistics type associated with the index (see ASSOCIATESTATISTICS). If no statistics type is associated with the domain index,then the statistics type associated with its indextype is used. If nostatistics type exists for either the index or its indextype, then nouser-defined statistics are collected. User-defined index statistics appear inthe STATISTICS column of the data dictionaryviews USER_USTATS, ALL_USTATS, and DBA_USTATS.

--對于domain indexes，收集user-defined indexstatistics 信息，可以通過user_ustats,all_ustats 和dba_ustats 來查看。

官網(wǎng)對domain index 的解釋：

A domain index is an index designed for a specializeddomain, such as spatial or image processing. Users can build a domain index ofa given type after the designer creates the indextype.The behavior. of domain indexes is specific to an industry, a business function,or some other special purpose; you must specify it during cartridgedevelopment.

http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/dom_idx.htm#ADDCI4409

Restriction on AnalyzingIndexes 

You cannotanalyze a domain index that is marked IN_PROGRESS or FAILED.

            --不能收集被標(biāo)記為in_progress 和 failed的domainindex 統(tǒng)計(jì)信息。

Note:

When you analyzean index from which a substantial number of rows has been deleted, OracleDatabase sometimes executes a COMPUTE statistics operation (which canentail a full table scan) even if you request an ESTIMATE statisticsoperation. Such an operation can be quite time consuming.

--當(dāng)我們收集統(tǒng)計(jì)信息時(shí)，如果表或索引上有大量的數(shù)據(jù)被刪除，那么如果采用compute或者 estimage 來收集，可以會進(jìn)行full table scan，因此會使用很多的時(shí)間。示例：

 SYS@anqing1(rac1)> analyze table ttestimate statistics;

Table analyzed.

SYS@anqing1(rac1)> analyze table ttcompute statistics;

Table analyzed.

SYS@anqing1(rac1)> analyze indexidx_tt_id compute statistics;

Index analyzed.

SYS@anqing1(rac1)> analyze indexidx_tt_id estimate statistics;

Index analyzed.

4.4 CLUSTER cluster

Specify acluster to be analyzed. When you collect statistics for a cluster, OracleDatabase also automatically collects the statistics for all the tables in thecluster and all their indexes, including the cluster index.

--當(dāng)收集cluster 的信息時(shí)，DB 會自動收集cluster所有表和索引的統(tǒng)計(jì)信息，包括cluster index。

For both indexedand hash clusters, the database collects the average number of data blockstaken up by a single cluster key (AVG_BLOCKS_PER_KEY). These statistics appearin the data dictionary viewsALL_CLUSTERS, USER_CLUSTERS,and DBA_CLUSTERS.

4.5 validation_clauses

The validationclauses let you validate REF values and the structure of the analyzedobject.

4.5.1VALIDATE REF UPDATE Clause

Specify VALIDATE REF UPDATE tovalidate the REF values in the specified table, check the rowidportion in each REF, compare it with the true rowid, and correct it, ifnecessary. You can use this clause only when analyzing a table.

            --指定validate ref update來驗(yàn)證ref的值，檢查時(shí)會那每個(gè)ref 的rowid 于表真實(shí)的rowid 進(jìn)行比較，如果不一致，就修改ref 的值。

If the owner ofthe table does not have SELECT object privilege on the referencedobjects, then Oracle Database will consider them invalid and set them to null.Subsequently these REF values will not be available in a query, evenif it is issued by a user with appropriate privileges on the objects.

            --如果表的用戶沒有selectreferenced 對象的權(quán)限，那么DB 會認(rèn)為他們是無效，并設(shè)置為空，因?yàn)樵谥蟮牟樵冎?，ref 值就不可用，即使用戶有權(quán)限的用戶重新發(fā)布了ref values。

4.5.2 SET DANGLING TO NULL  

SET DANGLING TO NULL setsto null any REF values (whether or not scoped) in the specified tablethat are found to point to an invalid or nonexistent object.

4.5.3 VALIDATE STRUCTURE

Analyze 的語法如下：

ANALYZE INDEX <index_name>

[PARTITION <partition_name>]

[SUBPARTITION <subpartition_name>]

VALIDATE STRUCTURE CASCADE

INTO <table_name> <OFFLINE |ONLINE>

select * from index_stats;

ANALYZE TABLE <table_name>

[PARTITION <partition_name>]

[SUBPARTITION <subpartition_name>]

VALIDATE STRUCTURE CASCADE

[INTO <table_name>] <OFFLINE |ONLINE>

ANALYZE CLUSTER <cluster_name>VALIDATE STRUCTURE CASCADE

INTO <table_name> <OFFLINE |ONLINE>

Specify VALIDATE STRUCTURE tovalidate the structure of the analyzed object. The statistics collected by thisclause are not used by the Oracle Database optimizer.

（1）For a table,Oracle Database verifies the integrity of each of the data blocks and rows. Foran index-organized table, the database also generates compression statistics(optimal prefix compression count) for the primary key index on the table.

--對于table，DB 驗(yàn)證每個(gè)datablock 和rows 的完整性。 對于index-organized table，db 也生成主鍵索引的compressionstatistics。

（2）For acluster, Oracle Database automatically validates the structure of the clustertables.

--對于cluster，db 自動驗(yàn)證clustertable 的structure。

（3）For apartitioned table, Oracle Database also verifies that each row belongs to thecorrect partition. If a row does not collate correctly, then its rowid isinserted into the INVALID_ROWS table.

--對于分區(qū)表，db 驗(yàn)證每個(gè)row 是否屬于正確的分區(qū)，如果row 沒有效驗(yàn)正確，那么這個(gè)rowid 會插入invalid_rows 表。

（4）For atemporary table, Oracle Database validates the structure of the table and itsindexes during the current session.

--對于臨時(shí)表，db 會驗(yàn)證其structure 和 current session 期間的索引。

（5）For an index,Oracle Database verifies the integrity of each data block in the index andchecks for block corruption. This clause does not confirm that each row in thetable has an index entry or that each index entry points to a row in the table.You can perform. these operations by validating the structure of the table withthe CASCADE clause.

--對于索引，db 驗(yàn)證每個(gè)索引block 的完整性和block 是否損壞。 這個(gè)命令不會確實(shí)每個(gè)表的row 是否和索引的row 匹配。

Oracle Databasealso computes compression statistics (optimal prefix compression count) for allnormal indexes.

Oracle Databasestores statistics about the index in the data dictionaryviews INDEX_STATS and INDEX_HISTOGRAM.

            --Oracle 存儲索引相關(guān)的statistics 在Index_stats和 index_histogram里。

If OracleDatabase encounters corruption in the structure of the object, then an errormessage is returned. In this case, drop and re-create the object.

--如果DB 在對象的structure里遇到corruption，那么會返回error，這種情況下需要drop和re-create 對象。

4.5.3.1 INTO 

The INTO clauseof VALIDATE STRUCTURE is valid only for partitioned tables.Specify a table into which Oracle Database lists the rowids of the partitionswhose rows do not collate correctly. If you omit schema, then the databaseassumes the list is in your own schema. If you omit this clause altogether,then the database assumes that the table is named INVALID_ROWS. The SQLscript. used to create this table is UTLVALID.SQL.

--into clause 僅對分區(qū)表有效。指定一個(gè)表來存放效驗(yàn)不正確的數(shù)據(jù)，如果忽略用戶，則認(rèn)為是的當(dāng)前的用戶，如果忽略表，則默認(rèn)會認(rèn)為表示INVALID_ROWS。創(chuàng)建這個(gè)表的sql 腳本是： $ORACLE_HOME/rdbms/admin/utlvalid.sql

[oracle@rac1 admin]$ cat utlvalid.sql

create tableINVALID_ROWS (

  owner_name         varchar2(30),

  table_name         varchar2(30),

  partition_name     varchar2(30),

  subpartition_name  varchar2(30),

  head_rowid         rowid,

  analyze_timestamp  date

);

4.5.3.2 CASCADE 

Specify CASCADE ifyou want Oracle Database to validate the structure of the indexes associatedwith the table or cluster. If you use this clause when validating a table, thenthe database also validates the indexes defined on the table. If you use thisclause when validating a cluster, then the database also validates all thecluster tables indexes, including the cluster index.

--當(dāng)指定cascade 后，DB 在驗(yàn)證對象時(shí)也會關(guān)聯(lián)其他的相關(guān)的對象，如索引關(guān)聯(lián)表，表就關(guān)聯(lián)索引。 如果是驗(yàn)證cluster，就關(guān)聯(lián)cluster tables 和indexes，包含cluster index。

Bydefault, CASCADE performs a COMPLETE validation, which canbe resource intensive. Specify FAST if you want the database to checkfor the existence of corruptions without reporting details about thecorruption. If the FAST check finds a corruption, you can then usethe CASCADE option without the FAST clause to locate andlearn details about it.

--默認(rèn)情況下，cascade 執(zhí)行的是complete validation，如果我們指定FAST，那么只檢查已經(jīng)存在的corruptions，并且不報(bào)告corruption的詳細(xì)信息。 如果FAST check 發(fā)現(xiàn)了corruption，那么可以使用cascade查看詳細(xì)信息。

If you use thisclause to validate an enabled (but previously disabled) function-based index,then validation errors may result. In this case, you must rebuild the index.

4.5.3.3 ONLINE | OFFLINE 

Specify ONLINE toenable Oracle Database to run the validation while DML operations are ongoingwithin the object. The database reduces the amount of validation performed toallow for concurrency.

--指定online 后可以在DML時(shí)進(jìn)行validate操作，但是這樣會降低validation 的性能。

Note:

When youvalidate the structure of an object ONLINE, Oracle Database does notcollect any statistics, as it does when you validate the structure of theobject OFFLINE.

            --這里要注意，在前面我們提到當(dāng)進(jìn)行validate 時(shí)會將收集的信息存放到Index_stats 和index_histogram里，如果我們指定ONLINE，則不會將statistics 信息寫入index_stats視圖。

Specify OFFLINE,to maximize the amount of validation performed. This settingprevents INSERT, UPDATE, and DELETE statements fromconcurrently accessing the object during validation but allows queries. This isthe default.

            --OFFLINE 是默認(rèn)值，這種也是性能最好的。 但是offline在驗(yàn)證期間會阻止對象的insert，update 和 delete操作。

所以對于在線業(yè)務(wù)可以考慮使用validatestructure online在線驗(yàn)證方法，但是validate strucutre online也有它的缺點(diǎn)，那就是online下結(jié)構(gòu)驗(yàn)證信息不會寫入index_stats 和 index_histogram 里。

4.5.3.4 Restriction on ONLINE

 You cannotspecify ONLINE when analyzing a cluster.

--online 不能使用于cluster 對象。

4.6 LIST CHAINED ROWS

有關(guān)chained rows的詳細(xì)內(nèi)容，下節(jié)在說明，先看該命令語法：

ANALYZE TABLE <table_name> LISTCHAINED ROWS

INTO <table_name>;

ANALYZE CLUSTER <cluster_name> LISTCHAINED ROWS INTO <table_name>;

LIST CHAINED ROWS letsyou identify migrated and chained rows of the analyzed table or cluster. Youcannot use this clause when analyzing an index.

--不能在索引上使用。

Inthe INTO clause, specify a table into which Oracle Database lists themigrated and chained rows. If you omit schema, then the database assumesthe chained-rows table is in your own schema. If you omit this clausealtogether, then the database assumes that the table isnamed CHAINED_ROWS. The chained-rows table must be on your local database.

執(zhí)行該命令時(shí)通過into 來指定存放有鏈化數(shù)據(jù)的表，如果沒有指定表，db 會假設(shè)表名為CHAINED_ROWS。

You can createthe CHAINED_ROWS table using one of these scripts:

--默認(rèn)情況下沒有創(chuàng)建這個(gè)表，我們要先創(chuàng)建這個(gè)表之后才能存放數(shù)據(jù)：

（1）UTLCHAIN.SQL usesphysical rowids. Therefore it can accommodate rows from conventional tables butnot from index-organized tables. (See the Note that follows.)

（2）UTLCHN1.SQL usesuniversal rowids, so it can accommodate rows from both conventional andindex-organized tables.

--可以使用上面的任一腳本來創(chuàng)建chained_rows 表。

If you create your own chained-rows table, then it must follow the format prescribed by oneof these two scripts.

If you areanalyzing index-organized tables based on primary keys (rather than universalrowids), then you must create a separate chained-rows table for eachindex-organized table to accommodate its primary-key storage.

Use the SQLscripts DBMSIOTC.SQL and PRVTIOTC.PLB to definethe BUILD_CHAIN_ROWS_TABLE procedure, and then execute this procedureto create an IOT_CHAINED_ROWS table for each such index-organizedtable.

4.7 DELETE STATISTICS

Specify DELETE STATISTICS todelete any statistics about the analyzed object that are currently stored inthe data dictionary. Use this statement when you no longer want Oracle Databaseto use the statistics.

When you usethis clause on a table, the database also automatically removes statistics forall the indexes defined on the table. When you use this clause on a cluster,the database also automatically removes statistics for all the cluster tablesand all their indexes, including the cluster index.

--當(dāng)我們delete 表的statistics 時(shí)，DB 會自動remove表上所有索引statistics。

Specify SYSTEM ifyou want Oracle Database to delete only system (not user-defined) statistics.If you omit SYSTEM, and if user-defined column or index statistics werecollected for an object, then the database also removes the user-definedstatistics by invoking the statistics deletion function specified in the statisticstype that was used to collect the statistics.

五．Chained Rows相關(guān)說明

            每個(gè)Block的大小都是有限的，當(dāng)單個(gè)數(shù)據(jù)塊沒有足夠的空間來保存新建的一行記錄或者更新的某行記錄時(shí)，有兩種解決方法：rowchaining和rowmigration。

Row chaining 就是我們說的chained rows，即鏈化現(xiàn)象。

在我之前的blog：

Oracle 數(shù)據(jù)塊 Block 說明

http://blog.csdn.net/tianlesoftware/article/details/6414765

中的9.5 小節(jié)有說明： Chained and MigratedRows

Row chaining：

 

Row migration：

 

When a row ischained or migrated, the I/O needed to retrieve the data increases. Thissituation results because Oracle Database must scan multiple blocks to retrievethe information for the row.

         當(dāng)產(chǎn)生大量的chained 或者 migrated 時(shí)，對會對I/O 產(chǎn)生影響。訪問這些數(shù)據(jù)的速度就會開始變慢，因?yàn)轭~外的i/o以及與i/o相關(guān)的閂定都會增加訪問時(shí)間，緩沖區(qū)緩存的效率開始下降，因?yàn)樾枰彺鎯蓚€(gè)塊，而如果沒有行遷移只需要緩存一個(gè)塊。另外表的大小和復(fù)雜性都有所增加。

通過調(diào)整PCTFREE參數(shù)，可以有效避免鏈化現(xiàn)象。根據(jù)段空間管理方法的不同，相關(guān)的參數(shù)也不一樣，默認(rèn)表空間使用  Locallymanaged tablespaces ，其又分utomatic segment space management (ASSM) 和manual segment space management(MSSM). 這部分內(nèi)容參考：

Oracle 自動段空間管理(ASSM:autosegment space management)

http://blog.csdn.net/tianlesoftware/article/details/4958989

默認(rèn)情況下行遷移是禁止的，可以通過如下SQL 驗(yàn)證：

SYS@anqing1(rac1)> select d.row_movement from  dba_tables d where table_name='TT';

ROW_MOVE

--------

DISABLED

啟用行遷移：

SQL>alter table table_name enable row movement;

必須在啟用行遷移的情況下才能使用Shrink 和FlashbackTable，這部分具體參考：

Oracle 10g Shrink Table 詳解

http://blog.csdn.net/tianlesoftware/article/details/4764254

Oracle Flashback 技術(shù) 總結(jié)

http://blog.csdn.net/tianlesoftware/article/details/4677378

既然chained row 對性能影響很大，我們就需要監(jiān)控它。這個(gè)就是我們的analyze 的作用所在。 但是收集到有chaining的row 需要保存到一個(gè)表里。 等我們analyze 完之后查看這個(gè)表就知道有多少chaining的數(shù)據(jù)了。

         而且在我們執(zhí)行analyze 命令之前，必須先創(chuàng)建這個(gè)表，否則就會報(bào)錯(cuò)。我們可以使用 $ORACLE_HOME/rdbms/admin/utlchain.sql 來創(chuàng)建這個(gè)表：

[root@rac1 ~]# cd $ORACLE_HOME/rdbms/admin

[root@rac1 admin]# cat utlchain.sql

create table CHAINED_ROWS (

 owner_name         varchar2(30),

 table_name         varchar2(30),

 cluster_name       varchar2(30),

 partition_name     varchar2(30),

 subpartition_name  varchar2(30),

 head_rowid         rowid,

 analyze_timestamp  date

);

創(chuàng)建完了就可以執(zhí)行analyze命令：

ANALYZE TABLE <table_name> LISTCHAINED ROWS

INTO <table_name>;

ANALYZE CLUSTER <cluster_name> LISTCHAINED ROWS INTO <table_name>;

六. 示例

6.1 驗(yàn)證Chained row

--沒有創(chuàng)建chained_rows表，這個(gè)是默認(rèn)的表名：

SYS@anqing1(rac1)> select * from CHAINED_ROWS;

select * from CHAINED_ROWS

              *

ERROR at line 1:

ORA-00942: table or view does not exist

--在沒有創(chuàng)建chained_row表的情況下，執(zhí)行analyze 失?。?/p>

SYS@anqing1(rac1)> Analyze table tt list chained rows;

Analyze table tt list chained rows

*

ERROR at line 1:

ORA-01495: specified chain row table notfound

--創(chuàng)建表，在執(zhí)行Analyze命令：

SYS@anqing1(rac1)>@?/rdbms/admin/utlchain.sql

SYS@anqing1(rac1)> Analyze table tt listchained rows;

Table analyzed.

--然后查詢chained_row表：

SYS@anqing1(rac1)> select * fromCHAINED_ROWS;

no rows selected

--我這里是測試表，沒有任何數(shù)據(jù)。官網(wǎng)的一個(gè)測試數(shù)據(jù)如下：

SELECT owner_name, table_name, head_rowid,analyze_timestamp

   FROM chained_rows

   ORDER BY owner_name, table_name, head_rowid, analyze_timestamp;

OWNER_NAME TABLE_NAME  HEAD_ROWID         ANALYZE_TIMESTAMP

---------- ----------  -----------------------------------

OE         ORDERS      AAAAZzAABAAABrXAAA25-SEP-2000

6.2 驗(yàn)證 structure

對于普通表直接執(zhí)行命令即可：

SYS@anqing1(rac1)> analyze table ttvalidate structure;

Table analyzed.

--指定cascade和 online：

SYS@anqing1(rac1)> analyze table ttvalidate structure cascade online;

Table analyzed.

--對于分區(qū)表， db 驗(yàn)證每個(gè)row 是否屬于正確的分區(qū)，如果row 沒有效驗(yàn)正確，那么這個(gè)rowid 會插入invalid_rows 表。

--默認(rèn)情況下invalid_rows表也沒有創(chuàng)建，其創(chuàng)建腳本是：

$ORACLE_HOME/rdbms/admin/utlvalid.sql

有關(guān)分區(qū)表參考：

Oracle 分區(qū)表 總結(jié)

http://blog.csdn.net/tianlesoftware/article/details/4717318

我們創(chuàng)建了一個(gè)分區(qū)表pt。

SYS@anqing1(rac1)> analyze table ptvalidate structure;

analyze table pt validate structure

*

ERROR at line 1:

ORA-14508: specified VALIDATE INTO tablenot found

--對于分區(qū)表的驗(yàn)證就必須指定into 和invalid_rows.

SYS@anqing1(rac1)>@?/rdbms/admin/utlvalid.sql

SYS@anqing1(rac1)> analyze table ptvalidate structure into invalid_rows;

Table analyzed.

SYS@anqing1(rac1)> analyze table ptvalidate structure cascade online into invalid_rows;

Table analyzed.

--analyze 完成之后查看invalid_rows表即可：

SYS@anqing1(rac1)> select * frominvalid_rows;

no rows selected

6.3 刪除統(tǒng)計(jì)信息

SYS@anqing1(rac1)> analyze table ttdelete statistics;

Table analyzed.

--該命令會刪除表和其所有索引的statistics。

小結(jié)：

從Oracle 8i以后analyze具備”validate”驗(yàn)證功能，并且和DBMS_STATS包在功能上進(jìn)行了劃分，analyze 主要負(fù)責(zé)驗(yàn)證表和索引的結(jié)構(gòu)以及鏈?zhǔn)叫?chained and migrated rows)信息，DBMS_STATS包主要負(fù)責(zé)統(tǒng)計(jì)信息的管理。

          在以上的說明和測試這塊也是花了很多的篇幅來說明analyze 在validate 和 list chained rows上的用法。

在開篇提到在如下兩種情況下Analyze的效果要比DBMS_STATS包好。

（1）To usethe VALIDATE or LIST CHAINED ROWS clauses

（2）To collectinformation on freelist blocks

感謝各位的閱讀，以上就是“Oracle中Analyze命令的詳細(xì)介紹”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Oracle中Analyze命令的詳細(xì)介紹這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！

當(dāng)前文章：Oracle中Analyze命令的詳細(xì)介紹
文章URL：http://bm7419.com/article10/jdiego.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、小程序開發(fā)、微信小程序、ChatGPT、網(wǎng)站維護(hù)、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)