Re: Timed statistics and SQL_TRACE for already running session
You will have to turn on timed-statistics for the entire database using 'alter system set timed_statistics=true'. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 08, 2001 11:13 AM I used the following syntax/package to run a trace on a user's already running session. EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); I then ran tkprof on the resulting trace file. It all worked fairly well but I didn't get any times on any of the statements that executed. Is there a way to use timed_statistics with this package? Or must I turn on timed statistics for the entire database? The user is using a canned application so there is no way for him to alter his session and set timed_statistics before he starts his work. Is there any way that I can alter his session and turn on timed_statistics for just his session? Thanks for any clever ideas, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Timed statistics and SQL_TRACE for already running session
I used the following syntax/package to run a trace on a user's already running session. EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); I then ran tkprof on the resulting trace file. It all worked fairly well but I didn't get any times on any of the statements that executed. Is there a way to use timed_statistics with this package? Or must I turn on timed statistics for the entire database? The user is using a canned application so there is no way for him to alter his session and set timed_statistics before he starts his work. Is there any way that I can alter his session and turn on timed_statistics for just his session? Thanks for any clever ideas, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Timed statistics and SQL_TRACE for already running session
There is another way. I'm managing user's environment with a procedure called by a db_startup on logon trigger. In that procedure, I'm changing the environment with dynamic sql depending on the user. This is how I'm setting up sort_area_size, hash_area_size,... specifically per user. HTH --- Igor Neyman [EMAIL PROTECTED] a écrit : You will have to turn on timed-statistics for the entire database using 'alter system set timed_statistics=true'. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 08, 2001 11:13 AM I used the following syntax/package to run a trace on a user's already running session. EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); I then ran tkprof on the resulting trace file. It all worked fairly well but I didn't get any times on any of the statements that executed. Is there a way to use timed_statistics with this package? Or must I turn on timed statistics for the entire database? The user is using a canned application so there is no way for him to alter his session and set timed_statistics before he starts his work. Is there any way that I can alter his session and turn on timed_statistics for just his session? Thanks for any clever ideas, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Vos albums photos en ligne, Yahoo! Photos : http://fr.photos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Timed statistics and SQL_TRACE for already running session
Which version of Oracle. From 8.1.6 onwards, dbms_system contains a call similar to set_sql_trace_in_session which is name something like: set_boll_param_in_session. Describe dbms_system to check the proper nmame and parms. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 August 2001 16:29 | |I used the following syntax/package to run a trace on a user's already |running |session. | |EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); | |I then ran tkprof on the resulting trace file. It all worked fairly well |but |I didn't get any times on any of the statements that executed. | |Is there a way to use timed_statistics with this package? |Or must I turn on timed statistics for the entire database? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Timed statistics and SQL_TRACE for already running session
Jonathan, Version is 8.0.4. I looked at the parameters for dbms_system in the 8.0.4 documentation and saw nothing pertaining to timed_statistics. I did an alter system over lunch and set timed statistics for the entire database without bouncing the database. That did the trick. Thanks for taking time to reply. It's cool to correspond directly with someone whose book I have read. Cherie Machler Oracle DBA Gelco Information Network Jonathan Lewis [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mon.co.uk cc: Sent by: Subject: Re: Timed statistics and SQL_TRACE for already running [EMAIL PROTECTED] session 08/08/01 12:56 PM Please respond to ORACLE-L Which version of Oracle. From 8.1.6 onwards, dbms_system contains a call similar to set_sql_trace_in_session which is name something like: set_boll_param_in_session. Describe dbms_system to check the proper nmame and parms. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 August 2001 16:29 | |I used the following syntax/package to run a trace on a user's already |running |session. | |EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); | |I then ran tkprof on the resulting trace file. It all worked fairly well |but |I didn't get any times on any of the statements that executed. | |Is there a way to use timed_statistics with this package? |Or must I turn on timed statistics for the entire database? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Timed statistics and SQL_TRACE for already running session
Stephane, This sounds very clever. However, it's too much work for me today as I have so irons in the fire. I'll keep it in mind for the future, though. Thanks for taking time to reply. Cherie paquette stephane stephane_paquette@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Subject: Re: Timed statistics and SQL_TRACE for already running [EMAIL PROTECTED] session 08/08/01 12:09 PM Please respond to ORACLE-L There is another way. I'm managing user's environment with a procedure called by a db_startup on logon trigger. In that procedure, I'm changing the environment with dynamic sql depending on the user. This is how I'm setting up sort_area_size, hash_area_size,... specifically per user. HTH --- Igor Neyman [EMAIL PROTECTED] a écrit : You will have to turn on timed-statistics for the entire database using 'alter system set timed_statistics=true'. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 08, 2001 11:13 AM I used the following syntax/package to run a trace on a user's already running session. EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); I then ran tkprof on the resulting trace file. It all worked fairly well but I didn't get any times on any of the statements that executed. Is there a way to use timed_statistics with this package? Or must I turn on timed statistics for the entire database? The user is using a canned application so there is no way for him to alter his session and set timed_statistics before he starts his work. Is there any way that I can alter his session and turn on timed_statistics for just his session? Thanks for any clever ideas, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Vos albums photos en ligne, Yahoo! Photos : http://fr.photos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
Re: Timed statistics and SQL_TRACE for already running session
Igor, Thanks for your reply. I decided to just turn it on for the whole database for a few minutes over lunch. Cherie Igor Neyman ineyman@perceTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ptron.comcc: Sent by: Subject: Re: Timed statistics and SQL_TRACE for already running [EMAIL PROTECTED]session om 08/08/01 10:51 AM Please respond to ORACLE-L You will have to turn on timed-statistics for the entire database using 'alter system set timed_statistics=true'. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 08, 2001 11:13 AM I used the following syntax/package to run a trace on a user's already running session. EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); I then ran tkprof on the resulting trace file. It all worked fairly well but I didn't get any times on any of the statements that executed. Is there a way to use timed_statistics with this package? Or must I turn on timed statistics for the entire database? The user is using a canned application so there is no way for him to alter his session and set timed_statistics before he starts his work. Is there any way that I can alter his session and turn on timed_statistics for just his session? Thanks for any clever ideas, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Timed statistics and SQL_TRACE for already running session
Do you know what does procedure WAIT_FOR_EVENT in this package is doing? Alex Hillman -Original Message- Sent: Wednesday, August 08, 2001 1:56 PM To: Multiple recipients of list ORACLE-L Which version of Oracle. From 8.1.6 onwards, dbms_system contains a call similar to set_sql_trace_in_session which is name something like: set_boll_param_in_session. Describe dbms_system to check the proper nmame and parms. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 August 2001 16:29 | |I used the following syntax/package to run a trace on a user's already |running |session. | |EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); | |I then ran tkprof on the resulting trace file. It all worked fairly well |but |I didn't get any times on any of the statements that executed. | |Is there a way to use timed_statistics with this package? |Or must I turn on timed statistics for the entire database? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Timed statistics and SQL_TRACE for already running session
This should do it; (works for me - I'm on 8.1.6.3) as system/manager... select sid,serial# from v$session where ...(to get process you want to enable trace for); exec dbms_system.set_sql_trace_in_session(sid,serial#,true); exec dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',tru e); Have user run query. Get trace file from udump dir and run tkprof. All stats and timings are there. Thanks for the pointer to this great system procedure Jonathan! -Original Message- Lewis Sent: Wednesday, August 08, 2001 1:56 PM To: Multiple recipients of list ORACLE-L Which version of Oracle. From 8.1.6 onwards, dbms_system contains a call similar to set_sql_trace_in_session which is name something like: set_boll_param_in_session. Describe dbms_system to check the proper nmame and parms. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 August 2001 16:29 | |I used the following syntax/package to run a trace on a user's already |running |session. | |EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE); | |I then ran tkprof on the resulting trace file. It all worked fairly well |but |I didn't get any times on any of the statements that executed. | |Is there a way to use timed_statistics with this package? |Or must I turn on timed statistics for the entire database? | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Timed statistics and SQL_TRACE for already running session
It's pure 8.1.6+ something like: dbms_system.set_bool_param(sid, serial,'parameter',true/false); I don't think there is ANYTHING sneaky you can do prior to 8.1.6; alter system is the only option for avoiding the bounce. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 August 2001 20:38 session | |Jonathan, | |Version is 8.0.4. I looked at the parameters for dbms_system in the 8.0.4 |documentation |and saw nothing pertaining to timed_statistics. | |I did an alter system over lunch and set timed statistics for the entire |database without |bouncing the database. That did the trick. | |Thanks for taking time to reply. It's cool to correspond directly with |someone whose |book I have read. | |Cherie Machler |Oracle DBA |Gelco Information Network | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
