8.未公开的Oracle数据库秘密笔记——索引监控
索引分为使用过和未使用两个类别。
未使用的索引对SELECT语句毫无用处,而且通过INSERT、UPDATE和DELETE语句所作的修改必须保留索引,清除这些未使用的索引也许是一种更好的选择。
V$OBJECT_USAGE是一个名不副实的视图,仅仅基于SYS模式中的数据字典表,而不是X$固定表。
sys@ORCL> desc v$object_usage;
Name Null? Type
------------------------------------------------------------- ------------------------------------
INDEX_NAME NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
DBA_INDEXES和DBA_SEGMENTS等视图都有OWNER一列,这样DBA能随心所欲地查看任何模式的信息。
1. 索引监控view_index_usage.sql如下:
-- run as SYS
GRANT SELECT ON obj$ TO site_sys WITH GRANT OPTION;
GRANT SELECT ON ind$ TO site_sys WITH GRANT OPTION;
GRANT SELECT ON object_usage TO site_sys WITH GRANTOPTION;
GRANT SELECT ON user$ TO site_sys WITH GRANT OPTION;
/* remove privileges */
DROP VIEW site_sys.index_usage;
CREATE OR REPLACE VIEW site_sys.index_usage
(owner,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
AS
SELECT u.name, io.name index_name, t.name table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.user$u, sys.object_usage ou
WHERE io.owner# = t.owner#
AND io.owner# = u.user#
AND i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;
-- have to grant to public, to allow non DBAs accessto the view
-- which is used by function MONITOR_SCHEMA_INDEXES,which runs with
-- AUTHID CURRENT_USER
GRANT SELECT ON site_sys.index_usage TO PUBLIC;
主程序:
@@view_index_usage
-- enable index usage monitoring in a schema
CREATE OR REPLACE FUNCTIONsite_sys.monitor_schema_indexes (
ownnameVARCHAR2 DEFAULT NULL,
failed_counter out number,
monitoring BOOLEAN DEFAULT TRUE
) RETURN INTEGER AUTHID CURRENT_USER
AS
resource_busy exception;
PRAGMAexception_init(resource_busy, -54);
counterinteger:=0;
schema_name varchar2(30);
stmtvarchar2(256);
cursornot_monitored(p_schema_name varchar2) is
SELECT index_name
FROM all_indexes i, all_tables t
where i.owner=p_schema_name
and i.table_name=t.table_name
and i.table_owner=t.owner
-- cannot be used on index of type IOT ORA-25176: storage specificationnot permitted for primary key
and t.iot_type IS NULL
and index_type != 'DOMAIN'
MINUS
SELECT index_name
FROM site_sys.index_usage
WHERE owner=p_schema_name
AND monitoring='YES';
cursormonitored(p_schema_name varchar2) is
SELECTindex_name
FROMsite_sys.index_usage
WHEREowner=p_schema_name
andmonitoring='YES';
begin
schema_name:=nvl(ownname,user);
failed_counter:=0;
IFmonitoring = TRUE THEN
for record in not_monitored(schema_name) LOOP
BEGIN
stmt:='ALTERINDEX '||schema_name||'."'||record.index_name||'" monitoring usage';
executeimmediate stmt;
counter:=counter+1;
EXCEPTION WHENresource_busy THEN
failed_counter:=failed_counter+1;
END;
END LOOP;
ELSE
for record in monitored(schema_name) LOOP
BEGIN
stmt:='ALTERINDEX '||schema_name||'."'||record.index_name||'" NOMONITORINGUSAGE';
execute immediatestmt;
counter:=counter+1;
EXCEPTION WHENresource_busy THEN
failed_counter:=failed_counter+1;
END;
END LOOP;
END IF;
returncounter;
/*
EXCEPTION WHEN OTHERS THEN
raise_application_error(-20000, 'Error in proceduresite_sys.monitor_schema_indexes executing '''
|| stmt||'''',TRUE);
*/
end;
/
show errors
GRANT execute ON site_sys.monitor_schema_indexes TOPUBLIC;