trace top sql session

yhj20041128001發表於2013-11-01
SQLSession

1. How can we find these sessions according these sql id?
The answer is check the view dba_hist_active_sess_history, for example:
select snap_id,dbid,sample_time,session_id,sql_id from dba_hist_active_sess_history where sql_id='gks5t3d6285bw';
2. Could you please provide the v$session output?
conn / as sysdba
set pages 1000
set num 20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set mark html on
spool sess.html
select * from v$session;
spool off
exit
 
We have reviewed the screenshot you uploaded.
But we need know the SQL which the report executed, otherwise we even don't know what is the issue.
Action Plan
========
1. First, we need to know the session in the database which was created by the report application.
Please work with your dba to find out the session id.
SQL> select * from v$session; <<<<<< find out the session id.
2. Then we need to get the spid for the session, you can get spid by:
SQL>select spid from v$session a, v$process b where a.paddr=b.addr and a.sid=123;==>change this to the actual sid.
3. Then we can trace the report.
SQL> connect / as sysdba
SQL>oradebug setospid 12345 ==>Please change the 12345 to your actual spid returned by step 2.
SQL>oradebug unlimit
SQL>oradebug event 10046 trace name context forever, level 12
==>Generate the report in your application till it finish.
SQL>oradebug event 10046 trace name context off;
A trace file will be generated under udump.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23757700/viewspace-775536/，如需轉載，請註明出處，否則將追究法律責任。

相關文章