Querying the execution of Oracle database SQL statements

1. View the top 10 SQL statements with the most total consumed time

SELECT * FROM (select v.sql_id, V.child_number, V.sql_text, V.elapsed_time, V.cpu_time, V.disk_reads, Rank () Over (o Rder by v.elapsed_time Desc) Elapsed_rank from V$sql v) a where Elapsed_rank <= 10;

2. View the top 10 SQL statements with the most CPU consumption time

SELECT * FROM (select v.sql_id, V.child_number, V.sql_text, V.elapsed_time, V.cpu_time, V.disk_reads, Rank () Over (o Rder by v.cpu_time Desc) Elapsed_rank from V$sql v) a where Elapsed_rank <= 10;

3. View the top 10 SQL statements that consume disk reads

SELECT * FROM (select v.sql_id, V.child_number, V.sql_text, V.elapsed_time, V.cpu_time, V.disk_reads, Rank () Over (o Rder by v.disk_reads Desc) Elapsed_rank from V$sql v) a where Elapsed_rank <= 10;

4. View the SQL that has the most executions of the current database, for example, query the SQL statement of top 15 that executes most frequently

Select Sql_text, executions from (select Sql_text, Executions, RANK () over (ORDER by executions DESC) Exec_rank from v$s Qlarea) WHERE Exec_rank <= 15;

Querying the execution of Oracle database SQL statements