查看某个表的统计信息
SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2');
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T1 2000 30 2017-07-16 14:02:23
T2 2000 30 2017-07-16 14:02:23
查看某个表上索引的统计信息
SQL> select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name in ('T1','T2');
TABLE_NAME INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
-------------- ---------------------- ---------- ---------- ----------- -------------------
T1 IDX_T1_OBJ_ID 1 2000 5 2017-07-16 12:06:33
T2 IDX_T2_OBJ_ID 1 2000 5 2017-07-16 14:02:23
T2 IDX_T2_OBJ_TYPE 1 2000 5 2017-07-16 14:02:23
T2 IDX_T2_OBJ_NAME 1 2000 8 2017-07-16 14:02:23
T2 IDX_T2_DATA_OBJ_ID 1 1198 3 2017-07-16 14:02:23
T2 IDX_T2_STATUS 1 2000 5 2017-07-16 14:02:23
T2 IDX_T2_CREATED 1 2000 6 2017-07-16 14:02:23
T2 IDX_T2_LAST_DDL_TIME 1 2000 6 2017-07-16 14:02:23
8 rows selected.
查看统计信息的脚本,来源于MOS:SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)
脚本下载地址参考文章:https://blog.csdn.net/u010692693/article/details/103120646
下面是脚本执行输出结果示例:
***********
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
--------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
T1 5,000 20 0 0 0 9 YES NO 5,000 06-30-2003
Column Column Distinct Number Global User Sample Date
Name Details Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ -------- ------- ------- ---------- ------ ------ ---------- ----------
T1C1 NUMBER(22) 5,000 0 1 0 YES NO 5,000 06-30-2003
T1C2 NUMBER(22) 7 0 1 0 YES NO 5,000 06-30-2003
T1C3 NUMBER(22) 8 0 1 0 YES NO 5,000 06-30-2003
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
--------------- --------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ----------
UN_T1 NONUNIQUE 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
UN_T1 T1C1 1 NUMBER(22)
***************
Partition Level
***************
Partition Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
Q1 2,144 10 0 0 0 9 YES NO 2,144 06-30-2003
Q2 2,856 10 0 0 0 9 YES NO 2,856 06-30-2003
Partition Column Distinct Number Global User Sample Date
Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY
--------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ----------
Q1 T1C1 2,144 0 1 0 YES NO 2,144 06-30-2003
T1C2 3 0 1 0 YES NO 2,144 06-30-2003
T1C3 8 0 1 0 YES NO 2,144 06-30-2003
Q2 T1C1 2,856 0 1 0 YES NO 2,856 06-30-2003
T1C2 4 0 1 0 YES NO 2,856 06-30-2003
T1C3 8 0 1 0 YES NO 2,856 06-30-2003
B Average Average
Index Partition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ----------
UN_T1 Q1 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003
UN_T1 Q2 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003
***************
SubPartition Level
***************
Partition SubPartition Number Empty Average Chain Average Global User Sample Date
Name Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
Q1 SYS_SUBP497 803 5 0 0 0 9 YES NO 803 06-30-2003
Q2 SYS_SUBP499 1,072 5 0 0 0 9 YES NO 1,072 06-30-2003
Q1 SYS_SUBP498 1,341 5 0 0 0 9 YES NO 1,341 06-30-2003
Q2 SYS_SUBP500 1,784 5 0 0 0 9 YES NO 1,784 06-30-2003
Partition SubPartition Column Distinct Number Global User Sample Date
Name Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY
--------------- --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ----------
Q1 SYS_SUBP497 T1C1 803 0 1 0 YES NO 803 06-30-2003
SYS_SUBP498 T1C1 1,341 0 1 0 YES NO 1,341 06-30-2003
SYS_SUBP497 T1C2 3 0 1 0 YES NO 803 06-30-2003
SYS_SUBP498 T1C2 3 0 1 0 YES NO 1,341 06-30-2003
SYS_SUBP497 T1C3 3 0 1 0 YES NO 803 06-30-2003
SYS_SUBP498 T1C3 5 0 1 0 YES NO 1,341 06-30-2003
Q2 SYS_SUBP499 T1C1 1,072 0 1 0 YES NO 1,072 06-30-2003
SYS_SUBP500 T1C1 1,784 0 1 0 YES NO 1,784 06-30-2003
SYS_SUBP499 T1C2 4 0 1 0 YES NO 1,072 06-30-2003
SYS_SUBP500 T1C2 4 0 1 0 YES NO 1,784 06-30-2003
SYS_SUBP499 T1C3 3 0 1 0 YES NO 1,072 06-30-2003
SYS_SUBP500 T1C3 5 0 1 0 YES NO 1,784 06-30-2003
B Average Average
Index Partition SubPartition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYY
--------------- --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------
UN_T1 Q1 SYS_SUBP497 1 2 803 803 1 1 2 YES NO 803 06-30-200
UN_T1 SYS_SUBP498 1 3 1,341 1,341 1 1 3 YES NO 1,341 06-30-200
UN_T1 Q2 SYS_SUBP499 1 3 1,072 1,072 1 1 3 YES NO 1,072 06-30-200
UN_T1 SYS_SUBP500 1 4 1,784 1,784 1 1 4 YES NO 1,784 06-30-200
oracle会在一个固定的时间将数据库里的表和索引的相关统计信息进行收集,默认选择周一到周五晚上10点,持续收集4小时,和周六周日早上6点,持续收集20小时。
oracle可以专门对表的记录变化量进行管理,当某表一天记录变化量没有超过指定的阀值时,oracle就不会对该表进行统计信息收集。
修改统计信息自动收集时间
SQL> set linesize 200
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a30
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
2 where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------ ------------------------------------------------------------ ---------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.
关闭自动统计信息收集
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SATURDAY_WINDOW"',
force => TRUE);
END;
/
修改自动统计信息持续时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(240,'minute'));
END;
/
修改自动统计信息开始时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0 ');
END;
/
开启自动统计信息收集
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SATURDAY_WINDOW"');
END;
/
SQL> set linesize 200
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a30
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
2 where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
----------------- ------------------------------------------------------------ --------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.
手动收集统计信息
收集表统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns');
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TAB_NAME',CASCADE=>TURE);
收集分区表的某个分区统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'RANGE_PART_TAB',partname => 'p_201312',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'USER',indname => 'IDX_OBJECT_ID',estimate_percent => '10',degree => '4');
收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
收集某个用户的统计信息
exec dbms_stats.gather_schema_stats(ownname=>'CS',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
收集整个数据库的统计信息
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
关于dbms_stats中参数的解释,具体参考官方文档:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582
ownname: USER_NAME
tabname: TABLE_NAME
partname: 分区表的某个分区名
estimate_percent: 采样百分比,有效范围为[0.000001,100]
block_sample:使用随机块采样代替随机行采样
method_opt:
cascade:是否收集此表索引的统计信息
degree:并行处理的cpu数量
granularity: 统计数据的收集,'ALL' - 收集所有(子分区,分区和全局)统计信息
动态采集统计信息
对于新创建的表,当访问此表时,oracle会动态的收集这个表的相关信息,等到晚上10点,再将其收集到数据字典中。
SQL> set autotrace off
SQL> set linesize 1000
SQL> drop table t_sample purge;
drop table t_sample purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t_sample as select * from dba_objects;
Table created.
SQL> create index idx_t_sample_objid on t_sample(object_id);
Index created.
新建的表,查不到统计信息
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select * from t_sample where object_id=20;
Execution Plan
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
93 consistent gets
1 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- dynamic sampling used for this statement (level=2) 表示动态采样,但是不记录数据字典,除非手动收集表的统计信息。
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL>