Introduction to common Oracle performance scripts and oracle performance scripts

Introduction to common Oracle performance scripts and oracle performance scripts

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 190COL sql_text FORMAT a100 WRAPSET PAGESIZE 100SELECT *  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 190COL sql_text FORMAT a100 WRAPSET PAGESIZE 100SELECT *  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

3. Search for events that have led to resource overhead in the last 30 minutes

--filename:top_event_in_30_min.sql--Last 30 minutes result those resources that are in high demand on your system.SET LINESIZE 180COL event FORMAT a60COL total_wait_time FORMAT 999999999999999999  SELECT active_session_history.event,         SUM (            active_session_history.wait_time            + active_session_history.time_waited)            total_wait_time    FROM v$active_session_history active_session_history   WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880                                                AND SYSDATE         AND active_session_history.event IS NOT NULLGROUP BY active_session_history.eventORDER BY 2 DESC;