【正文】 wner39。, tabname = 39。table_name39。 , estimate_percent = null , method_opt = 39。for all indexed columns39。 , cascade = true)。GATHER_INDEX_STATS==========================BEGIN (OwnName = 39。ABC39。, IndName = 39。IDX_FUNC_ABC39。, Estimate_Percent = 10, Degree = , No_Invalidate = FALSE)。END。10g自動(dòng)收集統(tǒng)計(jì)信息從10g開始，Oracle在建庫(kù)后就默認(rèn)創(chuàng)建了一個(gè)名為GATHER_STATS_JOB的定時(shí)任務(wù)，用于自動(dòng)收集CBO的統(tǒng)計(jì)信息。這個(gè)自動(dòng)任務(wù)默認(rèn)情況下在工作日晚上10：006：00和周末全天開啟。該過(guò)程首先檢測(cè)統(tǒng)計(jì)信息缺失和陳舊的對(duì)象。然后確定優(yōu)先級(jí)，再開始進(jìn)行統(tǒng)計(jì)信息?？梢酝ㄟ^(guò)以下查詢這個(gè)JOB的運(yùn)行情況：SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 39。GATHER_STATS_JOB39。其實(shí)同在10點(diǎn)運(yùn)行的Job還有一個(gè)AUTO_SPACE_ADVISOR_JOB：SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs。JOB_NAME LAST_START_DATE AUTO_SPACE_ADVISOR_JOB 30OCT08 PM +08:00GATHER_STATS_JOB 30OCT08 PM +08:00然而這個(gè)自動(dòng)化功能已經(jīng)影響了很多系統(tǒng)的正常運(yùn)行，晚上10點(diǎn)對(duì)于大部分生產(chǎn)系統(tǒng)也并非空閑時(shí)段。而自動(dòng)分析可能導(dǎo)致極為嚴(yán)重的閂鎖競(jìng)爭(zhēng)，進(jìn)而可能導(dǎo)致數(shù)據(jù)庫(kù)Hang或者Crash。所以建議最好關(guān)閉這個(gè)自動(dòng)統(tǒng)計(jì)信息收集功能：關(guān)閉及開啟自動(dòng)搜集功能，有兩種方法，分別如下：方法一：exec (39。39。)。exec (39。39。)。方法二：alter system set _optimizer_autostats_job=false scope=spfile。alter system set _optimizer_autostats_job=true scope=spfile。查看統(tǒng)計(jì)表/索引/列上的統(tǒng)計(jì)DBA_TABLESDBA_OBJECT_TABLESDBA_TAB_STATISTICSDBA_TAB_COL_STATISTICSDBA_TAB_HISTOGRAMSDBA_INDEXESDBA_IND_STATISTICSDBA_CLUSTERSDBA_TAB_PARTITIONSDBA_TAB_SUBPARTITIONSDBA_IND_PARTITIONSDBA_IND_SUBPARTITIONSDBA_PART_COL_STATISTICSDBA_PART_HISTOGRAMSDBA_SUBPART_COL_STATISTICSDBA_SUBPART_HISTOGRAMS直方圖統(tǒng)計(jì)直方圖的類型存儲(chǔ)在*TAB_COL_STATISTICS視圖的HISTOGRAM列上。 Verifies CBO Statistics verifies the CBO statistics in the data dictionary for all tables, indexes, and partitions. It also validates the statistics on tables and indexes owned by 39。SYS39。.The 5 generated reports , present the total of tables and indexes analyzed per module and per date.Script. provided in this Note can be used on any 8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12 instances如果是ERP數(shù)據(jù)庫(kù),則用APPS連接，否則用其他任何SYS權(quán)限用戶連接都可以sqlplus user/pwd SQL START Review spool output files files. Spool files get created on same directory from which this script. is executed. On NT, files may get created under $ORACLE_HOME/bin.If some modules have not been analyzed, or they have but not recently, these Apps objects must be analyzed using FND_STATS or if belonging to Oracle Apps. Otherwise use DBMS_STATS.If Oracle Apps, use corresponding concurrent program with an estimate of 10%, or execute equivalent FND_STATS procedure from SQL*Plus:SQL exec (39。APPLSYS39。)。 Where 39。APPLSYS39。 is the module (schema) that requires new statistics.If only a few tables require to have their statistics gathered, use the corresponding concurrent program to gather stats by table, or execute equivalent FND_STATS procedure from SQL*Plus:SQL exec (39。MRP39。,39。MRP_FORECAST_DATES39。)。Where 39。MRP39。 is the schema owner, and 39。MRP_FORECAST_DATES39。 is the table name. This syntax is only for nonpartitioned Tables.If any Partitioned Table requires its Global Stats being rebuilt, it is because at some point you gathered Stats on the table using a granularity of PARTITION. See second method below:begin(ownname = 39。APPLSYS39。, tabname = 39。WF_ITEM_ACTIVITY_STATUSES39。)。 (ownname = 39。APPLSYS39。, tabname = 39。WF_ITEM_ACTIVITY_STATUSES39。, granularity = 39。DEFAULT39。)。end。/Once you fix your stats, be sure to ALWAYS use the granularity of DEFAULT for partitioned tables.If you want to execute this script. against only one schema, modify DEF SCHEMA code line.分區(qū)表的統(tǒng)計(jì)信息實(shí)例ORATEA ORACLE的統(tǒng)計(jì)信息在執(zhí)行SQL的過(guò)程中扮演著非常重要的作用，而且ORACLE在表的各個(gè)層次都會(huì)有不同的統(tǒng)計(jì)信息，通過(guò)這些統(tǒng)計(jì)信息來(lái)描述表的，列的各種各樣的統(tǒng)計(jì)信息。下面通過(guò)一個(gè)復(fù)合分區(qū)表來(lái)說(shuō)明一些常見的和常見的統(tǒng)計(jì)信息。SQLcreate table testpartition by range(object_id)subpartition by hash(object_type) subpartitions 4(partition p1 values less than(10000),partition p2 values less than(20000),partition p3 values less than(30000),partition p4 values less than(maxvalue))asselect * from dba_objects。表已創(chuàng)建。sqlBEGIN(ownname = 39。SCOTT39。, tabname = 39。TEST39。, estimate_percent = 100, block_sample = FALSE, method_opt = 39。FOR ALL COLUMNS SIZE 1039。, granularity = 39。ALL39。, cascade = TRUE)。END。1,表級(jí)的統(tǒng)計(jì)信息SQL select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 39。TEST39。TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE TEST 50705 788 0 02,表上列的統(tǒng)計(jì)信息SQL select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 39。TEST39。TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY TEST OWNER 2