8.未公开的Oracle数据库秘密笔记——模式限制

来源：互联网发布：mac版dreamweaver cs6 编辑：程序博客网时间：2024/05/03 16:07

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50366069

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)

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

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;

0 0