Search for high-consumption Oracle statements _ javascript tips-js tutorial

This article describes how to find high-consumption Oracle statements, you can refer to the SQL statement used to generate the AWR report before running the following script. (The program script is generally stored in rdbms/admin under $ ORACLE_HOME and is named awrrpt. SQL, you need to enter the number of days for generating the AWR Report) Find the start and end snapshot numbers: begin_snap and end_snap.

The Code is as follows:

Set line 1000
Set linesize 200
Set pagesize 2000
Sets long 999999
Set echo on
Set markup html on
Select res .*
From (select to_char (d. end_interval_time, 'yyyy-mm-dd '),
A. PARSING_SCHEMA_NAME,
C. MODULE,
A. SQL _id,
A.exe cs as execution times,
ROUND (a. cpu_times/a.exe cs, 2) as single execution time,
A. cpu_times as cpu consumption time,
ROUND (a. cpu_times/B. sum_time * 100, 2) as cpu usage,
A. buffer_gets as logical read,
ROUND (a. buffer_gets/B. sum_buffer * 100, 2) as logical read percentage,
A. disk_read as physical read,
ROUND (a. disk_read/B. sum_disk * 100, 2) as physical read percentage,
C. SQL _fulltext
From (select PARSING_SCHEMA_NAME,
SQL _id,
Sum (EXECUTIONS_DELTA) AS execs,
Round (sum (CPU_TIME_DELTA)/1000000, 2) AS cpu_times,
Round (sum (ELAPSED_TIME_DELTA)/1000000, 2) AS elapsed_time,
Sum (BUFFER_GETS_DELTA) AS buffer_gets,
Sum (DISK_READS_DELTA) AS disk_read
From sys. WRH $ _ SQLSTAT wr, gv $ instance I
Where SNAP_ID <= & end_snap
And snap_id> = & begin_snap
And wr. INSTANCE_NUMBER = I. INSTANCE_NUMBER
And I. instance_number = & instance_number
Group by PARSING_SCHEMA_NAME, wr. INSTANCE_NUMBER, SQL _id),
(SELECT round (SUM (CPU_TIME_DELTA)/1000000, 2) sum_time,
SUM (BUFFER_GETS_DELTA) sum_buffer,
Sum (DISK_READS_DELTA) sum_disk
FROM sys. WRH $ _ SQLSTAT wr, gv $ instance I
Where SNAP_ID <= & end_snap
And snap_id> = & begin_snap
And wr. INSTANCE_NUMBER = I. INSTANCE_NUMBER
And I. instance_number = & instance_number) B,
V $ sqlarea c,
Dba_hist_snapshot d
Where a.exe cs> 0
And a. SQL _id = c. SQL _id
And a. PARSING_SCHEMA_NAME <> 'sys'
And d. snap_id = & end_snap
Order by cpu consumption time desc) res
Where rownum <41;
Exit

Save the Script output content to txt, and change the suffix of TXT to .html. Then, you can output the following webpage content: