Home » RDBMS Server » Performance Tuning » High Invalidations for SQL Area
High Invalidations for SQL Area [message #65731] Thu, 16 December 2004 01:40 Go to next message
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
Re: High Invalidations for SQL Area [message #65748 is a reply to message #65731] Tue, 21 December 2004 02:29 Go to previous messageGo to next message
Frank Naude
Messages: 4566
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:

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


Best regards.

Frank
Re: High Invalidations for SQL Area [message #65794 is a reply to message #65748] Fri, 24 December 2004 06:41 Go to previous message
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
Previous Topic: Analysing sql query
Next Topic: add 'AND 1 = 0' to your where clause. That will speed things up !
Goto Forum:
  


Current Time: Tue Jan 19 21:23:18 CST 2021