RE: Queue waits problem

From: Nelson, Allan <anelson_at_miswaco.com>
Date: Tue, 11 May 2004 10:53:37 -0500
Message-ID: <7E0DB3A126BA9146AC30744E2B7E42459741E1@midhouhqxch01.net.smith.com>

Found it. It is bug 1103378, execute it in svrmgrl instead.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nelson, Allan Sent: Tuesday, May 11, 2004 10:38 AM
To: oracle-l_at_freelists.org
Subject: Queue waits problem

We are using Oracle 8.1.7.4 with Financials 11.5.7. I found the =3D66ollowing in a statspack report.
Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)

Wt Time

-------------------------------------------- ------------ ------------
-------
db file sequential read                           863,376      587,236
53.46
queue messages                                        420      430,500
39.19
db file scattered read                            189,136       55,541
5.06
latch free                                         72,217        7,868


.72

enqueue                                               139        3,192

.29

When I looked on Metalink I found a forum post that indicated that messages could be queued and never dequeued which could be the cause of high queue messages waits. The article suggested monitoring V$AQ to see if the ready column ever went to 0. This is what I got when I tried to query the table:

desc v$aq

 Name                                      Null=3D3F    Type
 ----------------------------------------- --------
----------------------------
 QID                                                NUMBER
 WAITING                                            NUMBER
 READY                                              NUMBER
 EXPIRED                                            NUMBER
 TOTAL_WAIT                                         NUMBER
 AVERAGE_WAIT                                       NUMBER

apps_at_MBAS> select * from v$aq;
ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor

no rows selected

When I searched metalink for this error on this table I got readmes for patch releases. I'm running utlrp at the moment on the assumption that I have something invalid that I need.

Any other ideas, or has anyone else seen this before

> Allan L. Nelson
> Oracle DBA
> M-I L.L.C.=3D20
> (832) 295-2238 office=3D20
> (832) 351-4180 fax=3D20
> anelson_at_midf.com <mailto:anelson_at_midf.com>
>=3D20
>=3D20

________________________________________________________________________

---

This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

---

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 Tue May 11 2004 - 10:53:16 CDT