What are the criteria for a sql staying in the V$SQLAREA?

Question

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?

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.