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: Re[2]: what exactly 'tim' means in a 10046 TRACE file ?

RE: Re[2]: what exactly 'tim' means in a 10046 TRACE file ?

From: Anjo Kolk <anjo_at_oraperf.com>
Date: Wed, 31 Mar 2004 18:09:09 +0200
Message-ID: <019501c4173a$7fcb04d0$0c00a8c0@videoserver>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap Sent: Wednesday, March 31, 2004 4:19 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Mar 31 2004 - 10:09:23 CST

Original text of this message

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