RE: Querying v$ tables very slow after upgrade

  • From: Patty.Charlebois@xxxxxxxxxxxxxx
  • To: Joel.Patterson@xxxxxxxxxxx
  • Date: Wed, 27 Jun 2007 8:38:34 -0400

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

Patty



<Joel.Patterson@xxxxxxxxxxx>
2007/06/26 02:51 PM

To
<Patty.Charlebois@xxxxxxxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
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@xxxxxxxxxxx
x72546
904  727-2546
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Patty.Charlebois@xxxxxxxxxxxxxx
Sent: Tuesday, June 26, 2007 12:55 PM
To: Oracle-L@xxxxxxxxxxxxx
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.
--
//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.
--
//www.freelists.org/webpage/oracle-l


Other related posts: