---------------------------------------
10g自动收集统计信息
---------------------------------------
从10g开始,Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务,用于自动收集CBO的统计信息。
这个自动任务默认情况下在工作日晚上10:00-6:00和周末全天开启。
调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计信息。
可以通过以下查询这个JOB的运行情况:
SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 'GATHER_STATS_JOB';
其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB:
SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;
JOB_NAME LAST_START_DATE
------------------------------ ------------------------------------
AUTO_SPACE_ADVISOR_JOB 30-OCT-08 10.00.01.463000 PM +08:00
GATHER_STATS_JOB 30-OCT-08 10.00.01.463000 PM +08:00
然而这个自动化功能已经影响了很多系统的正常运行,晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争,进而可能导致数据库Hang或者Crash。
所以建议最好关闭这个自动统计信息收集功能:
关闭及开启自动搜集功能,有两种方法,分别如下:
方法一:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二:
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
---------------------------------------
查看统计
---------------------------------------
表/索引/列上的统计
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
---------------------------------------
直方图统计
---------------------------------------
直方图的类型存储在*TAB_COL_STATISTICS视图的HISTOGRAM列上。
------------------------------------------------------------------------------
bde_last_analyzed.sql - Verifies CBO Statistics
------------------------------------------------------------------------------
bde_last_analyzed.sql 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 'SYS'.
The 5 generated reports bde_last_analyzed_xxx.html, present the total of tables and indexes analyzed per module and per date.
Script. bde_last_analyzed.sql provided in this Note can be used on any 8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12 instances
如果是ERP数据库,则用APPS连接,否则用其他任何SYS权限用户连接都可以
#sqlplus <user>/<pwd>
SQL> START bde_last_analyzed.sql
Review spool output files bde_last_analyzed_xxx.html 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 coe_stats.sql 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 fnd_stats.gather_schema_statistics('APPLSYS'); Where 'APPLSYS' 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 fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');
Where 'MRP' is the schema owner, and 'MRP_FORECAST_DATES' is the table name. This syntax is only for non-partitioned 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
dbms_stats.delete_table_stats(ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES');
fnd_stats.gather_table_stats (ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES',
granularity => 'DEFAULT');
end;
/
Once you fix your stats, be sure to ALWAYS use the granularity of DEFAULT for partitioned tables.
If you want to execute this bde_last_analyzed.sql script. against only one schema, modify DEF SCHEMA code line.
---------------------------------------
分区表的统计信息实例
---------------------------------------
ORATEA ORACLE的统计信息在执行SQL的过程中扮演着非常重要的作用,而且ORACLE在表的各个层次都会有不同的统计信息,通过这些统计信息来描述表的,列的各种各样的统计信息。下面通过一个复合分区表来说明一些常见的和常见的统计信息。
SQL>
create table test
partition 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))
as
select * from dba_objects;
表已创建。
sql>
BEGIN
dbms_stats.gather_table_stats(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
block_sample => FALSE,
method_opt => 'FOR ALL COLUMNS SIZE 10',
granularity => 'ALL',
cascade => TRUE);
END;
1,表级的统计信息
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 'TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
TEST 50705 788 0 0
2,表上列的统计信息
SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 'TEST';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------------------------ ------------ ----------
TEST OWNER 25 .365014295
TEST OBJECT_NAME 30275 .000039205
TEST SUBOBJECT_NAME 191 .015657993
TEST OBJECT_ID 50705 .000019722
TEST DATA_OBJECT_ID 4334 .000248075
TEST OBJECT_TYPE 42 .271207855
TEST CREATED 2305 .001608457
TEST LAST_DDL_TIME 2369 .001566737
TEST TIMESTAMP 2412 .001610251
TEST STATUS 2 .000009861
TEST TEMPORARY 2 .000009861
TEST GENERATED 2 .000009861
TEST SECONDARY 2 .000009861
13 rows selected.
3,表上列的直方图信息
SQL>
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
TEST OBJECT_ID 0 2
TEST OBJECT_ID 1 5160
TEST OBJECT_ID 2 10587
TEST OBJECT_ID 3 15658
TEST OBJECT_ID 4 20729
TEST OBJECT_ID 5 25800
TEST OBJECT_ID 6 30870
TEST OBJECT_ID 7 35940
TEST OBJECT_ID 8 41089
TEST OBJECT_ID 9 46821
TEST OBJECT_ID 10 53497
4,分区的统计信息
SQL>
select partition_name,num_rows,blocks,empty_blocks,avg_space
from user_tab_partitions
where table_name = 'TEST';
PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
--------------- ---------- ---------- ------------ ----------
P1 9581 140 0 0
P2 9973 164 0 0
P3 10000 158 0 0
P4 21151 326 0 0
5,分区上列的统计信息
SQL> select column_name,num_distinct,density,num_nulls
from user_part_col_statistics
where table_name = 'TEST'
and partition_name = 'P1';
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
--------------- ------------ ---------- ----------
OWNER 7 .000052187 0
OBJECT_NAME 7412 .000156925 0
SUBOBJECT_NAME 26 .47017301 9496
OBJECT_ID 9581 .000104373 0
DATA_OBJECT_ID 1765 .000664385 7780
OBJECT_TYPE 34 .18494854 0
CREATED 913 .001977449 0
LAST_DDL_TIME 994 .001882695 0
TIMESTAMP 982 .001928775 0
STATUS 2 .000052187 0
TEMPORARY 2 .000052187 0
GENERATED 2 .000052187 0
SECONDARY 1 .000052187 0
6,分区上列的直方图信息
SQL> select column_name,bucket_number,endpoint_value
from user_part_histograms
where table_name = 'TEST'
and partition_name = 'P1'
and column_name = 'OBJECT_ID';
COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID 0 2
OBJECT_ID 1 1005
OBJECT_ID 2 1963
OBJECT_ID 3 2921
OBJECT_ID 4 3888
OBJECT_ID 5 4859
OBJECT_ID 6 5941
OBJECT_ID 7 6899
OBJECT_ID 8 7885
OBJECT_ID 9 8864
OBJECT_ID 10 9999
7,子分区的统计信息
SQL> select subpartition_name,num_rows,blocks,empty_blocks
from user_tab_subpartitions
where table_name = 'TEST'
and partition_name = 'P1';
SUBPARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
SYS_SUBP21 3597 50 0
SYS_SUBP22 3566 52 0
SYS_SUBP23 637 11 0
SYS_SUBP24 1781 27 0
8,子分区上的列的统计信息
SQL> select column_name,num_distinct,density
from user_subpart_col_statistics
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21';
COLUMN_NAME NUM_DISTINCT DENSITY
--------------- ------------ ----------
OWNER 6 .000139005
OBJECT_NAME 3595 .000278319
SUBOBJECT_NAME 4 .014285714
OBJECT_ID 3597 .000278009
DATA_OBJECT_ID 155 .006451613
OBJECT_TYPE 8 .000139005
CREATED 751 .002392334
LAST_DDL_TIME 784 .002302524
TIMESTAMP 768 .00235539
STATUS 1 .000139005
TEMPORARY 2 .000139005
GENERATED 2 .000139005
SECONDARY 1 .000139005
9,子分区上的列的直方图信息
SQL> select column_name,bucket_number,endpoint_value
from user_subpart_histograms
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21'
and column_name = 'OBJECT_ID';
COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID 0 208
OBJECT_ID 1 1525
OBJECT_ID 2 2244
OBJECT_ID 3 2892
OBJECT_ID 4 3252
OBJECT_ID 5 4047
OBJECT_ID 6 5238
OBJECT_ID 7 6531
OBJECT_ID 8 7661
OBJECT_ID 9 8474
OBJECT_ID 10 9998
我们对这个复合分区分析之后产生了上面这九种不同层次的统计信息。CBO想要得要一个高效的执行计划需要如此多的统计信息.