FROM gv$active_session_history
WHERE inst_id = 2 AND session_id = 249 AND session_serial# = 24899 GROUP BY sql_id
ORDER BY 2 DESC;
SELECT activity_pct percent, db_time, h.sql_id, sq.SQL_TEXT
FROM (SELECT round(100 * ratio_to_report(count(*)) OVER(), 1) AS activity_pct,
count(*) AS db_time,
sql_id
FROM gv$active_session_history
WHERE inst_id = 2 AND session_id = 249 AND session_serial# = 24899
AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY count(*) DESC) h ,
v$sql sq
where h.sql_id=sq.sql_id(+)
–ASH TOP5 SQL_ID=&&1 Executions by Elapsed Time
select *
from (select inst_id,
sql_id,
sql_plan_hash_value,
— sql_full_plan_hash_value,
sql_exec_id,
sql_child_number as CHILD_ID,
count(distinct sample_id) as ash_rows,
count(distinct inst_id||’,’||session_id||’,’||session_serial#) – 1 as PX,
max(sample_time) – min(sample_time) as “DURATIONs”,
min(sample_time) as min_sample_time,
max(sample_time) as max_sample_time
from gv$active_session_history
where sql_id = ’02mudnynv8kt8′
and (sql_plan_hash_value = 2406006552 )
and sql_exec_id > 0
group by inst_id, sql_id, sql_child_number, sql_exec_id, sql_plan_hash_value
–, sql_full_plan_hash_value
order by count(distinct sample_id) desc)
where rownum <= 5
sql_id=02mudnynv8kt8 is seen 12201 times within ash sample time and 54.5% of the time spent on that sql.
So now we look at the Enterprise Manager Sql Monitor for that sql_id
When we look at activity column, we see that sql is doing full scan and executing 83k times. 83K times full scan
on table and this is comprise 96% of total activitiy.
We find the this table on sql:
And create and index
create index SCHEMA.TABLE_IDX1 on SCHEMA.TABLE(CONTRACT_ID,ZEYILNO) tablespace IDX_TS;
After creating index, sql running 3 hours is just finished 3 minutes.
