Re: Manual execution vs v$session discrepancy

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 16 Sep 2015 23:49:17 +0200 (CEST)
Message-ID: <946354878.627321.1442440157824.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hi Ram,

> Is it possible to turn trace on in another user's session using the method you mentioned. It seems to be turning on tracing for one SQL in one's own
> session

Yes, as i said the event is "pushed" into the existing (other) sessions as well, if parameter "_evt_system_event_propagation" is set to TRUE. Here is a short demo of the behavior. SID 19 activates the event for SQL "select * from dual;" while SID 328 is already connected. SID 328 runs the corresponding SQL afterwards.

SYS_at_S12DB:19> alter system set events 'sql_trace [sql:a5ks9fhw2v9s1] wait=true,bind=true,plan_stat=all_executions';

SYS_at_S12DB:328> select * from dual;
…

*** 2015-09-16 23:38:47.912
*** SESSION ID:(328.58909) 2015-09-16 23:38:47.912
*** CLIENT ID:() 2015-09-16 23:38:47.912
*** SERVICE NAME:(S12DB) 2015-09-16 23:38:47.912
*** MODULE NAME:(sqlplus_at_localhost (TNS V1-V3)) 2015-09-16 23:38:47.912
*** CLIENT DRIVER:(SQL*PLUS ) 2015-09-16 23:38:47.912
*** ACTION NAME:() 2015-09-16 23:38:47.912

…
PARSING IN CURSOR #18446604434620268184 len=18 dep=0 uid=0 oct=3 lid=0 tim=1225114037 hv=942515969 ad='c0710840' sqlid='a5ks9fhw2v9s1' select * from dual
END OF STMT
EXEC #18446604434620268184:c=20,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=1225114029 WAIT #18446604434620268184: nam='SQL*Net message to client' ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1225114317 FETCH #18446604434620268184:c=169,e=168,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=1225114609 FETCH #18446604434620268184:c=9,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=1225115455 WAIT #18446604434620268184: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1225115564 …

NAME VALUE ISSYS_MOD DESCRIPTION

------------------------------------------------------- -------------------------------------------------- --------- ---------------------------------
_evt_system_event_propagation				TRUE						   IMMEDIATE disable system event propagation

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Ram Raman <veeeraman_at_gmail.com> hat am 16. September 2015 um 17:25 geschrieben:
>
> Stefan,
>
> Is it possible to turn trace on in another user's session using the method you mentioned. It seems to be turning on tracing for one SQL in one's
> own session.
>
> Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 16 2015 - 23:49:17 CEST