hmqcnoesy hmqcnoesy - 1 month ago 6
SQL Question

Recently executed SQL not in V$SQL

Under what circumstances does a select query not appear in the V$SQL view?

For example, if I run this:

select /*findme*/ * from T ...


And then immediately afterward run this:

select * from v$sql where sql_text like '%/*findme*/%';


Under what circumstances would I get no rows returned? And under those circumstances can I force a query's info to be recorded in V$SQL?

Answer

Under what circumstances does a select query not appear in the V$SQL view?

One situation is on a Real Application Cluster (RAC). In such environments, V$SQL will only contain the library cache of the current node. To see the library cache across all nodes, select from GV$SQL.