I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under
V$sqlarea. What are the criteria for Oracle to keep the query in those views?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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".
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.