Translate

Search This Blog

top resource intensive queries and unused indexes from AWR in oracle

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
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;

 

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
 
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

Follow by Email