Query Tuning With EM SQL Monitoring

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.

Tags

What do you think?

Leave a Reply

Your email address will not be published. Required fields are marked *

Related articles

Resumable_timeout

Today, one of our test guys said that their process was hang up and waited for a log time. The process is was very simple: parse the file, do some calculation and insert the Oracle database.

Read more
Contact us

Partner with Us for Comprehensive IT

Our expertise is available to address your concerns and work together to identify the optimal services for your needs.

Your benefits:
What happens next?
1

We Schedule a call at your convenience 

2

We do a discovery and consulting meting 

3

We prepare a proposal 

Schedule a Free Consultation