Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with 10046 level 12 trace

RE: Help with 10046 level 12 trace

From: Mandar A. Ghosalkar <mghosalk_at_byer.com>
Date: Thu, 22 Aug 2002 16:48:20 -0800
Message-ID: <F001.004BDC4A.20020822164820@fatcity.com>


Cary,Phillip,Mladen Thanks

This application wakes up every 15000 ms and checks if there is any job to be processed. That explains the avg 0.014475s per call (100.8s/6921 calls of SQLNet message from client).

This delphi app uses some ODAC component(http://crlab.com/odac/) to access database. Using this component we do not have to install sqlnet on the client pc, it uses OCI. Its dataset componenet has KeepPrepared property, which was false, so we have changed it to true.

also we are changing the frequency from 15000 ms to 1 min, which would reduce the number of calls.

Cary mentioned that the longest wait 15.57s was for this update statement. Developer was testing this app, so he was resetting the status, so he could get a fresh set of rows "IN QUEUE". in production this statement wont be there. but why would this update cause 'SQL*Net message from client' to wait for 15.57s?

**************from original raw trace*********************
Update WORKSHEET_JOBS
 Set wsj_status = 'IN QUEUE'
   where wsj_job_id =:JOB
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220031681 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1 p3=0 BINDS #1:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01    bfp=400aa510 bln=22 avl=04 flg=05
   value=291976
EXEC #1:c=0,e=0,p=0,cr=2,cu=1,mis=0,r=1,dep=0,og=3,tim=4220031681
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=30636 op='INDEX UNIQUE SCAN '
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1 p3=0
XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 0 p1=474 p2=0 p3=0
WAIT #0: nam='log file sync' ela= 1 p1=474 p2=0 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
*** 2002.08.22.12.00.10.482
WAIT #0: nam='SQL*Net message from client' ela= 1557 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net more data from client' ela= 11 p1=1952673875 p2=1 p3=0
**********************************************************************


> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Thursday, August 22, 2002 3:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Help with 10046 level 12 trace
>
>
> I'll second that motion. Oddly, though, it's even worse than
> that: This
> code is actually parsing MORE times than it's executing! ("Parse.
> Thanks, but nevermind. Okay, now parse the same thing again. Okay, now
> execute...")
>
> I pulled the trigger pretty quickly a minute ago when I suggested that
> Mandar should upload the file to our site. There's a lot you can learn
> from tkprof output alone. However, amplifying Mladen's point a little
> bit, you're losing a lot of data by viewing raw trace files
> with tkprof.
> For example, are so many parse calls causing latch contention to be a
> significant component of response time? With tkprof output,
> we can only
> guess.
>
> <ad>Even tkprof 9i output ignores significant amounts of useful
> data.</ad>
>
> We'll be glad to look at the raw trace file. Just let me know if you'd
> like us to.
>
>
> 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: Miracle Database Forum, Sep 20-22 Middelfart Denmark
>
>
>
> -----Original Message-----
> Douglass
> Sent: Thursday, August 22, 2002 4:20 PM
> To: Multiple recipients of list ORACLE-L
>
> Well, I'm sure a guru here will correct me if I am wrong, but at first
> blush it looks like your developer is not only parsing, executing and
> fetching in a loop, but is also parsing a few extra times
> just for fun.
> I'm pretty sure you will find that the bulk of your
> performance problem
> lies there.
>
> == the bad way ==
> loop
> parse
> execute
> fetch
> close
> end loop
>
> == the good way ==
> parse
> execute
> loop
> fetch
> end loop
> close
> --
> Philip Douglass
> Internet Networking Group
> Database Administrator
> SIRS Mandarin, Inc.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, August 22, 2002 4:41 PM
>
>
> Hello All,
>
> One our lead developers has just written a Delphi application
> to process
> some printing jobs. The application would every other minute
> query a job
> table and generate a report to be printed to specific network
> printers.
>
> I ran a 10046 level 12 trace using dbms_system.set_ev.
>
> I would be very glad if experts can help me understand how to
> interpret
> the trace and tkprof output. Also pls let me know if ull find anything
> which points to or would lead to performance problems.
>
> I am thinking of setting session_cached_cursors to a non zero value.
>
> thanks
> Mandar

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mandar A. Ghosalkar
  INET: mghosalk_at_byer.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 22 2002 - 19:48:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US