Re: Querying v$ tables very slow after upgrade

  • From: Patty.Charlebois@xxxxxxxxxxxxxx
  • To: Oracle-L@xxxxxxxxxxxxx
  • Date: Tue, 26 Jun 2007 12:55:23 -0400

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!


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.

Other related posts: