OraFAQ Forum: Performance Tuning » High Invalidations for SQL Area

Home » RDBMS Server » Performance Tuning » High Invalidations for SQL Area

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

High Invalidations for SQL Area [message #65731]

Thu, 16 December 2004 01:40

Vibhor Agarwal
Messages: 2
Registered: December 2004

Junior Member

Hi,

I have been monitoring my application database and see a high value of invalidations in v$librarycache for 'SQL Area'. Also the v$open_cursor shows a high count of open cursors while the number of open sessions (v$session) is substantially low.

The application does not uses any DDL or Analyze commands within, so can�anyone of the Tuning gurus�give me some idea of what can be the reason for high value of invalidations.

Regards,
Vibhor Agarwal

Report message to a moderator

Re: High Invalidations for SQL Area [message #65748 is a reply to message #65731]

Tue, 21 December 2004 02:29

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

Hi,

A high value means SQL statements that could have been shared were invalidated by some operation. For example, a DDL statement changed the definition of a dependent object.

Run this query to get a list of statements that are invalidated:

Code: [Select all] [Show/ hide]

SELECT sql_text, invalidations 
  FROM v$sqlarea 
 WHERE invalidations > 0
 ORDER BY invalidations;

Best regards.

Frank

Report message to a moderator

Re: High Invalidations for SQL Area [message #65794 is a reply to message #65748]

Fri, 24 December 2004 06:41

Vibhor Agarwal
Messages: 2
Registered: December 2004

Junior Member

Hi Frank,

Thanks for yoour reply. I know that invalidations occur due to DDL on dependent objects.

But, my concern is when my application which is J2EE based OLTP application is running then how the invalidations are occuring.

Regards,
Vibhor

Report message to a moderator

Previous Topic:	Analysing sql query
Next Topic:	add 'AND 1 = 0' to your where clause. That will speed things up !

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 08:51:25 CDT 2025