SQL - How to identify slow queries

Problem

Application takes a long time to service requests and you suspect poorly performing SQL queries. You might also see spikes in Queue Depth in AWS RDS Oracle metrics.

Solution

Using SQL Developer, connect to database using DBA role and execute the following query:

SELECT * FROM (SELECT     sql_fulltext,     sql_id,     elapsed_time/1000000,     ROUND((elapsed_time/1000000)/executions) "Cost",     child_number,     disk_reads,     executions,     first_load_time,     last_load_time FROM    v$sql ORDER BY elapsed_time DESC) WHERE executions >0 and ROWNUM < 10;

Look for results with the highest "Cost" numbers and consider whether they may be contributing to the poor performance.