RE: Querying v$ tables very slow after upgrade

I think my case is special. We are running a home-grown application that is indeed 'special' at times.

Patty

<Joel.Patterson_at_crowley.com>

2007/06/26 02:51 PM

To
<Patty.Charlebois_at_greenshield.ca>, <Oracle-L_at_freelists.org>
cc

Subject
RE: Querying v$ tables very slow after upgrade

Is this something that we all can expect? Or is your case special?

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patty.Charlebois_at_greenshield.ca Sent: Tuesday, June 26, 2007 12:55 PM
To: Oracle-L_at_freelists.org
Subject: Re: Querying v$ tables very slow after upgrade

Thanks to all of you that responded.

 I had to open a SEV 1 SR with Oracle because my slow querying of v$ tables snowballed into a total system meltdown.

The root cause was a blocking mutex latch in our newly upgraded production database. Oracle support determined that the latching issue was due to a high rate of hard parsing and low percentage of shared SQL.

I turned off mutex locking with an Oracle-supplied hidden parameter, added cursor_sharing=similar and increased session_cached_cursors.

I also found 2 offending SQL statements from our web apps that were doing a ton of LIOs and chewing up CPU and fixed them.

Today everything is stable and happy.

There is nothing like an Oracle upgrade to get your adrenalin flowing on a Monday morning!

Patty

This e-mail is confidential, intended solely for the use of the recipient(s) to whom it was addressed. If you have received it in error, please do not copy or distribute this e-mail. We ask that you notify us immediately by replying to the sender and then delete this e-mail. E-mail sent or received over the internet may not be secure. You should use caution when sending e-mail messages containing private and confidential information or consider other secure means to send the information. If you have any questions regarding the authenticity or security of the e-mail you have received please do not hesitate to contact us at 1-800-265-5615.

Ce message de courriel est confidentiel et s'adresse uniquement à la personne indiquée. Si vous l'avez reçu par erreur, veuillez ne pas copier ni distribuer ce message de courriel. Nous vous demandons de nous aviser immédiatement en répondant à l'expéditeur, puis en supprimant ce message. Les messages envoyés ou reçus par courriel pourraient ne pas être sécurisés. Vous devriez être prudent lorsque vous envoyez des messages de courriel contenant des renseignements confidentiels et privés ou songer à prendre d'autres moyens sécurisés pour envoyer les renseignements. Si vous avez des questions concernant l'authenticité ou la sécurité d'un courriel que vous avez reçu, n'hésitez pas à communiquer avec nous au 1 800 265-5615.

--
http://www.freelists.org/webpage/oracle-l






This e-mail is confidential, intended solely for the use of the
recipient(s) to whom it was addressed. If you have received it
in error, please do not copy or distribute this e-mail. We ask
that you notify us immediately by replying to the sender and
then delete this e-mail. E-mail sent or received over the
internet may not be secure. You should use caution when sending
e-mail messages containing private and confidential information
or consider other secure means to send the information. If you
have any questions regarding the authenticity or security of
the e-mail you have received please do not hesitate to contact
us at 1-800-265-5615.


Ce message de courriel est confidentiel et s’adresse uniquement
à la personne indiquée.  Si vous l’avez reçu par erreur,
veuillez ne pas copier ni distribuer ce message de courriel.
Nous vous demandons de nous aviser immédiatement en répondant à
l’expéditeur, puis en supprimant ce message.  Les messages
envoyés ou reçus par courriel pourraient ne pas être sécurisés.
Vous devriez être prudent lorsque vous envoyez des messages de
courriel contenant des renseignements confidentiels et privés
ou songer à prendre d’autres moyens sécurisés pour envoyer les
renseignements.  Si vous avez des questions concernant
l’authenticité ou la sécurité d’un courriel que vous avez reçu,
n’hésitez pas à communiquer avec nous au 1 800 265-5615.
--
http://www.freelists.org/webpage/oracle-l