Common Oracle performance scripts (SQL)

In the absence of visual tools to monitor database performance, common scripts come in handy. Below are a few scripts related to Oracle performance for your reference. The following scripts are successfully tested in Oracle 10 Gb, and Oracle 11 GB may be adjusted accordingly.

1. Find the SQL statement with the maximum BUFFER_GETS overhead

-- Filename: top_ SQL _by_buffer_gets. SQL
-- Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
Set linesize 190
COL SQL _text FORMAT a100 WRAP
Set pagesize 100

SELECT *
FROM (SELECT SQL _text,
SQL _id,
Executions,
Disk_reads,
Buffer_gets
FROM v $ sqlarea
Where decode (executions, 0, buffer_gets, buffer_gets/executions)>
(Select avg (DECODE (executions, 0, buffer_gets, buffer_gets/executions ))
+ STDDEV (DECODE (executions, 0, buffer_gets, buffer_gets/executions ))
FROM v $ sqlarea)
AND parsing_user_id! = 3D
Order by 4 DESC) x
Where rownum <= 10;

2. Find the SQL statement with the maximum DISK_READS overhead

-- Filename: top_ SQL _disk_reads. SQL
-- Identify heavy SQL (Get the SQL with heavy DISK_READS)
Set linesize 190
COL SQL _text FORMAT a100 WRAP
Set pagesize 100

SELECT *
FROM (SELECT SQL _text,
SQL _id,
Executions,
Disk_reads,
Buffer_gets
FROM v $ sqlarea
Where decode (executions, 0, disk_reads, disk_reads/executions)>
(Select avg (DECODE (executions, 0, disk_reads, disk_reads/executions ))
+ STDDEV (DECODE (executions, 0, disk_reads, disk_reads/executions ))
FROM v $ sqlarea)
AND parsing_user_id! = 3D
Order by 3 DESC) x
Where rownum <= 10

1 2 3 4 Next Page