> 参考 《收获不止SQL优化》 ##### 监控失效分区索引 ``` sql --普通索引 select t.owner,t.index_name,t.table_name,t.blevel,t.num_rows,t.leaf_blocks,t.distinct_keys from dba_indexes t where t.status='INVALID'; --分区索引 select t2.owner,t1.blevel,t.leaf_blocks,t1.index_name,t2.table_name,t1.partition_name,t1.status from dba_ind_partitions t1,dba_indexes t2 where t1.index_name=t2.index_name and t1.owner=t2.owner and t1.status='UNUSABLE'; ``` ##### 查找未建分区的大表 ``` sql select owner,segment_name,segment_type,sum(bytes/1024/1024/1024) GB from dba_segments where segment_type='TABLE' group by segment_name,segment_type having sum(bytes/1024/1024/1024) >=10 order by GB desc; ``` ##### 检查分区数过多的表 ``` sql --分区超过100个的 select owner,table_name,partitioning_type,subpartitioning_type from dba_part_tables where partition_count>=100; ``` ##### 检查分区表大小严重不均衡的 ``` sql select owner,table_name,num_rows from dba_tab_partitions where table_name='RANGE_PART_TAB' order by num_rows desc; ``` ##### 检查哪些全局临时表被收集统计信息 ``` sql select owner,table_name,last_analyzed,num_rows,blocks from dba_tables where temporary='Y' and last_analyzed is not null; ``` ##### 检查表中有没有过时的类型字段 ``` sql select owner,table_name,column_name,data_type from dba_tab_columns where data_type in('LONG','CHAR'); ```