How many records/entry are there in v$sql,v$ession. and how they flush like Weekly or Space pressure.
Thanks
V$SQL shows you the contents of the library cache. This is an area of the shared pool that stores executable SQL & PL/SQL.
How many entries there are depends on:
- How big your shared pool (library cache) is
- The complexity of the statements you run
Statements are flushed from this when:
- According to a least recently used algorithm. So the longer it's been since someone executed a SQL statement, the more like it is to be removed
- Someone flushes the shared pool or removes a single statement
- The cursor is marked invalid. This happens after actions such as stats gathering and DDL on the underlying objects. The database can use rolling invalidation to spread this out over a period of time. For more on this, read:
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/improving-rwp-cursor-sharing.html#GUID-E04CF45D-CC70-4122-9BAC-EAB5B4D0E17A