RE: Re[2]: what exactly 'tim' means in a 10046 TRACE file ?
- From: "Anjo Kolk" <anjo@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Mar 2004 18:09:09 +0200
I don't think that the error handling consumes a lot of CPU here, what
in this case (more than likely) is happening, is that some array
processing failed. The error number id 24381:
24381, 00000, "error(s) in array DML"
// *Cause: One or more rows failed in the DML.
// *Action: Refer to the error stack in the error handle.
In the new OCI interface you can upload an array of rows and it will
process all the rows and not return on the first row that fails. So you
could have a case where you process all rows and fail on the last one,
that will consume the CPU. In fact your error is not really an error but
an indication that the application need to check a couple of rows of the
array that failed.
I did a copy/paste from the OCI manual (9.2 chapter 4.9):
The OCI provides the ability to perform array DML operations. For
example, an
application can process an array of INSERT, UPDATE, or DELETE statements
with a
single statement execution. If one of the operations fails due to an
error from the
server, such as a unique constraint violation, the array operation
aborts and the OCI
returns an error. Any rows remaining in the array are ignored. The
application must
then reexecute the remainder of the array, and go through the whole
process again
if it encounters more errors, which makes additional round-trips.
To facilitate processing of array DML operations, the OCI provides the
batch error
mode (also called the enhanced DML array feature). This mode, which is
specified in
the OCIStmtExecute() call, simplifies DML array processing in the event
of one
or more errors. In this mode, the OCI attempts to INSERT, UPDATE, or
DELETE all
rows, and collects (batches) information about any errors which
occurred. The
application can then retrieve this error information and reexecute any
DML
operations which failed during the first call.
Anjo.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap
Sent: Wednesday, March 31, 2004 4:19 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Re[2]: what exactly 'tim' means in a 10046 TRACE file ?
I hadn't noticed that ERROR lines had tim values in different units than
dbcalls' tim values. I'm sure that error handling does consume CPU time.
It would be difficult to consider them on par with Oracle timed events
because there's no e or ela statistic associated with them. But if you
can crack the mapping between ERROR tim values and dbcall tim values,
then you might have something there.
I'm thinking back to when I've needed the ERROR information at all, and
in the one case I can recall, it was a "user requested end of txn" kind
of thing, which helped me understand that the user was doing something
the DBA didn't tell me (got tired of waiting and hit Ctrl-C, basically).
But I've not yet cared about how long the error-processing was taking.
Sounds like you're on an interesting trail of discovery. :)
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 4/6 Seattle, 5/7 Dallas, 5/18 New Jersey
- SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Edgar Chupit
Sent: Wednesday, March 31, 2004 4:23 AM
To: Cary Millsap
Subject: Re[2]: what exactly 'tim' means in a 10046 TRACE file ?
Hello Cary,
CM> The fortunate thing is that it's rarely necessary to be able to
convert
CM> a tim into a wall time and vice versa.
But how should we treat ERROR lines in trace files, for example on
9iR2 I have this lines in trace file:
EXEC
#31:c=0,e=3083,p=0,cr=9,cu=17,mis=0,r=1,dep=1,og=4,tim=1055355471165039
ERROR #31:err=24381 tim=694217847
As I understood from your book and metalink first time is in hsecs and
second time is in msecs.
How should I compare this two times? simply convert two tim values to
wall clock and subtract values or somehow differently?
Can I treat ERROR lines as wait events, because as I saw from some
tests ERROR handling consumes some amount of cpu time?
--
Edgar
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
