对于Oracle analyze table的使用总结 . 对于Oracle analyze table的使用总结 .

分析当前用户下所有表的记录总数 保证好用! 复制代码 代码如下: begin
dbms_utility.analyze_schema; end; select
t.table_name,t.num_rows,t.blocks,t.empty_blocks from user_tables t

高水位线下的许多数据块都是无数据的,但全表扫描的时候要扫描到高水位线的数据块,也就是说oracle要做许多的无用功!因此oracle提供了shrink
space碎片整理功能。对于索引,可以采取rebuild
online的方式进行碎片整理,一般来说,经常进行DML操作的对象DBA要定期进行维护,同时注意要及时更新统计信息!一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M

对于Oracle analyze table的使用总结 . 对于Oracle analyze table的使用总结 .

analyze table 一般可以指定分析: 表,所有字段,所有索引字段,所有索引。
若不指定则全部都分析。

SQL> analyze table my_table compute statistics;  

SQL> analyze table my_table compute statistics for table
for all indexes for all columns;   

SQL> analyze table my_table compute statistics for table for all
indexes for all indexed columns;  

其中:

SQL> analyze table my_table compute statistics;  

等价于:

SQL> analyze table my_table compute statistics for table for all
indexes for all columns;   

sample:

analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;

analyze table t5 compute statistics for all indexes;

analyze table t4 compute statistics;     (不指定)

另外,可以删除分析数据:

SQL> analyze table my_table delete statistics;

SQL> analyze table my_table delete statistics for table for all
indexes for all indexed columns;  

首先创建四个临时表t1,t2,t3,t4,和他们相对应的索引
复制内容到剪贴板
代码:
create table t1 as select * from user_objects;
create table t2 as select * from user_objects;
create table t3 as select * from user_objects;
create table t4 as select * from user_objects;
create unique index pk_t1_idx on t1(object_id);
create unique index pk_t2_idx on t2(object_id);
create unique index pk_t3_idx on t3(object_id);
create unique index pk_t4_idx on t4(object_id);

查看这个时候各个表对应的数据库统计信息(表,字段,索引)
复制内容到剪贴板
代码:
–查看表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_table
where table_names in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS
T1                       
T2                       
T3                       
T4                       

–查看字段的统计信息
select
table_name,column_name,num_distinct,low_value,high_value,density
from user_tab_columns
where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        COLUMN_NAME        NUM_DISTINCT       
LOW_VALUE        HIGH_VALUE        DENSITY
T1        OBJECT_NAME                               
T1        SUBOBJECT_NAME                               
T1        OBJECT_ID                               
T1        DATA_OBJECT_ID                               
T1        OBJECT_TYPE                               
T1        CREATED                               
T1        LAST_DDL_TIME                               
T1        TIMESTAMP                               
T1        STATUS                               
T1        TEMPORARY                               
T1        GENERATED                               
T1        SECONDARY                               
T2        OBJECT_NAME                               
T2        SUBOBJECT_NAME                               
T2        OBJECT_ID                               
T2        DATA_OBJECT_ID                               
T2        OBJECT_TYPE                               
T2        CREATED                               
T2        LAST_DDL_TIME                               
T2        TIMESTAMP                               
T2        STATUS                               
T2        TEMPORARY                               
T2        GENERATED                               
T2        SECONDARY                               
T3        OBJECT_NAME                               
T3        SUBOBJECT_NAME                               
T3        OBJECT_ID                               
T3        DATA_OBJECT_ID                               
T3        OBJECT_TYPE                               
T3        CREATED                               
T3        LAST_DDL_TIME                               
T3        TIMESTAMP                               
T3        STATUS                               
T3        TEMPORARY                               
T3        GENERATED                               
T3        SECONDARY                               
T4        OBJECT_NAME                               
T4        SUBOBJECT_NAME                               
T4        OBJECT_ID                               
T4        DATA_OBJECT_ID                               
T4        OBJECT_TYPE                               
T4        CREATED                               
T4        LAST_DDL_TIME                               
T4        TIMESTAMP                               
T4        STATUS                               
T4        TEMPORARY                               
T4        GENERATED                               
T4        SECONDARY                               

–查看索引的统计信息
select table_name,index_name,blevel,leaf_blocks,distinct_keys,
        avg_leaf_blocks_per_key
avg_leaf_blocks,avg_data_blocks_per_key
avg_data_blocks,clustering_factor,num_rows
from user_indexes
where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS       
DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS       
CLUSTERING_FACTOR        NUM_ROWS
T1       
PK_T1_IDX                                                       
T2       
PK_T2_IDX                                                       
T3       
PK_T3_IDX                                                       
T4        PK_T4_IDX          

现在我们分别对这个表做不同形式的analyze table处理
复制内容到剪贴板

代码:
analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;

我们再回头看看这是的oracle数据库对于各种统计信息
复制内容到剪贴板
代码:
–这是对于表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables
where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS
T1        3930        55        1
T2                       
T3                       
T4        3933        55        1
–我们可以据此得出结论,只有我们在analyze table命令中指定了for
table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息

–这是对于表中字段的统计信息
select
table_name,column_name,num_distinct,low_value,high_value,density
from user_tab_columns where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        COLUMN_NAME        NUM_DISTINCT       
LOW_VALUE        HIGH_VALUE        DENSITY
T1        OBJECT_NAME                               
T1        SUBOBJECT_NAME                               
T1        OBJECT_ID                               
T1        DATA_OBJECT_ID                               
T1        OBJECT_TYPE                               
T1        CREATED                               
T1        LAST_DDL_TIME                               
T1        TIMESTAMP                               
T1        STATUS                               
T1        TEMPORARY                               
T1        GENERATED                               
T1        SECONDARY                               
T2        OBJECT_NAME        3823        41423030       
D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5        .000270447891062615
T2        SUBOBJECT_NAME        77        503031        52455354       
.012987012987013
T2        OBJECT_ID        3930        C304062D        C30F4619       
.000254452926208651
T2        DATA_OBJECT_ID        3662        C304062D       
C30F4619        .000273074822501365
T2        OBJECT_TYPE        15       
4441544142415345204C494E4B        56494557        .000127194098193844
T2        CREATED        3684        7867081E111F33       
7868071211152F        .000547559423988464
T2        LAST_DDL_TIME        3574        7867081E11251B       
7868071211152F        .000565522924083892
T2        TIMESTAMP        3649       
323030332D30382D33303A31363A33303A3530       
323030342D30372D31383A31363A32303A3436        .000559822349362313
T2        STATUS        2        494E56414C4944        56414C4944       
.000127194098193844
T2        TEMPORARY        2        4E        59       
.000127194098193844
T2        GENERATED        2        4E        59       
.000127194098193844
T2        SECONDARY        2        4E        59       
.000127194098193844
T3        OBJECT_NAME                               
T3        SUBOBJECT_NAME                               
T3        OBJECT_ID        3931        C304062D        C30F461A       
.000254388196387688
T3        DATA_OBJECT_ID                               
T3        OBJECT_TYPE                               
T3        CREATED                               
T3        LAST_DDL_TIME                               
T3        TIMESTAMP                               
T3        STATUS                               
T3        TEMPORARY                               
T3        GENERATED                               
T3        SECONDARY                               
T4        OBJECT_NAME        3825        41423030       
D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5        .000261437908496732
T4        SUBOBJECT_NAME        77        503031        52455354       
.012987012987013
T4        OBJECT_ID        3932        C304062D        C30F461B       
.000254323499491353
T4        DATA_OBJECT_ID        3664        C304062D       
C30F461B        .00027292576419214
T4        OBJECT_TYPE        15       
4441544142415345204C494E4B        56494557        .0666666666666667
T4        CREATED        3685        7867081E111F33       
78680712111530        .000271370420624152
T4        LAST_DDL_TIME        3575        7867081E11251B       
78680712111530        .00027972027972028
T4        TIMESTAMP        3650       
323030332D30382D33303A31363A33303A3530       
323030342D30372D31383A31363A32303A3437        .000273972602739726
T4        STATUS        2        494E56414C4944        56414C4944       
.5
T4        TEMPORARY        2        4E        59        .5
T4        GENERATED        2        4E        59        .5
T4        SECONDARY        2        4E        59        .5
/*
在这个结果中我们可以看到,oracle数据库给t2,t4的所有字段都做了统计信息.
对表t3的object_id(索引字段)做了统计信息.
由此得出结论,
在指定for all columns
和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed
columns时,oracle只给[b]有索引的字段进行字段信息统计[/b],如果我们别有必要给所有字段统计信息时,这个属性就很有用了.
*/

–这里是对于索引的统计信息
select table_name,index_name,blevel,leaf_blocks,distinct_keys,
        avg_leaf_blocks_per_key
avg_leaf_blocks,avg_data_blocks_per_key
avg_data_blocks,clustering_factor,num_rows
from user_indexes where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS       
DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS       
CLUSTERING_FACTOR        NUM_ROWS
T1       
PK_T1_IDX                                                       
T2       
PK_T2_IDX                                                       
T3       
PK_T3_IDX                                                       
T4        PK_T4_IDX        1        9        3932        1       
1        2143        3932

–从这里我们可以看出,只有表t4有索引统计信息.
–再综合前面的我们就会发现,如果在运行analyze
table是我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)

补充,truncate命令不修改以上统计信息 复制内容到剪贴板
代码:
truncate table t1;
truncate table t2;
truncate table t3;
truncate table t4;
–我们在查看表和索引的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables
where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS
T1        3930        55        1
T2                       
T3                       
T4        3933        55        1

–索引的统计信息
select table_name,index_name,blevel,leaf_blocks,distinct_keys,
        avg_leaf_blocks_per_key
avg_leaf_blocks,avg_data_blocks_per_key
avg_data_blocks,clustering_factor,num_rows
from user_indexes where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS       
DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS       
CLUSTERING_FACTOR        NUM_ROWS
T1       
PK_T1_IDX                                                       
T2       
PK_T2_IDX                                                       
T3       
PK_T3_IDX                                                       
T4        PK_T4_IDX        1        9        3932        1       
1        2143        3932

–我们再对以上各表做一次分析
analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;

–现在再来查看表和索引的统计信息
select table_name,num_rows,blocks,empty_blocks,initial_extent,’8192′
block_size from user_tables where table_name in
(‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS       
INITIAL_EXTENT        BLOCK_SIZE
T1        0        0        8        65536        8192
T2                                65536        8192
T3                                65536        8192
T4        0        0        8        65536        8192

–索引的统计信息
select table_name,index_name,blevel,leaf_blocks,distinct_keys,
        avg_leaf_blocks_per_key
avg_leaf_blocks,avg_data_blocks_per_key
avg_data_blocks,clustering_factor,num_rows
from user_indexes where table_name in (‘T1′,’T2′,’T3′,’T4’);
TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS       
DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS       
CLUSTERING_FACTOR        NUM_ROWS
T1       
PK_T1_IDX                                                       
T2       
PK_T2_IDX                                                       
T3       
PK_T3_IDX                                                       
T4        PK_T4_IDX        0        0        0        0       
0        0        0
–由此得出结论,truncate命令不会修改数据的统计信息, –也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息

复制代码 代码如下:SQL> conn /as sysdba
已连接。 SQL> select default_tablespace from dba_users where
username=’HR’; DEFAULT_TABLESPACE
———————————————————— USERS
SQL> conn hr/hr 已连接。 SQL> insert into t1 select * from t1;
已创建 74812 行。 SQL> insert into t1 select * from t1; 已创建
149624 行。 SQL> commit; 提交完成。 SQL> create index idx_t1_id
on t1; 索引已创建。 SQL> exec
dbms_stats.gather_table_stats(‘HR’,’T1′,CASCADE=>TRUE); PL/SQL
过程已成功完成。 SQL> select count ———- 299248 SQL> select
sum/1024/1024 from dba_segments where segment_name=’T1′; SUM/1024/1024
——————– 34.0625 SQL> select sum/1024/1024 from
dba_segments where segment_name=’IDX_T1_ID’; SUM/1024/1024
——————– 6

二:估算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少!DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息

复制代码 代码如下:SQL> SELECT blocks,
empty_blocks, num_rows FROM user_tables WHERE table_name =’T1′;
BLOCKS EMPTY_BLOCKS NUM_ROWS ———- ———— ———- 4302 0
299248 SQL> analyze table t1 compute statistics; 表已分析。 SQL>
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE
table_name =’T1′; BLOCKS EMPTY_BLOCKS NUM_ROWS ———-
———— ———- 4302 50 299248 SQL> col table_name for a20
SQL> SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) – 3
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” 4
FROM USER_TABLES 5 WHERE table_name = ‘T1’; TABLE_NAME Data lower
than HWM in MB ——————– ————————- T1
5.07086182

三: 查看执行计划,全表扫描大概需要消耗CPU 1175

复制代码 代码如下:SQL> explain plan
for select * from t1; 已解释。 SQL> select * from table;

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website