SQL Trace
Title: Message Qs What is the Cause in particular (or in General) of Time Difference between "cpu" & "elapsed" Columns in the following Query ? Qs Is there Any Scope for improvement in the following Query ? Qs Is there any Best practise of working with Such Tables ? NOTE - 1) (tran_date , tran_id , part_tran_srl_num) fields form the unique index on the Table 2) Some Other Columns of the Table are also indexed 3) The Table is a Very Huge History Table to which only INSERT & SELECT Operations happen 4) The Table is the Largest of ALL Tables in the Database With a Size of about 100 GB select del_flg, tran_type, tran_sub_type, part_tran_type, gl_sub_head_code, acid, TO_CHAR(value_date,'DD-MM- HH24:MI:SS'), tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id, pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM- HH24:MI:SS'), TO_CHAR(pstd_date,'DD-MM- HH24:MI:SS'), TO_CHAR(vfd_date,'DD-MM- HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date, 'DD-MM- HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks, pstd_flg, prnt_advc_ind, amt_reservation_ind, reservation_amt||'!'||tran_crncy_code, restrict_modify_ind, lchg_user_id, TO_CHAR(lchg_time,'DD-MM- HH24:MI:SS'), rcre_user_id, TO_CHAR(rcre_time,'DD-MM- HH24:MI:SS'), cust_id, voucher_print_flg, module_id, br_code, fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code, navigation_flg, tran_crncy_code, ref_crncy_code, ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num, TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid FROM TBA_CUM_TRAN_DETAIL_TBL WHERE tran_date = TO_DATE( :1 ,'DD-MM- HH24:MI:SS') AND tran_id = :2 AND part_tran_srl_num = :3 call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.02 0.02 1 0 1 0Execute 2 2.62 2.43 0 0 0 0Fetch 2 7.10 8.79 7705 11 0 2--- -- -- -- -- -- --total 40001 9.74 11.24 7706 11 1 2 Misses in library cache during parse: 1Optimizer goal: RULEParsing user id: 20 (TBAGEN) Rows Row Source Operation--- --- 2 TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE 4 INDEX UNIQUE SCAN (object id 10353) Rows Execution Plan--- --- 0 SELECT STATEMENT GOAL: RULE 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUM_TRAN_DETAIL_TABLE' 4 INDEX (UNIQUE SCAN) OF 'IDX_CUM_TRAN_DETAIL_TABLE' (UNIQUE)
SQL TRACE
i have take trace file with SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE). and trace file size is the 5MB but outpu of tkproff 31KB . is it normal.? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arslan Bahar 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: SQL Trace
On Tuesday 19 November 2002 22:03, you wrote: > Qs What is the Cause in particular (or in General) of Time Difference > between "cpu" & "elapsed" Columns in the following Query ? > e = c + wait time (of anykind) (+ rounding errors) > Qs Is there Any Scope for improvement in the following Query ? > There probably is. > Qs Is there any Best practise of working with Such Tables ? > > NOTE - > 1) (tran_date , tran_id , part_tran_srl_num) fields form the unique > index on the Table > 2) Some Other Columns of the Table are also indexed > 3) The Table is a Very Huge History Table to which only INSERT & SELECT > Operations happen > 4) The Table is the Largest of ALL Tables in the Database With a Size of > about 100 GB > > > > > > select del_flg, tran_type, tran_sub_type, part_tran_type, > gl_sub_head_code, > acid, TO_CHAR(value_date,'DD-MM- HH24:MI:SS'), > tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id, > pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM- > HH24:MI:SS'), > TO_CHAR(pstd_date,'DD-MM- HH24:MI:SS'), > TO_CHAR(vfd_date,'DD-MM- > HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date, > 'DD-MM- HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks, > pstd_flg, >prnt_advc_ind, amt_reservation_ind, > reservation_amt||'!'||tran_crncy_code, > restrict_modify_ind, lchg_user_id, TO_CHAR(lchg_time,'DD-MM- > HH24:MI:SS'), rcre_user_id, TO_CHAR(rcre_time,'DD-MM- > HH24:MI:SS'), > cust_id, voucher_print_flg, module_id, br_code, > fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code, > navigation_flg, tran_crncy_code, ref_crncy_code, > ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num, > TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid > FROM > TBA_CUM_TRAN_DETAIL_TBL WHERE tran_date = TO_DATE( :1 ,'DD-MM- > HH24:MI:SS') AND tran_id = :2 AND part_tran_srl_num = :3 > > > call count cpuelapsed disk querycurrent > rows > --- -- -- -- -- -- > -- > Parse1 0.02 0.02 1 0 1 > 0 > Execute 2 2.62 2.43 0 0 0 > 0 > Fetch2 7.10 8.79 7705 11 0 > 2 > --- -- -- -- -- -- > -- > total40001 9.74 11.24 7706 11 1 > 2 > > Misses in library cache during parse: 1 > Optimizer goal: RULE > Parsing user id: 20 (TBAGEN) > > Rows Row Source Operation > --- --- > 2 TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE > 4 INDEX UNIQUE SCAN (object id 10353) > > > Rows Execution Plan > --- --- > 0 SELECT STATEMENT GOAL: RULE > 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUM_TRAN_DETAIL_TABLE' > 4INDEX (UNIQUE SCAN) OF 'IDX_CUM_TRAN_DETAIL_TABLE' (UNIQUE) -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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: SQL TRACE
Hi Arslan, it's a common situation. In general, the size of tkprof's output depends on number of identical sql statements. If my memory services me right tkprof groups identical sikvels by default. Regards, Ed > -Original Message- > From: Arslan Bahar [mailto:[EMAIL PROTECTED]] > Sent: 31 ìàÿ 2001 ã. 13:06 > To: Multiple recipients of list ORACLE-L > Subject: SQL TRACE > > > > i have take trace file with > SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE). > and trace file size is the 5MB but outpu of tkproff > 31KB . is it > normal.? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Arslan Bahar >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: Shevtsov, Eduard 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: SQL TRACE
Merhaba, If aggeragate=false, which is not default, SQL statement length does not make sense since each SQL are same lenght in raw file and output. in addition to Ed, check your output. If there ara a lot of kernel calls such as parse,fetch, etc. row file will be larger than output. regards... > Hi Arslan, > > it's a common situation. In general, the size of tkprof's output depends > on number of identical sql statements. If my memory services me right > tkprof groups identical sikvels by default. > > Regards, > Ed > > > -Original Message- > > From: Arslan Bahar [mailto:[EMAIL PROTECTED]] > > Sent: 31 ìàÿ 2001 ã. 13:06 > > To: Multiple recipients of list ORACLE-L > > Subject: SQL TRACE > > > > > > > > i have take trace file with > > SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE). > > and trace file size is the 5MB but outpu of tkproff > > 31KB . is it > > normal.? > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Arslan Bahar > >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: Shevtsov, Eduard > 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: Danisment Gazi Unal 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).
sql trace tuning articles
did a google search and couldnt find anything worth reading. other than the ones on hotsos any other good ones? namely ones on traces other than 10053 and 10046? Ive seen a few others mentioned but no details.
sql trace - recursive relationships
Reading Cary's "Optimizing Oracle Performance", page 91 it says: "A database call with dep=n+1 is the recursive child of the first SUBSEQUENT (empasis mine) dep=n database call listed in the SQL trace data stream" Does this apply to the SQL issued from PL/SQL? I am looking at the simple packaged stored proc: PACKAGE BODY nav_tree_pkg is PROCEDURE GET_NAV_PARENT_NODE_ID ( p_NodeId IN NUMBER, p_ParentNodeId OUT NUMBER) IS BEGIN SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId FROM NAV_NODE WHERE NAV_NODE_ID = p_NodeId; EXCEPTION WHEN NO_DATA_FOUND THEN p_ParentNodeId := -1 ; END; -- Procedure END; ... and here's what I see in the trace (sorry the lines are probably wrapped): = PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' BEGIN nav_tree_pkg.get_nav_parent_node_id( :p_nodeid, :p_parentnodeid ); END; END OF STMT PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=48 offset=0 bfp=83fbc005ff80 bln=22 avl=01 flg=05 value=0 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0 offset=24 bfp=83fbc005ff98 bln=22 avl=00 flg=01 = PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 BINDS #2: bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=4001 size=24 offset=0 bfp=83fbc005f660 bln=22 avl=01 flg=05 value=0 EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0 = ... Totaly different calls = So here it looks like the child CURSOR #2 with dep=1 is emitted AFTER the parent (CURSOR #1, dep=0) Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
sql trace - forward attribution
I don't have the book with me right now, but I am obviously missing something in the "forward attribution" concept as it doesn't seem to help me in explanation of the following lines: WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 WAIT #31: nam='SQL*Net message from client' ela= 2295 p1=1413697536 p2=1 p3=0 Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be less than 261? Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace tuning articles
Did you check out www.hotsos.com? Cary Millsap and Jeff Holt have a few (as well as a great book Optimizing Oracle Performance) Thanks/Richard --- Ryan <[EMAIL PROTECTED]> wrote: > did a google search and couldnt find anything worth reading. other than the > ones on hotsos any other good ones? namely ones on traces other than 10053 and > 10046? > > Ive seen a few others mentioned but no details. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Stroupe 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: sql trace tuning articles
To my knowledge , you don't need go to google , try on metalink TOP TECH DOCS --> DATABASE - SQL TUNING Madhu Reddy X13944 -Original Message-From: Ryan [mailto:[EMAIL PROTECTED]Sent: Sunday, September 21, 2003 9:30 PMTo: Multiple recipients of list ORACLE-LSubject: sql trace tuning articles did a google search and couldnt find anything worth reading. other than the ones on hotsos any other good ones? namely ones on traces other than 10053 and 10046? Ive seen a few others mentioned but no details.
Re: sql trace - recursive relationships
Boris, Cary is correct. It gets a little confusing, especially with pl/sql involved. It also helps to remember to differentiate between database calls (parse/execute/fetch) and other events (wait/stat). Using your example below, I'll attempt an explanation inline. Daniel Fink Boris Dali wrote: > Reading Cary's "Optimizing Oracle Performance", page > 91 it says: > "A database call with dep=n+1 is the recursive child > of the first SUBSEQUENT (empasis mine) dep=n database > call listed in the SQL trace data stream" > > Does this apply to the SQL issued from PL/SQL? > > I am looking at the simple packaged stored proc: > > PACKAGE BODY nav_tree_pkg is > PROCEDURE GET_NAV_PARENT_NODE_ID > ( p_NodeId IN NUMBER, >p_ParentNodeId OUT NUMBER) > IS > BEGIN > SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId > FROM NAV_NODE > WHERE NAV_NODE_ID = p_NodeId; > EXCEPTION > WHEN NO_DATA_FOUND THEN > p_ParentNodeId := -1 ; > END; -- Procedure > END; > > ... and here's what I see in the trace (sorry the > lines are probably wrapped): > > = > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 > tim=1617285502494 hv=1138148843 ad='605d0998' > BEGIN nav_tree_pkg.get_nav_parent_node_id( >:p_nodeid, >:p_parentnodeid > ); > END; > > END OF STMT > PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > > BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 > oacflg=01 oacfl2=0 size=48 offset=0 >bfp=83fbc005ff80 bln=22 avl=01 flg=05 >value=0 > bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 > oacflg=01 oacfl2=0 size=0 offset=24 >bfp=83fbc005ff98 bln=22 avl=00 flg=01 > = Here, the pl/sql block is parsed. The next step in the statement process is to execute the statement (parse/execute/fetch). However, notice that EXEC #1 is not the next database call. > > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 > tim=1617285503241 hv=1778717541 ad='606795e8' > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE > WHERE NAV_NODE_ID = :b1 > END OF STMT > PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > BINDS #2: > bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 > oacflg=03 oacfl2=4001 size=24 offset=0 >bfp=83fbc005f660 bln=22 avl=01 flg=05 >value=0 Okay, here we parse the sql statement inside the block. In the next couple of db calls, we do the execute/fetch of Cursor #2. > > EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > FETCH > #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > WAIT #1: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 Here is the execute database call for the block. It is the execution of the block that motivates the parse/execute/fetch of the inner statement. > > WAIT #1: nam='SQL*Net message from client' ela= 2470 > p1=1413697536 p2=1 p3=0 > = > ... Totaly different calls > = > > So here it looks like the child CURSOR #2 with dep=1 > is emitted AFTER the parent (CURSOR #1, dep=0) > > Thanks, > Boris Dali. > > __ > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Boris Dali > 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: sql trace - recursive relationships
Thanks for your reply, Daniel. Yes, it makes sense for the EXEC calls, but it doesn't explain the PARSEs, does it? PARSING IN CURSOR #1 len=94 dep=0... PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0... PARSING IN CURSOR #2 len=68 dep=1... PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1... Here I presume elapsed time of the PL/SQL call is 141-60=81 microsec, but it doesn't follow the same rule of parent db call following the recursive/child one - it is out of sequence. It is even more interesting with FETCHes. I can't seem to find FETCH call for the parent anywhere in the trace file. Maybe it makes sense to omit this call altogether as time tallied against PL/SQL proc call goes to EXEC anyway, but than again it breaks the rule. And no, I don't think it is a trace activation/data collection error as these parent-child cursors appear in the middle of the trace. Thanks again, Boris Dali. --- Daniel Fink <[EMAIL PROTECTED]> wrote: > Boris, > > Cary is correct. It gets a little confusing, > especially with pl/sql > involved. It also helps to remember to differentiate > between database > calls (parse/execute/fetch) and other events > (wait/stat). > > Using your example below, I'll attempt an > explanation inline. > > Daniel Fink > > Boris Dali wrote: > > > Reading Cary's "Optimizing Oracle Performance", > page > > 91 it says: > > "A database call with dep=n+1 is the recursive > child > > of the first SUBSEQUENT (empasis mine) dep=n > database > > call listed in the SQL trace data stream" > > > > Does this apply to the SQL issued from PL/SQL? > > > > I am looking at the simple packaged stored proc: > > > > PACKAGE BODY nav_tree_pkg is > > PROCEDURE GET_NAV_PARENT_NODE_ID > > ( p_NodeId IN NUMBER, > >p_ParentNodeId OUT NUMBER) > > IS > > BEGIN > > SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId > > FROM NAV_NODE > > WHERE NAV_NODE_ID = p_NodeId; > > EXCEPTION > > WHEN NO_DATA_FOUND THEN > > p_ParentNodeId := -1 ; > > END; -- Procedure > > END; > > > > ... and here's what I see in the trace (sorry the > > lines are probably wrapped): > > > > = > > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 > lid=83 > > tim=1617285502494 hv=1138148843 ad='605d0998' > > BEGIN nav_tree_pkg.get_nav_parent_node_id( > >:p_nodeid, > >:p_parentnodeid > > ); > > END; > > > > END OF STMT > > PARSE > #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > > > > BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 > pre=00 > > oacflg=01 oacfl2=0 size=48 offset=0 > >bfp=83fbc005ff80 bln=22 avl=01 flg=05 > >value=0 > > bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 > > oacflg=01 oacfl2=0 size=0 offset=24 > >bfp=83fbc005ff98 bln=22 avl=00 flg=01 > > = > > Here, the pl/sql block is parsed. The next step in > the statement process > is to execute the statement (parse/execute/fetch). > However, notice that > EXEC #1 is not the next database call. > > > > > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 > lid=98 > > tim=1617285503241 hv=1778717541 ad='606795e8' > > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE > > WHERE NAV_NODE_ID = :b1 > > END OF STMT > > PARSE > #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > > BINDS #2: > > bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 > > oacflg=03 oacfl2=4001 size=24 offset=0 > >bfp=83fbc005f660 bln=22 avl=01 flg=05 > >value=0 > > Okay, here we parse the sql statement inside the > block. In the next > couple of db calls, we do the execute/fetch of > Cursor #2. > > > > > EXEC > #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > > FETCH > > > #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > > WAIT #1: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > EXEC > #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 > > Here is the execute database call for the block. It > is the execution of > the block that motivates the parse/execute/fetch of > the inner statement. > > > > > WAIT #1: nam='SQL*Net message from client' ela= > 2470 > > p1=1413697536 p2=1 p3=0 > > = > > ... Totaly different calls > > = > > > > So here it looks like th
RE: sql trace - recursive relationships
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- Sent: Monday, November 24, 2003 8:10 PM To: Multiple recipients of list ORACLE-L Reading Cary's "Optimizing Oracle Performance", page 91 it says: "A database call with dep=n+1 is the recursive child of the first SUBSEQUENT (empasis mine) dep=n database call listed in the SQL trace data stream" Does this apply to the SQL issued from PL/SQL? I am looking at the simple packaged stored proc: PACKAGE BODY nav_tree_pkg is PROCEDURE GET_NAV_PARENT_NODE_ID ( p_NodeId IN NUMBER, p_ParentNodeId OUT NUMBER) IS BEGIN SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId FROM NAV_NODE WHERE NAV_NODE_ID = p_NodeId; EXCEPTION WHEN NO_DATA_FOUND THEN p_ParentNodeId := -1 ; END; -- Procedure END; ... and here's what I see in the trace (sorry the lines are probably wrapped): = PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' BEGIN nav_tree_pkg.get_nav_parent_node_id( :p_nodeid, :p_parentnodeid ); END; END OF STMT PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0 = ... Totaly different calls = So here it looks like the child CURSOR #2 with dep=1 is emitted AFTER the parent (CURSOR #1, dep=0) Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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). ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: sql trace - recursive relationships
Sorry about the last empty email ... Cary is right, the EXEC at dep=0 is the database call you should be looking for, why? because until #1 is parsed, db has no way of finding what needs to do. And once it finds that "Oh I must run a SQL", the dep increases. So, I'd look for a subsequent EXEC instead of PARSE line. I'll take a stab at this ... lines with --> are mine = PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' --> Anonymous block BEGIN nav_tree_pkg.get_nav_parent_node_id( :p_nodeid, :p_parentnodeid ); END; END OF STMT --> anon block gets parsed, it probably contains a sql. PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 --> Found the sql, so oracle opened another cursor #1 which is dependent on cursor #1 so dep = 1 PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' --> sql test SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT --> Successful parsing of cursor #2 PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 --> Executing cursor #2 EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 --> Fetch cursor #2 FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 --> Data returned to anon block WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 --> Now the anon block executes. the e time includes the time for all actions of cursor #2 EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0 Now, I'll just wait for Cary to come along and tell me that I got it all wrong ... Happy Thanksgiving (or Turky Day) 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 ! ** 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. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: sql trace - recursive relationships
But the previous email was a shining example of brevity in action! ;) I'll have to wait for Cary, et.al. as well as my understanding is the exact same as yours. Daniel "Jamadagni, Rajendra" wrote: > Sorry about the last empty email ... > > Cary is right, the EXEC at dep=0 is the database call you should be looking for, > why? because until #1 is parsed, db has no way of finding what needs to do. And once > it finds that "Oh I must run a SQL", the dep increases. So, I'd look for a > subsequent EXEC instead of PARSE line. > > I'll take a stab at this ... lines with --> are mine > > = > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 > hv=1138148843 ad='605d0998' > --> Anonymous block > BEGIN nav_tree_pkg.get_nav_parent_node_id( >:p_nodeid, :p_parentnodeid ); > END; > END OF STMT > --> anon block gets parsed, it probably contains a sql. > PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > --> Found the sql, so oracle opened another cursor #1 which is dependent on cursor > #1 so dep = 1 > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 > hv=1778717541 ad='606795e8' > --> sql test > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 > END OF STMT > --> Successful parsing of cursor #2 > PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > --> Executing cursor #2 > EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > --> Fetch cursor #2 > FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > --> Data returned to anon block > WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 > --> Now the anon block executes. the e time includes the time for all actions of > cursor #2 > EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 > WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0 > > Now, I'll just wait for Cary to come along and tell me that I got it all wrong ... > > Happy Thanksgiving (or Turky Day) > 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 ! > > ** > 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. > **4 > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > 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: sql trace - recursive relationships
Thanks, Raj. So yes, as I said in my other email - the rule stated in the book seem to apply to EXEC db calls only (in case of SQL fired from PL/SQL). I guess I misinterpreted it the way that it applies to ALL db calls for recursive cursors. Thanks, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Sorry about the last empty email ... > > Cary is right, the EXEC at dep=0 is the database > call you should be looking for, why? because until > #1 is parsed, db has no way of finding what needs to > do. And once it finds that "Oh I must run a SQL", > the dep increases. So, I'd look for a subsequent > EXEC instead of PARSE line. > > I'll take a stab at this ... lines with --> are > mine > > = > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 > lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' > --> Anonymous block > BEGIN nav_tree_pkg.get_nav_parent_node_id( >:p_nodeid, :p_parentnodeid ); > END; > END OF STMT > --> anon block gets parsed, it probably contains a > sql. > PARSE > #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > --> Found the sql, so oracle opened another cursor > #1 which is dependent on cursor #1 so dep = 1 > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 > lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' > --> sql test > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE > NAV_NODE_ID = :b1 > END OF STMT > --> Successful parsing of cursor #2 > PARSE > #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > --> Executing cursor #2 > EXEC > #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > --> Fetch cursor #2 > FETCH > #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > --> Data returned to anon block > WAIT #1: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > --> Now the anon block executes. the e time includes > the time for all actions of cursor #2 > EXEC > #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 > WAIT #1: nam='SQL*Net message from client' ela= 2470 > p1=1413697536 p2=1 p3=0 > > > Now, I'll just wait for Cary to come along and tell > me that I got it all wrong ... > > Happy Thanksgiving (or Turky Day) > Raj __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - recursive relationships
Boris, thanks for sending me your data. The following note pertains only to the excerpt you sent me; I didn't look at the whole trace file. Here's the excerpt you sent: = PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1614119418158 hv=1138148843 ad='605d0998' BEGIN nav_tree_pkg.get_nav_parent_node_id( :p_nodeid, :p_parentnodeid ); END; END OF STMT PARSE #1:c=0,e=1177,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1614119418146 BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=8001 size=48 offset=0 bfp=83fbc005f2c0 bln=22 avl=01 flg=05 value=0 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=8001 size=0 offset=24 bfp=83fbc005f2d8 bln=22 avl=00 flg=01 = PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1614119426242 hv=1778717541 ad='606795e8' SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT PARSE #2:c=0,e=676,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1614119426225 BINDS #2: bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=24 offset=0 bfp=83fbc005f6f8 bln=22 avl=01 flg=05 value=0 EXEC #2:c=0,e=1345,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1614119427797 WAIT #2: nam='db file sequential read' ela= 2899 p1=14 p2=119562 p3=1 WAIT #2: nam='db file sequential read' ela= 4290 p1=11 p2=28810 p3=1 FETCH #2:c=0,e=7381,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1614119435232 WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0 EXEC #1:c=1,e=17026,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1614119435461 WAIT #1: nam='SQL*Net message from client' ela= 15771 p1=1413697536 p2=1 p3=0 = Here's the stuff from the excerpt that is required to do this exercise. The first step that most people mess up is the failure to ignore the PARSING IN CURSOR sections during the mechanical step of determining the recursive SQL relationships: PARSE #1:c=0,e=1177,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1614119418146 PARSE #2:c=0,e=676,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1614119426225 EXEC #2:c=0,e=1345,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1614119427797 FETCH #2:c=0,e=7381,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1614119435232 EXEC #1:c=1,e=17026,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1614119435461 I think your tree is as follows (it's late, and my eyes are beginning to blur). I've used indentation to represent the parent-child relationships (the number of tabs on a line equals the call's dep value), and the number represents the sequence in which the line was encountered within the trace file. 1. PARSE #1:e=1177 5. EXEC #1:3=17026 2. PARSE #2:e=676 3. EXEC #2:e=1345 4. FETCH #2:e=7381 When you use this method, it helps to leave a blank line for each level by which the dep value of a call exceeds the prior line's dep value. This leaves enough space into which you can later backpatch when you find the recursive parent. In this case, the tree looked like this right before I parsed line 5 of the abbreviated trace data: 1. PARSE #1:e=1177 2. PARSE #2:e=676 3. EXEC #2:e=1345 4. FETCH #2:e=7381 Then I plugged in the "5. EXEC #1" when I encountered the dep=0 dbcall on line 5 that was the parent of all the outstanding dep=1 children. This is a really convenient notation, by the way. I wish I had thought of it in time for the book. It beats the heck out of trying to find a super-wide sheet of paper and then drawing boxes all over it. I will incorporate this into our PD101 course notes, though, so thanks for the inspiration. At this point, to derive meaning from the relationships we've charted, we need now to pay attention to the PARSING IN CURSOR information. Here, the PARSE, EXEC, and FETCH calls (P/E/F) upon cursor #2 are all recursive children of the EXEC call upon cursor #1. Therefore, the P/E/F operations upon the SELECT...FROM NAV_NODE statement are children of the EXEC of the PL/SQL block. I suspect that Raj and Dan are exactly right, but I don't have the energy tonight to cross-check their notes with what I've said here. Happy Thanksgiving, everyone. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - 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- Boris Dali Sent: Wednesday, November 26, 2003 8:30 AM To: Multiple recipients of list ORACLE-L Thanks, Raj. So yes, as I said in my other email - the rule stated in the book seem to apply to EXEC db calls only (in case of SQL fired from PL/SQL). I guess I misinterpreted it the way that it applies to ALL db calls for recursive cursors. Thanks, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Sorry about the last empty email ... > > Cary is right, the EXEC at dep=0 is t
RE: sql trace - recursive relationships
Thanks a lot, Cary. Indeed the indented notation seems rather convenient. I would be delighted to take your PD101 course, just not sure if people here dealing with the training budget would share the delight with me :-( Doesn't hurt to ask though... Thanks again, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
> >WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 >WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1 p3=0 >WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 >FETCH #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 >WAIT #31: nam='SQL*Net message from client' ela= 2295 p1=1413697536 p2=1 p3=0 > Boris, "SQL*Net message..." events are "between-call" events. Their times are not included in the following dbcall's elapsed time. But it *is* appropriate to "blame" the dbcall that follows for the time consumed by the event. That is, if you can eliminate the dbcall that follows, then you can eliminate the between-call event (and its elapsed time). The "assignment of blame" is what "forward attribution" is about. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Monday, December 29, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I don't have the book with me right now, but I am obviously missing something in the "forward attribution" concept as it doesn't seem to help me in explanation of the following lines: WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 WAIT #31: nam='SQL*Net message from client' ela= 2295 p1=1413697536 p2=1 p3=0 Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be less than 261? Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Thanks a lot for your reply, Cary. One follow-up question. What would motivate "a chat" of sometimes 5, sometimes 10-20 'SQL*Net message to/from client' consecutive wait lines emitted to the trace file in the following manner: WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3463 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3322 p1=1413697536 p2=1 p3=0 I see this pattern of "message exchanges" before calling a stored code from the app server (OCI), so using forward attribution it is a call to a stored code that it to blame correct? I can't of course eliminate a call to a stored code but is there something that can be done to minimize amount of these 'SQL*Net message...' lines? While the latency of these waits is low, these 3-5 milliseconds get accumulated slowly, but surely. Also does cursor #0 has some special meaning in traces? I can't seem to create a test-case where I get cursor #0 emitted for me and yet tracing real applications I see it all over (like in the excerpt above) I guess I have more than one follow-up question :-( Thanks, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > >WAIT #31: nam='SQL*Net message from client' ela= > 692 p1=1413697536 p2=1 > p3=0 > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 > p3=0 >FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > >WAIT #31: nam='SQL*Net message from client' ela= > 2295 p1=1413697536 > p2=1 p3=0 > > > > Boris, "SQL*Net message..." events are > "between-call" events. Their > times are not included in the following dbcall's > elapsed time. But it > *is* appropriate to "blame" the dbcall that follows > for the time > consumed by the event. That is, if you can eliminate > the dbcall that > follows, then you can eliminate the between-call > event (and its elapsed > time). The "assignment of blame" is what "forward > attribution" is about. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Monday, December 29, 2003 9:39 AM > To: Multiple recipients of list ORACLE-L > > I don't have the book with me right now, but I am > obviously missing something in the "forward > attribution" concept as it doesn't seem to help me > in > explanation of the following lines: > > > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message from client' ela= 692 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > WAIT #31: nam='SQL*Net message from client' ela= > 2295 > p1=1413697536 p2=1 p3=0 > > > Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be > less than 261? > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > Thanks, > Boris Dali. > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > 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: sql trace - forward attribution
Boris, Cursor #0 seems reserved for two special uses: (1) wait events associated with COMMIT processing (also, of course, ROLLBACK and SAVEPOINT), and (2) wait events associated with dbcalls not instrumented because of bug 2425312. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Thursday, January 01, 2004 10:29 AM To: Multiple recipients of list ORACLE-L Thanks a lot for your reply, Cary. One follow-up question. What would motivate "a chat" of sometimes 5, sometimes 10-20 'SQL*Net message to/from client' consecutive wait lines emitted to the trace file in the following manner: WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3463 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3322 p1=1413697536 p2=1 p3=0 I see this pattern of "message exchanges" before calling a stored code from the app server (OCI), so using forward attribution it is a call to a stored code that it to blame correct? I can't of course eliminate a call to a stored code but is there something that can be done to minimize amount of these 'SQL*Net message...' lines? While the latency of these waits is low, these 3-5 milliseconds get accumulated slowly, but surely. Also does cursor #0 has some special meaning in traces? I can't seem to create a test-case where I get cursor #0 emitted for me and yet tracing real applications I see it all over (like in the excerpt above) I guess I have more than one follow-up question :-( Thanks, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > >WAIT #31: nam='SQL*Net message from client' ela= > 692 p1=1413697536 p2=1 > p3=0 > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 > p3=0 >FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > >WAIT #31: nam='SQL*Net message from client' ela= > 2295 p1=1413697536 > p2=1 p3=0 > > > > Boris, "SQL*Net message..." events are > "between-call" events. Their > times are not included in the following dbcall's > elapsed time. But it > *is* appropriate to "blame" the dbcall that follows > for the time > consumed by the event. That is, if you can eliminate > the dbcall that > follows, then you can eliminate the between-call > event (and its elapsed > time). The "assignment of blame" is what "forward > attribution" is about. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Monday, December 29, 2003 9:39 AM > To: Multiple recipients of list ORACLE-L > > I don't have the book with me right now, but I am > obviously missing something in the "forward > attribution" concept as it doesn't seem to help me > in > explanation of the following lines: > > > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message from client' ela= 692 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > WAIT #31: nam='SQL*Net message from client' ela= > 2295 > p1=1413697536 p2=1 p3=0 > > > Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be > less than 261? > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > Thanks, > Boris Dali. > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > 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:
RE: sql trace - forward attribution
Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > Thanks a lot for your reply, Cary. > > One follow-up question. What would motivate "a chat" > of sometimes 5, sometimes 10-20 'SQL*Net message > to/from client' consecutive wait lines emitted to > the > trace file in the following manner: > > WAIT #0: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 678 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3463 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3322 > p1=1413697536 p2=1 p3=0 > > > I see this pattern of "message exchanges" before > calling a stored code from the app server (OCI), so > using forward attribution it is a call to a stored > code that it to blame correct? > I can't of course eliminate a call to a stored code > but is there something that can be done to minimize > amount of these 'SQL*Net message...' lines? While > the > latency of these waits is low, these 3-5 > milliseconds > get accumulated slowly, but surely. > > Also does cursor #0 has some special meaning in > traces? I can't seem to create a test-case where I > get > cursor #0 emitted for me and yet tracing real > applications I see it all over (like in the excerpt > above) > > > I guess I have more than one follow-up question :-( > > Thanks, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 692 p1=1413697536 p2=1 > > p3=0 > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 > > p3=0 >FETCH > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 2295 p1=1413697536 > > p2=1 p3=0 > > > > > > > Boris, "SQL*Net message..." events are > > "between-call" events. Their > > times are not included in the following dbcall's > > elapsed time. But it > > *is* appropriate to "blame" the dbcall that > follows > > for the time > > consumed by the event. That is, if you can > eliminate > > the dbcall that > > follows, then you can eliminate the between-call > > event (and its elapsed > > time). The "assignment of blame" is what "forward > > attribution" is about. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Monday, December 29, 2003 9:39 AM > > To: Multiple recipients of list ORACLE-L > > > > I don't have the book with me right now, but I am > > obviously missing something in the "forward > > attribution" concept as it doesn't seem to help me > > in > > explanation of the following lines: > > > > > > WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #31: nam='SQL*Net message from client' ela= > 692 > > p1=1413697536 p2=1 p3=0 > > WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > FETCH
RE: sql trace - forward attribution
In-line... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Boris Dali Sent: Monday, January 05, 2004 8:59 AM To: Multiple recipients of list ORACLE-L Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? [Cary Millsap] The event most often associated with COMMIT processing that is attributed to cursor #0 is 'log file sync'. In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. [Cary Millsap] Oracle Forms and one of Oracle's report writers (I forget the name) trigger this bug). And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? [Cary Millsap] I'm very curious, too. I don't know the answer. Can you produce a minimal test case that reproduces the behavior? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > Thanks a lot for your reply, Cary. > > One follow-up question. What would motivate "a chat" > of sometimes 5, sometimes 10-20 'SQL*Net message > to/from client' consecutive wait lines emitted to > the > trace file in the following manner: > > WAIT #0: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 678 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3463 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3322 > p1=1413697536 p2=1 p3=0 > > > I see this pattern of "message exchanges" before > calling a stored code from the app server (OCI), so > using forward attribution it is a call to a stored > code that it to blame correct? > I can't of course eliminate a call to a stored code > but is there something that can be done to minimize > amount of these 'SQL*Net message...' lines? While > the > latency of these waits is low, these 3-5 > milliseconds > get accumulated slowly, but surely. > > Also does cursor #0 has some special meaning in > traces? I can't seem to create a test-case where I > get > cursor #0 emitted for me and yet tracing real > applications I see it all over (like in the excerpt > above) > > > I guess I have more than one follow-up question :-( > > Thanks, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 692 p1=1413697536 p2=1 > > p3=0 > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 > > p3=0 >FETCH > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 2295 p1=1413697536 > > p2=1 p3=0 > > > > > > > Boris, "SQL*Net message..." events are > > "between-call" events. Their > > times are not included in the following dbcall's > > elapsed time. But it > > *is* appropriate to "blame" the dbcall that > follows > > for the time > > consumed by the event. That is, if you can > eliminate > > the dbcall that > > follows, then you can eliminate the between-call > > event (and its elapsed > > time). The "assignment of blame" is what "forward > > attribution" is about. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2
RE: sql trace - forward attribution
Cursor 0 also happens in oracle due to session switching (multiple sessions in the same process), oracle apps uses that but it also could happen with certain other application servers (haven't investigated it). Anjo. -Original Message- Boris Dali Sent: Monday, January 05, 2004 3:59 PM To: Multiple recipients of list ORACLE-L Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > Thanks a lot for your reply, Cary. > > One follow-up question. What would motivate "a chat" > of sometimes 5, sometimes 10-20 'SQL*Net message > to/from client' consecutive wait lines emitted to > the > trace file in the following manner: > > WAIT #0: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 > p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3463 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3322 > p1=1413697536 p2=1 p3=0 > > > I see this pattern of "message exchanges" before > calling a stored code from the app server (OCI), so > using forward attribution it is a call to a stored > code that it to blame correct? > I can't of course eliminate a call to a stored code > but is there something that can be done to minimize > amount of these 'SQL*Net message...' lines? While > the > latency of these waits is low, these 3-5 > milliseconds > get accumulated slowly, but surely. > > Also does cursor #0 has some special meaning in > traces? I can't seem to create a test-case where I > get > cursor #0 emitted for me and yet tracing real > applications I see it all over (like in the excerpt > above) > > > I guess I have more than one follow-up question :-( > > Thanks, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 692 p1=1413697536 p2=1 > > p3=0 > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 > > p3=0 >FETCH > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 2295 p1=1413697536 > > p2=1 p3=0 > > > > > > > Boris, "SQL*Net message..." events are > > "between-call" events. Their > > times are not included in the following dbcall's > > elapsed time. But it > > *is* appropriate to "blame" the dbcall that > follows > > for the time > > consumed by the event. That is, if you can > eliminate > > the dbcall that > > follows, then you can eliminate the between-call > > event (and its elapsed > > time). The "assignment of blame" is what "forward attribution" is > > about. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Monday, December 29, 2003 9:39 AM > > To: Multiple recipients of list ORACLE-L > > > > I don't have the book with me right now, but I am > > obviously missing something in the "forward > > attribution" concept as it doe
Re: sql trace - forward attribution
Oracle Portal uses session switching as well (and Apps 11i uses Portal...) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 05, 2004 7:49 PM > > > Cursor 0 also happens in oracle due to session switching (multiple > sessions in the same process), oracle apps uses that but it also could > happen with certain other application servers (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events > associated with COMMIT processing"? Why does Oracle > need this "exchange of messages" with the client > (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored code > > but is there something that can be done to minimize > > amount of these 'SQL*Net message...' lines? While > > the > > latency of these waits is low, these 3-5 > > milliseconds > > get accumulated slowly, but surely. > > > > Also does cursor #0 has some special meaning in > > traces? I can't seem to create a test-case where I > > get > > cursor #0 emitted for me and yet tracing real > > applications I see it all over (like in the excerpt > > above) > > > > > > I guess I have more than one follow-up question :-( > > > > Thanks, > > Boris Dali. > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > > p1=1413697536 p2=1 p3=0 > > > >WAIT #31: nam='SQL*Net message from client' ela= > > > 692 p1=1413697536 p2=1 > > > p3=0 > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > > p1=1413697536 p2=1 > > > p3=0 >FETCH > > > > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > > >WAIT #31: nam='SQL*Net message from client' ela= > > > 2295 p1=1413697536 > > > p2=1 p3=0 > > > > > > > > > > Boris, "SQL*Net message..." events are > > > "between-call" events. Their > > > times are not included in the following dbcall's > > > elapsed time. But it > > > *is* appropriate to "blame" the dbcall that > > follows > > > for the time > > > consumed by the event. That is, if you can > > eliminate > > > the dbcall that > > > follows, then you can eliminate the between-call > > > event (and its elapsed > > > time). The "assignment of blame" is what "forward attribution" is > > > about. > > > > > > > > > Cary Millsap > > > Hotsos Enterpri
RE: sql trace - forward attribution
Thanks, Anjo. When session switching occurs does the new session get the same sid and serial#? And what happens with the session being "switched/replaced" - does the transaction it was performing get commited/rollbacked? I don't see XCTEND markers before those pesky WAIT #0 in the trace file. Also if session gets switched, wouldn't this terminate sql trace for the session (in my case it doesn't)? Thanks, Boris Dali. --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events > associated with COMMIT processing"? Why does Oracle > need this "exchange of messages" with the client > (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks > as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of > course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a > chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= > 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), > so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored > code > > but is there something that can be done to > minimize > > amount of these 'SQL*Net message...' lines? While > > the > > latency of these waits is low, these 3-5 > > milliseconds > > get accumulated slowly, but surely. > > > > Also does cursor #0 has some special meaning in > > traces? I can't seem to create a test-case where I > > get > > cursor #0 emitted for me and yet tracing real > > applications I see it all over (like in the > excerpt > > above) > > > > > > I guess I have more than one follow-up question > :-( > > > > Thanks, > > Boris Dali
RE: sql trace - forward attribution
No, Each session will have its own sid and serail#, but they all run in the same process. Basically the client side tells oracle, that it wants to switch from session to session and oracle will keep the state of the switched out session. So you don't have to commit or rollback on every switch that you perform. SQL trace is inherited by the process it you set in a session, so other sessions that run in the same process will produce also trace output. Anjo. -Original Message- Boris Dali Sent: Monday, January 05, 2004 7:34 PM To: Multiple recipients of list ORACLE-L Thanks, Anjo. When session switching occurs does the new session get the same sid and serial#? And what happens with the session being "switched/replaced" - does the transaction it was performing get commited/rollbacked? I don't see XCTEND markers before those pesky WAIT #0 in the trace file. Also if session gets switched, wouldn't this terminate sql trace for the session (in my case it doesn't)? Thanks, Boris Dali. --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events associated with > COMMIT processing"? Why does Oracle need this "exchange of messages" > with the client (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks > as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of > course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a > chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 > > p3=0 WAIT #0: nam='SQL*Net message from client' ela= > 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= > 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), > so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored > code > > but is there something that can be done to > minimize > > amount of these 'SQL*Net messa
RE: sql trace - forward attribution
I actually build a testcase for this and it still failed on 9.2 without any patches. It is supposed to be fixed in some later patch. I don't have the patches -Original Message- Anjo Kolk Sent: Monday, January 05, 2004 6:49 PM To: Multiple recipients of list ORACLE-L Cursor 0 also happens in oracle due to session switching (multiple sessions in the same process), oracle apps uses that but it also could happen with certain other application servers (haven't investigated it). Anjo. -Original Message- Boris Dali Sent: Monday, January 05, 2004 3:59 PM To: Multiple recipients of list ORACLE-L Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > Thanks a lot for your reply, Cary. > > One follow-up question. What would motivate "a chat" > of sometimes 5, sometimes 10-20 'SQL*Net message > to/from client' consecutive wait lines emitted to > the > trace file in the following manner: > > WAIT #0: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 > p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 > p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3463 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3322 > p1=1413697536 p2=1 p3=0 > > > I see this pattern of "message exchanges" before > calling a stored code from the app server (OCI), so > using forward attribution it is a call to a stored > code that it to blame correct? > I can't of course eliminate a call to a stored code > but is there something that can be done to minimize > amount of these 'SQL*Net message...' lines? While > the > latency of these waits is low, these 3-5 > milliseconds > get accumulated slowly, but surely. > > Also does cursor #0 has some special meaning in > traces? I can't seem to create a test-case where I > get > cursor #0 emitted for me and yet tracing real > applications I see it all over (like in the excerpt > above) > > > I guess I have more than one follow-up question :-( > > Thanks, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 692 p1=1413697536 p2=1 > > p3=0 > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 > > p3=0 >FETCH > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 2295 p1=1413697536 > > p2=1 p3=0 > > > > > > > Boris, "SQL*Net message..." events are > > "between-call" events. Their > > times are not included in the following dbcall's > > elapsed time. But it > > *is* appropriate to "blame" the dbcall that > follows > > for the time > > consumed by the event. That is, if you can > eliminate > > the dbcall that > > follows, then you can eliminate the between-call > > event (and its elapsed > > time). The "assignment of blame" is what "forward attribution" is > > about. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > >
RE: sql trace - forward attribution
Anjo, I suppose your test-case involved more than just use of sqlplus. Probably some middle tier with connection/session pooling of some sort? --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > I actually build a testcase for this and it still > failed on 9.2 without > any patches. It is supposed to be fixed in some > later patch. I don't > have the patches > > -Original Message- > Anjo Kolk > Sent: Monday, January 05, 2004 6:49 PM > To: Multiple recipients of list ORACLE-L > > > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events > associated with COMMIT processing"? Why does Oracle > need this "exchange of messages" with the client > (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks > as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of > course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a > chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= > 1 p1=1413697536 > > p2=1 p3=0 WAIT #0: nam='SQL*Net message from > client' ela= 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), > so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored > code > > but is there something that can be done to > minimize > > amount of these 'SQL*Net message...' lines? While > > the > > latency of these waits is low, these 3-5 > > milliseconds > > get accumulated slowly, but surely. > > > > Also does cursor #0 has some special meaning in > > traces? I can't seem to create a test-case where I > > get > > cursor #0 emitted for me and yet tracing real > > applications I see it all over (like in the > excerpt > > above) > > > > > > I guess I have more than one follow-up question > :-( > > > > Thanks, > > Boris Dali. > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > > > > >WAIT #31: nam='SQL*Net message to client' ela= > 1 > > > p1=1413697536 p2=1 p3=0 > > > >WAIT #31: nam='SQL*Net message from client' > ela= > > > 692 p1=1413697536 p2=1 > > > p3=0 > > > >WAIT #31: nam='SQL*Net message to client' ela= > 1 > > > p1=1413697536 p2=1 > > > p3=0 >FETCH > > > > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > > >WAIT #31: nam='SQL*Net message from client' > ela= > > > 2295 p1=1413697536 > > > p2=1 p3=0 > > > > > > > > > > Boris, "SQL*Net message..." events are > > > "between-call" events. Their > > > times are not included in the following dbcall's > > > elapsed time. But it > > > *is* appropriate to "blame" the dbcall that > > follows > > > for the time >
RE: sql trace - forward attribution
Right, but the new session (that inherits the sql trace attribute) - wouldn't it produce a **separate** trace file? In my case there's only one trace file with sid.serial# clearly stated at the begining of the trace file and WAIT #0 scattered all over the trace. ..Or am I missing something? --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > No, > > Each session will have its own sid and serail#, but > they all run in the > same process. Basically the client side tells > oracle, that it wants to > switch from session to session and oracle will keep > the state of the > switched out session. So you don't have to commit or > rollback on every > switch that you perform. SQL trace is inherited by > the process it you > set in a session, so other sessions that run in the > same process will > produce also trace output. > > Anjo. > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Anjo. > > When session switching occurs does the new session > get > the same sid and serial#? And what happens with the > session being "switched/replaced" - does the > transaction it was performing get > commited/rollbacked? > I don't see XCTEND markers before those pesky WAIT > #0 > in the trace file. > Also if session gets switched, wouldn't this > terminate > sql trace for the session (in my case it doesn't)? > > Thanks, > Boris Dali. > > --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > > > Cursor 0 also happens in oracle due to session > > switching (multiple > > sessions in the same process), oracle apps uses > that > > but it also could > > happen with certain other application servers > > (haven't investigated it). > > > > Anjo. > > > > > > -Original Message- > > Boris Dali > > Sent: Monday, January 05, 2004 3:59 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks, Cary. > > > > Could you elaborate what do you mean by "wait > events associated with > > COMMIT processing"? Why does Oracle need this > "exchange of messages" > > with the client (well, with the app server really > in my case of a > > 3-tier deployment) to perform a commit? > > > > > > In any event, as I described earlier in my case I > > think Cursor #0 doesn't fall in neither of the two > > uses you mentioned. > > > > Bug 2425312 is RPC related as I understand. I > don't > > work distributed (single DB) and app server (and > > clients - thin) don't have their own SQL engine, > so > > all SQL processing is happening strictly on the DB > > server. So this doesn't seem to apply to me. > > > > And I see Cursor #0 used with no commits/rollbacks > > as > > part of one Oracle transaction. > > > > > > I see these WAIT #0 flying back and forth between > DB > > and the app server sometimes 20 times just before > > stored procs are called and I can't figure out > why. > > Another bug? > > > > Thank you, > > Boris Dali. > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > Boris, > > > > > > Cursor #0 seems reserved for two special uses: > (1) > > > wait events > > > associated with COMMIT processing (also, of > > course, > > > ROLLBACK and > > > SAVEPOINT), and (2) wait events associated with > > > dbcalls not instrumented > > > because of bug 2425312. > > > > > > > > > Cary Millsap > > > Hotsos Enterprises, Ltd. > > > http://www.hotsos.com > > > > > > Upcoming events: > > > - Performance Diagnosis 101: 1/27 Atlanta > > > - SQL Optimization 101: 2/16 Dallas > > > - Hotsos Symposium 2004: March 7-10 Dallas > > > - Visit www.hotsos.com for schedule details... > > > > > > > > > -Original Message- > > > Boris Dali > > > Sent: Thursday, January 01, 2004 10:29 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks a lot for your reply, Cary. > > > > > > One follow-up question. What would motivate "a > > chat" > > > of sometimes 5, sometimes 10-20 'SQL*Net message > > > to/from client' consecutive wait lines emitted > to > > > the > > > trace file in the following manner: > > > > > > WAIT #0: nam=
Re: sql trace - forward attribution
Trace file has server process number in it's name, not session number, thus as long as the sessions are served by the same server process, the contents will be written into one single file. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 05, 2004 10:49 PM > Right, but the new session (that inherits the sql > trace attribute) - wouldn't it produce a **separate** > trace file? In my case there's only one trace file > with sid.serial# clearly stated at the begining of the > trace file and WAIT #0 scattered all over the t [EMAIL PROTECTED],Eachsessionw illhaveitsownsidandserail#,buttheyallruninthesameprocess.Basicallytheclients idetellsoracle,thatitwantstoswitchfromsessiontosession and oracle will keep > > the state of the > > switched out session. So you don't have to commit or > > rollback on every > > switch that you perform. SQL trace is inherited by > > the process it you > > set in a session, so other sessions that run in the > > same process will > > produce also trace output. > > > > Anjo. > > > > -Original Message- > > Boris Dali > > Sent: Monday, January 05, 2004 7:34 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks, Anjo. > > > > When session switching occurs does the new session > > get > > the same sid and serial#? And what happens with the > > session being "switched/replaced" - does the > > transaction it was performing get > > commited/rollbacked? > > I don't see XCTEND markers before those pesky WAIT > > #0 > > in the trace file. > > Also if session gets switched, wouldn't this > > terminate > > sql trace for the session (in my case it doesn't)? > > > > Thanks, > > Boris Dali. > > > > --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > > > > > Cursor 0 also happens in oracle due to session > > > switching (multiple > > > sessions in the same process), oracle apps uses > > that > > > but it also could > > > happen with certain other application servers > > > (haven't investigated it). > > > > > > Anjo. > > > > > > > > > -Original Message- > > > Boris Dali > > > Sent: Monday, January 05, 2004 3:59 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Thanks, Cary. > > > > > > Could you elaborate what do you mean by "wait > > events associated with > > > COMMIT processing"? Why does Oracle need this > > "exchange of messages" > > > with the client (well, with the app server really > > in my case of a > > > 3-tier deployment) to perform a commit? > > > > > > > > > In any event, as I described earlier in my case I > > > think Cursor #0 doesn't fall in neither of the two > > > uses you mentioned. > > > > > > Bug 2425312 is RPC related as I understand. I > > don't > > > work distributed (single DB) and app server (and > > > clients - thin) don't have their own SQL engine, > > so > > > all SQL processing is happening strictly on the DB > > > server. So this doesn't seem to apply to me. > > > > > > And I see Cursor #0 used with no commits/rollbacks > > > as > > > part of one Oracle transaction. > > > > > > > > > I see these WAIT #0 flying back and forth between > > DB > > > and the app server sometimes 20 times just before > > > stored procs are called and I can't figure out > > why. > > > Another bug? > > > > > > Thank you, > > > Boris Dali. > > > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > > Boris, > > > > > > > > Cursor #0 seems reserved for two special uses: > > (1) > > > > wait events > > > > associated with COMMIT processing (also, of > > > course, > > > > ROLLBACK and > > > > SAVEPOINT), and (2) wait events associated with > > > > dbcalls not instrumented > > > > because of bug 2425312. > > > > > > > > > > > > Cary Millsap > > > > Hotsos Enterprises, Ltd. > > > > http://www.hotsos.com > > > > > > > > Upcoming events: > > > > - Performance Diagnosis 101: 1/27 Atlanta > > > > - SQL Optimization 10
Re: sql trace - forward attribution
Tanel, What I see in the trace file header is something like the following: ... *** SESSION ID:(22.9304) 2003-12-29 15:04:45.743 ... Which is sid.serial# isn't it? If "session switching" occurs, handled by the same shadow process and the new session with a different sid.serial# continues to write to the **same** trace file... wouldn't you expect to see line similar to the above, but with a new sid.serial# in it? --- Tanel Poder <[EMAIL PROTECTED]> wrote: > Trace file has server process number in it's name, > not session number, thus > as long as the sessions are served by the same > server process, the contents > will be written into one single file. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Monday, January 05, 2004 10:49 PM > > > > Right, but the new session (that inherits the sql > > trace attribute) - wouldn't it produce a > **separate** > > trace file? In my case there's only one trace file > > with sid.serial# clearly stated at the begining of > the > > trace file and WAIT #0 scattered all over the t > race === message truncated === __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
They write all to the same trace file. So there should be different sid.serial# combinations. -Original Message- Boris Dali Sent: Monday, January 05, 2004 9:49 PM To: Multiple recipients of list ORACLE-L Right, but the new session (that inherits the sql trace attribute) - wouldn't it produce a **separate** trace file? In my case there's only one trace file with sid.serial# clearly stated at the begining of the trace file and WAIT #0 scattered all over the trace. ..Or am I missing something? --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > No, > > Each session will have its own sid and serail#, but > they all run in the > same process. Basically the client side tells > oracle, that it wants to > switch from session to session and oracle will keep > the state of the > switched out session. So you don't have to commit or > rollback on every > switch that you perform. SQL trace is inherited by > the process it you > set in a session, so other sessions that run in the > same process will > produce also trace output. > > Anjo. > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Anjo. > > When session switching occurs does the new session > get > the same sid and serial#? And what happens with the > session being "switched/replaced" - does the > transaction it was performing get > commited/rollbacked? > I don't see XCTEND markers before those pesky WAIT > #0 > in the trace file. > Also if session gets switched, wouldn't this > terminate > sql trace for the session (in my case it doesn't)? > > Thanks, > Boris Dali. > > --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > > > Cursor 0 also happens in oracle due to session > > switching (multiple > > sessions in the same process), oracle apps uses > that > > but it also could > > happen with certain other application servers > > (haven't investigated it). > > > > Anjo. > > > > > > -Original Message- > > Boris Dali > > Sent: Monday, January 05, 2004 3:59 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks, Cary. > > > > Could you elaborate what do you mean by "wait > events associated with > > COMMIT processing"? Why does Oracle need this > "exchange of messages" > > with the client (well, with the app server really > in my case of a > > 3-tier deployment) to perform a commit? > > > > > > In any event, as I described earlier in my case I > > think Cursor #0 doesn't fall in neither of the two > > uses you mentioned. > > > > Bug 2425312 is RPC related as I understand. I > don't > > work distributed (single DB) and app server (and > > clients - thin) don't have their own SQL engine, > so > > all SQL processing is happening strictly on the DB > > server. So this doesn't seem to apply to me. > > > > And I see Cursor #0 used with no commits/rollbacks > > as > > part of one Oracle transaction. > > > > > > I see these WAIT #0 flying back and forth between > DB > > and the app server sometimes 20 times just before > > stored procs are called and I can't figure out > why. > > Another bug? > > > > Thank you, > > Boris Dali. > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > Boris, > > > > > > Cursor #0 seems reserved for two special uses: > (1) > > > wait events > > > associated with COMMIT processing (also, of > > course, > > > ROLLBACK and > > > SAVEPOINT), and (2) wait events associated with > > > dbcalls not instrumented > > > because of bug 2425312. > > > > > > > > > Cary Millsap > > > Hotsos Enterprises, Ltd. > > > http://www.hotsos.com > > > > > > Upcoming events: > > > - Performance Diagnosis 101: 1/27 Atlanta > > > - SQL Optimization 101: 2/16 Dallas > > > - Hotsos Symposium 2004: March 7-10 Dallas > > > - Visit www.hotsos.com for schedule details... > > > > > > > > > -Original Message- > > > Boris Dali > > > Sent: Thursday, January 01, 2004 10:29 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks a lot for your reply, Cary. > > > > > > One follow-up question. What would motivate "a > > chat" > > > of sometimes 5, sometimes 10-20 'SQL
RE: sql trace - forward attribution
Thanks to Anjo, Cary, Tanel, and everybody who
provided feedback back channel.
Just to rule out the possibility of a collection error
(somebody suggested that cursor #0 is simply not
captured) I bounced the DB today, enabled a DB-wide
trace ... and as expected
grep -i "cursor #0" *
returned nothing, while "wait #0" gives plenty. So it
is not a trace activation/termination error.
---
I think what we deal with here is a "variant" of what
Anjo described, but not exactly that as I don't see
*** SESSION ID:(sid.serial#) lines in the middle of
any trace file, only in the header, but I think it
still might be "session switching" of a kind.
What we use here is an n-tier proxy authentication and
I suspect these waits is the price we pay for it. Not
sure, but maybe if proxy attributes are "switched" sql
trace doesn't capture this properly, "forgeting" to
emit new session info? I would be interested to know
how to
1) confirm or refute this
2) since waits #0 appear only before the calls to a
stored code - I don't know if they deliberatly "switch
sessions" in the code that runs on the app server and
run the stored code as the schema owner (similar to
switching current schema as an alternative to using
synonyms) or it is a feature of Oracle's proxy
authentication implementation
3) how to check "proxy identity" of the user - i.e.
how to run something like sys_context('userenv',
'proxy_user') for sessions other than my own.
Thanks,
Boris Dali.
--- Anjo Kolk <[EMAIL PROTECTED]> wrote: > They write
all to the same trace file. So there
> should be different
> sid.serial# combinations.
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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: sql trace - forward attribution
you may not be seeing parse etc entries for cursor #0 merely because maybe by design, cursor#0 gets invoked before trace gets activated. This way, you will never get cursor #0 info. You can tell, I am guessing but to get similar experience, start trace in an already active session and you'll see. 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- Sent: Tuesday, January 06, 2004 12:00 PM To: Multiple recipients of list ORACLE-L Thanks to Anjo, Cary, Tanel, and everybody who provided feedback back channel. Just to rule out the possibility of a collection error (somebody suggested that cursor #0 is simply not captured) I bounced the DB today, enabled a DB-wide trace ... and as expected grep -i "cursor #0" * returned nothing, while "wait #0" gives plenty. So it is not a trace activation/termination error. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: sql trace - forward attribution
> 2) since waits #0 appear only before the calls to a
> stored code - I don't know if they deliberatly "switch
> sessions" in the code that runs on the app server and
> run the stored code as the schema owner (similar to
> switching current schema as an alternative to using
> synonyms) or it is a feature of Oracle's proxy
> authentication implementation
If this overhead happens only with stored code executions, could there be
some dependency tracking like with forms & dblinks (this
remote_dependencies_mode parameter etc..).
This proxy authentication is quite new and probably quite low level
functionality, it wouldn't be a surprise if Oracle had some special shortcut
there (internal cursor #0 which isn't ever parsed or similar?)
> 3) how to check "proxy identity" of the user - i.e.
> how to run something like sys_context('userenv',
> 'proxy_user') for sessions other than my own.
Check V$SESSION_CONNECT_INFO view.
CLIENT_IDENTIFIER in V$SESSION might show something as well, if mid-tier is
configured to pass client id to server.
Tanel.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tanel Poder
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: sql trace - forward attribution
Thanks, Tanel. I did checked v$session_connect info, but it doesn't tell me much, except authentication_type='PROXY' which is less than useful as it doesn't tell to whom. v$session.client_identifier is empty. Thanks, Boris Dali. > Check V$SESSION_CONNECT_INFO view. > CLIENT_IDENTIFIER in V$SESSION might show something > as well, if mid-tier is > configured to pass client id to server. > > Tanel. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Thanks, Raj, but I don't think so. The whole reason behind enabling a db-wide trace was to capture everything sql trace is instrumented to capture. As you know sql_trace is a static parameter, so I got everything "from the begining", including: PARSING IN CURSOR #1 ALTER DATABASE MOUNT .. PARSING IN CURSOR #1 ALTER DATABASE OPEN PARSING IN CURSOR #2 create table bootstrap$ ( line# PARSING IN CURSOR #2 CREATE ROLLBACK SEGMENT SYSTEM .. but not the cursor #0 (Interestingly "alter database mount" went to one trace file, while "alter database open" and the rest to another - so to mount a DB Oracle spawns a different process on your behalf and than passes control back to your foreground to open it?) --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > you may not be seeing parse etc entries for cursor > #0 merely because maybe by design, cursor#0 gets > invoked before trace gets activated. This way, you > will never get cursor #0 info. > > You can tell, I am guessing but to get similar > experience, start trace in an already active session > and you'll see. > 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- > Sent: Tuesday, January 06, 2004 12:00 PM > To: Multiple recipients of list ORACLE-L > > > Thanks to Anjo, Cary, Tanel, and everybody who > provided feedback back channel. > > Just to rule out the possibility of a collection > error > (somebody suggested that cursor #0 is simply not > captured) I bounced the DB today, enabled a DB-wide > trace ... and as expected > > grep -i "cursor #0" * > > returned nothing, while "wait #0" gives plenty. So > it > is not a trace activation/termination error. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
I had the same discussion with Jeff Holt (one of Cary's partners in crime) and
he described #0 attributions as actions not associated with a cursor (i.e.
statement). For example, I worked on a web server which would maintain a
persistent connection. Every 90 minutes, it would execute a series of statements
to load up the web cache. At the end of the series, it closed all the cursors
and issued a "rollback" . As all cursors were closed, the wait time
until the next 'awakening' was attributed to cursor #0.
The non-association of #0 also explains why you should not see #0 parses,
executes, fetches or stats.
Daniel Fink
Boris Dali wrote:
> Thanks to Anjo, Cary, Tanel, and everybody who
> provided feedback back channel.
>
> Just to rule out the possibility of a collection error
> (somebody suggested that cursor #0 is simply not
> captured) I bounced the DB today, enabled a DB-wide
> trace ... and as expected
>
> grep -i "cursor #0" *
>
> returned nothing, while "wait #0" gives plenty. So it
> is not a trace activation/termination error.
>
> ---
>
> I think what we deal with here is a "variant" of what
> Anjo described, but not exactly that as I don't see
>
> *** SESSION ID:(sid.serial#) lines in the middle of
> any trace file, only in the header, but I think it
> still might be "session switching" of a kind.
>
> What we use here is an n-tier proxy authentication and
> I suspect these waits is the price we pay for it. Not
> sure, but maybe if proxy attributes are "switched" sql
> trace doesn't capture this properly, "forgeting" to
> emit new session info? I would be interested to know
> how to
>
> 1) confirm or refute this
> 2) since waits #0 appear only before the calls to a
> stored code - I don't know if they deliberatly "switch
> sessions" in the code that runs on the app server and
> run the stored code as the schema owner (similar to
> switching current schema as an alternative to using
> synonyms) or it is a feature of Oracle's proxy
> authentication implementation
> 3) how to check "proxy identity" of the user - i.e.
> how to run something like sys_context('userenv',
> 'proxy_user') for sessions other than my own.
>
> Thanks,
> Boris Dali.
>
> --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > They write
> all to the same trace file. So there
> > should be different
> > sid.serial# combinations.
>
> __
> Post your free ad now! http://personals.yahoo.ca
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Boris Dali
> 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 W. 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: sql trace - forward attribution
Btw, reading through a 10046/12 trace from instance startup & database opening can reveal really lots of interesting information :) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 8:24 PM > Thanks, Raj, but I don't think so. The whole reason > behind enabling a db-wide trace was to capture > everything sql trace is instrumented to capture. As > you know sql_trace is a static parameter, so I got > everything "from the begining", including: > > PARSING IN CURSOR #1 > ALTER DATABASE MOUNT > .. > PARSING IN CURSOR #1 > ALTER DATABASE OPEN > > PARSING IN CURSOR #2 > create table bootstrap$ ( line# > > PARSING IN CURSOR #2 > CREATE ROLLBACK SEGMENT SYSTEM > > .. but not the cursor #0 > > (Interestingly "alter database mount" went to one > trace file, while "alter database open" and the rest > to another - so to mount a DB Oracle spawns a > different process on your behalf and than passes > control back to your foreground to open it?) > > > --- "Jamadagni, Rajendra" > <[EMAIL PROTECTED]> wrote: > you may not be > seeing parse etc entries for cursor > > #0 merely because maybe by design, cursor#0 gets > > invoked before trace gets activated. This way, you > > will never get cursor #0 info. > > > > You can tell, I am guessing but to get similar > > experience, start trace in an already active session > > and you'll see. > > 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- > > Sent: Tuesday, January 06, 2004 12:00 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks to Anjo, Cary, Tanel, and everybody who > > provided feedback back channel. > > > > Just to rule out the possibility of a collection > > error > > (somebody suggested that cursor #0 is simply not > > captured) I bounced the DB today, enabled a DB-wide > > trace ... and as expected > > > > grep -i "cursor #0" * > > > > returned nothing, while "wait #0" gives plenty. So > > it > > is not a trace activation/termination error. > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Jamadagni, Rajendra > > 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). > > __ > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Boris Dali > 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: Tanel Poder 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).
set sql*trace VB/Crystal
List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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:set sql*trace VB/Crystal
Barb, More than likely VB is spawning Crystal in a separate database session, therefore the alter session command will not work. You could have her start the report & then use top sessions to extract the sql and explain plan from the DB. Or you could extract the sql from the crystal report & go from there. Dick Goulet Reply Separator Author: "Baker; Barbara" <[EMAIL PROTECTED]> Date: 8/12/2002 12:23 PM List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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). -- 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: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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: set sql*trace VB/Crystal
Title: RE: set sql*trace VB/Crystal If you want to see the trace do the following connected to the appropriate database: SQL> show parameters dump; NAME TYPE VALUE --- -- background_core_dump string partial background_dump_dest string /opt/oracle/admin/ods/bdump core_dump_dest string /opt/oracle/admin/ods/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /opt/oracle/admin/ods/udump I believe it is under user_dump_dest - and is constrained by the max_dump_file_size. You will then need to use tkprof commands to format *.trc file. To check it is correct trace file can grep session id or even bit of SQL she used that would be specific to her session. Most of the Oracle references have tkprof examples, so does metalink and cdrom with oracle doc. -Original Message- From: Stankus, Paula G Sent: Monday, August 12, 2002 4:03 PM To: '[EMAIL PROTECTED]' Subject: RE: set sql*trace VB/Crystal I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS. Otherwise, you will have to send her the trace file from the server - you guys will get quickly tired of that. 'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily show you - but I believe that is related to partitioning and parallelism. -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: set sql*trace VB/Crystal List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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: set sql*trace VB/Crystal
You can see the sql generated by the report in Crystal, so take that sql and run it in sqlplus to see the access plan. You can also check in v$sqltext the select run by the report. --- "Baker, Barbara" <[EMAIL PROTECTED]> a écrit : > > List: > We have a crystal report performing badly. (No! ,you > say. You're shocked!) > The report has a visual basic front end. > > Our developer wants to set sql trace in the VB code. > It's not working. > When I tkprof her trace file, all that's in there is > the "ALTER SESSION SET > SQL_TRACE TRUE" command. > > Is there some trick here? I don't know VB at all, > so I don't know how to > advise her. She looked on the Microsoft site, but > it was not helpful. > > Thanks for any help! > > Barb > > -- > 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). > > = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: set sql*trace VB/Crystal
What connection are they using? If they are using Oracle Object Of OLE I think there is a parameter that can be set. >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: set sql*trace VB/Crystal >Date: Mon, 12 Aug 2002 13:08:23 -0800 > >If you want to see the trace do the following connected to the appropriate >database: >SQL> show parameters dump; > >NAME TYPEVALUE > --- -- >background_core_dump string partial >background_dump_dest string /opt/oracle/admin/ods/bdump >core_dump_dest string /opt/oracle/admin/ods/cdump >max_dump_file_size string UNLIMITED >shadow_core_dump string partial >user_dump_dest string /opt/oracle/admin/ods/udump > >I believe it is under user_dump_dest - and is constrained by the >max_dump_file_size. > >You will then need to use tkprof commands to format *.trc file. To check >it >is correct trace file can grep session id or even bit of SQL she used that >would be specific to her session. > >Most of the Oracle references have tkprof examples, so does metalink and >cdrom with oracle doc. > >-Original Message- >Sent: Monday, August 12, 2002 4:03 PM >To: '[EMAIL PROTECTED]' > > >I don't know vb either. However the trace file is likely generated on the >server-side. She would not see the output. She might want to try alter >session set autotrace on; instead. That way she should see the results. >That is how it works in SQL*PLUS. Otherwise, you will have to send her the >trace file from the server - you guys will get quickly tired of that. >'AUTOTRACE ON' does have some limitations/bugs where it doesn't necessarily >show you - but I believe that is related to partitioning and parallelism. > >-Original Message- >Sent: Monday, August 12, 2002 4:23 PM >To: Multiple recipients of list ORACLE-L > > > >List: >We have a crystal report performing badly. (No! ,you say. You're shocked!) >The report has a visual basic front end. > >Our developer wants to set sql trace in the VB code. It's not working. >When I tkprof her trace file, all that's in there is the "ALTER SESSION SET >SQL_TRACE TRUE" command. > >Is there some trick here? I don't know VB at all, so I don't know how to >advise her. She looked on the Microsoft site, but it was not helpful. > >Thanks for any help! > >Barb > >-- >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). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade 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: set sql*trace VB/Crystal
Try to find out the sid and serial# of her session. >From a dba user use "exec dbms_system.set_sql_trace_in_session(sid, serial#,true); Babu "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on 08/12/2002 03:23:23 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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). -- 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: set sql*trace VB/Crystal
Barb, To get all the data you might need for the session, use the 10046 level 8 tracing attribute available through the various means described at www.hotsos.com/dnloads/1/10046a. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago -Original Message- Barbara Sent: Monday, August 12, 2002 3:23 PM To: Multiple recipients of list ORACLE-L List: We have a crystal report performing badly. (No! ,you say. You're shocked!) The report has a visual basic front end. Our developer wants to set sql trace in the VB code. It's not working. When I tkprof her trace file, all that's in there is the "ALTER SESSION SET SQL_TRACE TRUE" command. Is there some trick here? I don't know VB at all, so I don't know how to advise her. She looked on the Microsoft site, but it was not helpful. Thanks for any help! Barb -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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).
SQL Trace ( Perl script help needed)
Hello Gurus, I was trying to pull distinct sql's and their total "execute count" from a bunch of 250 trace output files(output from tkprof trace files). How can I achive this. What i need the cumulative sum of execute count from different files for each sql's. As u all know sql can extend more than one line so ! I know this can be handled using perl script. file1.prf .. select * from tablename1 where col1=:1 and col2=:2 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse5 0.00 0.00 0 0 0 0 Execute 5 0.02 0.02 0600 0 0 Fetch5 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 15 0.02 0.02 0600 0 0 select * from tablename2 where col1=:1 and col2=:2 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse5 0.00 0.00 0 0 0 0 Execute 5 0.02 0.02 0600 0 0 Fetch5 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 15 0.02 0.02 0600 0 0 file2.prf select * from tablename1 where col1=:1 and col2=:2 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse5 0.00 0.00 0 0 0 0 Execute 5 0.02 0.02 0600 0 0 Fetch5 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 15 0.02 0.02 0600 0 0 select * from tablename3 where col1=:1 and col2=:2 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse5 0.00 0.00 0 0 0 0 Execute 5 0.02 0.02 0600 0 0 Fetch5 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 15 0.02 0.02 0600 0 0 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Valiveru, Siva 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).
CPU/Parse Time Reported by SQL Trace
While working on application performance issues, I noticed significant discrepancy in time reported by SQL Trace and actual time taken by the application. Total Elapsed time reported by SQL Trace was 180 seconds, but it took 500 seconds to run it. (It was a PL/SQL procedure. I just measured the time to run the PL/SQL procedure.) Parse Elapsed Time reported by SQL Trace is 90 seconds, but V$SESSTAT reported parse time elapsed of only 15 seconds. Parse CPU Time reported by SQL Trace is 60 seconds, but V$SESSTAT reported parse time CPU of only 14 seconds. Any explanations on why such a big discrepancy on reported time? Thanks in advance, Jay This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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).
sql trace - XCTEND rlbk=1, rd_only=1
I've got a third party package that connects to Oracle via OCI and works in HTTP-like (stateless) fashion. Reviewing raw SQL trace output I don't see a single commit or rollback there, but there are plenty of XCTEND tx markers with rlbk=1 (after about every SELECT statement). Is this normal? Does this mean that this app rollbacks (implicitly?) after each of those selects? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
Ynt: SQL Trace ( Perl script help needed)
hello Siva, You can concatenate raw SQL_TRACE files as a single file, then upload this file to itrprof. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Tuesday, June 19, 2001 2:30 AM > Hello Gurus, > > I was trying to pull distinct sql's and their total "execute count" from a > bunch of 250 trace output files(output from tkprof trace files). How can I > achive this. What i need the cumulative sum of execute count from different > files for each sql's. > > As u all know sql can extend more than one line so ! I know this can be > handled using perl script. > > file1.prf > . > select * from tablename1 > where col1=:1 > and col2=:2 > > > call count cpuelapsed disk querycurrent > rows > --- -- -- -- -- -- > -- > Parse5 0.00 0.00 0 0 0 > 0 > Execute 5 0.02 0.02 0600 0 > 0 > Fetch5 0.00 0.00 0 0 0 > 0 > --- -- -- -- -- -- > -- > total 15 0.02 0.02 0600 0 > 0 > ... > > select * from tablename2 > where col1=:1 > and col2=:2 > > > call count cpuelapsed disk querycurrent > rows > --- -- -- -- -- -- > -- > Parse5 0.00 0.00 0 0 0 > 0 > Execute 5 0.02 0.02 0600 0 > 0 > Fetch5 0.00 0.00 0 0 0 > 0 > --- -- -- -- -- -- > -- > total 15 0.02 0.02 0600 0 > 0 > > > file2.prf > > > select * from tablename1 > where col1=:1 > and col2=:2 > > call count cpuelapsed disk querycurrent > rows > --- -- -- -- -- -- > -- > Parse5 0.00 0.00 0 0 0 > 0 > Execute 5 0.02 0.02 0600 0 > 0 > Fetch5 0.00 0.00 0 0 0 > 0 > --- -- -- -- -- -- > -- > total 15 0.02 0.02 0600 0 > 0 > > > select * from tablename3 > where col1=:1 > and col2=:2 > > > call count cpuelapsed disk querycurrent > rows > --- -- -- -- -- -- > -- > Parse5 0.00 0.00 0 0 0 > 0 > Execute 5 0.02 0.02 0600 0 > 0 > Fetch5 0.00 0.00 0 0 0 > 0 > --- -- -- -- -- -- > -- > total 15 0.02 0.02 0600 0 > 0 > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Valiveru, Siva > 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: 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: sql trace - XCTEND rlbk=1, rd_only=1
Boris It look like the app's is doing rollback :-( Have a look in Note 39817.1 Interpreting Raw SQL_TRACE ... for more info. XCTEND rlbk=(0 or 1) rd_only= (0 or 1) rlbk : 1 = rollback 0 = commit rd_only : 1 = read only transaction 0 = none read only /peter Boris Dali wrote: I've got a third party package that connects to Oracle via OCI and works in HTTP-like (stateless) fashion. Reviewing raw SQL trace output I don't see a single commit or rollback there, but there are plenty of XCTEND tx markers with rlbk=1 (after about every SELECT statement). Is this normal? Does this mean that this app rollbacks (implicitly?) after each of those selects? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Peter Gram comp : Miracle A/S Addr : Kratvej 2, 2760 Maaloev Phone : +45 2527 7107, Fax : +45 4466 8856, Home +45 3874 5696 mail : [EMAIL PROTECTED] - http://www.miracleas.dk Upcoming events: Miracle Master Class with Tom Kyte, 12-14 January 2004 Visit http://miracleas.dk/en/events.html#MasterClass Visit http://www.miracleas.dk fore news ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram 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: sql trace - XCTEND rlbk=1, rd_only=1
OCI defaults to rollback on transactions on disconnect if OCITransRollback() has not been called. Don't ask me for too much detail, as I'm not an OCI programmer, I just pulled this straight from The Fine Manual. Jared On Mon, 2003-11-24 at 16:54, Boris Dali wrote: > I've got a third party package that connects to Oracle > via OCI and works in HTTP-like (stateless) fashion. > Reviewing raw SQL trace output I don't see a single > commit or rollback there, but there are plenty of > XCTEND tx markers with rlbk=1 (after about every > SELECT statement). Is this normal? Does this mean that > this app rollbacks (implicitly?) after each of those > selects? > > Thanks, > Boris Dali. > > __ > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Boris Dali > 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: Jared Still 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: sql trace - XCTEND rlbk=1, rd_only=1
hmmm... I think I meant OCITransCommit() there, not OCITransRollback(). Nobody caught that? :) Jared On Mon, 2003-11-24 at 18:29, Jared Still wrote: > OCI defaults to rollback on transactions on disconnect if > OCITransRollback() has not been called. > > Don't ask me for too much detail, as I'm not an OCI programmer, > I just pulled this straight from The Fine Manual. > > Jared > > On Mon, 2003-11-24 at 16:54, Boris Dali wrote: > > I've got a third party package that connects to Oracle > > via OCI and works in HTTP-like (stateless) fashion. > > Reviewing raw SQL trace output I don't see a single > > commit or rollback there, but there are plenty of > > XCTEND tx markers with rlbk=1 (after about every > > SELECT statement). Is this normal? Does this mean that > > this app rollbacks (implicitly?) after each of those > > selects? > > > > Thanks, > > Boris Dali. > > > > __ > > Post your free ad now! http://personals.yahoo.ca > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Boris Dali > > 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: Jared Still > 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: Jared Still 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: sql trace - XCTEND rlbk=1, rd_only=1
Jared, actually your initial post made sense anyway - since you can't roll back committed transactions anyway. Also, rollback is done on session end if you haven't done the rollback manually ;) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, November 25, 2003 4:54 PM > hmmm... > > I think I meant OCITransCommit() there, not OCITransRollback(). > > Nobody caught that? :) > > Jared > > On Mon, 2003-11-24 at 18:29, Jared Still wrote: > > OCI defaults to rollback on transactions on disconnect if > > OCITransRollback() has not been called. > > > > Don't ask me for too much detail, as I'm not an OCI programmer, > > I just pulled this straight from The Fine Manual. > > > > Jared > > > > On Mon, 2003-11-24 at 16:54, Boris Dali wrote: > > > I've got a third party package that connects to Oracle > > > via OCI and works in HTTP-like (stateless) fashion. > > > Reviewing raw SQL trace output I don't see a single > > > commit or rollback there, but there are plenty of > > > XCTEND tx markers with rlbk=1 (after about every > > > SELECT statement). Is this normal? Does this mean that > > > this app rollbacks (implicitly?) after each of those > > > selects? > > > > > > Thanks, > > > Boris Dali. > > > > > > __ > > > Post your free ad now! http://personals.yahoo.ca > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Boris Dali > > > 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: Jared Still > > 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: Jared Still > 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: Tanel Poder 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: sql trace - XCTEND rlbk=1, rd_only=1
Thanks, Jared, Tanel. I was a little supprised to see a combination rlbk=1,rd_only=1. Why read-only bit is set here if it rolls back anyway? So I thought may be they mark their tx explicitly as read-only (aka "set transaction read-only"). Reveiwing OCI fine manual there seemed to be an option of doing just that with the OCITransStart() call and setting some flags... But than simple test-case of tracing commit and rollback in SQL*Plus disproved the theory about read-only tx, as "normal" commit/rollback produce this combination regardless of the tx type: commit - XCTEND rlbk=0, rd_only=1 rollback - XCTEND rlbk=1, rd_only=1 set transaction read only commit - XCTEND rlbk=0, rd_only=1 rollback - XCTEND rlbk=1, rd_only=1 So much for my theory :-( Another question I had here is whether or not having so many (implicit?) rollbacks on about every SELECT statement all over my trace file bears any overhead and proves deficiency of a stateless architecture used by this application - but again reviewing v$sesstat before and after I see user rollbacks count incremented, but no additional redo vectors generated. I guess I can try Tom Kyte's test harness, but it just doesn't sit well with me that 30 selects are as cheap as 30 selects with 30 rollbacks. Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - XCTEND rlbk=1, rd_only=1
Using a slightly modified version of run_stats to return timings in 1/1 of a second, the timing of 1000 iterations of a loop executing noop vs. 1000 doing rollback: .0005 secs .0354 secs Here are the stats that were different between the two, kind of what you would expect: LATCH.cache buffers chains 11313320 STAT...redo size 27160 27648 488 STAT...user rollbacks 0 1000 1000 LATCH.enqueues0 1001 1001 LATCH.shared pool 3 1004 1001 LATCH.session idle bit0 1001 1001 STAT...execute count 3 1004 1001 LATCH.library cache pin 13 2014 2001 LATCH.session allocation 0 2002 2002 STAT...recursive calls4 2008 2004 LATCH.library cache 14 3018 3004 On Tue, 2003-11-25 at 14:14, Boris Dali wrote: > Thanks, Jared, Tanel. > > I was a little supprised to see a combination > rlbk=1,rd_only=1. Why read-only bit is set here if it > rolls back anyway? > So I thought may be they mark their tx explicitly as > read-only (aka "set transaction read-only"). Reveiwing > OCI fine manual there seemed to be an option of doing > just that with the OCITransStart() call and setting > some flags... > > But than simple test-case of tracing commit and > rollback in SQL*Plus disproved the theory about > read-only tx, as "normal" commit/rollback produce this > combination regardless of the tx type: > > commit - XCTEND rlbk=0, rd_only=1 > rollback - XCTEND rlbk=1, rd_only=1 > > set transaction read only > > commit - XCTEND rlbk=0, rd_only=1 > rollback - XCTEND rlbk=1, rd_only=1 > > So much for my theory :-( > > > Another question I had here is whether or not having > so many (implicit?) rollbacks on about every SELECT > statement all over my trace file bears any overhead > and proves deficiency of a stateless architecture used > by this application - but again reviewing v$sesstat > before and after I see user rollbacks count > incremented, but no additional redo vectors generated. > I guess I can try Tom Kyte's test harness, but it just > doesn't sit well with me that 30 selects are as cheap > as 30 selects with 30 rollbacks. > > Thanks, > Boris Dali. > > __ > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Boris Dali > 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: Jared Still 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: sql trace - XCTEND rlbk=1, rd_only=1
Thanks, Jared. Yes, running select vs select+rollback in a loop of 1000 iterations I got similar results (average over 3 runs): STAT...user rollbacks 0 1,000 1,000 LATCH...enqueues 570 1,574 1,004 LATCH...shared pool7,434 9,063 1,629 STAT...recursive 7,754 10,264 2,510 LATCH...library cache 13,401 17,429 4,028 So, every select w/ a rollback is at least 1 enqueue and 4 latches (1 shared pool + 3 library cache) more expensive than select wo/ a rollback. Since latches are scalability inhibitors - would it be far away from the truth to say that an application that uses OCI session pooling (as opposed to connection pooling I suppose? - just reading chapter 9 of the OCI manual) doesn't scale well with respect to the number of concurrent users? Thanks, Boris Dali. --- Jared Still <[EMAIL PROTECTED]> wrote: > Using a slightly modified version of run_stats to > return > timings in 1/1 of a second, the timing of 1000 > iterations > of a loop executing noop vs. 1000 doing rollback: > > .0005 secs > .0354 secs > > Here are the stats that were different between the > two, > kind of what you would expect: > > LATCH.cache buffers chains 113 > 13320 > STAT...redo size 27160 > 27648 488 > STAT...user rollbacks 0 > 1000 1000 > LATCH.enqueues0 > 1001 1001 > LATCH.shared pool 3 > 1004 1001 > LATCH.session idle bit0 > 1001 1001 > STAT...execute count 3 > 1004 1001 > LATCH.library cache pin 13 > 2014 2001 > LATCH.session allocation 0 > 2002 2002 > STAT...recursive calls4 > 2008 2004 > LATCH.library cache 14 > 3018 3004 __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
phyrds in v$filestat and sql trace not match !!
list, i'm doing benchmarking using two DB's with different block size i run a count(*) on a 17 million row table, and compare the sql_trace file and the v$filestat stats.. the db was bounced before each test, the init.ora params were identical, EXCEPT in DB1 (4k block size) the muldiblock read was 16, and DB2(block size 8k) it was 8 4 samples were taken... CPU time : DB1 = 9023 DB2 = 8027 elapsed time: DB1 = 19171 DB2 = 18045 phy reads: (from sql_trace) DB1 = 327022 DB2 = 159347 PHYRDS from v$filestat DB1 = 16386 DB2 = 16385 PHYBLKRDS from v$filestat DB1 = 262148 DB2 = 131073 my question is... why the physical reads in the v$filestat are equal ?? but the p reads in the sql_trace file are different ?? TIA rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: phyrds in v$filestat and sql trace not match !!
My guess will be that PHYRDS is the count of start i/o's. Each start i/o read mutilblock_read_count blocks from the disk. The data buffer that you read with each start i/o is the same: 8 blocks of 8k or 16 blocks of 4k. So you get the same number of start i/o's. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Tuesday, November 12, 2002 12:43 PM > list, > i'm doing benchmarking using two DB's with different block size > i run a count(*) on a 17 million row table, and compare the sql_trace file > and the v$filestat stats.. > the db was bounced before each test, the init.ora params were identical, > EXCEPT > in DB1 (4k block size) the muldiblock read was 16, and DB2(block size 8k) it > was 8 > > 4 samples were taken... > > CPU time : > DB1 = 9023 > DB2 = 8027 > > elapsed time: > DB1 = 19171 > DB2 = 18045 > > phy reads: (from sql_trace) > DB1 = 327022 > DB2 = 159347 > > PHYRDS from v$filestat > DB1 = 16386 > DB2 = 16385 > > PHYBLKRDS from v$filestat > DB1 = 262148 > DB2 = 131073 > > my question is... why the physical reads in the v$filestat are equal ?? but > the p reads in the sql_trace > file are different ?? > > TIA > rahul > > > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rahul > 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: Yechiel Adar 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).
