Posted by It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today 
When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are one or two poorly performing SQL statements, or the server being overly stressed with CPU load with no single SQL being the obvious cause.
In either case, a common plan of attack is probe V$SQL to gauge what statements are potential trouble makers. Due to the myriad of statistical metrics stored in V$SQL it is a very flexible view to discover more about potential problems just by adjusting the predicates. For example:
High I/O?
order by DISK_READS desc
High CPU?
order by BUFFER_GETS desc
Poor parsing applications?
order by PARSE_CALLS / EXECUTIONS
Memory hogs?
order by SHARABLE_MEM desc
each of which can be tweaked to hone into more specific requirements. But there is a risk to doing this. In particular, if you are diagnosing a system that is under duress, than hammering away at V$SQL may do more damage than good. Don’t forget that a system under duress is probably having its shared memory structures contended for just as heavily. And by aggressively querying V$SQL, you may be adding to that workload and/or contention.
You might be thinking this leaves in a Catch-22 situation – how can we diagnose a struggling system if diagnosing it will make it struggle more? Fortunately, a simple remedy is at hand.
Instead of querying V$SQL, try querying V$SQLSTATS instead. From the documentation:
V$SQLSTATS displays basic performance statistics for SQL cursors and contains one row per SQL statement (that is, one row per unique value of SQL_ID). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.
Obviously this isn’t a leave pass to smash away at this performance view, but by avoiding V$SQL you are steering clear of a critical shared memory structure which any session wishing to parse/execute SQL will be competing for. V$SQLSTATS has been present since 10g and should be a drop-in replacement for your existing queries that use V$SQL.
Learning is not a spectator sport 
One detail – anyone on an unpatched 11.2.0.2 or 11.2.0.3, with a shared pool large enough to get multiple sub-pools, should beware: https://jonathanlewis.wordpress.com/2012/11/30/vsqlstats/ (and read the comments – so that you can follow up the bug note ( “.8”) for the bug number.
(I wouldn’t mention it, except I know that systems on these versions aren’t rare.)
Hello Connor, I am troubleshooting an issue where my listener keeps going UP & DOWN, probably due to the server being overly stressed with CPU load, & bad Code, so I am trying to pull all sql_fulltext during the time the issue happened.
Will this query helps me to get the info?
select sql_id,parsing_schema_name,service,module,FIRST_LOAD_TIME,last_load_time,sql_fulltext
from v$sql
where FIRST_LOAD_TIME >=’2020-09-02/10:00:01′
and FIRST_LOAD_TIME <='2020-09-02/15:00:01'
order by first_load_time asc;
Thanks
Regards,
Jorge
I would suspect you would be better off looking at v$active_session_history for that timeframe