> 參考 《收穫不止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');
```

【TABLE】Oracle監控異常的表設計

相關文章