一、索引管理
1、获取索引定义
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; |
2、查询表的索引
select * from user_indexes where table_name='表名'; select * from user_ind_columns where index_name='索引名'; |
3、查询索引占用大小
select owner,segment_name,(sum(bytes)/1024/1024)||'MB',tablespace_name from dba_segments where segment_name = 'IDX_T' group by owner,segment_name,tablespace_name; |
4、列出失效索引或索引分区
--以下三个脚本分别列出数据库中的失效的索引、索引分区、子分区: REM list of the unusable index,index partition,index subpartition in Database Select owner, index_name, status From dba_indexes where status = 'UNUSABLE' and owner not in ('SYS','SYSTEM', 'SYSMAN', 'EXFSYS', 'WMSYS', 'OLAPSYS', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB', 'FLOWS_030000', 'FLOWS_FILES') order by 1, 2 / select index_owner, index_name, partition_name from dba_ind_partitions where status ='UNUSABLE' and index_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'EXFSYS', 'WMSYS', 'OLAPSYS', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB', 'FLOWS_030000', 'FLOWS_FILES') order by 1,2 / Select Index_Owner , Index_Name , partition_name , SUBPARTITION_NAME From DBA_IND_SUBPARTITIONS Where status = 'UNUSABLE' and index_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'EXFSYS', 'WMSYS', 'OLAPSYS', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB', 'FLOWS_030000', 'FLOWS_FILES') order by 1, 2 / |
5、查询索引碎片的比例
注意x$kdxst是oracle的一个内存表，然后analyze index indexname validate structure这条语句会往x$kdxst写一些统计信息，当执行select * from x$kdxst的时候，oracle这里实际上是把你执行analyze index indexname validate structure时的那个indexname给当作绑定变量给传进来了，所以你只能看到你执行analyze index indexname validate structure的那个index的统计信息。也就是只有在执行过analyze index indexname validate structure后且在同一个session下才能从index_stats中看到数据
analyze index indexname validate structure; select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30; |
6、监控oracle数据库索引使用情况
监控索引一般有两种方式：
1）直接监控索引的使用情况
（1）设置所要监控的索引：ALTER INDEX IDX_T_XX MONITORING USAGE;
（2）查看该索引有没有被使用：SELECT * FROM V$OBJECT_USAGE;
（3）关闭监控：ALTER INDEX IDX_T_XX NOMONITORING USAGE;
2）schema级别索引监控
如果想在系统中监控所有的索引，可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。可以直接执行脚本来开启索引监控，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。
--1、开启索引监控 SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor, 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;' disable_monitor FROM dba_indexes WHERE INDEX_TYPE != 'LOB' and owner IN (SELECT username FROM dba_users WHERE account_status = 'OPEN') AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP') AND owner not like '%SYS%'; --2、查询数据库中所有被监控索引的使用情况 SELECT U.NAME OWNER, IO.NAME INDEX_NAME, T.NAME TABLE_NAME, DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING, DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED, OU.START_MONITORING START_MONITORING, OU.END_MONITORING END_MONITORING FROM SYS.USER$ U, SYS.OBJ$ IO, SYS.OBJ$ T, SYS.IND$ I, SYS.OBJECT_USAGE OU WHERE I.OBJ# = OU.OBJ# AND IO.OBJ# = OU.OBJ# AND T.OBJ# = I.BO# AND U.USER# = IO.OWNER#; --3、历史执行计划分析索引使用情况 WITH TMP1 AS (SELECT I.OWNER INDEX_OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, (SELECT NB.CREATED FROM DBA_OBJECTS NB WHERE NB.OWNER = I.OWNER AND NB.OBJECT_NAME = I.INDEX_NAME AND NB.SUBOBJECT_NAME IS NULL AND NB.OBJECT_TYPE = 'INDEX') CREATED, (SUM(S.BYTES) / 1024 / 1024) INDEX_MB, (SELECT COUNT(1) FROM DBA_IND_COLUMNS DIC WHERE DIC.INDEX_NAME = I.INDEX_NAME AND DIC.TABLE_NAME = I.TABLE_NAME AND DIC.INDEX_OWNER = I.OWNER) COUNT_INDEX_COLS FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE I.INDEX_NAME = S.SEGMENT_NAME AND I.OWNER = S.OWNER AND S.OWNER NOT LIKE '%SYS%' GROUP BY I.OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE HAVING SUM(S.BYTES) > 1024 * 1024), TMP2 AS (SELECT INDEX_OWNER, INDEX_NAME, PLAN_OPERATION, (SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')) FROM DBA_HIST_SNAPSHOT NB WHERE NB.SNAP_ID = V.MIN_SNAP_ID) MIN_DATE, (SELECT MAX(TO_CHAR(NB.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')) FROM DBA_HIST_SNAPSHOT NB WHERE NB.SNAP_ID = V.MAX_SNAP_ID) MAX_DATE, COUNTS FROM (SELECT D.OBJECT_OWNER INDEX_OWNER, D.OBJECT_NAME INDEX_NAME, D.OPERATION || ' ' || D.OPTIONS PLAN_OPERATION, MIN(H.SNAP_ID) MIN_SNAP_ID, MAX(H.SNAP_ID) MAX_SNAP_ID, COUNT(1) COUNTS FROM DBA_HIST_SQL_PLAN D, DBA_HIST_SQLSTAT H WHERE D.OPERATION LIKE '%INDEX%' AND D.SQL_ID = H.SQL_ID GROUP BY D.OBJECT_OWNER, D.OBJECT_NAME, D.OPERATION, D.OPTIONS) V) SELECT A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_OWNER, A.INDEX_NAME, A.CREATED, A.INDEX_TYPE, A.INDEX_MB, A.COUNT_INDEX_COLS, B.PLAN_OPERATION, CASE WHEN MIN_DATE IS NULL THEN (SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')) FROM DBA_HIST_SNAPSHOT NB) ELSE MIN_DATE END AS MIN_DATE, CASE WHEN MAX_DATE IS NULL THEN (SELECT MAX(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')) FROM DBA_HIST_SNAPSHOT NB) ELSE MAX_DATE END AS MAX_DATE, COUNTS FROM TMP1 A LEFT OUTER JOIN TMP2 B ON (A.INDEX_OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME); |
7、查看单个索引使用情况
--查看当前索引使用情况 SELECT p.object_name, p.operation, p.options, COUNT(1) FROM v$sql_plan p, v$sql s WHERE p.object_owner <> 'SYS' AND p.OBJECT_NAME in (select index_name from dba_indexes where table_name = 'S_SHIP_UNIT_LINE') AND p.sql_id = s.sql_id GROUP BY p.object_name, p.operation, p.options ORDER BY 1, 2, 3; --查看历史索引使用情况 SELECT p.object_name, p.operation, p.options, COUNT(1) FROM dba_hist_sql_plan p, dba_hist_sqlstat s WHERE p.object_owner <> 'SYS' AND p.object_name in (select index_name from dba_indexes where table_name = 'S_SHIP_UNIT_LINE') AND p.sql_id = s.sql_id GROUP BY p.object_name, p.operation, p.options ORDER BY 1, 2, 3; |
8、获得索引使用频率脚本（idx_usage_detail.sql）
下面的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进。
--1、数据库索引的使用频率脚本（idx_usage_detail.sql） set linesize 140 set pagesize 160 clear breaks clear computes break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB compute sum of NR_EXEC on TABLE_NAME SKIP 2 compute sum of MB on TABLE_NAME SKIP 2 SET TIMI OFF set linesize 140 set pagesize 10000 set verify off col OWNER noprint col TABLE_NAME for a30 heading 'Table name' col INDEX_NAME for a30 heading 'Index name' col INDEX_TYPE for a15 heading 'Index type' col INDEX_OPERATION for a21 Heading 'Index operation' col NR_EXEC for 9G999G990 heading 'Executions' col MB for 999G990D90 Heading 'Index|Size MB' justify right WITH Q AS ( SELECT S.OWNER A_OWNER, TABLE_NAME A_TABLE_NAME, INDEX_NAME A_INDEX_NAME, INDEX_TYPE A_INDEX_TYPE, SUM(S.bytes) / 1048576 A_MB FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = '&&1' AND I.OWNER = '&&1' AND INDEX_NAME = SEGMENT_NAME GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE HAVING SUM(S.BYTES) > 1048576 * &&2 ) SELECT /*+ NO_QUERY_TRANSFORMATION(S) */ A_OWNER OWNER, A_TABLE_NAME TABLE_NAME, A_INDEX_NAME INDEX_NAME, A_INDEX_TYPE INDEX_TYPE, A_MB MB, DECODE (OPTIONS, null, ' -',OPTIONS) INDEX_OPERATION, COUNT(OPERATION) NR_EXEC FROM Q, DBA_HIST_SQL_PLAN d WHERE D.OBJECT_OWNER(+)= q.A_OWNER AND D.OBJECT_NAME(+) = q.A_INDEX_NAME GROUP BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB, DECODE (OPTIONS, null, ' -',OPTIONS) ORDER BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB DESC, NR_EXEC DESC ; PROMPT "Showed only indexes in &&1 schema whose size > &&2 MB in period:" SET HEAD OFF; select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY') || '-' || to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY') from dba_hist_snapshot; SET HEAD ON SET TIMI ON --2、获得当前数据库索引的使用频率 --当前数据库中schema为RFUSER且索引大小大于100MB的索引的使用频率 SQL> @idx_usage_detail.sql Enter value for 1: RFUSER Enter value for 2: 100 |
9、索引质量分析脚本
对于生产环境中的数据库，可以通过查询相关数据字典得到索引的质量的高低，通过这个分析来指导如何改善索引的性能。
--1、script name: idx_quality.sql --Author : Leshami --index quality retrieval SET LINESIZE 145 SET PAGESIZE 1000 SET VERIFY OFF CLEAR COMPUTES CLEAR BREAKS BREAK ON table_name ON num_rows ON blocks COLUMN owner FORMAT a14 HEADING 'Index owner' COLUMN table_name FORMAT a25 HEADING 'Table' COLUMN index_name FORMAT a25 HEADING 'Index' COLUMN num_rows FORMAT 999G999G990 HEADING 'Table|Rows' COLUMN MB FORMAT 9G990 HEADING 'Index|Size MB' COLUMN blocks HEADING 'Table|Blocks' COLUMN num_blocks FORMAT 9G990 HEADING 'Data|Blocks' COLUMN avg_data_blocks_per_key FORMAT 999G990 HEADING 'Data Blks|per Key' COLUMN avg_leaf_blocks_per_key FORMAT 999G990 HEADING 'Leaf Blks|per Key' COLUMN clustering_factor FORMAT 999G999G990 HEADING 'Clust|Factor' COLUMN Index_Quality FORMAT A13 HEADING 'Index|Quality' --SPOOL index_quality SELECT i.table_name, t.num_rows, t.blocks, i.index_name, o.bytes / 1048576 mb, i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key, i.clustering_factor, CASE WHEN NVL (i.clustering_factor, 0) = 0 THEN '0-No Stats' WHEN NVL (t.num_rows, 0) = 0 THEN '0-No Stats' WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6 THEN '5-Excellent' WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7 AND 11 THEN '4-Very Good' WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12 AND 15 THEN '2-Good' WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16 AND 25 THEN '2-Fair' ELSE '1-Poor' END index_quality FROM dba_indexes i, dba_segments o, dba_tables t WHERE -- i.index_name LIKE UPPER ('%&&1%') AND i.owner = t.owner AND i.table_name = t.table_name AND i.owner = o.owner AND i.index_name = o.segment_name AND t.owner = UPPER('&input_owner') AND t.table_name LIKE UPPER('%&input_tbname%') ORDER BY table_name, num_rows, blocks, index_quality DESC; --SPOOL OFF; ============================================================== --2、获取指定schema或表上的索引质量信息报告 SQL> @idx_quality.sql Enter value for input_owner: RFUSER Enter value for input_tbname: TAB_GOODSLABEL --省略具体的表名则会输出整个schema的索引质量报告 |
9、查询单表上索引列的相关信息
对于上面索引质量比较低的索引还应结合该索引的使用频率来考量该索引存在的必要性，对于聚簇因子，只能通过重新组织表上的数据来，以及调整相应索引列的顺序得以改善。大多数情况下，单表上6-7个索引是比较理想的。过多的索引导致过大的资源开销，以及降低DML性能。
--1、script name: idx_info.sql --get the index column information by specified table set linesize 180 col cl_nam format a20 col table_name format a25 col cl_pos format 9 col idx_typ format a15 SELECT b.table_name, a.index_name, a.column_name cl_nam, a.column_position cl_pos, b.status, b.index_type idx_typ, a.descend dscd FROM dba_ind_columns a, dba_indexes b WHERE a.index_name = b.index_name AND owner = upper('&owner') AND a.table_name LIKE upper('%&table_name%') ORDER BY 2, 4; --2、查询单表上索引列的相关信息 SQL> @idx_info.sql Enter value for owner: RFUSER Enter value for table_name: TAB_GOODSLABEL |
