How to trace a session when user is trying to run reports remotely or from any website for a slowness issue:

I recently faced this issue where our client is seeing some slowness issues while running reports via
from some portal kinda webiste. So, bascially we jus need to figure out the SQL_ID of the sql
queries they are running.

• When the user runs the report, fetch the sid , spid from TOAD ( easy way )  or v$session

select sid,spid from v$session where status='ACTIVE' and type!='BACKGROUND'


 connect / as sysdba
 oradebug setospid 28611 – this is os pid you want to trace
 oradebug unlimit
 oradebug event 10046 trace name context forever,level 12;
 oradebug event 10053 trace name context forever,level 1;

 oradebug tracefile_name;  -- this will give you the trace file name.

 oradebug event 10046 trace name context off;
 oradebug event 10053 trace name context off;
    

Now check your trace file:

/orahome/oracle/product/11.2.0.4/diag/rdbms/wmsprd/wmsprd/trace/wmsprd_ora_28611.trc
/orahome/oracle/product/11.2.0.4/diag/rdbms/wmsprd/wmsprd/trace/wmsprd_ora_29076.trc


Check the historical executions and PHV history for the SQL ID:

select SQL_ID,SQL_PLAN_HASH_VALUE,to_char(SQL_EXEC_START,'DD-MON-RR HH24:MI')  from dba_hist_active_sess_history where SQL_ID='93zs4x57ktash'