74 lines
2.2 KiB
SQL
74 lines
2.2 KiB
SQL
@@header
|
|
|
|
PROMPT *********************
|
|
PROMPT SCRIPT INCOMPLETE
|
|
PROMPT *********************
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display index usage from AWR data.
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-May-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
COLUMN table_owner HEADING "Table Owner" FORMAT a20
|
|
COLUMN table_name HEADING "Table Name" FORMAT a20
|
|
COLUMN index_name HEADING "Index Name" FORMAT a20
|
|
COLUMN Monitoring HEADING "Monitoring" FORMAT a10
|
|
COLUMN Used HEADING "Used" FORMAT a4
|
|
|
|
|
|
SELECT object_name
|
|
, sql_id
|
|
, ROUND(avg(daily_exections)) avg_daily_exections
|
|
FROM
|
|
(
|
|
SELECT p.object_name
|
|
, p.sql_id
|
|
--, p.plan_hash_value
|
|
, to_char(end_interval_time,'YYYY-MM-DD') Day
|
|
, SUM(ss.executions_delta) daily_exections
|
|
FROM dba_hist_sql_plan p
|
|
, dba_hist_sqlstat ss
|
|
, dba_hist_snapshot s
|
|
WHERE s.dbid = ss.dbid
|
|
AND s.instance_number = ss.instance_number
|
|
AND s.snap_id = ss.snap_id
|
|
AND ss.plan_hash_value = p.plan_hash_value
|
|
AND ss.dbid = p.dbid
|
|
AND ss.sql_id = p.sql_id
|
|
AND (p.object_name LIKE 'TRN_IX%' OR p.object_name = 'TRN_UK1')
|
|
AND p.object_name = 'TRN_IX13'
|
|
--AND s.end_interval_time BETWEEN TO_DATE('01-Jun-12','DD-Mon-YY') and TO_DATE('30-Jun-12','DD-Mon-YY')
|
|
GROUP BY p.object_name
|
|
, p.sql_id
|
|
--, p.plan_hash_value
|
|
, to_char(end_interval_time,'YYYY-MM-DD')
|
|
-- having sum(ss.executions_delta) > 1
|
|
ORDER BY p.object_name
|
|
, p.sql_id
|
|
--, p.plan_hash_value
|
|
, sum(ss.executions_delta) desc
|
|
)
|
|
GROUP BY object_name
|
|
, sql_id
|
|
--HAVING ROUND(avg(daily_exections)) > 10
|
|
ORDER BY object_name
|
|
, sql_id
|
|
, avg_daily_exections desc
|
|
;
|
|
|
|
|
|
@@footer
|