Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under v$sql and V$sqlarea. What are the criteria for Oracle to keep the query in those views?

share|improve this question

1 Answer 1

v$sql and v$sqlarea are views into the SQL area in the library cache (which is part of the shared pool, in the SGA).

What you see in those views are parsed/"compiled" statements that are in the cache. When a new statement comes in, Oracle checks to see if it already has it in that cache. If it does, it re-uses that (called a soft parse). (See SQL Sharing Criteria.) If not, it has to do a full parse and find room in the cache to store it (hard parse). If there's no room left in the cache, older statements are aged out.

How long a statement stays in cache depends on the size of the cache and how your database is used. If you have "few" distinct queries and use bind variables everywhere, and a sufficiently large library cache, it should be mostly static after "warmup".
If you're not using binds at all and have many distinct queries, or simply have a very small library cache, it'll recycle very fast.

For more details, see Configuring and Using the Shared Pool and Large Pool in the Memory Configuration and Use section of the tuning guide, especially the Library Cache Concepts and following sections.

share|improve this answer
    
acidently unmarked your answer as correct on my ipad. Trying to market back but I can't. –  Melara Nov 30 '12 at 22:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.