Top statement for each metric within 30 days of getting the database in Oracle

To query for a short period of time can be from v$sql or V$sqlarea if you want to query a week or one months then there may be in the V$sqlarea can not find! The following is obtained through the historical Dba_hist_sqlstat, which is retained by means of a snapshot.

-

The code is as follows

Copy Code

-The longest execution time 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 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 '/* SQL a% ' ORDER by Avg_disk_reads DESC) where RO Wnum <=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 '/* SQL a% ' ORDER by avg_elapsed_time_sec DESC) W Here 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 '/* 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 '/* 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 '/* 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 to avg_physical_read_kb 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 '/* SQL a% ' ORDER by Exec_total_num DESC) where R Ownum <=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 '/* SQL a% ' ORDER by avg_elapsed_time_sec DESC) where RO Wnum <=5;