Obtain the TOP statement of each Metric in the database for 30 days

You can query from v $ SQL or v $ sqlarea in a short period of time. If you want to query within a week or a month, you may not be able to find it in V $ SQLAREA! The following is obtained through history DBA_HIST_SQLSTAT, Which is retained through snapshot.

-- With bastable as (SELECT DBMS_LOB.SUBSTR (SQL _TEXT, 4000, 1) AS SQL _FULL_TEXT, DHST. SQL _ID, ROUND (X. ELAPSED_TIME/1000000/X. EXECUTIONS_DELTA, 3) AVG_ELAPSED_TIME_SEC, ROUND (X. CPU_TIME/1000000/X. EXECUTIONS_DELTA, 3) AVG_CPU_TIME_SEC, ROUND (X. BUFFER_GETS_DELTA/X. EXECUTIONS_DELTA, 3) AVG_BUFFER_GETS, ROUND (X. PARSE_CALLS_DELTA/X. EXECUTIONS_DELTA * 100, 3) EXEC_PARSE_RATE, ROUND (X. PHYSICAL_READ_BYTES_DELTA/1024/X. EXECUTIONS_DELTA, 3) AVG_PHYSICAL_READ_KB, ROUND (X. DISK_READS_DELTA/X. EXECUTIONS_DELTA, 3) AVG_DISK_READS, EXECUTIONS_DELTA AS EXEC_TOTAL_NUM, DHST. COMMAND_TYPE, N. COMMAND_NAME FROM DBA_HIST_SQLTEXT DHST, DBA_HIST_SQLCOMMAND_NAME N, (select dhss. SQL _ID, SUM (DHSS. CPU_TIME_DELTA) CPU_TIME, SUM (DHSS. ELAPSED_TIME_DELTA) ELAPSED_TIME, case sum (DHSS. EXECUTIONS_DELTA) WHEN 0 THEN 1 else sum (DHSS. EXECUTIONS_DELTA) end as EXECUTIONS_DELTA, case sum (DHSS. SORTS_DELTA) WHEN 0 THEN 1 else sum (DHSS. SORTS_DELTA) end as SORTS_DELTA, case sum (DHSS. FETCHES_DELTA) WHEN 0 THEN 1 else sum (DHSS. FETCHES_DELTA) end as FETCHES_DELTA, case sum (DHSS. PARSE_CALLS_DELTA) WHEN 0 THEN 1 else sum (DHSS. PARSE_CALLS_DELTA) end as PARSE_CALLS_DELTA, case sum (DHSS. DISK_READS_DELTA) WHEN 0 THEN 1 else sum (DHSS. DISK_READS_DELTA) end as DISK_READS_DELTA, case sum (DHSS. BUFFER_GETS_DELTA) WHEN 0 THEN 1 else sum (DHSS. BUFFER_GETS_DELTA) end as BUFFER_GETS_DELTA, case sum (DHSS. IOWAIT_DELTA) WHEN 0 THEN 1 else sum (DHSS. IOWAIT_DELTA) end as IOWAIT_DELTA, case sum (DHSS. PHYSICAL_READ_BYTES_DELTA) WHEN 0 THEN 1 else sum (DHSS. PHYSICAL_READ_BYTES_DELTA) end as PHYSICAL_READ_BYTES_DELTA FROM DBA_HIST_SQLSTAT dhss where dhss. SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME> = TRUNC (SYSDATE)-30 AND END_INTERVAL_TIME <TRUNC (SYSDATE)-0) and dhss. PARSING_SCHEMA_NAME = UPPER ('shark') group by dhss. SQL _ID) X WHERE X. SQL _ID = DHST. SQL _ID AND DHST. COMMAND_TYPE = N. COMMAND_TYPE) SELECT * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_DISK_READS AS VALUE_S, 'avg _ disk_reads' AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like '/* SQL A %' ORDER BY AVG_DISK_READS DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'avg _ ELAPSED_TIME_SEC 'AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like'/* SQL A % 'order BY AVG_ELAPSED_TIME_SEC DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_CPU_TIME_SEC AS VALUE_S, 'avg _ CPU_TIME_SEC 'AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like'/* SQL A % 'ORDER BY AVG_CPU_TIME_SEC DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_BUFFER_GETS AS VALUE_S, 'avg _ buffer_gets' AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like '/* SQL A %' ORDER BY AVG_BUFFER_GETS DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, EXEC_PARSE_RATE AS VALUE_S, 'exec _ PARSE_RATE 'AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like'/* SQL A % 'ORDER BY EXEC_PARSE_RATE DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_PHYSICAL_READ_KB AS VALUE_S, 'avg _ PHYSICAL_READ_KB 'AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 order by limit DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, EXEC_TOTAL_NUM AS VALUE_S, 'exec _ TOTAL_NUM 'AS VALUES_TYPE from bastable where COMMAND_TYPE <> 47 AND SQL _FULL_TEXT not like'/* SQL A % 'order BY EXEC_TOTAL_NUM DESC) where rownum <= 5 union all select * FROM (SELECT SQL _FULL_TEXT, SQL _ID, EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'Procedures _ EXEC_TIME 'AS VALUES_TYPE from bastable where COMMAND_TYPE = 47 AND SQL _FULL_TEXT not like'/* SQL A % 'order BY AVG_ELAPSED_TIME_SEC DESC) WHERE ROWNUM <= 5;