Wednesday, July 6, 2011

Find which queries use a specific segment

If you want to find out which of the cursors currently loaded in the library cache use a specific table or index:
SELECT
   P.INST_ID,
   P.OBJECT_NAME,
   P.OPERATION,
   P.OPTIONS,
   S.SQL_ID,
   S.MODULE,
   S.ACTION,
   S.PARSING_SCHEMA_NAME,
   S.SQL_TEXT
FROM
   GV$SQL_PLAN P,
   GV$SQL S
WHERE P.OBJECT_OWNER = 'AX'
AND P.OBJECT_NAME = 'AX_EVENTS_N1'
AND P.SQL_ID = S.SQL_ID
AND P.INST_ID = S.INST_ID;

You may also use the following query to get the result based on AWR data:
SELECT
   P.OBJECT_NAME,
   P.OPERATION,
   P.OPTIONS,
   S.SQL_ID,
   S.MODULE,
   S.ACTION,
   S.PARSING_SCHEMA_NAME,
   T.SQL_TEXT
FROM
   DBA_HIST_SQL_PLAN P,
   DBA_HIST_SQLSTAT S,
   DBA_HIST_SQLTEXT T
WHERE P.OBJECT_OWNER='AX'
AND P.OBJECT_NAME='AX_EVENTS_N1'
AND P.SQL_ID = S.SQL_ID
AND S.SQL_ID = T.SQL_ID;

Remember, by default AWR collects the top-N (default N=30) heaviest SQL queries in the library cache each time.
So, both queries cannot provide you a 100% way to monitor the usage of segments.

No comments:

Post a Comment