Monday, August 22, 2011

Manual steps to pull out problematic SQL in Oracle

1. Pull out the sid, wait event, schema name, os user etc along with sql_hash_value and sql id for all the active sessions connected to the database
select  sid,v$session.event,v$session.schemaname,v$session.osuser,v$session.machine,v$session.module,v$session.sql_hash_value,v$session.sql_id     from v$session where sql_hash_value is not null  and status = 'ACTIVE'

2. If you want to find out the text of any the SQL that above users are running, pull it out by passing hash_value from the above query

select sql_text,users_executing from v$sql where hash_value = 1220590810
3. You can then query The V$session_Event view for the particular SID from step 1 and order the results by the Time_Waited Column. This way you can easily pick out the bottlenecks that may be contributing to poor performance.

select * From V$session_Event Where Sid = 168 order by time_waited

4. DBMS_XPLAN

You can view the explain plan by using the below mentioned script. It can take a sql_id, a child number and a format parameter. I like the format ‘ALL’ best, as it gives me most information about a statement.

select * from table(dbms_xplan.display_cursor('3xjhbw9m5u9qu',format=>'ALL'))

5. This is yet another awesome tool in your repository if you have the license. It allows you to check what’s happening during the execution of a SQL statement, which is more than useful in larger data warehouse style queries.


select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id => '3xjhbw9m5u9qu',
report_level=>'ALL')
from dual;

No comments:

Post a Comment