Below top query based on awr is based on earlier post
This query can be modified to sort by any parameter and use any filter as commented.
SELECT T1.*, DBA_HIST_SQLTEXT.sql_text,dba_hist_snapshot.BEGIN_INTERVAL_TIME
FROM(
SELECT
FROM(
SELECT
sub.module, parsing_schema_name,
ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,
sub.execs_since_date,
sub.gets_since_date,
sub.snap_id,
ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,
execs_since_date)/60,2) avg_exec_time,
sub.sql_id
FROM
( -- sub to sort before rownum
SELECT module, parsing_schema_name,
sql_id,
ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,
SUM(executions_delta) AS execs_since_date,
SUM(buffer_gets_delta) AS gets_since_date,
snap_id
FROM
dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat
WHERE
dba_hist_sqlstat.parsing_schema_name ='&USER_NAME'
AND begin_interval_time BETWEEN sysdate-1 AND sysdate
AND module NOT LIKE '%exp%' AND module NOT LIKE '%imp%'
AND module NOT LIKE '%TOAD%'
GROUP BY
module,sql_id,snap_id,parsing_schema_name
ORDER BY snap_id DESC
) sub
WHERE ROWNUM <=10
)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot
WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id
AND T1.snap_id=dba_hist_snapshot.snap_id
--AND ( LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%' --OR
-- LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%insert%' OR LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%')
AND sql_text NOT LIKE '%DBMS_STATS%'
AND sql_text NOT LIKE '%parallel(t,2)%'
AND sql_text NOT LIKE '%maxbkt%'
AND sql_text NOT LIKE '%substrb%'
ORDER BY elapsed_time_delta_mins ;--T1.snap_id
Same query sql_id can take different execution plan over time. So in order to find out
which plan for the given SQL_ID was in effect at what time, you can query plan_hash_value
from DBA_HIST_SQLSTAT
select distinct plan_hash_value, min(begin_interval_time)
first,
max(end_interval_time) last
from dba_hist_sqlstat natural join dba_hist_snapshot
where sql_id='&SQL_ID'
group by sql_id, plan_hash_value
order by 3;
which plan for the given SQL_ID was in effect at what time, you can query plan_hash_value
from DBA_HIST_SQLSTAT
select distinct plan_hash_value, min(begin_interval_time)
first,
max(end_interval_time) last
from dba_hist_sqlstat natural join dba_hist_snapshot
where sql_id='&SQL_ID'
group by sql_id, plan_hash_value
order by 3;
Query to find unused indexes in oracle:
Indexes can be put in monitoring mode and v$db_object_usage view can be queried to know if index has been used or not but the problem with this approach is if any table has been analyzed with cascade=>true clause then corresponding indexes are marked as used.
So we can again use query based on AWR view to know if index has been used.
-- below query is based on DBA_HIST_SQL_PLAN
-- query can be amended to exclude foreign key indexes as they are not mainly for --performance rather they are to lessen restriction of locks
undefine USER_NAME
undefine start_snap_id
undefine stop_snap_id
SELECT owner, table_name , index_name, index_type , LAST_ANALYZED
FROM DBA_INDEXES
WHERE index_name IN
(SELECT index_name FROM
(SELECT owner, index_name
FROM DBA_INDEXES di
WHERE di.index_type != 'LOB'
AND owner ='&&USER_NAME'
MINUS
SELECT index_owner owner, index_name
FROM DBA_CONSTRAINTS dc
WHERE index_owner ='&&USER_NAME'
MINUS
SELECT p.object_owner owner, p.object_name index_name
FROM DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p
WHERE sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_type = 'INDEX'
)
)
AND owner ='&&USER_NAME'
ORDER BY 1
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE LAST_ANALYZED
SCOTT T3 IDXT3 NORMAL 27-04-2013 18:11:08
SCOTT ZIGGY_STUFF ZIGGY_STUFF_CODE_ID_I NORMAL 06-07-2013 14:23:03
SCOTT ZIGGY_STUFF ZIGGY_STUFF_CODE_ID_I NORMAL 06-07-2013 14:23:03
Based on last_analyzed you may like to analyze the ununsed index and run the query after workload of few days to see if index is still unused and it needs to be dropped.
Similarly query can be written to find the frequency of index usage :
SELECT
p.object_name search_columns,
ROUND (COUNT(*)/15 ,2 ) COUNT
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p,
DBA_HIST_SQLSTAT st
WHERE
st.sql_id = p.sql_id
AND
sn.snap_id = st.snap_id
AND
p.object_type = 'INDEX'
AND sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_owner ='&USER_NAME'
GROUP BY
p.object_name
ORDER BY
2 DESC,
1
Posts
