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: 10046 event is useless in 9.2.0.4 version for response time measuring !!!

RE: 10046 event is useless in 9.2.0.4 version for response time measuring !!!

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 2 Aug 2004 13:09:56 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKCECNFCAA.mwf@rsiz.com> 





I think Jurijs question was not about the overhead to run the buggy 10046,
but whether the distribution of wait times could be trusted.



Possibly I'm twisting his meaning, but that is how I took it. I'm with still
you though Cary. I'd still run the 10046 and make some suppositions knowing
that it might be skewing the CPU results rather than just begin supposing
with no input or just aggregated averages.



mwf



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
Sent: Monday, August 02, 2004 12:09 PM


To: oracle-l_at_freelists.org


Subject: RE: 10046 event is useless in 9.2.0.4 version for response time
measuring !!!




Jurijs,



Even if something takes 6x longer to run the one time you trace it, it's
still better to have the detailed 10046 data than risk being tricked by =
the


aggregation problems introduced by using V$ data.



The problem with 9.2.0.4 is measured in hours at worst. The problem with
being tricked by V$ data is often measurable in months.



I'm not sure whether there's a 9.2.0.4 back-port available. It's a good
question to ask Oracle.


=20




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com


* Nullius in verba *



Upcoming events:


- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 =
Charlotte


- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford


- 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 J.Velikanovs_at_alise.lv


Sent: Monday, August 02, 2004 10:46 AM


To: oracle-l_at_freelists.org


Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: RE: 10046 event is useless in 9.2.0.4 version for response time
measuring !!!



Cary,


Thanks for your replay,


But that the .=20


How to discover performance problems on 9.2.0.4, then?
It is seem that 9.2.0.5 not the option ;(
.


Is to ask backport is a good idea in this case, I wonder.
Does some body in the list have tried it?
.


Cary, that is your experience? How to get around it?
.


PS Forgot to mention, test has been executed on the one machine. Two=20
separate OH. RH AS 3.0.



Regards,


Jurijs


9268222


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

http://otn.oracle.com/ocm/jvelikanovs.html








"Cary Millsap" <cary.millsap_at_hotsos.com>
Sent by: oracle-l-bounce_at_freelists.org


02.08.2004 18:07


Please respond to oracle-l


=20


        To:     <oracle-l_at_freelists.org>
        cc:=20
        Subject:        RE: 10046 event is useless in 9.2.0.4 version =


for=20


response time measuring !!!




Jurijs,



You've identified the bug correctly. The response time impact will vary =


=3D



from


virtually zero to the 6X number you've discovered, or even worse. (The
problem is the worst for sessions that nest NESTED LOOPS row source
operations inside each other.)



It's a bug. Fix it. The problem is fixed in 9.2.0.5.




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com


* Nullius in verba *



Upcoming events:


- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 =3D
Charlotte


- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =3D
Hartford


- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...




-----Original Message-----


From: oracle-l-bounce_at_freelists.org =3D


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of J.Velikanovs_at_alise.lv


Sent: Monday, August 02, 2004 8:19 AM


To: 'oracle-l_at_freelists.org '


Subject: 10046 event is useless in 9.2.0.4 version for response time
measuring !!!



My regards to all,


At the moment I have impression that, 10046 event is useless in =
9.2.0.4=3D20


version for response time measuring!


I have tied to run loop.sql (see below for full text) with and =
without=3D20


10046 event on 9.2.0.4 & 9.2.0.5.


The results scared me.


Take a look:


.


9.2.0.4=3D20


without 6,45 sec=3D20


!!!!!!!!!!! with 37,36 sec=3D20


.


9.2.0.5


without 5,48 sec=3D20


!!!!!!!!!!! with 7,19 sec


.


After short investigation it is appears that it is BUG 3009359.
http://metalink.oracle.com/metalink/plsql/showdoc?db=3D3DNOT&id=3D3D30093=
59.8=3D



No one-off fix on 9.2.0.4.


.


So it's appears, that 10046 is useless on 9.2.0.4, because SQL =
Trace=3D20


itself takes 580% of response time.


.


Any comments?!


Have any body solution for this issue.


.


As appears from dictation from this list, to go to 9.2.0.5 is not =
good=3D20


idea, because of instability and other issues.



Thanks in advance,


Jurijs








TKPROF output




9204




SELECT count(v)


from


 testlio where n between 10000 and 20000




call     count       cpu    elapsed       disk      query    current  =


=3D



rows

------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
Parse        1      0.00       0.00          0          0          0     =




=3D



0


Execute   1000      0.08       0.08          0          0          0     =


=3D



0


Fetch     1000     37.07      36.19          0     132000          0  =


=3D



1000

------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
total     2001     37.15      36.27          0     132000          0  =




=3D



1000



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------




   1000  SORT AGGREGATE (cr=3D3D132000 r=3D3D0 w=3D3D0 time=3D3D36185613 =
us)


10001000   TABLE ACCESS BY INDEX ROWID TESTLIO (cr=3D3D132000 r=3D3D0 =
w=3D3D0=3D20


time=3D3D28015044 us)


10001000    INDEX RANGE SCAN TESTLIO_I1 (cr=3D3D25000 r=3D3D0 w=3D3D0 =


=3D



time=3D3D9976625=3D20


us)(object id 6318)





9205




SELECT COUNT(V)



FROM



 TESTLIO WHERE N BETWEEN 10000 AND 20000



call     count       cpu    elapsed       disk      query    current  =


=3D



rows

------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
Parse        1      0.00       0.00          0          0          0     =




=3D



0


Execute   1000      0.05       0.07          0          0          0     =


=3D



0


Fetch     1000      6.89       6.72          0     132000          0  =


=3D



1000

------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
total     2001      6.94       6.81          0     132000          0  =




=3D



1000



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------




   1000  SORT AGGREGATE



10001000   TABLE ACCESS BY INDEX ROWID TESTLIO

10001000    INDEX RANGE SCAN TESTLIO_I1 (object id 6289)






RAW portion of TRC file




9204




PARSING IN CURSOR #5 len=3D3D60 dep=3D3D1 uid=3D3D0 oct=3D3D3 lid=3D3D0 =


=3D



tim=3D3D1065854307286108=3D20


hv=3D3D518780277 ad=3D3D'5aca2538'


SELECT count(v) from testlio where n between 10000 and 20000
END OF STMT



PARSE =3D



#5:c=3D3D0,e=3D3D485,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D1,r=3D3D0,dep=3D3D=
1,og=3D3D4,tim=3D3D1=3D


065854307286097


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D134,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D=
1,og=3D3D4,tim=3D3D1=3D


065854307286405


FETCH=3D20



#5:c=3D3D40000,e=3D3D36240,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,=
dep=3D3D1,og=3D3D4=3D


,tim=3D3D1065854307322684


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D85,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854307322899


FETCH=3D20



#5:c=3D3D40000,e=3D3D36128,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,=
dep=3D3D1,og=3D3D4=3D


,tim=3D3D1065854307359065


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D81,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854307359268


FETCH=3D20



#5:c=3D3D30000,e=3D3D36181,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,=
dep=3D3D1,og=3D3D4=3D


,tim=3D3D1065854307395487


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D81,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854307395689


FETCH=3D20



#5:c=3D3D40000,e=3D3D36145,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,=
dep=3D3D1,og=3D3D4=3D


,tim=3D3D1065854307431871


BINDS #5:





9204




PARSING IN CURSOR #5 len=3D3D60 dep=3D3D1 uid=3D3D0 oct=3D3D3 lid=3D3D0 =


=3D



tim=3D3D1065854501680779=3D20


hv=3D3D4259315813 ad=3D3D'5bc9bfdc'


SELECT COUNT(V) FROM TESTLIO WHERE N BETWEEN 10000 AND 20000

END OF STMT



PARSE=3D20



#5:c=3D3D0,e=3D3D7476,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D1,r=3D3D0,dep=3D3=
D1,og=3D3D4,tim=3D3D=3D


1065854501680769


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D134,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D=
1,og=3D3D4,tim=3D3D1=3D


065854501681077


FETCH=3D20



#5:c=3D3D10000,e=3D3D6736,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,d=
ep=3D3D1,og=3D3D4,=3D


tim=3D3D1065854501687852


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D82,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854501688065


FETCH=3D20



#5:c=3D3D0,e=3D3D6707,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,dep=3D=
3D1,og=3D3D4,tim=3D3D=3D


1065854501694810


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D81,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854501695011


FETCH=3D20



#5:c=3D3D10000,e=3D3D6699,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,d=
ep=3D3D1,og=3D3D4,=3D


tim=3D3D1065854501701747


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D79,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854501701944


FETCH=3D20



#5:c=3D3D10000,e=3D3D6747,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,d=
ep=3D3D1,og=3D3D4,=3D


tim=3D3D1065854501708729


BINDS #5:



EXEC =3D



#5:c=3D3D0,e=3D3D80,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3D1=
,og=3D3D4,tim=3D3D10=3D


65854501708927


FETCH=3D20



#5:c=3D3D10000,e=3D3D6650,p=3D3D0,cr=3D3D132,cu=3D3D0,mis=3D3D0,r=3D3D1,d=
ep=3D3D1,og=3D3D4,=3D


tim=3D3D1065854501715614


BINDS #5:







=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=

3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D


=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=

3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D


=3D3D=3D3D=3D3D



-- mk loop


drop table testlio ;


create table testlio (n number, v varchar2(1000)) tablespace users;
begin for f in 1..30000 loop


insert into testlio values=3D20

(f,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');=




=3D




end loop;


end;


/


create index testlio_i1 on testlio (n) tablespace users;





Jurijs


9268222


=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=

3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D


=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=

3D=3D3D=3D3D=3D3D=3D3D


http://otn.oracle.com/ocm/jvelikanovs.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
-----------------------------------------------------------------


----------------------------------------------------------------
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 Mon Aug 02 2004 - 13:16:49 CDT












Original text of this message













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