From oracle-l-bounce@freelists.org Fri Sep 9 14:40:12 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89JeCWP032126 for ; Fri, 9 Sep 2005 14:40:12 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j89Je8IP032112 for ; Fri, 9 Sep 2005 14:40:08 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 64BA01EB38E; Fri, 9 Sep 2005 14:40:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32507-06; Fri, 9 Sep 2005 14:40:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D54281EB376; Fri, 9 Sep 2005 14:40:00 -0500 (EST) X-IronPort-AV: i="3.96,183,1122872400"; d="scan'208,217"; a="309972875:sNHT61658914" X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5B576.00F372D7" Subject: RE: What do you make of this? Date: Fri, 9 Sep 2005 14:38:07 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: What do you make of this? Thread-Index: AcW1Xt+T2y4VjtD2TBS1v2WQrXuqOAAFvsNg From: To: , Cc: X-OriginalArrivalTime: 09 Sep 2005 19:38:08.0676 (UTC) FILETIME=[018DB640:01C5B576] X-archive-position: 25245 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ravi_Kulkarni@Dell.com Precedence: normal Reply-To: Ravi_Kulkarni@Dell.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.5 required=5.0 tests=AWL,BAYES_00, HTML_FONTCOLOR_BLUE,HTML_MESSAGE,NO_REAL_NAME autolearn=no version=2.63 ------_=_NextPart_001_01C5B576.00F372D7 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Is it No to Index or is it a No to Trigger .. ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Paul Baumgartel Sent: Friday, September 09, 2005 11:52 AM To: Deepak Sharma Cc: Kulkarni, Ravi; oracle-l@freelists.org Subject: Re: What do you make of this? No, but I wouldn't think that would affect parse elapsed time. On 9/9/05, Deepak Sharma wrote:=20 Any index on lastaccess ? =09 --- Ravi_Kulkarni@Dell.com wrote: =09 > Paul, > > Can you confirm any (before-update) triggers on > VALIDKEYS ? > What are the cursors that the raw trace file show > (with dep > 0) ?=20 > > Thanks, > Ravi. > > ________________________________ > > From: oracle-l-bounce@freelists.org > [mailto: oracle-l-bounce@freelists.org ] On Behalf Of > Paul Baumgartel > Sent: Friday, September 09, 2005 11:18 AM > To: oracle-l@freelists.org > Subject: What do you make of this?=20 > > > Hi, all. Oracle10g (10.1.0.4.0) on Windows 2000. > > I have a set of traces that are puzzling. I had the > traces done in > controlled conditions, with a single user on the=20 > database; CPU usage on > the DB server was less than 25%. This statement is > a good example of > what I'm trying to figure out. There's a high ratio > of elapsed to CPU > on the parse, but not much in the way of wait=20 > information--except the > SQL*Net message from client. The client is a COM+ > application server. > No recent changes that I know of on that machine, > nor on the DB server > itself. I have inspected the raw trace file as well=20 > and don't see > anything to suggest that the tkprof timing summaries > are incorrect. > > II'm looking for ideas on how to go about diagnosing > the SQL*Net waits > (assuming they're accounting for the different=20 > between CPU and elapsed) > or finding where the rest of the elapsed time comes > from. Thanks in > advance for any suggestions! > > UPDATE VALIDKEYS SET LASTACCESS =3D :V00001 > WHERE=20 > VALIDKEYS_PK =3D :V00002 > > > call count cpu elapsed disk > query current > rows > ------- ------ -------- ---------- ---------- > ---------- ---------- > ---------- > Parse 198 0.46 2.72 0 > 0 0 > 0 > Execute 99 0.12 0.49 0 > 297 396 > 99 > Fetch 0 0.00 0.00 0 > 0 0 > 0 > ------- ------ -------- ---------- ---------- > ---------- ---------- > ---------- > total 297 0.59 3.22 0 > 297 396 > 99 > > Misses in library cache during parse: 0 > Optimizer mode: ALL_ROWS > Parsing user id: 12492 > > Elapsed times include waiting on following events:=20 > Event waited on Times > Max. Wait Total > Waited > ---------------------------------------- Waited > ---------- > ------------ > SQL*Net message to client 381 > 0.00 > 0.00 > SQL*Net message from client 381 > 0.01 > 0.51 > log file sync 1 > 0.00 > 0.00 > =09 ************************************************************************ > ******** > > -- > Paul Baumgartel > paul.baumgartel@aya.yale.edu > > > =09 =09 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com =09 --=20 Paul Baumgartel paul.baumgartel@aya.yale.edu ------_=_NextPart_001_01C5B576.00F372D7 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable
Is it No to Index or is it a No to Trigger=20 ..


From: oracle-l-bounce@freelists.org=20 [mailto:oracle-l-bounce@freelists.org] On Behalf Of Paul=20 Baumgartel
Sent: Friday, September 09, 2005 11:52 = AM
To:=20 Deepak Sharma
Cc: Kulkarni, Ravi;=20 oracle-l@freelists.org
Subject: Re: What do you make of=20 this?

No, but I wouldn't think that would affect parse elapsed=20 time.

On 9/9/05, Deepak=20 Sharma <sharmakdeep_oracle@yahoo.com= >=20 wrote:
Any=20 index on lastaccess ?

--- Ravi_Kulkarni@Dell.com=20 wrote:

> Paul,
>
> Can you confirm any = (before-update)=20 triggers on
> VALIDKEYS ?
> What are the cursors that the = raw=20 trace file show
> (with dep > 0) ?
>
> = Thanks,
>=20 Ravi.
>
> ________________________________
>
> = From:=20 oracle-l-bounce@freelists.o= rg
>=20 [mailto:=20 oracle-l-bounce@freelists.org] On Behalf Of
> Paul=20 Baumgartel
> Sent: Friday, September 09, 2005 11:18 AM
> = To: oracle-l@freelists.org
>= =20 Subject: What do you make of this?
>
>
> Hi,=20 all.  Oracle10g (10.1.0.4.0) on Windows = 2000.
>
> I have=20 a set of traces that are puzzling.  I had the
> traces = done=20 in
> controlled conditions, with a single user on the
> = database;=20 CPU usage on
> the DB server was less than 25%.  This=20 statement is
> a good example of
> what I'm trying to = figure=20 out.  There's a high ratio
> of elapsed to CPU
> = on the=20 parse, but not much in the way of wait
> information--except=20 the
> SQL*Net message from client.  The client is a=20 COM+
> application server.
> No recent changes that I know = of on=20 that machine,
> nor on the DB server
> = itself.  I have=20 inspected the raw trace file as well
> and don't see
> = anything=20 to suggest that the tkprof timing summaries
> are=20 incorrect.
>
> II'm looking for ideas on how to go about=20 diagnosing
> the SQL*Net waits
> (assuming they're = accounting for=20 the different
> between CPU and elapsed)
> or finding = where the=20 rest of the elapsed time comes
> from.  Thanks = in
>=20 advance for any suggestions!
>
> UPDATE VALIDKEYS SET = LASTACCESS =3D=20 :V00001
> WHERE
>  VALIDKEYS_PK =3D=20 :V00002
>
>
> call    =20 count      =20 cpu    elapsed       = disk
> query    current
> rows
> = -------=20 ------  -------- ---------- ----------
> ----------=20 ----------
> ----------
>=20 = Parse      198     = ; 0.46      =20 = 2.72          0
>=     0        =   0
>=20 0
> Execute    =20 = 99      0.12     &= nbsp;=20 = 0.49          0
>=   297        396
>= ;=20 99
>=20 = Fetch        0   &= nbsp;  0.00      =20 = 0.00          0
>=     0        =   0
>=20 0
> ------- ------  -------- ---------- = ----------
>=20 ---------- ----------
> ----------
>=20 = total      297     = ; 0.59      =20 = 3.22          0
>=   297        396
>= ;=20 99
>
> Misses in library cache during parse: 0
> = Optimizer=20 mode: ALL_ROWS
> Parsing user id: 12492
>
> Elapsed = times=20 include waiting on following events:
>   Event waited = = on            = ;            =     =20 Times
>  Max. Wait  Total
>=20 Waited
>  =20 ----------------------------------------   Waited
>=20 ----------
> ------------
>   SQL*Net message to = = client           &= nbsp;        =20 381
>       0.00
>=20 0.00
>   SQL*Net message from=20 = client           &= nbsp;      =20 381
>       0.01
>=20 0.51
>   log file=20 = sync           &nb= sp;           &nbs= p;          =20 1
>       0.00
>=20 = 0.00
>
*********************************************************= ***************
>=20 ********
>
> --
> Paul Baumgartel
> paul.baumgartel@aya.yale.edu=
>
>
>


_________________________________= _________________
Do=20 You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam = protection around
http://mail.yahoo.com
=


--
Paul Baumgartel
paul.baumgartel@aya.yale.edu=

------_=_NextPart_001_01C5B576.00F372D7-- -- http://www.freelists.org/webpage/oracle-l