【正文】 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自動收集統(tǒng)計信息從10g開始，Oracle在建庫后就默認(rèn)創(chuàng)建了一個名為GATHER_STATS_JOB的定時任務(wù)，用于自動收集CBO的統(tǒng)計信息。這個自動任務(wù)默認(rèn)情況下在工作日晚上10：006：00和周末全天開啟。該過程首先檢測統(tǒng)計信息缺失和陳舊的對象。然后確定優(yōu)先級，再開始進(jìn)行統(tǒng)計信息。可以通過以下查詢這個JOB的運(yùn)行情況：SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 39。GATHER_STATS_JOB39。其實(shí)同在10點(diǎn)運(yùn)行的Job還有一個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然而這個自動化功能已經(jīng)影響了很多系統(tǒng)的正常運(yùn)行，晚上10點(diǎn)對于大部分生產(chǎn)系統(tǒng)也并非空閑時段。而自動分析可能導(dǎo)致極為嚴(yán)重的閂鎖競爭，進(jìn)而可能導(dǎo)致數(shù)據(jù)庫Hang或者Crash。所以建議最好關(guān)閉這個自動統(tǒng)計信息收集功能：關(guān)閉及開啟自動搜集功能，有兩種方法，分別如下：方法一：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)計表/索引/列上的統(tǒng)計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)計直方圖的類型存儲在*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ù)庫,則用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)計信息實(shí)例ORATEA ORACLE的統(tǒng)計信息在執(zhí)行SQL的過程中扮演著非常重要的作用，而且ORACLE在表的各個層次都會有不同的統(tǒng)計信息，通過這些統(tǒng)計信息來描述表的，列的各種各樣的統(tǒng)計信息。下面通過一個復(fù)合分區(qū)表來說明一些常見的和常見的統(tǒng)計信息。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,表級的統(tǒng)計信息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)計信息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