| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: set_sql_trace_in_session
How about for your current session:
ALTER SESSION SET timed_statistics=true; ALTER SESSION SET max_dump_file_size=unlimited; ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Or for another user's session:
SELECT s.username, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.username like 'LUSER%';
ALTER SYSTEM SET timed_statistics=true;
ALTER SYSTEM SET max_dump_file_size=unlimited;
oradebug setospid <spid>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
Both straight from the pages (41-42) of "Oracle Performance Tuning 101" by Gaja Vaidyanatha and Kirti Deshpande (hope you guys don't mind - if you do just slap me down). Not sure about for a particular schema. Get this book and read chapter 2 over and over again. It doesn't matter how much it costs (not much) - its worth it! Check bookpool and amazon...
--Scott Shafer
San Antonio, TX
> Hi Bruce,
>
> I thought of that but we have many VERY quick connects and disconnects
(web
> application without persistent connections) so I'm not sure whether the
loop
> would catch everything or what the overhead may be. I'll probably give it
a
> try anyway to see what comes out.
>
> Well I could do this:
> alter SYSTEM set events '10046 trace name context forever, level 8';
> but... it gives a lot of sys user stuff too. How to do this on a single
> schema?
>
> Anyone ever done this on a production system? It's connected to developing
a
> "real life" benchmarking routine.
>
> Steve Orr
>
>
> -----Original Message-----
> [mailto:Bruce.Reardon_at_comalco.riotinto.com.au]
> Sent: Monday, November 19, 2001 4:36 PM
> To: 'ORACLE-L_at_fatcity.com'
> Cc: Orr, Steve
>
>
> Steve,
>
> How about writing a PL SQL cursor to loop through all dedicated
connections
> and then for each Sid, to use:
> SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, TRUE);
>
> Do a similar thing to turn it off.
>
> Bruce Reardon
>
> -----Original Message-----
> Sent: Tuesday, 20 November 2001 10:25
>
> On a similar note, I'm looking for a way to toggle trace system-wide.
> Changing the init.ora sql_trace parameter is not an option since it
requires
> recycling the database.
>
> Any way to turn sql trace on for a few hours and then turn it back off?
>
>
> Steve Orr
>
> -----Original Message-----
> Sent: Monday, November 19, 2001 8:36 AM
>
> It is in the 8.1.6 documentation I have just looked at
> (Oracle8i Supplied PL/SQL Packages Reference
> Release 2 (8.1.6) A76936-01
> However I have a page on my website which shows how to put various bits of
> tracing on
> try http://www.hcresources.co.uk for the home page or
> http://www.hcresources.f2s.com/trace.htm
> to go direct.
> John
> -----Original Message-----
> Sent: 19 November 2001 15:05
>
> DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION isn't documented in the 8.1.6/7
> doc sets. The package (and the procedure) exists, though even the
> package spec source code is wrapped. What gives? Is this package
> about to be desupported? Is there an alternative way of setting trace
> on in another session?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: sknd100_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Nov 19 2001 - 19:20:34 CST
 |
 |