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