How to trace another session

[Subha]

I have an appication which is connect to the database .
I have to grep for the appicatiob for finding the unix process id and how do i find which session is that in oracle .Then I need to turn on the trace for that particular session .

How to do that??

[marist89]

Find out what the sid, serial# is from this query. Your unix process id is the server_pid column:

set pages 50
col kill_by for a12
col username for a12
col program for a40 
col "MACHINE/PID" for a20
set linesize 132
set pages 100
select '''' || s.sid ||','||s.serial# || '''' kill_by, 
   p.spid server_pid, s.username, s.program, 
   decode(s.process, NULL, s.machine, s.machine || ' PID: ' ||  s.process) "MACHINE/PID" ,
   s.server, s.status
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
order by to_number(p.spid)
/

Then, start tracing the session by issuing the following from sqlplus as system:

exec sys.dbms_system.set_sql_Trace_in_session(sid_from_above_query, serial#_from_above_query, true);

[Subha]

thanks it worked wonderfully.

[Subha]

select all trade.object_id, trade.lock_owner,
trade.object_type, trade.reference,
trade.a_content_type, trade.object_name,
trade.current_state, trade.policy_id,
trade.link_cnt, trade.is_virtual_doc,
trade.content_size, trade.assembled_from_id
from
trade_sp trade where ( ( ( (upper(trade.object_name)=
upper('RA00045367')) and (upper(trade.document_class)=
upper('non-allied')) ) and (trade.hidden=0) ) ) and
(trade.folder = 1 and trade.deleted = 0)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.05 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 37.85 116.13 30582 2187191 10 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 37.87 116.18 30582 2187191 10 2

Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 25 (SNMP)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
272466 TABLE ACCESS FULL TRADE_S
1 TABLE ACCESS BY INDEX ROWID CDS_SYSOBJECT_S
544930 INDEX UNIQUE SCAN (object id 4920)

This statement is killing my application .What best can be done for performance .

[marist89]

This:
upper(trade.object_name)= upper('RA00045367'))

and this:
(upper(trade.document_class)= upper('non-allied'))

are killing your query. I would suggest forcing trade.object_name and trade.document_class as uppercase when they are stored. That way, when you index these fields, the optimizer will be able to use it.

As an alternative, you can create a function based index on the two fields.