-
tracing session to see how the application was executed in Oracle
Hi all,
I have the application and I want to know how it executed behind the scene if I look into v$SQLAREA I don't know which one belong to that session and if I executed the script below
SELECT A.SID,A.USERNAME,S.SQL_TEXT
FROM V$SESSION A,V$SQLTEXT S
WHERE A.SQL_ADDRESS = S.ADDRESS
AND A.SQL_HASH_VALUE = S.HASH_VALUE
ORDER BY A.USERNAME,A.SID,S.PIECE;
sometime it doesn't capture. does anyone have a better scripts to capture the query that was executed from the application???
Thanks
BTW: how can we clear the V$SQLAREA ????
-
could set sql_trace=true in init.ora
but that could produce u tons of information, not the best method
try looking in v$sql as well
-
Do the following ...
Alter system set timed_statistics=true;
select sid,serial#,username,machine,terminal from v$session
set tracing for the particular session with this command:
Execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
Go to your UDUMP directory. You should find some trace files here. Run:
TKPROF trace_filename1, trace_reportname1
TKPROF trace_filename2, trace_reportname2
Look at the trace_reportname1, trace_reportname2, .. files for the SQL that the user is running.
Regards,
K.Diwakar
-
HI,
As Davey has already pointed out, this could potentially provide you with tons of information, TKPROF and Tracing is usually used to tune the statement.
You could do it the quick and dirty way by finding the SID of the user from v$session and query v$sqlarea with ..
select sql_text from v$sqlarea where parsing_user_id =
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Code:
create or replace trigger sqltrace
after logon on database
begin
if user='XXXXX'
then
execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
end if;
end;
/
just change XXXX for any user you want to trace
Last edited by pando; 12-04-2002 at 01:29 PM.
-
set head on
set pagesize 5000
set linesize 135
column pu format a8 heading 'O/S|Login|ID' justify left
column su format a8 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Oracle|Session|ID' justify right
column sser format 999999 heading 'Oracle|Serial|No' justify right
column spid format 999999 heading 'UNIX|Process|ID' justify right
column txt format a28 heading 'Current Statment' justify center word
column logon_time format a15
column prog format a50 heading 'Program|running|they are' justify left
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time,'DD-MON-RR hh24:mi') logon_time,
substr(sa.sql_text,1,540) txt ,
s.machine,
s.program prog
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
order by 1,2,7
/
column pu clear
column su clear
column stat clear
column ssid clear
column sser clear
column spid clear
/
Giani
-
Pando,
thanks for the triggers but I have problem when I log in to the database as the user was traced.
Redo thread mounted by this instance: 1
Oracle process number: 23
Windows thread id: 1788, image: ORACLE.EXE
*** SESSION ID:(25.1839) 2002-12-04 09:52:39.107
Skipped error 604 during the execution of TEST.SQLTRACE
*** 2002-12-04 09:52:39.107
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-02095: specified initialization parameter cannot be modified
ORA-06512: at line 4
it happened on two or three different instance that I have tried, is there anything else I have to run ???
-
well you have not used my trigger because I dont change any parameter, also you must grant alter session privilege directly to trigger owner
-
Originally posted by pando
well you have not used my trigger because I dont change any parameter, also you must grant alter session privilege directly to trigger owner
what do you mean ??? I thought I only have to change XXXX to the user I want to trace and also I have grant
grant alter session to TEST
but it still doesn't work
-
well it works for me 100% so I am not sure what you have done
run alter session set event in a sqlplus session and see if you can do that
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|