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