Monitoring index usage in query plans in Oracle 10g

To monitor the use of indexes, Oracle offers an easy way to turn on index monitoring and turn off the period significant for a given index to complete. Description on the Oracle site here . As a result, in the V $ OBJECT_USAGE view you can see the answer “Yes” or “No”.

But what to do if:
- You already know that the index is used,
- the population of requests is already so large that it is not possible to analyze them for use by requests
- You need additional. query execution information

The answer is quite obvious - you need to monitor the current work of the server for the period which is quite acceptable for you to evaluate (calendar month, for example, when all basic operations are carried out).
')
For this, you can use the data that AWR collects, an example of such use is described in the article “ORACLE INDEX USAGE TRACKING” .
But even here everything is not so good - you depend on how often the base images are taken and how long the images are updated (that is, when there is the last image). It is likely that you will want to analyze the operation of the system for some object in a week or a few, and the AWR data is stored only for the last few days.

For monitoring, you can use the following algorithm:
1. Create a table in which to collect interesting information.
2. Create a timer task with a certain period in which to monitor all parsed plans for requests for the use in them of analyzing the element for a long time (in this case, the index)
3. During and at the end of the period, turn off the timer task and analyze the results obtained.
4. Upon completion of monitoring, delete all monitoring objects, or at least turn off JOB.

Below is an example of the implementation of the described algorithm:

1.1. We will prepare all the necessary rights. Under sys, you need to give rights to V $ SQL, V $ SQL_PLAN, V $ SQL_BIND_CAPTURE (note that you cannot give rights to the names V $ SQL, V $ SQL_PLAN because they are synonyms):

grant select on V_$SQL to schema_name; grant select on V_$SQL_PLAN to schema_name; grant select on V$SQL_BIND_CAPTURE to schema_name; 

where schema_name is the name of the schema on which to monitor.

1.2. Create tables to store useful information for analysis:

 --      V$SQL      CREATE TABLE monitoring_index_usage_table as SELECT * FROM v$sql s WHERE s.hash_value IN (SELECT v.hash_value FROM v$sql_plan v WHERE v.object_name = 'XXX'); -- Add/modify columns -- Add/modify columns ALTER TABLE monitoring_index_usage_table ADD what_mon VARCHAR2(100); ALTER TABLE monitoring_index_usage_table add dt_mon date; -- Add comments to the columns COMMENT ON COLUMN monitoring_index_usage_table.what_mon is ' '; COMMENT ON COLUMN monitoring_index_usage_table.dt_mon is '  '; -- Create/Recreate indexes CREATE INDEX idx_MONITORING_INDEX_USAGE_TABLE on MONITORING_INDEX_USAGE_TABLE (sql_id); --      v$sql_plan -   CREATE TABLE monitoring_index_plans AS SELECT * FROM v$sql_plan WHERE ROWNUM = 0; --                (    ) CREATE TABLE monitoring_sql_bind_capture as SELECT sql_id, name, position, datatype_string, was_captured, last_captured, value_string FROM v$sql_bind_capture WHERE sql_id = '-----'; 

where XXX is the database element being analyzed, in my case it was an index whose name will be object_name

2.1. Create a procedure to populate the tables:

 CREATE OR REPLACE PROCEDURE monitoring_sql_plans IS BEGIN --  ,         -- object_name        --    -- monitoring object_name on schema_name INSERT INTO monitoring_index_usage_table SELECT s.*, 'object_name usage', SYSDATE FROM v$sql s WHERE s.last_active_time > '14.02.2012 19:20' AND s.parsing_schema_name = 'schema_name' AND (s.address, s.hash_value) IN (SELECT v.address, v.hash_value FROM v$sql_plan v WHERE v.object_name IN ('object_name') AND v.object_owner = 'schema_name') AND (address, hash_value) NOT IN (SELECT address, hash_value FROM monitoring_index_usage_table); FOR v_i IN (SELECT DISTINCT address, hash_value FROM v$sql_plan WHERE object_name IN ('object_name') AND (address, hash_value) NOT IN (SELECT address, hash_value FROM monitoring_index_plans)) LOOP INSERT INTO monitoring_index_plans SELECT * FROM v$sql_plan v WHERE v.hash_value = v_i.hash_value AND v.address = v_i.address; END LOOP; --------------------------------------------------------------------------------- --  bind            FOR v_i IN (SELECT sql_id, NAME, position, datatype_string, was_captured, last_captured, value_string FROM v$sql_bind_capture WHERE sql_id IN (SELECT DISTINCT sql_id FROM monitoring_index_usage_table) AND (sql_id, last_captured) NOT IN (SELECT DISTINCT sql_id, last_captured FROM monitoring_sql_bind_capture)) LOOP INSERT INTO monitoring_sql_bind_capture VALUES (v_i.sql_id, v_i.name, v_i.position, v_i.datatype_string, v_i.was_captured, v_i.last_captured, v_i.value_string); END LOOP; END monitoring_sql_plans; 

where schema_name is the name of your database schema

2.2. Create a JOB to run once every half hour (set a convenient time for you):

 BEGIN sys.dbms_job.submit(job => :job, what => 'begin monitoring_sql_plans; end;', next_date => SYSDATE + 1 / 24 / 60 / 60, INTERVAL => 'SYSDATE+1/48'); COMMIT; END; / 

3. Further from time to time or upon completion of a significant period we analyze the result. To do this, I cite several useful different sections:

 SELECT round(t.cpu_time / 1000000, 2) AS time_seq, t.loads, t.executions, decode(nvl(t.executions, 0), 0, 0, round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load, t.* FROM monitoring_index_usage_table t WHERE what_mon = 'UK_OBJ_DOC_OBJ_PROD_PART_BIRT usage' ORDER BY time_per_load DESC; 

 -- BIND VARIABLE    SELECT * FROM monitoring_sql_bind_capture WHERE sql_id -- = '6pdbd2w2nd9w9' IN (SELECT sql_id FROM (SELECT decode(nvl(t.executions, 0), 0, 0, round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load, t.* FROM monitoring_index_usage_table t -- 22 -- 71  12 -- 116  13 WHERE what_mon = 'object_name usage' ORDER BY time_per_load DESC) WHERE rownum = 1); 

Description V $ SQL, see here .
Description V $ SQL_PLAN, see here .
Description V $ SQL_BIND_CAPTURE see here .

For my needs, this code turned out to be enough and, besides, it did not load the database even during the period of active work hundreds of users with the system.

I hope someone this example will help.

Also, a quick procedure is done which makes distinct clobs that store SQL_FULLTEXT:

 CREATE OR REPLACE PROCEDURE mon_index_usage_get_only_sql IS n NUMBER; BEGIN -- for getting distinct sqls to table mon_index_usage_sqls -- from monitoring_index_usage_table DELETE FROM mon_index_usage_sqls; FOR v_i IN (SELECT * FROM monitoring_index_usage_table t) LOOP SELECT COUNT(*) INTO n FROM mon_index_usage_sqls s WHERE dbms_lob.compare(s.sql_fulltext, v_i.sql_fulltext) = 0; IF (n = 0) THEN INSERT INTO mon_index_usage_sqls (sql_text, sql_fulltext) VALUES (v_i.sql_text, v_i.sql_fulltext); END IF; END LOOP; END; 

And its use:

 BEGIN -- Call the procedure mon_index_usage_get_only_sql; END; / 

 SELECT * FROM mon_index_usage_sqls; 

4. After the monitoring has finished, it is better to clean the database of unnecessary tables and data:

 EXECUTE DBMS_JOB.REMOVE(:jobno); DROP TABLE monitoring_index_usage_table ; DROP TABLE monitoring_index_plans ; DROP TABLE monitoring_sql_bind_capture ; DROP PROCEDURE monitoring_sql_plans ; DROP PROCEDURE mon_index_usage_get_only_sql ; 

Conclusions: The proposed method of monitoring the use of the index can be used to monitor any object in the query plans in the section you need and as often as you need)))