Database Reference
In-Depth Information
Switch off index monitoring on all indexes in the current schema.
SQL> EXEC :success_counter:=site_sys.monitor_schema_indexes( -
> failed_counter=>:failed_counter, monitoring=>false);
Enabling Index Monitoring on Schema HR
To enable index usage monitoring on all indexes in schema HR, connect as user HR and run
SITE_SYS.MONITOR_SCHEMA_INDEXES
. Before doing so, you may wish to query
V$OBJECT_USAGE
to
confirm that none of the indexes in schema HR have ever been monitored.
SQL> CONNECT hr/secret
SQL> SELECT * FROM v$object_usage;
no rows selected
SQL> VARIABLE success_counter NUMBER
SQL> VARIABLE failed_counter NUMBER
SQL> SET AUTOPRINT ON
SQL> EXEC :success_counter:=site_sys.monitor_schema_indexes( -
> failed_counter=>:failed_counter);
PL/SQL procedure successfully completed.
FAILED_COUNTER
--------------
0
SUCCESS_COUNTER
---------------
18
SQL> SELECT table_name, index_name, monitoring, used,
start_monitoring, end_monitoring
FROM v$object_usage ORDER BY 1, 2;
TABLE_NAME INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
----------- ----------------- ---------- ---- ------------------- --------------
DEPARTMENTS DEPT_ID_PK YES NO 10/04/2007 17:21:54
DEPARTMENTS DEPT_LOCATION_IX YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_DEPARTMENT_IX YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_EMAIL_UK YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_EMP_ID_PK YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_JOB_IX YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_MANAGER_IX YES NO 10/04/2007 17:21:55
EMPLOYEES EMP_NAME_IX YES NO 10/04/2007 17:21:55
…
The SQL*Plus setting
SET
AUTOTRACE
TRACEONLY
EXPLAIN
tells SQL*Plus to merely run
EXPLAIN
PLAN
on the statements entered, without actually executing them or fetching any rows in case
of a
SELECT
statement. May I ask you to cast a vote? Will
EXPLAIN
PLAN
mark indexes indicated by
a plan as used, or is it necessary to actually access an index by fetching rows? Please cast your
vote before you read on.
