Re: tkprof output
I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start tracing on 8.1.6 db . What should I do to stop tracing without exiting out of session . Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 9:07 AM An action with dep=n+1 (n=0) for cursor #k is the recursive child of the next dep=n action for cursor #k that immediately follows in the trace data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Wednesday, March 12, 2003 10:16 AM To: Multiple recipients of list ORACLE-L Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to:
Re: tkprof output
also how can we check if one particular session is being traced (10046 ) or not . Basically I want to be sure that tracing is stopped for the session and its not fillling up disk space . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 9:07 AM An action with dep=n+1 (n=0) for cursor #k is the recursive child of the next dep=n action for cursor #k that immediately follows in the trace data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Wednesday, March 12, 2003 10:16 AM To: Multiple recipients of list ORACLE-L Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an
Re: tkprof output
sys.dbms_system.set_ev( v_seid ,v_sernum ,10046, 0,'') Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 10:49 AM I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start tracing on 8.1.6 db . What should I do to stop tracing without exiting out of session . Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 9:07 AM An action with dep=n+1 (n=0) for cursor #k is the recursive child of the next dep=n action for cursor #k that immediately follows in the trace data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Wednesday, March 12, 2003 10:16 AM To: Multiple recipients of list ORACLE-L Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the
RE: tkprof output
sys.dbms_system.set_ev( v_seid, v_sernum, 10046, 0, '' ) ^ Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Thursday, March 13, 2003 9:49 AM To: Multiple recipients of list ORACLE-L I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start tracing on 8.1.6 db . What should I do to stop tracing without exiting out of session . Thanks, -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 9:07 AM An action with dep=n+1 (n=0) for cursor #k is the recursive child of the next dep=n action for cursor #k that immediately follows in the trace data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Wednesday, March 12, 2003 10:16 AM To: Multiple recipients of list ORACLE-L Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ:
Re: tkprof output
Repeat but changing the 12 to a zero should work. You will find, however, that any cursor that has not closed when you stop tracing will not dump its 'STAT' lines (including execution plan) to the trace file. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 13 March 2003 15:49 I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start tracing on 8.1.6 db . What should I do to stop tracing without exiting out of session . Thanks, -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: tkprof output
Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Re: tkprof output
The examples you have given are SYS-recursive, The call to cdef$ is Oracle looking for some information about constraints (one possibility is that you keep breaking a PK or UK constraint and Oracle has to keep looking up the name of the constraint because it doesn't cache constraint names). The call to seq$ usually appears because you are using a sequence with a very small, or no, CACHE set - so as you get the next value from the sequence, oracle has to bump the sequence high-water value and write it to disc. In general, the recursive depth simply tells you how far down the stack of calls your cursor is. For example (which may be wrong in detail, but right in gist) if you execute an anonymous pl/sql which runs an SQL statement that uses a sequence.nextval that happens to bump the sequence high-water, you would (probably see): anonymous pl/sqldep = 0 SQL statementdep = 1 update seq$ statementdep = 3 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 12 March 2003 16:15 Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: tkprof output
An action with dep=n+1 (n=0) for cursor #k is the recursive child of the next dep=n action for cursor #k that immediately follows in the trace data. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Mar 25-27 Oxford - Hotsos Clinic 101, Apr 8-10 Chicago -Original Message- Sent: Wednesday, March 12, 2003 10:16 AM To: Multiple recipients of list ORACLE-L Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:54 PM Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
Re: tkprof output
Thanks Jonathan for the information . Let me go through raw -trace file once more to get info . Everytime I go through it I find something interesting . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 8:54 AM The examples you have given are SYS-recursive, The call to cdef$ is Oracle looking for some information about constraints (one possibility is that you keep breaking a PK or UK constraint and Oracle has to keep looking up the name of the constraint because it doesn't cache constraint names). The call to seq$ usually appears because you are using a sequence with a very small, or no, CACHE set - so as you get the next value from the sequence, oracle has to bump the sequence high-water value and write it to disc. In general, the recursive depth simply tells you how far down the stack of calls your cursor is. For example (which may be wrong in detail, but right in gist) if you execute an anonymous pl/sql which runs an SQL statement that uses a sequence.nextval that happens to bump the sequence high-water, you would (probably see): anonymous pl/sqldep = 0 SQL statementdep = 1 update seq$ statementdep = 3 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 12 March 2003 16:15 Thanks Jonathan, what is meaning of recursive depth ? I see calls to cdef$, seq$ tables/views does it hint something . I though procedure is using some sequence and these are internal calls to generate seq numbers . Is that rite ? -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: tkprof output
Run tkprof with SYS = YES option and find recursive calls that contribute to this time. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:29 PM I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: tkprof output
Using sys=yes on the tkprof command line would be a good start. That way you will see which statements are generating all the recursive SQL. Jared AK [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/11/2003 11:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:tkprof output I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: tkprof output
Any SQL within a pl/sql block is recursive SQL (user recursive, rather than SYS recursive) so this time could simply be the cost of running your application code. Unfortunately the tkprof output doesn't quote the recursive depth of the SQL - however if you identify possible suspects, you can check back in the raw trace file for lines like: PARSING IN CURSOR #N there will be a bit in the line like dep=n If n is not zero, then this is a 'recursive' cursor. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 19:29 I am running tkprof on event 10046 output and I see at the end . elapsed time of 18 secs in recursive calls thats what surprises me . Do you know what I should look at next and what can be done to reduce these timings Thanks, -ak OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 26 0.08 0.10 3 26 0 0 Execute 37 0.04 0.08 0 4 6 2 Fetch 32 1.13 2.14 5079 5189 70 30 --- -- -- -- -- -- -- total 95 1.25 2.32 5082 5219 76 32 Misses in library cache during parse: 23 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 41 0.03 0.05 0 0 0 0 Execute541 4.40 12.30 1051 6442 63782 38712 Fetch 550 3.23 6.31 3977 24298340 419 --- -- -- -- -- -- -- total 1132 7.66 18.66 5028 30740 64122 39131 Misses in library cache during parse: 9 164 user SQL statements in session. 41 internal SQL statements in session. 205 SQL statements in session. 0 statements EXPLAINed in this session. ** ** Trace file: ora_28633_ak.trc Trace file compatibility: 8.00.04 Sort options: default 1 session in tracefile. 164 user SQL statements in trace file. 41 internal SQL statements in trace file. 205 SQL statements in trace file. 167 unique SQL statements in trace file. 5369 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Tkprof output
Set you 10046 trace at level 4 or 12, and then check the trace file. Paul -Original Message- Sent: Friday, February 22, 2002 3:53 PM To: Multiple recipients of list ORACLE-L Hi Listers, I got the following statement in the tkprof output file : update EMP set ENAME=:V001,EMPNO=:V002 where rowid = :V003 I'm just wondering how to get the value of that variables (instead of :V001, :V002 and :V003). Is there a way to do that ? Thanks. Aldi _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco 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: Tkprof output
Paul (or anyone) - Is there a way to set the level within the session as: alter session set sql_trace = true Or do I have to start the session, start a SQLPLUS session, find the session_id and run the procedure to set it on another session? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 22, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Set you 10046 trace at level 4 or 12, and then check the trace file. Paul -Original Message- Sent: Friday, February 22, 2002 3:53 PM To: Multiple recipients of list ORACLE-L Hi Listers, I got the following statement in the tkprof output file : update EMP set ENAME=:V001,EMPNO=:V002 where rowid = :V003 I'm just wondering how to get the value of that variables (instead of :V001, :V002 and :V003). Is there a way to do that ? Thanks. Aldi _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Tkprof output
Dennis: In the session: alter session set events '10046 trace name context forever, level 12' Barb -- From: DENNIS WILLIAMS[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, February 22, 2002 3:08 PM To: Multiple recipients of list ORACLE-L Subject: RE: Tkprof output Paul (or anyone) - Is there a way to set the level within the session as: alter session set sql_trace = true Or do I have to start the session, start a SQLPLUS session, find the session_id and run the procedure to set it on another session? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, February 22, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Set you 10046 trace at level 4 or 12, and then check the trace file. Paul -Original Message- Sent: Friday, February 22, 2002 3:53 PM To: Multiple recipients of list ORACLE-L Hi Listers, I got the following statement in the tkprof output file : update EMP set ENAME=:V001,EMPNO=:V002 where rowid = :V003 I'm just wondering how to get the value of that variables (instead of :V001, :V002 and :V003). Is there a way to do that ? Thanks. Aldi _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Baker, Barbara 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: Tkprof output
Title: RE: Tkprof output A huge thanks for all those who took the time out to respond to my problem. The query ran to completion yesterday and (as I said to Lisa) I was later told that I was not to worry as it was a one off migration (well that only wasted about 1- 2 hours of my time investigating and whatever it took you guys to help me out). Gr Lee -Original Message-From: Paul Baumgartel [mailto:[EMAIL PROTECTED]]Sent: 30 August 2001 19:31To: Multiple recipients of list ORACLE-LSubject: RE: Tkprof output Also note the very high"query" number (i.e. buffers gotten for consistent read). That could account for a lot of the i/o, which is the proximate cause of the lng elapsed time. From the (to use one of Lisa's favorite terms) doco (for 9i): Read Consistency Your system might spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios: If there are many small transactions and an active long-running query is running in the background on the same table where the changes are happening, then the query might need to roll back those changes often, in order to obtain a read-consistent image of the table. Compare the following V$SYSSTAT statistics to determine whether this is happening: consistent changes statistic indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources. consistent gets statistic counts the number of logical reads in consistent mode. If there are few very, large rollback segments, then your system could be spending a lot of time rolling back the transaction table during delayed block cleanout in order to find out exactly which SCN a transaction was committed. The ratio of the following V$SYSSTAT statistics should be close to 1: ratio = transaction tables consistent reads undo records applied / transaction tables consistent read rollbacks A solution is to create more, smaller rollback segments, or to use automatic undo management. Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED] -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 30, 2001 12:47 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Tkprof output Lee, This query seems suspect UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1WHEREACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 because of this call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 0 0.00 0.00 0 0 0 0Execute 39562 4.55 7.22 10897 118687 1 1Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 39562 4.55 7.22 10897 118687 1 1 It's reading a ton of blocks to operate on ONE record. What's the table structure here? What's the index structure? Cardinality? The buffer gets in the other queries are suspect too. What's your blocksize? It's reading a ton of blocks to arrive at the result. -Original Message- From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject: Tkprof output Apologies for the length of the mail. This query is running for a mad amount of time, anyone any ideas. Code and tkprof out put shown below. Huge TIA Lee (who must learn more about such things !!!) DECLARE CURSOR TEMP_CDS ISSELECT ACXIOM_CUSTOMER_KEY, VERSION_NO, ADDRESS_OCCUPANCY_KEYFROM CUSTOMER_DETAIL_SOURCEWHERE VISIBLE=1; COUNTER NUMBER(8); BEGIN COUNTER:=0; FOR I IN TEMP_CDS LOOP UPDATE SCHEMA..SINGLE_CUSTOMER SC SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..ADDRESS_OCCUPANCY AO SET VISIBLE = 1 WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY; COUNTER := COUNTER + 1; IF (COUNTER = 5) THEN COUNTER:=0; COMMIT; END IF; END LOOP; COMMIT; So
RE: Tkprof output
Title: RE: Tkprof output Lee, This query seems suspect UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1 WHERE ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 because of this call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 0 0.00 0.00 0 0 0 0 Execute 39562 4.55 7.22 10897 118687 1 1 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 39562 4.55 7.22 10897 118687 1 1 It's reading a ton of blocks to operate on ONE record. What's the table structure here? What's the index structure? Cardinality? The buffer gets in the other queries are suspect too. What's your blocksize? It's reading a ton of blocks to arrive at the result. -Original Message- From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject: Tkprof output Apologies for the length of the mail. This query is running for a mad amount of time, anyone any ideas. Code and tkprof out put shown below. Huge TIA Lee (who must learn more about such things !!!) DECLARE CURSOR TEMP_CDS IS SELECT ACXIOM_CUSTOMER_KEY, VERSION_NO, ADDRESS_OCCUPANCY_KEY FROM CUSTOMER_DETAIL_SOURCE WHERE VISIBLE=1; COUNTER NUMBER(8); BEGIN COUNTER:=0; FOR I IN TEMP_CDS LOOP UPDATE SCHEMA..SINGLE_CUSTOMER SC SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..ADDRESS_OCCUPANCY AO SET VISIBLE = 1 WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY; COUNTER := COUNTER + 1; IF (COUNTER = 5) THEN COUNTER:=0; COMMIT; END IF; END LOOP; COMMIT; Sort options: prsela exeela fchela count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1 WHERE ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 0 0.00 0.00 0 0 0 0 Execute 39562 15.51 398.98 56555 181085 40672 39562 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 39562 15.51 398.98 56555 181085 40672 39562 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 39 (VM_LIVE) (recursive depth: 1) Rows Execution Plan --- --- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'SINGLE_CUSTOMER' 0 TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER' 0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE) UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1 WHERE ADDRESS_OCCUPANCY_KEY = :b1 call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 0 0.00 0.00 0 0 0 0 Execute 39562 12.57 186.88 57285 124038 40726 39562 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 39562 12.57 186.88 57285 124038 40726 39562 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 39 (VM_LIVE) (recursive depth: 1) Rows Execution Plan --- --- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'ADDRESS_OCCUPANCY' 0 INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE) UPDATE
RE: Tkprof output
Are you on 8i? Then you can use BULK binding which will definitely give you some speed. IF you need example ... let me know. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 Are you on 8i?Then youcan use BULK binding which will definitely give you some speed. IF you need example ... let me know. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art !
RE: Tkprof output
Title: RE: Tkprof output Also note the very high"query" number (i.e. buffers gotten for consistent read). That could account for a lot of the i/o, which is the proximate cause of the lng elapsed time. From the (to use one of Lisa's favorite terms) doco (for 9i): Read Consistency Your system might spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios: If there are many small transactions and an active long-running query is running in the background on the same table where the changes are happening, then the query might need to roll back those changes often, in order to obtain a read-consistent image of the table. Compare the following V$SYSSTAT statistics to determine whether this is happening: consistent changes statistic indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources. consistent gets statistic counts the number of logical reads in consistent mode. If there are few very, large rollback segments, then your system could be spending a lot of time rolling back the transaction table during delayed block cleanout in order to find out exactly which SCN a transaction was committed. The ratio of the following V$SYSSTAT statistics should be close to 1: ratio = transaction tables consistent reads undo records applied / transaction tables consistent read rollbacks A solution is to create more, smaller rollback segments, or to use automatic undo management. Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED] -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 30, 2001 12:47 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Tkprof output Lee, This query seems suspect UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1WHEREACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 because of this call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 0 0.00 0.00 0 0 0 0Execute 39562 4.55 7.22 10897 118687 1 1Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 39562 4.55 7.22 10897 118687 1 1 It's reading a ton of blocks to operate on ONE record. What's the table structure here? What's the index structure? Cardinality? The buffer gets in the other queries are suspect too. What's your blocksize? It's reading a ton of blocks to arrive at the result. -Original Message- From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject: Tkprof output Apologies for the length of the mail. This query is running for a mad amount of time, anyone any ideas. Code and tkprof out put shown below. Huge TIA Lee (who must learn more about such things !!!) DECLARE CURSOR TEMP_CDS ISSELECT ACXIOM_CUSTOMER_KEY, VERSION_NO, ADDRESS_OCCUPANCY_KEYFROM CUSTOMER_DETAIL_SOURCEWHERE VISIBLE=1; COUNTER NUMBER(8); BEGIN COUNTER:=0; FOR I IN TEMP_CDS LOOP UPDATE SCHEMA..SINGLE_CUSTOMER SC SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..ADDRESS_OCCUPANCY AO SET VISIBLE = 1 WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY; COUNTER := COUNTER + 1; IF (COUNTER = 5) THEN COUNTER:=0; COMMIT; END IF; END LOOP; COMMIT; Sort options: prsela exeela fchelacount = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1WHEREACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 call count cpu elapsed disk query cu
RE: Tkprof output
Title: Message Take a look at using array fetches to retrieve rows more efficeintly. It appears you are fetching 1 row at a time. Which can be grossly inefficient. Another helpful thing would to generate the TKPROF with Explan plans so you can see the rows per step and the paths chosen. Also, consider of bulk transaction use for the updates, which seem to be touching alot of blocks. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 11:56 AMTo: Multiple recipients of list ORACLE-LSubject: Tkprof output Apologies for the length of the mail. This query is running for a mad amount of time, anyone any ideas. Code and tkprof out put shown below. Huge TIA Lee (who must learn more about such things !!!) DECLARE CURSOR TEMP_CDS ISSELECT ACXIOM_CUSTOMER_KEY, VERSION_NO, ADDRESS_OCCUPANCY_KEYFROM CUSTOMER_DETAIL_SOURCEWHERE VISIBLE=1; COUNTER NUMBER(8); BEGIN COUNTER:=0; FOR I IN TEMP_CDS LOOP UPDATE SCHEMA..SINGLE_CUSTOMER SC SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE = 1 WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY AND VERSION_NO =I.VERSION_NO; UPDATE SCHEMA..ADDRESS_OCCUPANCY AO SET VISIBLE = 1 WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY; COUNTER := COUNTER + 1; IF (COUNTER = 5) THEN COUNTER:=0; COMMIT; END IF; END LOOP; COMMIT; Sort options: prsela exeela fchela count = number of times OCI procedure was executedcpu = cpu time in seconds executing elapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1 WHEREACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 0 0.00 0.00 0 0 0 0Execute 39562 15.51 398.98 56555 181085 40672 39562Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 39562 15.51 398.98 56555 181085 40672 39562 Misses in library cache during parse: 0Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: 39 (VM_LIVE) (recursive depth: 1) Rows Execution Plan--- --- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'SINGLE_CUSTOMER' 0 TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER' 0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE) UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1 WHEREADDRESS_OCCUPANCY_KEY = :b1 call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 0 0.00 0.00 0 0 0 0Execute 39562 12.57 186.88 57285 124038 40726 39562Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 39562 12.57 186.88 57285 124038 40726 39562 Misses in library cache during parse: 0Optimizer goal: CHOOSEParsing user id: 39 (VM_LIVE) (recursive depth: 1) Rows Execution Plan--- --- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'ADDRESS_OCCUPANCY' 0 INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE) UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1 WHEREACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2 call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 0 0.00 0.00 0 0 0 0Execute 39562 4.55 7.22 10897 118687 1 1Fetch 0 0.00 0.00 0 0 0 0--- -- -- --
