Re: TKProf Analyzer
Hi, check out Mogens site, he has a PL/SQL tool for storing trace files in a repository. Its called TraceFile Repository and was written by Torben Holm and its free - have a look http://www.miracleas.dk/tools/Mir TFR104.zip -t might be an alternative for you? - it has a web based front end using htp and htf so is quite handy to view your trace files. hope this helps Kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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).
TKProf Analyzer
Dear Guru's I am frequently running tkprof of a PL/SQL program and I would like to have a repository of Tkprof output. I would like to build a utility that would parse the tkprof output into different areas and store it in the database. Say we can split the tkprof output into 1. Statement Section 2. Execution Statistics secion(parse,execute,fetch) 2. Explain plan section I would use this repository for analyzing the change in the execution plan/statistics. Has any body used a tool for this ? Your pointers are very much appriciated. Best Regards Sriram Kumar DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
Help on tkprof output
Hi Gurus, Could someone shed some light on the following tkprof output. To get 0 record it is aking more than 11 seconds. Also I see huge difference between CPU time and elapsed time even though the system is not so busy(It is a test machine. very low load on it). If you say it is waiting on something, could you tell me how to identify the wait event associated with this and how to rectify the same? Also please let me know why the query count is very high? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse2 0.07 0.08 0 0 0 0 Execute 2 0.00 0.02 0 0 0 0 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total6 43.02 133.31 58730 118694 24 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 165 Rows Row Source Operation --- --- 0 SORT ORDER BY 0 NESTED LOOPS 1NESTED LOOPS 1590 HASH JOIN 239 TABLE ACCESS FULL c 1589 HASH JOIN 239 VIEW VW_NSO_1 239SORT UNIQUE 239 INDEX FAST FULL SCAN (object id 76648) 1589 TABLE ACCESS FULL p 1589 TABLE ACCESS BY INDEX ROWID e 1589 INDEX UNIQUE SCAN (object id 76709) 0INDEX UNIQUE SCAN (object id 76899) OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse4 0.07 0.08 0 0 0 0 Execute 5 0.00 0.05 0 0 0 2 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total 11 43.02 133.34 58730 118694 24 2 Misses in library cache during parse: 1 Thanks Jay -- 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: Help on tkprof output
Hi, It is spending a lot of time waiting for IO and something like that. If you want to see what is the session waiting for ,just do: alter session set timed_statistics = true; (ignore it if it is already true) alter session set events '10046 trace name context forever,level 8'; --do your sql here. find the trace file and tkprof(use oracle 9.2 tkprof if your oracle version is not 9.2, not sure 9.0 will work)it like: tkprof file=your_tracefile waits=y For your SQL, I think more hash_join should be used instead of nested loop. Try it. regards Zhu Chao - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 7:19 AM Hi Gurus, Could someone shed some light on the following tkprof output. To get 0 record it is aking more than 11 seconds. Also I see huge difference between CPU time and elapsed time even though the system is not so busy(It is a test machine. very low load on it). If you say it is waiting on something, could you tell me how to identify the wait event associated with this and how to rectify the same? Also please let me know why the query count is very high? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse2 0.07 0.08 0 0 0 0 Execute 2 0.00 0.02 0 0 0 0 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total6 43.02 133.31 58730 118694 24 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 165 Rows Row Source Operation --- --- 0 SORT ORDER BY 0 NESTED LOOPS 1NESTED LOOPS 1590 HASH JOIN 239 TABLE ACCESS FULL c 1589 HASH JOIN 239 VIEW VW_NSO_1 239SORT UNIQUE 239 INDEX FAST FULL SCAN (object id 76648) 1589 TABLE ACCESS FULL p 1589 TABLE ACCESS BY INDEX ROWID e 1589 INDEX UNIQUE SCAN (object id 76709) 0INDEX UNIQUE SCAN (object id 76899) OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse4 0.07 0.08 0 0 0 0 Execute 5 0.00 0.05 0 0 0 2 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total 11 43.02 133.34 58730 118694 24 2 Misses in library cache during parse: 1 Thanks Jay -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: Help on tkprof output
You full scans within the loops which are hitting the disks, ie.58730 which gives the big difference between cpu elapsed as it's waiting for IO Also why do you need and p.business_country_id=c.countryabbrev As well as and p.business_country_id in ( select countryabbrev from c ) Do you want to join to table c for any reason or do you just want to see if the country abbreviation is in table c? At the moment you are doing both. SO maybe fine tune the SQL to only do what you really need it to and your disk IO would go down along with your elapsed time. Hth Ian -Original Message- [EMAIL PROTECTED] Sent: Wednesday, 3 December 2003 9:49 To: Multiple recipients of list ORACLE-L Hi Gurus, Could someone shed some light on the following tkprof output. To get 0 record it is aking more than 11 seconds. Also I see huge difference between CPU time and elapsed time even though the system is not so busy(It is a test machine. very low load on it). If you say it is waiting on something, could you tell me how to identify the wait event associated with this and how to rectify the same? Also please let me know why the query count is very high? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse2 0.07 0.08 0 0 0 0 Execute 2 0.00 0.02 0 0 0 0 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total6 43.02 133.31 58730 118694 24 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 165 Rows Row Source Operation --- --- 0 SORT ORDER BY 0 NESTED LOOPS 1NESTED LOOPS 1590 HASH JOIN 239 TABLE ACCESS FULL c 1589 HASH JOIN 239 VIEW VW_NSO_1 239SORT UNIQUE 239 INDEX FAST FULL SCAN (object id 76648) 1589 TABLE ACCESS FULL p 1589 TABLE ACCESS BY INDEX ROWID e 1589 INDEX UNIQUE SCAN (object id 76709) 0INDEX UNIQUE SCAN (object id 76899) OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse4 0.07 0.08 0 0 0 0 Execute 5 0.00 0.05 0 0 0 2 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total 11 43.02 133.34 58730 118694 24 2 Misses in library cache during parse: 1 Thanks Jay -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Biddell, Ian 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: Help on tkprof output
I'm not so sure. The query returns no rows and the second to last nested loop already has only 1 row in the resultset. I'd try to determine what the most limiting condition is - or set of conditions - those that eliminate most rows early on and make sure the optimizer starts with that. I could be mistaken, but the query appears odd. Isn't the condition and p.business_country_id in ( select countryabbrev from c ) nonsensical/superfluous in light of the condition and p.business_country_id=c.countryabbrev ? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc At 06:59 PM 12/2/2003, you wrote: Hi, It is spending a lot of time waiting for IO and something like that. If you want to see what is the session waiting for ,just do: alter session set timed_statistics = true; (ignore it if it is already true) alter session set events '10046 trace name context forever,level 8'; --do your sql here. find the trace file and tkprof(use oracle 9.2 tkprof if your oracle version is not 9.2, not sure 9.0 will work)it like: tkprof file=your_tracefile waits=y For your SQL, I think more hash_join should be used instead of nested loop. Try it. regards Zhu Chao Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Help on tkprof output
(select countryabbrev from c) subquery will have some more predicate (where clause) to restric few countries. Basically I don't want to select all the countries. For simplicity sake I remove those where clause(business logic). Anyway EXECUTION PLAN will not get changed. -Original Message- Biddell, Ian Sent: Tuesday, December 02, 2003 9:54 PM To: Multiple recipients of list ORACLE-L You full scans within the loops which are hitting the disks, ie.58730 which gives the big difference between cpu elapsed as it's waiting for IO Also why do you need and p.business_country_id=c.countryabbrev As well as and p.business_country_id in ( select countryabbrev from c ) Do you want to join to table c for any reason or do you just want to see if the country abbreviation is in table c? At the moment you are doing both. SO maybe fine tune the SQL to only do what you really need it to and your disk IO would go down along with your elapsed time. Hth Ian -Original Message- [EMAIL PROTECTED] Sent: Wednesday, 3 December 2003 9:49 To: Multiple recipients of list ORACLE-L Hi Gurus, Could someone shed some light on the following tkprof output. To get 0 record it is aking more than 11 seconds. Also I see huge difference between CPU time and elapsed time even though the system is not so busy(It is a test machine. very low load on it). If you say it is waiting on something, could you tell me how to identify the wait event associated with this and how to rectify the same? Also please let me know why the query count is very high? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse2 0.07 0.08 0 0 0 0 Execute 2 0.00 0.02 0 0 0 0 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total6 43.02 133.31 58730 118694 24 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 165 Rows Row Source Operation --- --- 0 SORT ORDER BY 0 NESTED LOOPS 1NESTED LOOPS 1590 HASH JOIN 239 TABLE ACCESS FULL c 1589 HASH JOIN 239 VIEW VW_NSO_1 239SORT UNIQUE 239 INDEX FAST FULL SCAN (object id 76648) 1589 TABLE ACCESS FULL p 1589 TABLE ACCESS BY INDEX ROWID e 1589 INDEX UNIQUE SCAN (object id 76709) 0INDEX UNIQUE SCAN (object id 76899) OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse4 0.07 0.08 0 0 0 0 Execute 5 0.00 0.05 0 0 0 2 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total 11 43.02 133.34 58730 118694 24 2 Misses in library cache during parse: 1 Thanks Jay -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Biddell, Ian 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
RE: Help on tkprof output
Depending on the where clauses added to the subqueries I would say there is potential for the execution plan to change. Especially if the column matched to the IN clause is indexed. You are correct in that the clause may well be required but always be careful when performance tuning a slightly different query - sometimes the difference in execution time/plan can be surprising. Sami [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] albox.com cc: Sent by: Subject: RE: Help on tkprof output [EMAIL PROTECTED] .com 03/12/2003 14:34 Please respond to ORACLE-L (select countryabbrev from c) subquery will have some more predicate (where clause) to restric few countries. Basically I don't want to select all the countries. For simplicity sake I remove those where clause(business logic). Anyway EXECUTION PLAN will not get changed. -Original Message- Biddell, Ian Sent: Tuesday, December 02, 2003 9:54 PM To: Multiple recipients of list ORACLE-L You full scans within the loops which are hitting the disks, ie.58730 which gives the big difference between cpu elapsed as it's waiting for IO Also why do you need and p.business_country_id=c.countryabbrev As well as and p.business_country_id in ( select countryabbrev from c ) Do you want to join to table c for any reason or do you just want to see if the country abbreviation is in table c? At the moment you are doing both. SO maybe fine tune the SQL to only do what you really need it to and your disk IO would go down along with your elapsed time. Hth Ian -Original Message- [EMAIL PROTECTED] Sent: Wednesday, 3 December 2003 9:49 To: Multiple recipients of list ORACLE-L Hi Gurus, Could someone shed some light on the following tkprof output. To get 0 record it is aking more than 11 seconds. Also I see huge difference between CPU time and elapsed time even though the system is not so busy(It is a test machine. very low load on it). If you say it is waiting on something, could you tell me how to identify the wait event associated with this and how to rectify the same? Also please let me know why the query count is very high? select countryname, e.lastupdatedate from e e, p p, c c where p.pid = e.pid and p.hsbc_user_category='GIB' and p.business_country_id=c.countryabbrev and e.userstatusid in ( select userstatusid from userstatus ) and p.business_country_id in ( select countryabbrev from c ) order by countryname, e.lastupdatedate desc call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse2 0.07 0.08 0 0 0 0 Execute 2 0.00 0.02 0 0 0 0 Fetch2 42.95 133.21 58730 118694 24 0 --- -- -- -- -- -- -- total6 43.02 133.31 58730 118694 24 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 165 Rows Row Source Operation --- --- 0 SORT ORDER BY 0 NESTED LOOPS 1NESTED LOOPS
More help with TKPROF
List:
My apologies. Seems like I'm taking up more than my
share of bandwith. I'm doing major battle with a
vendor. Their app runs this thing that takes 10
seconds. Unfortunately it runs this thing several
thousand times a day. This is a critical issue for
us.
I trapped what's running in that 10 seconds. The code
looks just swell. Seems pretty obvious to me that the
problem is with the large number of parses occurring.
I see 1 spot where they parse 5 times and return 0
rows; another where they parse, execute, and fetch 5
times for no obvious reason.
We do not have access to the vendor code. The vendor
says set cursor_sharing to force. I say that's
solving the wrong problem.
First naive question: Without their code, is there
any way for me to know what could cause this large
number of parses?
2nd question: I see an exact match between the number
of parses and the times waited on sql*net message to
client. Is this coincidence, or can I make some
correlation here?
Thanks so much for your patience and your help.
Database is 8.1.7.4 on Solaris 8.
Barb
SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS,
COLWIDTH, COLSPACE, HEIGHT
FROM PAGE
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse2 0.02 0.02 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch1 0.01 0.01 0
13 12 72
--- -- -- --
-- -- --
total4 0.03 0.03 0
13 12 72
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAGE'
Elapsed times include waiting on following events:
Event waited on Times
Max. Wait Total Waited
Waited
--
SQL*Net message to client 2
0.00 0.00
SQL*Net message from client 2
0.00 0.00
SQL*Net more data to client 1
0.00 0.00
SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS,
COLWIDTH, COLSPACE, HEIGHT
FROM
PAGE WHERE PAPER = :1 AND PAGE = :2
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse2 0.00 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch1 0.00 0.00 0
2 0 1
--- -- -- --
-- -- --
total4 0.00 0.01 0
2 0 1
Misses in library cache during parse: 1
Parsing user id: 586 (SYSADMIN)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'PAGE'
0INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_PAG1' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times
Max. Wait Total Waited
Waited
--
SQL*Net message to client 2
0.00 0.00
SQL*Net message from client 2
0.09 0.09
SELECT HID, PARENT, CHILD, NODE_TYPE, NODE_ITEM, TAG,
ATTRIBUTE_ITEM
FROM
XMLS_HIERARCHY WHERE HID = :1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse5 0.00 0.00 0
0 0 0
Execute 0 0.00 0.00 0
0 0 0
Fetch0 0.00 0.00 0
0 0 0
--- -- -- --
-- -- --
total5 0.00 0.00 0
0 0 0
Misses in library cache during parse: 1
Parsing user id: 586 (SYSADMIN)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF
'XMLS_HIERARCHY'
0INDEX
Re: More help with TKPROF
On 11/13/2003 10:09:26 AM, Barbara Baker wrote: First naive question: Without their code, is there any way for me to know what could cause this large number of parses? Large number of parses can be caused by executing dynamic SQL, or not using bind variables. One way to try amending this would setting CURSOR_SHARE to FORCE, but in 8i it's a dangerous thing to do and can break many other things. Another way would be to negotiate with the vendor and ask him to reduce number of parses and use bind variables. I'm sure that the three of you can successfully negotiate with any vendor. Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: More help with TKPROF
Not a coincidence. See Optimizing Oracle Performance, Chapter 12, Case
3: Large SQL*Net Event Duration on pages 337-344. See also the SQL*Net
material on pp311-315.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit
- SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Barbara Baker
Sent: Thursday, November 13, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
List:
My apologies. Seems like I'm taking up more than my
share of bandwith. I'm doing major battle with a
vendor. Their app runs this thing that takes 10
seconds. Unfortunately it runs this thing several
thousand times a day. This is a critical issue for
us.
I trapped what's running in that 10 seconds. The code
looks just swell. Seems pretty obvious to me that the
problem is with the large number of parses occurring.
I see 1 spot where they parse 5 times and return 0
rows; another where they parse, execute, and fetch 5
times for no obvious reason.
We do not have access to the vendor code. The vendor
says set cursor_sharing to force. I say that's
solving the wrong problem.
First naive question: Without their code, is there
any way for me to know what could cause this large
number of parses?
2nd question: I see an exact match between the number
of parses and the times waited on sql*net message to
client. Is this coincidence, or can I make some
correlation here?
Thanks so much for your patience and your help.
Database is 8.1.7.4 on Solaris 8.
Barb
SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS,
COLWIDTH, COLSPACE, HEIGHT
FROM PAGE
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse2 0.02 0.02 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch1 0.01 0.01 0
13 12 72
--- -- -- --
-- -- --
total4 0.03 0.03 0
13 12 72
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAGE'
Elapsed times include waiting on following events:
Event waited on Times
Max. Wait Total Waited
Waited
--
SQL*Net message to client 2
0.00 0.00
SQL*Net message from client 2
0.00 0.00
SQL*Net more data to client 1
0.00 0.00
SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS,
COLWIDTH, COLSPACE, HEIGHT
FROM
PAGE WHERE PAPER = :1 AND PAGE = :2
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse2 0.00 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch1 0.00 0.00 0
2 0 1
--- -- -- --
-- -- --
total4 0.00 0.01 0
2 0 1
Misses in library cache during parse: 1
Parsing user id: 586 (SYSADMIN)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'PAGE'
0INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_PAG1' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times
Max. Wait Total Waited
Waited
--
SQL*Net message to client 2
0.00 0.00
SQL*Net message from client 2
0.09 0.09
SELECT HID, PARENT, CHILD, NODE_TYPE, NODE_ITEM, TAG,
ATTRIBUTE_ITEM
FROM
XMLS_HIERARCHY WHERE HID = :1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse5 0.00 0.00 0
0 0 0
Execute 0 0.00 0.00 0
0 0
Re: More help with TKPROF
Since this is COTS, you may not be able to *fix* the problem, but you may be able to circumvent it . We have an app here which has amazing amounts of ugly SQL, with a corresponding lack of bind variables. I could not fix that, but I could fix very poorly performing SQL by the judicious application of optimizer_index_caching, optimizer_index_cost_adjust, optmizer_max_permutations, some new indexes and histograms on selected columns. You may have too many parses, but if parses are only 10% of your response time problem, and you decrease parse time by 50%, you have still only increased response time by 5%, or 0.5 seconds in the case of the transaction in question. W never did fix the parsing problem, but did greatly increase the the performance of the application. I am currently working on yet another problem for this app. Simple SQL tuning. I'm sure that many of us here can't recommend 'Optimizing Oracle Performance' enough. It will provide a foundation for solving performance problems that would be rather difficult to pick up by reading this list, or by reading most ( 99% ) of the tuning books available. HTH Jared Barbara Baker [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/13/2003 07:09 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:More help with TKPROF List: My apologies. Seems like I'm taking up more than my share of bandwith. I'm doing major battle with a vendor. Their app runs this thing that takes 10 seconds. Unfortunately it runs this thing several thousand times a day. This is a critical issue for us. I trapped what's running in that 10 seconds. The code looks just swell. Seems pretty obvious to me that the problem is with the large number of parses occurring. I see 1 spot where they parse 5 times and return 0 rows; another where they parse, execute, and fetch 5 times for no obvious reason. We do not have access to the vendor code. The vendor says set cursor_sharing to force. I say that's solving the wrong problem. First naive question: Without their code, is there any way for me to know what could cause this large number of parses? 2nd question: I see an exact match between the number of parses and the times waited on sql*net message to client. Is this coincidence, or can I make some correlation here? Thanks so much for your patience and your help. Database is 8.1.7.4 on Solaris 8. Barb SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS, COLWIDTH, COLSPACE, HEIGHT FROM PAGE call countcpu elapseddisk query currentrows --- -- -- -- -- -- -- Parse2 0.020.02 0 0 0 0 Execute 1 0.000.00 0 0 0 0 Fetch1 0.010.01 0 13 12 72 --- -- -- -- -- -- -- total4 0.030.03 0 13 12 72 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 586 (SYSADMIN) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PAGE' Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited -- SQL*Net message to client2 0.00 0.00 SQL*Net message from client 2 0.00 0.00 SQL*Net more data to client 1 0.00 0.00 SELECT PAPER, PAGE, PAGENAME, PAGENO, COLUMNS, COLWIDTH, COLSPACE, HEIGHT FROM PAGE WHERE PAPER = :1 AND PAGE = :2 call countcpu elapseddisk query currentrows --- -- -- -- -- -- -- Parse2 0.000.01 0 0 0 0 Execute 1 0.000.00 0 0 0 0 Fetch1 0.000.00 0 2 0 1 --- -- -- -- -- -- -- total4 0.000.01 0 2 0 1 Misses in library cache during parse: 1 Parsing user id: 586 (SYSADMIN) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PAGE' 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'I_PAG1' (UNIQUE) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited -- SQL*Net message to client2 0.00 0.00 SQL*Net message from client 2 0.09 0.09 SELECT HID, PARENT, CHILD, NODE_TYPE, NODE_ITEM, TAG
Help Interpreting TKProf
Hi.
I'm running tkprof on my PC with a version 9i client.
The trace file was generated on a Solars version
8.1.7.4 database. (I'm using tkprof on 9i to get wait
statistics.) The trace is 10046 level 12
I assume the time waited is in addition to the elapsed
time for the call -- correct?
Since the database itself is 8i, does that mean that
the wait stats are in centiseconds? I want to know
what the sql*net message from client wait time of
10.73 represents:
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total
Waited
Waited -- -
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 10.71 10.73
Here's all the code:
select /*ClassSQL*/ distinct co.class,cl.claname
from
classorder co,class cl where co.paper='DNA' and
co.page='EM' and co.class=
cl.class and co.paper=cl.paper and cl.clatype='0'
order by 1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.01 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch8 0.01 0.01 0
169 0 82
--- -- -- --
-- -- --
total 10 0.02 0.02 0
169 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Row Source Operation
---
---
82 SORT UNIQUE
82 NESTED LOOPS
83INDEX RANGE SCAN (object id 395118)
82TABLE ACCESS BY INDEX ROWID CLASS
164 INDEX UNIQUE SCAN (object id 395113)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
82 SORT (UNIQUE)
82NESTED LOOPS
83 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'I_CLO1' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'CLASS'
164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_CLA1' (UNIQUE)
Thanks for any help.
Barb
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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: Help Interpreting TKProf
Barbs, what this trace file tells you is that the client program
takes much more time delivering the data to oracle then it takes
for oracle to deliver the data to the program. In other words, your
program takes its time between the calls to the database. Maybe you
should run the profiler and see where time in the program is spent.
On 11/12/2003 01:09:24 PM, Barbara Baker wrote:
Hi.
I'm running tkprof on my PC with a version 9i client.
The trace file was generated on a Solars version
8.1.7.4 database. (I'm using tkprof on 9i to get wait
statistics.) The trace is 10046 level 12
I assume the time waited is in addition to the elapsed
time for the call -- correct?
Since the database itself is 8i, does that mean that
the wait stats are in centiseconds? I want to know
what the sql*net message from client wait time of
10.73 represents:
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total
Waited
Waited -- -
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 10.71 10.73
Here's all the code:
select /*ClassSQL*/ distinct co.class,cl.claname
from
classorder co,class cl where co.paper='DNA' and
co.page='EM' and co.class=
cl.class and co.paper=cl.paper and cl.clatype='0'
order by 1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.01 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch8 0.01 0.01 0
169 0 82
--- -- -- --
-- -- --
total 10 0.02 0.02 0
169 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Row Source Operation
---
---
82 SORT UNIQUE
82 NESTED LOOPS
83INDEX RANGE SCAN (object id 395118)
82TABLE ACCESS BY INDEX ROWID CLASS
164 INDEX UNIQUE SCAN (object id 395113)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
82 SORT (UNIQUE)
82NESTED LOOPS
83 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'I_CLO1' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'CLASS'
164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_CLA1' (UNIQUE)
Thanks for any help.
Barb
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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).
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege is
waived or lost by any mistransmission. If you receive this message in error, please
immediately delete it and all copies of it from your system, destroy any hard copies
of it and notify the sender. You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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
Re: Help Interpreting TKProf
Barb,
This is a great example of where reading the trace file will tell you
what you need to know. Is the 10 centiseconds of time in 8 1.25
centisecond events or in 1 10 centisecond event and 7 .1 centisecond
events? The location (parse/execute/fetch/post tx) are also important.
Daniel
Barbara Baker wrote:
Hi.
I'm running tkprof on my PC with a version 9i client.
The trace file was generated on a Solars version
8.1.7.4 database. (I'm using tkprof on 9i to get wait
statistics.) The trace is 10046 level 12
I assume the time waited is in addition to the elapsed
time for the call -- correct?
Since the database itself is 8i, does that mean that
the wait stats are in centiseconds? I want to know
what the sql*net message from client wait time of
10.73 represents:
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total
Waited
Waited -- -
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 10.71 10.73
Here's all the code:
select /*ClassSQL*/ distinct co.class,cl.claname
from
classorder co,class cl where co.paper='DNA' and
co.page='EM' and co.class=
cl.class and co.paper=cl.paper and cl.clatype='0'
order by 1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.01 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch8 0.01 0.01 0
169 0 82
--- -- -- --
-- -- --
total 10 0.02 0.02 0
169 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Row Source Operation
---
---
82 SORT UNIQUE
82 NESTED LOOPS
83INDEX RANGE SCAN (object id 395118)
82TABLE ACCESS BY INDEX ROWID CLASS
164 INDEX UNIQUE SCAN (object id 395113)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
82 SORT (UNIQUE)
82NESTED LOOPS
83 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'I_CLO1' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'CLASS'
164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_CLA1' (UNIQUE)
Thanks for any help.
Barb
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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: Daniel Fink
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: Help Interpreting TKProf
Barb,
I think tkprof prints output in seconds, so I think you're looking at
10.73 seconds of response time here.
First, some definitions:
- The SQL*Net message from client event maps to an OS read() call to
the file descriptor to which SQL*Net is connected.
- The SQL*Net message to client event maps to an OS write() call to
the file descriptor to which SQL*Net is connected.
The Oracle kernel uses a to event just to write a short message (often
1 byte) to a pipe. It takes practically no time to do that--usually on
the order of 3 microseconds (0.03 seconds).
However, as soon as a given to event completes, the kernel often next
executes a read() from the same file descriptor. The duration of this
read() is what ultimately shows up as the from event's duration.
What this means is that all the time spent in the following tiers gets
logged as SQL*Net message from client (see p13 of my book for a
picture):
- comm from db server to apps server
- apps server computation time
- comm from apps server to browser
- browser computation time
- user think time
- comm from browser to apps server
- apps server computation time
- comm from apps server to db server
Thus you'll almost always (maybe even always always) see from events
lasting longer than to events.
Getting good information out of SQL*Net message from client is
possible only if you are careful in how you collect your trace data. If,
for example, you allow 10 seconds of user think time into your trace
data, then it forces you to do a lot more analytical work on your trace
data to properly ignore the right data--unless, of course, the thing
you need to find out is that the user is wasting your business's time by
consuming 10 seconds instead of 2.
Optimizing Oracle Performance covers all this in great detail.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit
- SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Barbara Baker
Sent: Wednesday, November 12, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L
Hi.
I'm running tkprof on my PC with a version 9i client.
The trace file was generated on a Solars version
8.1.7.4 database. (I'm using tkprof on 9i to get wait
statistics.) The trace is 10046 level 12
I assume the time waited is in addition to the elapsed
time for the call -- correct?
Since the database itself is 8i, does that mean that
the wait stats are in centiseconds? I want to know
what the sql*net message from client wait time of
10.73 represents:
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total
Waited
Waited -- -
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 10.71 10.73
Here's all the code:
select /*ClassSQL*/ distinct co.class,cl.claname
from
classorder co,class cl where co.paper='DNA' and
co.page='EM' and co.class=
cl.class and co.paper=cl.paper and cl.clatype='0'
order by 1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.01 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch8 0.01 0.01 0
169 0 82
--- -- -- --
-- -- --
total 10 0.02 0.02 0
169 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Row Source Operation
---
---
82 SORT UNIQUE
82 NESTED LOOPS
83INDEX RANGE SCAN (object id 395118)
82TABLE ACCESS BY INDEX ROWID CLASS
164 INDEX UNIQUE SCAN (object id 395113)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
82 SORT (UNIQUE)
82NESTED LOOPS
83 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'I_CLO1' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'CLASS'
164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'I_CLA1' (UNIQUE)
Thanks for any help.
Barb
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
Re: Help Interpreting TKProf
In other words, you can finish performance analysis of the client
only by finding out where the time is spent on the client side.
If it's an interactive program and you have a homo sapiens interacting
with it, then think time is a factor. If, on the other hand, it's
a batch program, then run a profiler (gprof, Jprof or alike) to find
out where the time is spent.
On 11/12/2003 02:09:28 PM, Cary Millsap wrote:
Barb,
I think tkprof prints output in seconds, so I think you're looking at
10.73 seconds of response time here.
First, some definitions:
- The SQL*Net message from client event maps to an OS read() call to
the file descriptor to which SQL*Net is connected.
- The SQL*Net message to client event maps to an OS write() call to
the file descriptor to which SQL*Net is connected.
The Oracle kernel uses a to event just to write a short message (often
1 byte) to a pipe. It takes practically no time to do that--usually on
the order of 3 microseconds (0.03 seconds).
However, as soon as a given to event completes, the kernel often next
executes a read() from the same file descriptor. The duration of this
read() is what ultimately shows up as the from event's duration.
What this means is that all the time spent in the following tiers gets
logged as SQL*Net message from client (see p13 of my book for a
picture):
- comm from db server to apps server
- apps server computation time
- comm from apps server to browser
- browser computation time
- user think time
- comm from browser to apps server
- apps server computation time
- comm from apps server to db server
Thus you'll almost always (maybe even always always) see from events
lasting longer than to events.
Getting good information out of SQL*Net message from client is
possible only if you are careful in how you collect your trace data. If,
for example, you allow 10 seconds of user think time into your trace
data, then it forces you to do a lot more analytical work on your trace
data to properly ignore the right data--unless, of course, the thing
you need to find out is that the user is wasting your business's time by
consuming 10 seconds instead of 2.
Optimizing Oracle Performance covers all this in great detail.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit
- SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Barbara Baker
Sent: Wednesday, November 12, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L
Hi.
I'm running tkprof on my PC with a version 9i client.
The trace file was generated on a Solars version
8.1.7.4 database. (I'm using tkprof on 9i to get wait
statistics.) The trace is 10046 level 12
I assume the time waited is in addition to the elapsed
time for the call -- correct?
Since the database itself is 8i, does that mean that
the wait stats are in centiseconds? I want to know
what the sql*net message from client wait time of
10.73 represents:
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total
Waited
Waited -- -
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 10.71 10.73
Here's all the code:
select /*ClassSQL*/ distinct co.class,cl.claname
from
classorder co,class cl where co.paper='DNA' and
co.page='EM' and co.class=
cl.class and co.paper=cl.paper and cl.clatype='0'
order by 1
call count cpuelapsed disk
querycurrentrows
--- -- -- --
-- -- --
Parse1 0.01 0.01 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch8 0.01 0.01 0
169 0 82
--- -- -- --
-- -- --
total 10 0.02 0.02 0
169 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 586 (SYSADMIN)
Rows Row Source Operation
---
---
82 SORT UNIQUE
82 NESTED LOOPS
83INDEX RANGE SCAN (object id 395118)
82TABLE ACCESS BY INDEX ROWID CLASS
164 INDEX UNIQUE SCAN (object id 395113)
Rows Execution Plan
---
---
0 SELECT STATEMENT GOAL: CHOOSE
82 SORT (UNIQUE)
82NESTED LOOPS
83 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'I_CLO1' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'CLASS'
164 INDEX
Re: Help Interpreting TKProf
Daniel, it's seconds, not centiseconds. The tkprof writes things down in seconds. If it's a 9i trace, then the times within trc files are in microseconds. In trc file produced by 8i, it's centiseconds. By some magic, tkprof knows the difference and usually gets the right times. On 11/12/2003 01:49:34 PM, Daniel Fink wrote: Barb, This is a great example of where reading the trace file will tell you what you need to know. Is the 10 centiseconds of time in 8 1.25 centisecond events or in 1 10 centisecond event and 7 .1 centisecond events? The location (parse/execute/fetch/post tx) are also important. Daniel Barbara Baker wrote: Hi. I'm running tkprof on my PC with a version 9i client. The trace file was generated on a Solars version 8.1.7.4 database. (I'm using tkprof on 9i to get wait statistics.) The trace is 10046 level 12 I assume the time waited is in addition to the elapsed time for the call -- correct? Since the database itself is 8i, does that mean that the wait stats are in centiseconds? I want to know what the sql*net message from client wait time of 10.73 represents: Elapsed times include waiting on following events: Event waited onTimes Max. Wait Total Waited Waited -- - SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 10.71 10.73 Here's all the code: select /*ClassSQL*/ distinct co.class,cl.claname from classorder co,class cl where co.paper='DNA' and co.page='EM' and co.class= cl.class and co.paper=cl.paper and cl.clatype='0' order by 1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch8 0.01 0.01 0 169 0 82 --- -- -- -- -- -- -- total 10 0.02 0.02 0 169 0 82 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 586 (SYSADMIN) Rows Row Source Operation --- --- 82 SORT UNIQUE 82 NESTED LOOPS 83INDEX RANGE SCAN (object id 395118) 82TABLE ACCESS BY INDEX ROWID CLASS 164 INDEX UNIQUE SCAN (object id 395113) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 82 SORT (UNIQUE) 82NESTED LOOPS 83 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_CLO1' (NON-UNIQUE) 82 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLASS' 164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'I_CLA1' (UNIQUE) Thanks for any help. Barb __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Daniel Fink 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly
Re: Help Interpreting TKProf
Daniel: Does this mean I was supposed to be paying attention in class?? Here's everything I can find associated with sql*net message from client in that cursor. Can't figure out from this how it came up with 10.73 (although that one wait matches up with the max wait of 10.71) Am I being really dense? WAIT!! don't answer that! WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1071 p1=1413697536 p2=1 p3=0 --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, This is a great example of where reading the trace file will tell you what you need to know. Is the 10 centiseconds of time in 8 1.25 centisecond events or in 1 10 centisecond event and 7 .1 centisecond events? The location (parse/execute/fetch/post tx) are also important. Daniel Barbara Baker wrote: Hi. I'm running tkprof on my PC with a version 9i client. The trace file was generated on a Solars version 8.1.7.4 database. (I'm using tkprof on 9i to get wait statistics.) The trace is 10046 level 12 I assume the time waited is in addition to the elapsed time for the call -- correct? Since the database itself is 8i, does that mean that the wait stats are in centiseconds? I want to know what the sql*net message from client wait time of 10.73 represents: Elapsed times include waiting on following events: Event waited onTimes Max. Wait Total Waited Waited -- - SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 10.71 10.73 Here's all the code: select /*ClassSQL*/ distinct co.class,cl.claname from classorder co,class cl where co.paper='DNA' and co.page='EM' and co.class= cl.class and co.paper=cl.paper and cl.clatype='0' order by 1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch8 0.01 0.01 0 169 0 82 --- -- -- -- -- -- -- total 10 0.02 0.02 0 169 0 82 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 586 (SYSADMIN) Rows Row Source Operation --- --- 82 SORT UNIQUE 82 NESTED LOOPS 83INDEX RANGE SCAN (object id 395118) 82TABLE ACCESS BY INDEX ROWID CLASS 164 INDEX UNIQUE SCAN (object id 395113) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 82 SORT (UNIQUE) 82NESTED LOOPS 83 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_CLO1' (NON-UNIQUE) 82 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLASS' 164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'I_CLA1' (UNIQUE) Thanks for any help. Barb __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Daniel Fink 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
Re: Help Interpreting TKProf
Barbara, I know how distracted you were with all the jokes being tossed about, but really... It looks to me (with just this snippet of trace) that the cursor #3 did some communication and then waited for 10 seconds for a response. Without seeing the raw trace file and sequence of events, this is my best guess. Daniel Barbara Baker wrote: Daniel: Does this mean I was supposed to be paying attention in class?? Here's everything I can find associated with sql*net message from client in that cursor. Can't figure out from this how it came up with 10.73 (although that one wait matches up with the max wait of 10.71) Am I being really dense? WAIT!! don't answer that! WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 wait_time = 1 centisecond p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 wait_time = wait_time (1 cs) + 1 cs = 2 cs p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1071 p1=1413697536 p2=1 p3=0 wait_time = wait_time(2 cs) + 1071 cs = 1073 cs -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: Help Interpreting TKProf
Barb, What you're really after is called forward attribution in the book. You need to see the dbcall that immediately follows each WAIT nam='SQL*Net message from client' event in the trace file. This will tell you what database call's execution it was that ended the read(). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Daniel Fink Sent: Wednesday, November 12, 2003 2:35 PM To: Multiple recipients of list ORACLE-L Barbara, I know how distracted you were with all the jokes being tossed about, but really... It looks to me (with just this snippet of trace) that the cursor #3 did some communication and then waited for 10 seconds for a response. Without seeing the raw trace file and sequence of events, this is my best guess. Daniel Barbara Baker wrote: Daniel: Does this mean I was supposed to be paying attention in class?? Here's everything I can find associated with sql*net message from client in that cursor. Can't figure out from this how it came up with 10.73 (although that one wait matches up with the max wait of 10.71) Am I being really dense? WAIT!! don't answer that! WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 wait_time = 1 centisecond p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 wait_time = wait_time (1 cs) + 1 cs = 2 cs p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1071 p1=1413697536 p2=1 p3=0 wait_time = wait_time(2 cs) + 1071 cs = 1073 cs -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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: Cary Millsap 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: Help Interpreting TKProf
To finish off the answer to your direct question, compute the sum of the ela values shown in your excerpt: 0 cs 0 1 0 0 0 1 1071 --- 1073 cs = 10.73 seconds Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Barbara Baker Sent: Wednesday, November 12, 2003 1:59 PM To: Multiple recipients of list ORACLE-L Daniel: Does this mean I was supposed to be paying attention in class?? Here's everything I can find associated with sql*net message from client in that cursor. Can't figure out from this how it came up with 10.73 (although that one wait matches up with the max wait of 10.71) Am I being really dense? WAIT!! don't answer that! WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1071 p1=1413697536 p2=1 p3=0 --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, This is a great example of where reading the trace file will tell you what you need to know. Is the 10 centiseconds of time in 8 1.25 centisecond events or in 1 10 centisecond event and 7 .1 centisecond events? The location (parse/execute/fetch/post tx) are also important. Daniel Barbara Baker wrote: Hi. I'm running tkprof on my PC with a version 9i client. The trace file was generated on a Solars version 8.1.7.4 database. (I'm using tkprof on 9i to get wait statistics.) The trace is 10046 level 12 I assume the time waited is in addition to the elapsed time for the call -- correct? Since the database itself is 8i, does that mean that the wait stats are in centiseconds? I want to know what the sql*net message from client wait time of 10.73 represents: Elapsed times include waiting on following events: Event waited onTimes Max. Wait Total Waited Waited -- - SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 10.71 10.73 Here's all the code: select /*ClassSQL*/ distinct co.class,cl.claname from classorder co,class cl where co.paper='DNA' and co.page='EM' and co.class= cl.class and co.paper=cl.paper and cl.clatype='0' order by 1 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch8 0.01 0.01 0 169 0 82 --- -- -- -- -- -- -- total 10 0.02 0.02 0 169 0 82 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 586 (SYSADMIN) Rows Row Source Operation --- --- 82 SORT UNIQUE 82 NESTED LOOPS 83INDEX RANGE SCAN (object id 395118) 82TABLE ACCESS BY INDEX ROWID CLASS 164 INDEX UNIQUE SCAN (object id 395113) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 82 SORT (UNIQUE) 82NESTED LOOPS 83 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_CLO1' (NON-UNIQUE) 82 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CLASS' 164 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'I_CLA1' (UNIQUE) Thanks for any help. Barb __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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
tkprof issues - was Performance Problem
Title: Message unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though the disk/memory read was not above 95%, it was at 92% so that is probably why no performance gain was noticed. We are using PL/SQL procedures heavily. The stats on the Library Cache looked good though. I read something this weekend about how using 'logical' drives to separate the different files can cause a performance hit. I am using logical disks, and I plan to change when I can, but I'm not sure yet how much that will help. I have redistributed some of the rollback segments so that they are not all located on the same disk. However since some of the drives are logical, that may not have done any good. I've rebuilt indexes, changed extent sizes to reduce the amount of extents, added rollback segments, etc. In lieu of this, code is next... Thanks, Laura -Original Message- Sent: Tuesday, August 26, 2003 1:29 PM To: Multiple recipients of list ORACLE-L Did you analyze the sys schema by mistake. This can stop the fastest database. We had a contractor do that to an 8.0.5 database once, and only once. Ruth -- Please see the official ORACLE-L FAQ: http
RE: tkprof issues - was Performance Problem
Title: Message Swap is 16G, 1.2% used RAM is 16G, 16 processors. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 6:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though
RE: tkprof issues - was Performance Problem
Title: Message Raj, can you do truss on that tkprof? It would be nice to see where exactly does tkprof fail. HP-UX 11 has truss. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Wednesday, August 27, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Swap is 16G, 1.2% used RAM is 16G, 16 processors. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 6:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time.. Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other
RE: tkprof issues - was Performance Problem
Title: Message Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though the disk/memory read was not above 95%, it was at 92% so that is probably why no performance gain was noticed. We are using PL/SQL procedures heavily. The stats on the Library Cache looked good though. I read something this weekend about how using 'logical' drives to separate the different files can cause a performance hit. I am using logical disks, and I plan to change when I can, but I'm not sure yet how much that will help. I have redistributed some of the rollback segments so that they are not all located on the same disk. However since some of the drives are logical, that may not have done any good
Re: core dump for tkprof
It's 8.1.6 db . I ran tkprof succesfully on same box with same version 3 days back . But now it's geting core dump . -ak - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, March 27, 2003 3:28 PM Subject: RE: core dump for tkprof remove a line that starts with APPNAME or something like that ... in the very beginning of the trace file. Raj -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Thursday, March 27, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: core dump for tkprof when I am trying to execute tkprof on 10046 output file ( level 8 ) , i am getting core dump . any idea why ? thanks, ak
RE: core dump for tkprof
remove a line that starts with APPNAME or something like that ... in the very beginning of the trace file. Raj -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Thursday, March 27, 2003 2:49 PMTo: Multiple recipients of list ORACLE-LSubject: core dump for tkprof when I am trying to execute tkprof on 10046 output file ( level 8 ) , i am getting core dump . any idea why ? thanks, ak *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
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
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 E
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
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).
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).
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).
Tkprof On Windows 9.0
Hi All, Go to metalink and download patch 2271678 to install tkprof on 9i Release 1. As was already mentioned this has been resolved in 9iR2. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ora NT DBA 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).
tkprof on 901 database
I have a 9.01 database installed on my laptop and cannot find the tkprof.exe. Has it been renamed or is it part of an installation that I did not do? Thanks for any and all replys. Randy R. Pace Development Team Leader Accela, Inc. 9662 South 700 East Sandy UT, 84070 Tel: 801 495 9300 Fax: 801 495 9301 www.accela.com http://www.accela.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Randy Pace 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 on 901 database
nope - its an oversight of oracle's. You can upgrade to 9.2 (which I'd recommend because its so much better) or it might even be in one of the 9.0 patches. Using the 817 or 92 tkprof binary also seems to work effectively. hth connor --- Randy Pace [EMAIL PROTECTED] wrote: I have a 9.01 database installed on my laptop and cannot find the tkprof.exe. Has it been renamed or is it part of an installation that I did not do? Thanks for any and all replys. Randy R. Pace Development Team Leader Accela, Inc. 9662 South 700 East Sandy UT, 84070 Tel: 801 495 9300 Fax: 801 495 9301 www.accela.com http://www.accela.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Randy Pace 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
question on tkprof
Hi gurus Anyone knows about the comparision of tkprof output of the trace files generated by same application in diffrent times and anyone have solution to Problem with pl/sql in insert script loading into tkprof_table. Thanks in advance Banarasi
Re: question on tkprof
First, always use the SORT= option to generate TKPROF output -- the utility is useless if you don't sort the output. For sorting, I have two suggestions: * sort by "logical reads" (i.e. SORT=EXEQRY,FCHQRY,EXECU,FCHCU) * sort by "elapsed time" (i.e. SORT=PRSELA,FCHELA,EXEELA) If elapsed time data is available (i.e. TIMED_STATISTICS=TRUE in database), then I prefer to use that. If not, then I use logical reads... Second, if you are sorting the TKPROF output, then use the PRINT= option to limit the amount of output. The idea here is that you only care about the "worst" couple of SQL statements. Using PRINT=10 means that your TKPROF output will be limited to only 10 statements... So, comparing two files that have been sorted and have only 10 statements is pretty easy to do just by looking at it... --- As far as any problems loading into "tkprof_table", I can only guess that the user which you are running EXPLAIN PLANs into (using the EXPLAIN= option) does not have permission or space-quota to create a smalltemporarytable in which to place EXPLAIN PLAN output. Check the permissions of the user you are specifying; log into SQL*Plus using the same user and try to create a small dummy table... - Original Message - From: BanarasiBabu Tippa To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 21, 2003 4:34 AM Subject: question on tkprof Hi gurus Anyone knows about the comparision of tkprof output of the trace files generated by same application in diffrent times and anyone have solution to Problem with pl/sql in insert script loading into tkprof_table. Thanks in advance Banarasi
Re: Re: question on tkprof
Three options: * use the Hotsos Profiler trace analysis tool from http://www.hotsos.com * use the itrprof trace analysis tool from http://www.ubtools.com * copy a TKPROF executable from an Oracle9i release and run (through SQL*Net!) against your earlier database. Not guaranteed to work, of course, but it could be worth a try... ** if you are on UNIX and are comfortable with the make utility, copy the s0kvpf.o object file from the $ORACLE_HOME/rdbms/lib on an Oracle9i software distribution and copy it to your own target RDBMS version's $ORACLE_HOME/rdbms/lib (making sure to save the existing file first, of course). Then, attempt to relink the TKPROF executable using make -f ins_rdbms.mk itkprof from the OH/rdbms/lib directory... Cary Millsap and Jeff Holt (of www.hotsos.com) have found lots of anomalies in Oracle .trc files, so they have compensated for these in their tool and Cary is about to publish a book that explains many of them. Gazi Unal-Bilism of www.ubtools.com has had his itrprof tool available as a service from the web and has been including WAIT information in his analysis for quite a while... Of course, the third option is entirely at your own risk. Whether that is attractive to you or not makes for an interesting discussion on its own! :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 7:14 AM Tim Gorman, hi, in oracle 9i there is waits=y to print the wait time in the tkprofed file, but in 7-8i, tkprof does not support this option. Is there any script that can generate the wait time from the 10046 trace file? Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-21 04:58:00 ,you wrote£º=== First, always use the SORT= option to generate TKPROF output -- the utility is useless if you don't sort the output. For sorting, I have two suggestions: * sort by logical reads (i.e. SORT=EXEQRY,FCHQRY,EXECU,FCHCU) * sort by elapsed time (i.e. SORT=PRSELA,FCHELA,EXEELA) If elapsed time data is available (i.e. TIMED_STATISTICS=TRUE in database), then I prefer to use that. If not, then I use logical reads... Second, if you are sorting the TKPROF output, then use the PRINT= option to limit the amount of output. The idea here is that you only care about the worst couple of SQL statements. Using PRINT=10 means that your TKPROF output will be limited to only 10 statements... So, comparing two files that have been sorted and have only 10 statements is pretty easy to do just by looking at it... --- As far as any problems loading into tkprof_table, I can only guess that the user which you are running EXPLAIN PLANs into (using the EXPLAIN= option) does not have permission or space-quota to create a small temporary table in which to place EXPLAIN PLAN output. Check the permissions of the user you are specifying; log into SQL*Plus using the same user and try to create a small dummy table... - Original Message - From: BanarasiBabu Tippa To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 21, 2003 4:34 AM Subject: question on tkprof Hi gurus Anyone knows about the comparision of tkprof output of the trace files generated by same application in diffrent times and anyone have solution to Problem with pl/sql in insert script loading into tkprof_table. Thanks in advance Banarasi = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: Tim Gorman 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
Re: question on tkprof
As a test I took a *trc file generated on a V7.3.4.5 DB and copied it to a system where I have a dataless 9i copy of the same DB. I used TKPROF from 9i got all the wait statistics reported along with the relevant EXPLAIN PLAN! HTH YMMV HAND! -- 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: question on tkprof
Thank you very much Tim for your solution for the first question. Comming to second, that is not my problem. I was unable to load the data generated by tkprof whenINSERT option is used into tkprof_table. Most of the PL/SQL blocks are rejected. I searched for the same in metalink, they are saying that is a bug, but it was closed saying that this is a problem with SQL*Plus. They are suggesting to run from server manager but it's also unable do it andgving the MGR-11401 error. regards Banarasi Babu -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 21, 2003 6:29 PMTo: Multiple recipients of list ORACLE-LSubject: Re: question on tkprof First, always use the SORT= option to generate TKPROF output -- the utility is useless if you don't sort the output. For sorting, I have two suggestions: * sort by "logical reads" (i.e. SORT=EXEQRY,FCHQRY,EXECU,FCHCU) * sort by "elapsed time" (i.e. SORT=PRSELA,FCHELA,EXEELA) If elapsed time data is available (i.e. TIMED_STATISTICS=TRUE in database), then I prefer to use that. If not, then I use logical reads... Second, if you are sorting the TKPROF output, then use the PRINT= option to limit the amount of output. The idea here is that you only care about the "worst" couple of SQL statements. Using PRINT=10 means that your TKPROF output will be limited to only 10 statements... So, comparing two files that have been sorted and have only 10 statements is pretty easy to do just by looking at it... --- As far as any problems loading into "tkprof_table", I can only guess that the user which you are running EXPLAIN PLANs into (using the EXPLAIN= option) does not have permission or space-quota to create a smalltemporarytable in which to place EXPLAIN PLAN output. Check the permissions of the user you are specifying; log into SQL*Plus using the same user and try to create a small dummy table... - Original Message - From: BanarasiBabu Tippa To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 21, 2003 4:34 AM Subject: question on tkprof Hi gurus Anyone knows about the comparision of tkprof output of the trace files generated by same application in diffrent times and anyone have solution to Problem with pl/sql in insert script loading into tkprof_table. Thanks in advance Banarasi
RE: Interpretation of TKPROF output
Wow! This is a completely new view on the TKPROF. So far, I've been inclined to look upon the TKPROF output as on the holly scripture. Mentioning the tkprof bugs is like discovering Kumran rolls! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Monday, October 14, 2002 1:32 AM To: Multiple recipients of list ORACLE-L Subject: RE: Interpretation of TKPROF output Actually two things can cause differences between tkprof output and explain plan output: 1. Schema changes between when the trace output was collected and when the tkprof was executed. 2. tkprof bugs. Tkprof often produces incorrect execution plans from the STAT lines in the trace file. You can tell by viewing the STAT lines yourself. A line's pid is that row source operation's parent row source id. Children at the same level are listed in id order. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Bobak Sent: Sunday, October 13, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Peter, Row Source Operation is what Oracle actually did, or, at least what was recorded in the trace file. Execution Plan is what TkProf did in response to you specifying explain= option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time. When they differ, the Row Source Operation is a better bet as to what actually happened. Hope that helps, -Mark On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=user/password sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The Execution Plan table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the Row Source Operation table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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.com -- Author: Cary Millsap 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.com -- Author: Gogala, Mladen 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
Interpretation of TKPROF output
I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=user/password sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The Execution Plan table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the Row Source Operation table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter 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: Interpretation of TKPROF output
Hello; Somebody knows how to connect to a Oracle DB, In a Solaris 8 Server, and insert data With Visual Basic, through internet? Thank you. Francisco Murillo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Francisco Murillo Montoya 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: Interpretation of TKPROF output
Peter, Row Source Operation is what Oracle actually did, or, at least what was recorded in the trace file. Execution Plan is what TkProf did in response to you specifying explain= option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time. When they differ, the Row Source Operation is a better bet as to what actually happened. Hope that helps, -Mark On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=user/password sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The Execution Plan table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the Row Source Operation table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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: Interpretation of TKPROF output
Peter, In the perfect world you would expect the two explain plans (don't worry about the different headings) to be identical. Someone can probably answer this precisely but the first one is created during the trace and the second one is provided because you supplied the explain=user/pass option to tkprof - it basically says go back and get an explain plan for each query. Are you sure that both explain plans are generated using the same username on the same instance? Is one perhaps connecting to a different database such as development? In essence - if you run sqlplus user/pass from the same place you ran tkprof are you connected to the exact same location as the session that created the trace file? Unfortunately if you are connected to the same area then I cannot help much more. Perhaps try taking the statement and use autotrace in sqlplus to see which plan it provides. Perhaps row level security of something is kicking in and modifying the query (this caught us out once when explain plans changed because we were connected as a different user)? Regards, Mark. Schauss, Peter [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hgrum.com cc: Sent by: Subject: Interpretation of TKPROF output [EMAIL PROTECTED] 14/10/02 06:03 Please respond to ORACLE-L I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=user/password sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The Execution Plan table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the Row Source Operation table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind
RE: Interpretation of TKPROF output
Actually two things can cause differences between tkprof output and explain plan output: 1. Schema changes between when the trace output was collected and when the tkprof was executed. 2. tkprof bugs. Tkprof often produces incorrect execution plans from the STAT lines in the trace file. You can tell by viewing the STAT lines yourself. A line's pid is that row source operation's parent row source id. Children at the same level are listed in id order. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Bobak Sent: Sunday, October 13, 2002 7:53 PM To: Multiple recipients of list ORACLE-L Peter, Row Source Operation is what Oracle actually did, or, at least what was recorded in the trace file. Execution Plan is what TkProf did in response to you specifying explain= option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time. When they differ, the Row Source Operation is a better bet as to what actually happened. Hope that helps, -Mark On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote: I am running tkprof on some trace files with the following options: (Oracle 8.1.7) explain=user/password sort=exeqry,fchqry,execu,fchcu Output after each query includes the following two tables: Rows Row Source Operation --- --- 36 INLIST ITERATOR 36 TABLE ACCESS BY INDEX ROWID PART_LIST 72INDEX RANGE SCAN (object id 3451) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST' The Execution Plan table corresponds to the Explain Plan section of the Performance manual. There is no mention, however, of the Row Source Operation table. I note particularly, that in some cases the first table seems to agree with the second. In others, however, such as the example here, the Row Source table shows use of an index while the Execution Plan shows a Table Access (Full table scan?). Am I interpreting this correctly? If so, what is going on? Thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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.com -- Author: Cary Millsap 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).
Trace file with tkprof
Hi DBAs, Is there any way to get the same execution statistics between the finished sql statement and interrupted sql statement? Supposed table A have ten million rows. If select A.a, A.b from A where ..., in sqlplus session, actually the returned could be millions. I could not wait for all the selected rows coming out. I have to interrupte it in the process. Could I still get the same execution statistics in trace file? The same happened to set autotrace on in sqlplus session. I could only see the execution plan at the end of execution. Thanks in advance, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
RE: Trace file with tkprof
Title: RE: Trace file with tkprof
-Original Message-
From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
Is there any way to get the same execution statistics between
the finished sql statement and interrupted sql statement?
Supposed table A have ten million rows.
If select A.a, A.b from A where ..., in sqlplus session,
actually the returned could be millions. I could not wait for
all the selected rows coming out. I have to interrupte it in
the process. Could I still get the same execution statistics
in trace file?
The same happened to set autotrace on in sqlplus session. I
could only see the execution plan at the end of execution.
Would set autotrace traceonly explain or set autotrace traceonly explain statistics do what you need?
From the SQL*Plus manual, SET command:
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). The report can include execution statistics and the query execution path.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle9i SQL Reference manual.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
Information about Execution Plans and the statistics is documented in the Oracle9i Performance Guide and Reference manual.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
RE: rows values in tkprof
Ravi, The rows value comes from the execution plan stored in the trace file at runtime. The execution plan is only dumped to trace file when the cursor is closed. So, if you take another try with sql*plus for instance and quit sql*plus before closing the trace, then you will see the rows values printed in your trace. Or if you commit the transaction even if it is a select then you will see the rows count. That's a matter of fact there is a tkprof option to add the explain plan into the output. All the option does is connect to the database when you issue the tkprof command line and compute a explain plan. I see two quirks here first you do not have a real image of what happened at runtime and second you don't have the rows count. By the way if your cursor is closed ( you have stats info in your trace file) and you issue a tkprof command with the explain option you will see two execution plan in the output, the stored one and the newly computed one. Jean Remacle -Original Message- From: Nalla Ravi [mailto:[EMAIL PROTECTED]] Sent: lundi 24 juin 2002 21:08 To: Multiple recipients of list ORACLE-L Subject:rows values in tkprof Dear All, In my tkprof output rows values under explain plan section are not getting printed, is there any parameter to be set? Thanks, Ravi __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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: Remacle Jean 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).
rows values in tkprof
Dear All, In my tkprof output rows values under explain plan section are not getting printed, is there any parameter to be set? Thanks, Ravi __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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).
tkprof analysis
Hi, Can any one point me or kindly send me the analysis of TKProf outout please. Thanks you so much for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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 analysis
Oracle8i Server Tuning guide (part #A76992, available for free download from http://docs.oracle.com), pages 6-22 through 6-30 for TKPROF output example - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 7:38 AM Hi, Can any one point me or kindly send me the analysis of TKProf outout please. Thanks you so much for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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: Tim Gorman 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: Response time analysis and TKPROF
Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg I do think so. My point was that the SQL statement level is the wrong granularity. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Response time analysis and TKPROF
Well , Depending on what you want: 1) The only way to see 'response times' on SQL statements is to use 10046, but that adds overhead. There are third party tools out there that will do for you without the 10046 trace. 2) v$sesstat will show the service component (cpu) on the session level, not on the SQL statement level. 3) v$session_wait is close to useless ;-) It will tell you what a session is waiting on right now or what the last wait of a session was. That is not enough. You need v$session_event Again that is on the session level and not on the SQL statement level. See my comments at 1) about that. If you response times on the SQL statement level, you need to use 10046 or a third party tool. On session level use v$sesstat and v$session_event. On instance level use v$sysstat and v$system_event. Read more on how to do that in the YAPP paper. Anjo. Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Anjo Kolk 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: Response time analysis and TKPROF
Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, Stephane Faroult wrote: Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg I do think so. My point was that the SQL statement level is the wrong granularity. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Anjo Kolk 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: Response time analysis and TKPROF
Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I meet more and more (business) processes in which, without being top-notch, SQL statements do not look terribly bad. Rewrite everything, and it roars. I am not sure that digging deep in this case inside trace files is the most effective. Having a talk round the coffee-machine with end-users also helps. And you always have that terrible SQL statement which runs at 2 am and about which nobody cares as long as the maintenance window is large enough. What I question is the need to abuse queue theory when, let's put it clearly, the problem is awful code written by beginners under the leadership of people too often unable to reread what has been written by their 'subordinates'. And I have strong doubts about how easily you will 'sell' it to a management who better understands that a faster processor (or an additional processor) may make things run faster - even if we all know that it is far from being always true. How much simpler for a 'decision taker' than purchasing days of consulting for a result which may, and usually will, be much more efficient, but for which quantifying (even wrongly) results is much more delicate. End of rant ;-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Response time analysis and TKPROF
If someone were to ask me whether a TKPROF report tells you anything about wait events, my first response would be No. Yet what, if anything, can I learn from a quick glance at the totals for CPU time and elapsed time? If... response time = service time + wait time then is it correct to say: * response time is: TKPROF total elapsed time * service time is: TKPROF total CPU time * wait time is: TKPROF total elapsed time - total CPU time If this is true, then from a TKPROF report, I could glance at these two totals and see whether wait events are significantly affecting the SQL statement. Case #1: The two totals are very close. Waits are not affecting this SQL statement. Case #2: Total CPU time is significantly less than total elapsed time. Waits are significantly affecting the statement. Consider running the SQL in a stand alone session, and use before and after snapshots of v$session_event to see what it's waiting on. Are my assumptions true? And as Stephane points out, even if they are true, are they of any practical value? Personally, I think it would be kind of cool to be able to look at a TKPROF report and get a quick read on whether wait events are significantly affecting this particular SQL statement. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Response time analysis and TKPROF
Yes, I think that we are talking about different things: 1) I don't feel that we are abusing the queue theory by borrowing terms like service time and wait time. Actually when I did the YAPP method, it was back in 1996 on a project that involved Tuxedo and the programmers on the project wanted more processes. I had to convince them to do it with less and I could do that with the service and wait time model (calculating the response time in the Oracle server for sessions). And it really works well. 2) The problem with tuning by hit ratios and tuning by counting (like number of buffer gets and number of physical I/O from v$sqlarea) is that we ignore the cost or the time they take. We assume that each Logical I/O or buffer get is the same cost. Which is not true. So the statement with a 1000 LIO could be more expensive than the statement with a 1200 LIO. The same is true for the Physical I/O. Not each physical I/O has the same cost or response time. Again the statement with 1000 I/Os may be more expensive than the 1200 I/Os, because one is going after different disks. I have run tests that show LIO for the same statement to be all most twice as slow (depending on some settings, but the SQL is the same, same plan) 3) If you now take the service time (which is CPU) and that part is 80 percent of the total response time, we can tell management that a 50 percent faster CPU will make roughly a 40 percent difference. That is not to say that is the right approach, because the opposite may also happen (20 percent CPU and 80 percent wait, 50 percent faster will only make a 10 percent improvement). I have seen customers with response time problems that consisted for over 80 percent of I/O problems (I/O too slow). They needed a 50 percent improvement but couldn't fix the I/O. So they wanted to find 50 percent some where else. That didn't happen ofcourse and they had to fix the I/O problem. Now that really helped management to understand where the priorities were: Yeeh, this is not a database problem but a disk array problem. 4) I believe that 80-90 percent of all Oracle applications out there in the field are highly inefficient. And that doesn't mean that they don't use bind variables or that they do many logical I/Os. And that the only way to fix them is faster CPU's (open to flames here ;-)) or do some serious redesigning. 5) Oracle provides many interesting statistics, but most of them only count. Now in Oracle 9i they have added some long overdue response time or timing statistics. But still it is lacking very important information. For example, how can we tell what a SQL statement waited for a particular session between 2 AM and 3 AM (without 10046 tracing) or for all sessions ? If the session performs a business function, what resources did the session use in that period for that business function ? That information is hard to come by, or with very high overhead with the traditional Oracle tools. 6) probably the most important point. The database doesn't decide WHAT SQL to execute. The database decides HOW SQL should be executed. The application decides WHAT to execute. Many fast SQL statements can still result in a slow business function, because do we need all those functions ? I have another favorite formula for that: Amount * Cost = total cost. So either reduce the cost or reduce the amount of SQL statements. Showed this formula to bunch of people at an Oracle user group in the netherlands. 2 days later, I got an email from someone saying that they concentrated on the amount instead of cost They reduced the batch job time from 2 hours to 10 minutes without tuning the SQL statement, but tuned the function. 7) The response time model gives the end-user perspective (without actually having to go to the coffee machine, unless you want coffee ;-)), but talking to them is very valuable. That doesn't mean they are right ;-) Anjo. Stephane Faroult wrote: Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I meet more and more (business) processes in which, without being top-notch, SQL statements do not look terribly bad. Rewrite everything, and it roars. I am not sure that digging deep in this case inside trace files is the most effective. Having a talk round the coffee-machine with end-users also helps. And you always have that terrible SQL statement which runs at 2 am and about which nobody cares as long as the maintenance window is large enough. What I question is the need to abuse queue theory
RE: Response time analysis and TKPROF
Anjo, you mentioned third party tool using instead of 10046 event trace files. What is the tool and how it works - if you have this info of cource. Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anjo Kolk Sent: Sunday, May 05, 2002 8:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: Response time analysis and TKPROF Yes, I think that we are talking about different things: 1) I don't feel that we are abusing the queue theory by borrowing terms like service time and wait time. Actually when I did the YAPP method, it was back in 1996 on a project that involved Tuxedo and the programmers on the project wanted more processes. I had to convince them to do it with less and I could do that with the service and wait time model (calculating the response time in the Oracle server for sessions). And it really works well. 2) The problem with tuning by hit ratios and tuning by counting (like number of buffer gets and number of physical I/O from v$sqlarea) is that we ignore the cost or the time they take. We assume that each Logical I/O or buffer get is the same cost. Which is not true. So the statement with a 1000 LIO could be more expensive than the statement with a 1200 LIO. The same is true for the Physical I/O. Not each physical I/O has the same cost or response time. Again the statement with 1000 I/Os may be more expensive than the 1200 I/Os, because one is going after different disks. I have run tests that show LIO for the same statement to be all most twice as slow (depending on some settings, but the SQL is the same, same plan) 3) If you now take the service time (which is CPU) and that part is 80 percent of the total response time, we can tell management that a 50 percent faster CPU will make roughly a 40 percent difference. That is not to say that is the right approach, because the opposite may also happen (20 percent CPU and 80 percent wait, 50 percent faster will only make a 10 percent improvement). I have seen customers with response time problems that consisted for over 80 percent of I/O problems (I/O too slow). They needed a 50 percent improvement but couldn't fix the I/O. So they wanted to find 50 percent some where else. That didn't happen ofcourse and they had to fix the I/O problem. Now that really helped management to understand where the priorities were: Yeeh, this is not a database problem but a disk array problem. 4) I believe that 80-90 percent of all Oracle applications out there in the field are highly inefficient. And that doesn't mean that they don't use bind variables or that they do many logical I/Os. And that the only way to fix them is faster CPU's (open to flames here ;-)) or do some serious redesigning. 5) Oracle provides many interesting statistics, but most of them only count. Now in Oracle 9i they have added some long overdue response time or timing statistics. But still it is lacking very important information. For example, how can we tell what a SQL statement waited for a particular session between 2 AM and 3 AM (without 10046 tracing) or for all sessions ? If the session performs a business function, what resources did the session use in that period for that business function ? That information is hard to come by, or with very high overhead with the traditional Oracle tools. 6) probably the most important point. The database doesn't decide WHAT SQL to execute. The database decides HOW SQL should be executed. The application decides WHAT to execute. Many fast SQL statements can still result in a slow business function, because do we need all those functions ? I have another favorite formula for that: Amount * Cost = total cost. So either reduce the cost or reduce the amount of SQL statements. Showed this formula to bunch of people at an Oracle user group in the netherlands. 2 days later, I got an email from someone saying that they concentrated on the amount instead of cost They reduced the batch job time from 2 hours to 10 minutes without tuning the SQL statement, but tuned the function. 7) The response time model gives the end-user perspective (without actually having to go to the coffee machine, unless you want coffee ;-)), but talking to them is very valuable. That doesn't mean they are right ;-) Anjo. Stephane Faroult wrote: Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I
Response time analysis and TKPROF
For a single SQL statement, I'd like to determine the response time components. Since TKPROF output provides totals for Elapsed Time and CPU Time, is it correct to say that the service time component is total CPU, and the wait time component is total Elapsed Time minus total CPU time? Is this formula perfect, a very good and workable approximation, or way off? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Response time analysis and TKPROF
Greg Moore wrote: For a single SQL statement, I'd like to determine the response time components. Since TKPROF output provides totals for Elapsed Time and CPU Time, is it correct to say that the service time component is total CPU, and the wait time component is total Elapsed Time minus total CPU time? Is this formula perfect, a very good and workable approximation, or way off? Greg, Sorry for saying so, but I think that you are erring (unless it's me). What is for you the 'service'? I presume that it's getting your result from Oracle. So your server is Oracle. But your server itself is at the mercy of a number of things and can wait for : a) CPU, b) a latch, c) a lock, d) I/Os and probably many other things ... Elapsed time minus CPU time is certainly a 'wait time' component, but some of this 'wait' can be a perfectly legitimate part of the 'service' - such as waiting for a latch or bringing the block into memory. Queue theory is indeed very interesting, but what do you want to do with it? As I see it, the benefit of knowing service time and wait time is to say 'well, if I want to ensure that say 90% of my clients do not spend more than xxx time units in the system, then I must have that many servers'. Great for sizing hardware, but I am afraid that tackling the problem as you are trying to do it cannot lead anywhere. You cannot add more of the same SQL query, can you? I mean, can you apply queue theory when servers are brought in by clients? Or your query is just a part of a 'service' as a transactional monitor understands it, which is, to me, a bit different. My feeling is that it is wrong to see one SQL statement as a service. A business process is a service, and you can diminish service time by tuning your code - and I am a bit confused about wait times at this level. The (primitive) way I see elapsed time and CPU time in tkprof output is that first elapsed time is how users see it. If the number of logical reads is high, I have a problem (either the query is inherently costly, either it is poorly written). If I have a high number of logical reads, it is somewhat normal to have physical reads as well, and therefore a significant difference between elapsed and CPU time(I/O waits). If I have no physical I/O and a huge difference between CPU and elapsed time, then either I have locking problems or the system is in a sad state. And so on. But I find theorizing difficult at this level. Contradictors welcome :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Response time analysis and TKPROF
The way I am reading Gregs questions, he is asking if service time means CPU time (sys + user). And that is correct. But this is all on the server side, so in Oracle .. Anjo. Stephane Faroult wrote: Greg Moore wrote: For a single SQL statement, I'd like to determine the response time components. Since TKPROF output provides totals for Elapsed Time and CPU Time, is it correct to say that the service time component is total CPU, and the wait time component is total Elapsed Time minus total CPU time? Is this formula perfect, a very good and workable approximation, or way off? Greg, Sorry for saying so, but I think that you are erring (unless it's me). What is for you the 'service'? I presume that it's getting your result from Oracle. So your server is Oracle. But your server itself is at the mercy of a number of things and can wait for : a) CPU, b) a latch, c) a lock, d) I/Os and probably many other things ... Elapsed time minus CPU time is certainly a 'wait time' component, but some of this 'wait' can be a perfectly legitimate part of the 'service' - such as waiting for a latch or bringing the block into memory. Queue theory is indeed very interesting, but what do you want to do with it? As I see it, the benefit of knowing service time and wait time is to say 'well, if I want to ensure that say 90% of my clients do not spend more than xxx time units in the system, then I must have that many servers'. Great for sizing hardware, but I am afraid that tackling the problem as you are trying to do it cannot lead anywhere. You cannot add more of the same SQL query, can you? I mean, can you apply queue theory when servers are brought in by clients? Or your query is just a part of a 'service' as a transactional monitor understands it, which is, to me, a bit different. My feeling is that it is wrong to see one SQL statement as a service. A business process is a service, and you can diminish service time by tuning your code - and I am a bit confused about wait times at this level. The (primitive) way I see elapsed time and CPU time in tkprof output is that first elapsed time is how users see it. If the number of logical reads is high, I have a problem (either the query is inherently costly, either it is poorly written). If I have a high number of logical reads, it is somewhat normal to have physical reads as well, and therefore a significant difference between elapsed and CPU time(I/O waits). If I have no physical I/O and a huge difference between CPU and elapsed time, then either I have locking problems or the system is in a sad state. And so on. But I find theorizing difficult at this level. Contradictors welcome :-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Anjo Kolk 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: Response time analysis and TKPROF
But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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?
I have heard two different ones, no idea if either is correct :-) trace kernel profiler transient kernel profiler John [EMAIL PROTECTED] wrote: What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA 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?
I thought it was Trace Kernel Profiler. PB --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Transient Kernel Profile. - Kirti -Original Message- Sent: Wednesday, April 10, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Nemeth 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: Deshpande, Kirti 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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).
TKPROF?
What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Nemeth 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?
Transient Kernel Profile. - Kirti -Original Message- Sent: Wednesday, April 10, 2002 8:38 PM To: Multiple recipients of list ORACLE-L What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Nemeth 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: Deshpande, Kirti 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?
Tool Kernel PROFiler Anjo. David Nemeth wrote: What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Nemeth 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: Anjo Kolk 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?
Anjo: I always thought it Trace Kernel PROFile? Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Sent: Wednesday, April 10, 2002 8:23 PM To: Multiple recipients of list ORACLE-L Tool Kernel PROFiler Anjo. David Nemeth wrote: What does it stand for? Tool Kit Profiler??? tia, David Nemeth __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Nemeth 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: Anjo Kolk 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: K Gopalakrishnan 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: strange tkprof output for SQLs - II
Hello all, Thanks to Bjorn Engsig of MiracleAS.dk Also, Here is the answer from Oracle support. I wanted to share with you: # Begin tkprof substracts the child statistics from parent ones. . the trace file is missing the line for PARSE call of the blsql block - as it's missing it's impossible to to determine the start and therefore the times are reported to be zero - could be the result of how the tracing was enabled. . As it seems enabling trace from different session can produce incomplete trace file I suggest you enable it in current session to get most accurate results. # End I had seen this wrong behavior for current statements before. But as seen in the thread, while closing the parent PL/SQL block, we are missing the PARSE line of parent statement in the trace since it's parsed before enabling trace. So, tkprof is unable to determine the starting time and sets cpu,elapsed times to ZERO. as a result: Be careful if you enable sql trace from other session. You can see wrong tkprof outputs not only for the current running statement, but also for statements which are parsed before enabling trace. regards... Danisment Gazi Unal wrote: Hello Mogens, I saw this problem whether or not shared pool was flushed for PL/SQLs. Here are my observations for SQLs!!! and PL/SQLs: - Enabling sql trace from current session: When a cursor is closed after their child recursive statements, their recursive statistics parsed after the statement are included in parent's statistics. Recursive statements parsed before the statement are not included. - Enabling sql trace from other session after a while(after the parenet statement is parsed): Recursive statistics are not included in parent statement. But tkprof doesn't use exac values in raw trace file. If they are not included, why doesn't tkprof return exac values in raw trace files ? I'll debug tkprof if it calls Oracle's kernel function to see if tkprof is just a simple formatter or calculates something that I'm missing. I did not understand this is expected behavior or not. I would like to know statistics in raw trace files are inclusive or exclusive. I'm facing diffierent behaviors for this problem. I've opened an iTAR. thanks in advance... Mogens Nrgaard wrote: Danisment - isn't it always "alter system flush shard_pool"? I thought so... Danisment Gazi Unal (Unal Bilisim) wrote: >Hello, > >I did 2 tests for PL/SQL and SQL statements. This is the test for >SQL. > >Here are the steps: > >SQL > alter session flush shared_pool; >SQL > alter session set sql_trace=true; >SQL > insert into test select * from test; >SQL > alter session set sql_trace=false; > > >FROM DICTIONARY: > >SVRMGR> select >SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql >where sql_text like 'insert into test select * from test%'; > >SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE >COMMAND_TY >- -- -- -- >-- >insert into test select * from test 345 1014 >8192 2 >1 row selected. > > >FROM RAW TRACE FILE: > >PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 >hv=895761708 ad='5083d50c' >insert into test select * from test >END OF STMT >PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 >. >other recursive statements. >. >. near end of file >EXEC >#1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464 > > > >BUFFER GETS IN RAW TRACE FILE: >cr: 597 + 42 = 639 >cu: 373 + 2 = 375 > >Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS > > >DISK_READS IN RAW TRACE FILE: > >p: 17 + 328 = 345, which is same as v$sql.DISK_READS. > >According to these test, results in dictionary and raw trace files are >same. But tkprof formats as below: > > >insert into test select * from test > > >call count cpu elapsed disk query >current rows >--- -- -- -- -- -- >-- >Parse 1 0.00 0.02 17 42 >1 0 >Execute 1 0.12 1.86 91 126 >356 8192 >Fetch 0 0.00 0.00 0 0 >0 0 >--- -- -- ------ -- -- >-- >total 2 0.12 1.88 108 168 >357 8192 > > >DISK_READS = 108 >BUFFER GETS = 168 + 357 = 525 > >Question: > >Which one is correct ? Dictionary/raw trace file or tkprof results ? > >My comment: > >I guess, tkprof substructs child recursive statements from parent user >statement ? Why ? This is not a PL/SQL statement ? So, statistics are >already not included in parent statement ? I guess statistics in raw >trace files are inclusive statistics which include statistics of their >child statements according to call orders of kernel calls. But is this >expected behavior. > >
Re: strange tkprof output for SQLs - II
Hello Mogens, I saw this problem whether or not shared pool was flushed for PL/SQLs. Here are my observations for SQLs!!! and PL/SQLs: - Enabling sql trace from current session: When a cursor is closed after their child recursive statements, their recursive statistics parsed after the statement are included in parent's statistics. Recursive statements parsed before the statement are not included. - Enabling sql trace from other session after a while(after the parenet statement is parsed): Recursive statistics are not included in parent statement. But tkprof doesn't use exac values in raw trace file. If they are not included, why doesn't tkprof return exac values in raw trace files ? I'll debug tkprof if it calls Oracle's kernel function to see if tkprof is just a simple formatter or calculates something that I'm missing. I did not understand this is expected behavior or not. I would like to know statistics in raw trace files are inclusive or exclusive. I'm facing diffierent behaviors for this problem. I've opened an iTAR. thanks in advance... Mogens Nrgaard wrote: Danisment - isn't it always "alter system flush shard_pool"? I thought so... Danisment Gazi Unal (Unal Bilisim) wrote: >Hello, > >I did 2 tests for PL/SQL and SQL statements. This is the test for >SQL. > >Here are the steps: > >SQL > alter session flush shared_pool; >SQL > alter session set sql_trace=true; >SQL > insert into test select * from test; >SQL > alter session set sql_trace=false; > > >FROM DICTIONARY: > >SVRMGR> select >SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql >where sql_text like 'insert into test select * from test%'; > >SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE >COMMAND_TY >- -- -- -- >-- >insert into test select * from test 345 1014 >8192 2 >1 row selected. > > >FROM RAW TRACE FILE: > >PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 >hv=895761708 ad='5083d50c' >insert into test select * from test >END OF STMT >PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 >. >other recursive statements. >. >. near end of file >EXEC >#1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464 > > > >BUFFER GETS IN RAW TRACE FILE: >cr: 597 + 42 = 639 >cu: 373 + 2 = 375 > >Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS > > >DISK_READS IN RAW TRACE FILE: > >p: 17 + 328 = 345, which is same as v$sql.DISK_READS. > >According to these test, results in dictionary and raw trace files are >same. But tkprof formats as below: > > >insert into test select * from test > > >call count cpu elapsed disk query >current rows >--- -- -- -- -- -- >-- >Parse 1 0.00 0.02 17 42 >1 0 >Execute 1 0.12 1.86 91 126 >356 8192 >Fetch 0 0.00 0.00 0 0 >0 0 >--- -- -- ------ -- -- >-- >total 2 0.12 1.88 108 168 >357 8192 > > >DISK_READS = 108 >BUFFER GETS = 168 + 357 = 525 > >Question: > >Which one is correct ? Dictionary/raw trace file or tkprof results ? > >My comment: > >I guess, tkprof substructs child recursive statements from parent user >statement ? Why ? This is not a PL/SQL statement ? So, statistics are >already not included in parent statement ? I guess statistics in raw >trace files are inclusive statistics which include statistics of their >child statements according to call orders of kernel calls. But is this >expected behavior. > >Thanks in advance... > >-- >Danisment Gazi Unal >http://www.unal-bilisim.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?= 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). -- Danisment Gazi Unal http://www.unal-bilisim.com
strange tkprof output for SQLs - II
Hello, I did 2 tests for PL/SQL and SQL statements. This is the test for SQL. Here are the steps: SQL alter session flush shared_pool; SQL alter session set sql_trace=true; SQL insert into test select * from test; SQL alter session set sql_trace=false; FROM DICTIONARY: SVRMGR select SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql where sql_text like 'insert into test select * from test%'; SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE COMMAND_TY - -- -- -- -- insert into test select * from test 345 1014 8192 2 1 row selected. FROM RAW TRACE FILE: PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 hv=895761708 ad='5083d50c' insert into test select * from test END OF STMT PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 . other recursive statements. . . near end of file EXEC #1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464 BUFFER GETS IN RAW TRACE FILE: cr: 597 + 42 = 639 cu: 373 + 2 = 375 Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS DISK_READS IN RAW TRACE FILE: p: 17 + 328 = 345, which is same as v$sql.DISK_READS. According to these test, results in dictionary and raw trace files are same. But tkprof formats as below: insert into test select * from test call count cpuelapsed disk query currentrows --- -- -- -- -- -- -- Parse1 0.00 0.02 17 42 1 0 Execute 1 0.12 1.86 91126 3568192 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2 0.12 1.88108168 3578192 DISK_READS = 108 BUFFER GETS = 168 + 357 = 525 Question: Which one is correct ? Dictionary/raw trace file or tkprof results ? My comment: I guess, tkprof substructs child recursive statements from parent user statement ? Why ? This is not a PL/SQL statement ? So, statistics are already not included in parent statement ? I guess statistics in raw trace files are inclusive statistics which include statistics of their child statements according to call orders of kernel calls. But is this expected behavior. Thanks in advance... -- Danisment Gazi Unal http://www.unal-bilisim.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (Unal Bilisim) 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).
strange tkprof output for PL/SQLs
Hello, I did 2 tests for PL/SQL and SQL statements. This is the test for PL/SQL. I've enabled sql_trace from another session by DBMS_SYSTEM.SET_EV(). Here are my observations and questions: - The caller PL/SQL package are seen at the end of the raw file as below: PARSING IN CURSOR #1 len=19 dep=0 uid=5 oct=47 lid=5 tim=2882392980 hv=3659692972 ad='507cbd68' BEGIN pdoug; END; END OF STMT EXEC #1:c=0,e=0,p=3533,cr=5229,cu=132854,mis=1,r=1,dep=0,og=4,tim=2882392980 *** 2002-03-20 15:20:33.571 Here are the v$SQL results: select SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED from v$sql where upper(SQL_TEXT) like '%DOUG%'; SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE -- -- -- -- INSERT INTO DOUG VALUES ( :b1,:b2 ) 3533 138083 12 BEGIN pdoug; END; 3533 138083 1 Comparision of PL/SQL package with the raw trace file: Physical reads: --- Raw trace file: 3533 V$SQL : 3533 Logical IO: --- Raw trace file: 5229 + 132854 = 138083 V$SQL : 138083 As you see raw trace file and V$SQL are consistent for this parent PL/SQL statement. This is true for both enabling sql_trace from current session or other session. Here are the tkprof output for parent PL/SQL and its child SQL statement. Child recursive statement called from parent PL/SQL statement: INSERT INTO DOUG VALUES ( :b1,:b2 ) call count cpuelapsed disk query currentrows --- -- -- -- -- -- -- Parse0 0.00 0.00 0 0 0 0 Execute 83646 41.52 110.37218565 92483 83646 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total83646 41.52 110.37218565 92483 83646 Parent statement calls its recursive SQL statement given above: BEGIN pdoug; END; call count cpuelapsed disk query currentrows --- -- -- -- -- -- -- Parse0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00844 1279 40206 1 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total1 0.00 0.00844 1279 40206 1 Here are the questions: 1- if recursive statements are included in the parent statement, why are the child statement's some statistics(cpu,elapsed,current) greater than the parent statement's statistics. Also, please have a look at parent statistics. is it possible for 1279+40206=41485 blocks to be processed in ZERO time and total elapsed time is ZERO too. 2- if recursive statements are not included in the parent statement, why tkprof output shows different values for disk and logical IO. Here are the comparisions between tkprof output and V$SQL for parent PL/SQL: Physical reads: --- Raw trace file: 3533 tkprof output : 844 Logical IO: --- Raw trace file: 5229 + 132854 = 138083 tkprof output : 1279 + 40206 = 41485 As a result, raw trace file is consistent with V$SQL for the parent statement. But tkprof reports different values. What is your comment ? Thanks in advance... -- Danisment Gazi Unal http://www.unal-bilisim.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (Unal Bilisim) 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: strange tkprof output for SQLs - II
Danisment - isn't it always alter system flush shard_pool? I thought so... Danisment Gazi Unal (Unal Bilisim) wrote: Hello, I did 2 tests for PL/SQL and SQL statements. This is the test for SQL. Here are the steps: SQL alter session flush shared_pool; SQL alter session set sql_trace=true; SQL insert into test select * from test; SQL alter session set sql_trace=false; FROM DICTIONARY: SVRMGR select SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql where sql_text like 'insert into test select * from test%'; SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE COMMAND_TY - -- -- -- -- insert into test select * from test 345 1014 8192 2 1 row selected. FROM RAW TRACE FILE: PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 hv=895761708 ad='5083d50c' insert into test select * from test END OF STMT PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 . other recursive statements. . . near end of file EXEC #1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464 BUFFER GETS IN RAW TRACE FILE: cr: 597 + 42 = 639 cu: 373 + 2 = 375 Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS DISK_READS IN RAW TRACE FILE: p: 17 + 328 = 345, which is same as v$sql.DISK_READS. According to these test, results in dictionary and raw trace files are same. But tkprof formats as below: insert into test select * from test call count cpuelapsed disk query currentrows --- -- -- -- -- -- -- Parse1 0.00 0.02 17 42 1 0 Execute 1 0.12 1.86 91126 3568192 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2 0.12 1.88108168 3578192 DISK_READS = 108 BUFFER GETS = 168 + 357 = 525 Question: Which one is correct ? Dictionary/raw trace file or tkprof results ? My comment: I guess, tkprof substructs child recursive statements from parent user statement ? Why ? This is not a PL/SQL statement ? So, statistics are already not included in parent statement ? I guess statistics in raw trace files are inclusive statistics which include statistics of their child statements according to call orders of kernel calls. But is this expected behavior. Thanks in advance... -- Danisment Gazi Unal http://www.unal-bilisim.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?= 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: where is tkprof?
patch set number? --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Jonathan, Some intelligent person at MD forgot to add the tkprof (Trace Kernel PROFile) with the NT versions. OWS has a patch for that. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Gennick Sent: Wednesday, March 06, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm running 9.0.1.1.1 on Win NT, and I don't have a tkprof executable. Am I losing my mind? Did Oracle do away with tkprof on NT? I realize the answer to both those questions may be yesgrin, but I'm most interested in why I don't have tkprof. Best regards, Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). winmail.dat
tkprof plan missing rows
Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same version and OS, that tkprof DOES have row counts. I believe I'm just missing a parameter somewhere, but I don't know where. Thanks for any ideas. Barb $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd sys=no * *** Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) here's a tkprof from a different database: Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 1440212 SORT (GROUP BY) 2785044HASH JOIN 3109095 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WORK_ORDER_DETAILS' 3762491 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE) 5727880 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SLS_OF_REC' -- 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). winmail.dat
Re: tkprof plan missing rows
Do you have data in the tables that you are running the query againt? You do have row count zero, so tkprof seems to count the rows but the rows do not seem to be there or are not selected. --- Baker, Barbara [EMAIL PROTECTED] wrote: Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same version and OS, that tkprof DOES have row counts. I believe I'm just missing a parameter somewhere, but I don't know where. Thanks for any ideas. Barb $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd sys=no * *** Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) here's a tkprof from a different database: Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 1440212 SORT (GROUP BY) 2785044HASH JOIN 3109095 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WORK_ORDER_DETAILS' 3762491 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE) 5727880 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SLS_OF_REC' -- 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). ATTACHMENT part 2 application/ms-tnef name=winmail.dat = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir 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: where is tkprof?
Last night I applied the 9.0.1.2 patchset (for Win NT 4.0). Tkprof.exe appeared after I was done :) - Kirti -Original Message- Sent: Thursday, March 07, 2002 7:18 AM To: Multiple recipients of list ORACLE-L patch set number? --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Jonathan, Some intelligent person at MD forgot to add the tkprof (Trace Kernel PROFile) with the NT versions. OWS has a patch for that. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Gennick Sent: Wednesday, March 06, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm running 9.0.1.1.1 on Win NT, and I don't have a tkprof executable. Am I losing my mind? Did Oracle do away with tkprof on NT? I realize the answer to both those questions may be yesgrin, but I'm most interested in why I don't have tkprof. Best regards, Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
tkprof plan missing rows
Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same version and OS, that tkprof DOES have row counts. I believe I'm just missing a parameter somewhere, but I don't know where. Thanks for any ideas. Barb $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd sys=no * *** Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) here's a tkprof from a different database: Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 1440212 SORT (GROUP BY) 2785044HASH JOIN 3109095 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WORK_ORDER_DETAILS' 3762491 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE) 5727880 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SLS_OF_REC' -- 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 plan missing rows
It usually means the cursor for that query was not closed before the end of file (e.g. SQL in pl/sql and you didn't do an exit to get out of sql*plus) so Oracle never got around to dumping the STAT lines. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 08 March 2002 18:15 | |Oracle 7.3.4, OpenVMS 7.1 | |My tkprof report is missing the row count in the execution plan. Does |anyone know why what I might be missing? |If I autotrace the same query in the same database, I do get cardinality. |The tables have been analyzed. | |If I tkprof another database using the same version and OS, that tkprof DOES |have row counts. |I believe I'm just missing a parameter somewhere, but I don't know where. | |Thanks for any ideas. |Barb | |$ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd |sys=no | |* |*** | | |Rows Execution Plan |--- --- | 0 SELECT STATEMENT GOAL: CHOOSE | 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' | 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) | |* *** | | -- 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: where is tkprof?
patch set number? --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Jonathan, Some intelligent person at MD forgot to add the tkprof (Trace Kernel PROFile) with the NT versions. OWS has a patch for that. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Gennick Sent: Wednesday, March 06, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm running 9.0.1.1.1 on Win NT, and I don't have a tkprof executable. Am I losing my mind? Did Oracle do away with tkprof on NT? I realize the answer to both those questions may be yesgrin, but I'm most interested in why I don't have tkprof. Best regards, Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Help with Parsing and TKPROF output
You indicate, that you are specifying max_opencursors (I assume you mean maxopencursors) and that your program program is written in Pro*C (or another precompiler) Do remember, that maxopencursors only influences implicit cursors in the precompiler and you must code explicit cursors correctly, so that they do not re-parse contineously. For explicit cursors, you should avoid doing CLOSE, simply using OPEN again (on an already open cursor) causes it to avoid the parse step. I would also recommend inspecting the raw trace file to see which of the parse calls is really taking that long. Thanks, Bjørn. On Wednesday 06 March 2002 04:53, you wrote: Hi All, I have an online program that is timing out and when I trace it I get the following at the bottom of my tkprof output. It's easy to see why the transaction is running slow, because of all the parsing. But the program and all called modules are compiled with max_opencursors = 75 to stop the parsing problem, though it doesn't seem to be helping here. There are only 64 unique sql statements that all use host variables, so why does it also say there 786 sql statements in the session, what could be causing the 64 to turn into 786 and be getting reparsed all the time :-(( Any help on this would be greatly appreciated as the transaction dies after awhile and it's in production doh! Thanks Ian OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 667 1.50 22.38 0 0 0 0 Execute 5071 0.30 0.32 0460 31 5514 Fetch 7439 1.17 6.60 1 66144 4 7257 --- -- -- -- -- -- -- total13177 2.97 29.30 1 66604 35 12771 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 118 0.19 3.89 0 0 0 0 Execute533 0.32 4.56 0 0 0 532 Fetch 267 0.00 0.02 0271532 267 --- -- -- -- -- -- -- total 918 0.51 8.47 0271532 799 Misses in library cache during parse: 0 784 user SQL statements in session. 2 internal SQL statements in session. 786 SQL statements in session. 64 statements EXPLAINed in this session. Trace file: ora00503.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 784 user SQL statements in trace file. 2 internal SQL statements in trace file. 786 SQL statements in trace file. 68 unique SQL statements in trace file. 64 SQL statements EXPLAINed using schema: CSISDBA.prof$plan_table Default table was used. Table was created. Table was dropped. 19969 lines in trace file. Content-Type: text/html; charset=us-ascii; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= 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).
where is tkprof?
I'm running 9.0.1.1.1 on Win NT, and I don't have a tkprof executable. Am I losing my mind? Did Oracle do away with tkprof on NT? I realize the answer to both those questions may be yesgrin, but I'm most interested in why I don't have tkprof. Best regards, Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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: Help with Parsing and TKPROF output
Hi Bjorn, Thanks for your reply. Based on what you have said I think I might have come across what could be the problem. We have common modules (the server side is microfocus cobol - oracle 7.3.4 on NT) that are used by the online servers and also batch programs. Normally the online programs are compiled with mode=ansi and the batch programs are compiled with mode=oracle so that cursors can be kept open across commits So I am wondering if the calls to common modules by the online system which are compiled with mode=ansi causes the cursor to be closed and the cursor cache entry lost so the next time the common module is called it has to reparse it, even though it may still be in the same online transaction. Trouble is I don't really know what to do to fix this problem as I'm sure we had to have the mode=ansi for the online programs for some reason (it was all designed about 3 years ago so I'm having trouble remembering the reasons for some things) Do you think it would be a problem having mode=oracle for the online system ?? Thanks again Ian -Original Message- Sent: Wednesday, 6 March 2002 18:28 To: Multiple recipients of list ORACLE-L You indicate, that you are specifying max_opencursors (I assume you mean maxopencursors) and that your program program is written in Pro*C (or another precompiler) Do remember, that maxopencursors only influences implicit cursors in the precompiler and you must code explicit cursors correctly, so that they do not re-parse contineously. For explicit cursors, you should avoid doing CLOSE, simply using OPEN again (on an already open cursor) causes it to avoid the parse step. I would also recommend inspecting the raw trace file to see which of the parse calls is really taking that long. Thanks, Bjørn. On Wednesday 06 March 2002 04:53, you wrote: Hi All, I have an online program that is timing out and when I trace it I get the following at the bottom of my tkprof output. It's easy to see why the transaction is running slow, because of all the parsing. But the program and all called modules are compiled with max_opencursors = 75 to stop the parsing problem, though it doesn't seem to be helping here. There are only 64 unique sql statements that all use host variables, so why does it also say there 786 sql statements in the session, what could be causing the 64 to turn into 786 and be getting reparsed all the time :-(( Any help on this would be greatly appreciated as the transaction dies after awhile and it's in production doh! Thanks Ian OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 667 1.50 22.38 0 0 0 0 Execute 5071 0.30 0.32 0460 31 5514 Fetch 7439 1.17 6.60 1 66144 4 7257 --- -- -- -- -- -- -- total13177 2.97 29.30 1 66604 35 12771 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 118 0.19 3.89 0 0 0 0 Execute533 0.32 4.56 0 0 0 532 Fetch 267 0.00 0.02 0271532 267 --- -- -- -- -- -- -- total 918 0.51 8.47 0271532 799 Misses in library cache during parse: 0 784 user SQL statements in session. 2 internal SQL statements in session. 786 SQL statements in session. 64 statements EXPLAINed in this session. Trace file: ora00503.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 784 user SQL statements in trace file. 2 internal SQL statements in trace file. 786 SQL statements in trace file. 68 unique SQL statements in trace file. 64 SQL statements EXPLAINed using schema: CSISDBA.prof$plan_table Default table was used. Table was created. Table was dropped. 19969 lines in trace file. Content-Type: text/html; charset=us-ascii; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ
RE: where is tkprof?
Jonathan, Some intelligent person at MD forgot to add the tkprof (Trace Kernel PROFile) with the NT versions. OWS has a patch for that. Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Gennick Sent: Wednesday, March 06, 2002 3:54 PM To: Multiple recipients of list ORACLE-L I'm running 9.0.1.1.1 on Win NT, and I don't have a tkprof executable. Am I losing my mind? Did Oracle do away with tkprof on NT? I realize the answer to both those questions may be yesgrin, but I'm most interested in why I don't have tkprof. Best regards, Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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).
Help with Parsing and TKPROF output
Hi All, I have an online program that is timing out and when I trace it I get the following at the bottom of my tkprof output. Its easy to see why the transaction is running slow, because of all the parsing. But the program and all called modules are compiled with max_opencursors = 75 to stop the parsing problem, though it doesnt seem to be helping here. There are only 64 unique sql statements that all use host variables, so why does it also say there 786 sql statements in the session, what could be causing the 64 to turn into 786 and be getting reparsed all the time :-(( Any help on this would be greatly appreciated as the transaction dies after awhile and its in production doh! Thanks Ian OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 667 1.50 22.38 0 0 0 0 Execute 5071 0.30 0.32 0 460 31 5514 Fetch 7439 1.17 6.60 1 66144 4 7257 --- -- -- -- -- -- -- total 13177 2.97 29.30 1 66604 35 12771 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 118 0.19 3.89 0 0 0 0 Execute 533 0.32 4.56 0 0 0 532 Fetch 267 0.00 0.02 0 271 532 267 --- -- -- -- -- -- -- total 918 0.51 8.47 0 271 532 799 Misses in library cache during parse: 0 784 user SQL statements in session. 2 internal SQL statements in session. 786 SQL statements in session. 64 statements EXPLAINed in this session. Trace file: ora00503.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 784 user SQL statements in trace file. 2 internal SQL statements in trace file. 786 SQL statements in trace file. 68 unique SQL statements in trace file. 64 SQL statements EXPLAINed using schema: CSISDBA.prof$plan_table Default table was used. Table was created. Table was dropped. 19969 lines in trace file.
RE: Help with Parsing and TKPROF output
Ian, I'll start with some easy questions: What version of Oracle and what OS? Can you show the SQL being run or some of it? What does v$session_event and v$session_wait show? Can you reproduce it when you run the SQL called by the program from SQLPlus? I had a problem ion 815 / NT with really really long SQL statements (produced by Forms) that would parse forever and never complete - is your statement (or any of them) really long? Regards, Bruce Reardon -Original Message- Sent: Wednesday, 6 March 2002 14:53 Hi All, I have an online program that is timing out and when I trace it I get the following at the bottom of my tkprof output. It's easy to see why the transaction is running slow, because of all the parsing. But the program and all called modules are compiled with max_opencursors = 75 to stop the parsing problem, though it doesn't seem to be helping here. There are only 64 unique sql statements that all use host variables, so why does it also say there 786 sql statements in the session, what could be causing the 64 to turn into 786 and be getting reparsed all the time :-(( Any help on this would be greatly appreciated as the transaction dies after awhile and it's in production doh! Thanks Ian OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse 667 1.50 22.38 0 0 0 0 Execute 5071 0.30 0.32 0460 315514 Fetch 7439 1.17 6.60 1 66144 47257 --- -- -- -- -- -- -- total13177 2.97 29.30 1 66604 35 12771 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse 118 0.19 3.89 0 0 0 0 Execute533 0.32 4.56 0 0 0 532 Fetch 267 0.00 0.02 0271532 267 --- -- -- -- -- -- -- total 918 0.51 8.47 0271532 799 Misses in library cache during parse: 0 784 user SQL statements in session. 2 internal SQL statements in session. 786 SQL statements in session. 64 statements EXPLAINed in this session. Trace file: ora00503.trc Trace file compatibility: 7.03.02 Sort options: default 1 session in tracefile. 784 user SQL statements in trace file. 2 internal SQL statements in trace file. 786 SQL statements in trace file. 68 unique SQL statements in trace file. 64 SQL statements EXPLAINed using schema: CSISDBA.prof$plan_table Default table was used. Table was created. Table was dropped. 19969 lines in trace file. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Tkprof output
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).
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).
