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 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;

4. Find the most users waiting for the last 30 minutes

--filename:top_wait_by_user.sql--What user is waiting the most?SET LINESIZE 180COL event FORMAT a60COL total_wait_time FORMAT 999999999999999999  SELECT ss.sid,         NVL (ss.username, 'oracle') AS username,         SUM (ash.wait_time + ash.time_waited) total_wait_time    FROM v$active_session_history ash, v$session ss   WHERE ash.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE AND ash.session_id = ss.sidGROUP BY ss.sid, ss.usernameORDER BY 3 DESC;

5. Find the SQL statements that consume the most resources within 30 minutes

--filename:top_sql_by_wait.sql-- What SQL is currently using the most resources?SET LINESIZE 180COL sql_text FORMAT a90 WRAPCOL username FORMAT a20 WRAPSET PAGESIZE 200SELECT *  FROM (  SELECT sqlarea.sql_text,                 dba_users.username,                 sqlarea.sql_id,                 SUM (active_session_history.wait_time + active_session_history.time_waited)                    total_wait_time            FROM v$active_session_history active_session_history, v$sqlarea sqlarea, dba_users           WHERE     active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE                 AND active_session_history.sql_id = sqlarea.sql_id                 AND active_session_history.user_id = dba_users.user_id        GROUP BY active_session_history.user_id,                 sqlarea.sql_text,                 sqlarea.sql_id,                 dba_users.username        ORDER BY 4 DESC) x WHERE ROWNUM <= 11;

6. objects with the most waits

--filename:top_object_by_wait.sql--What object is currently causing the highest resource waits?SET LINESIZE 180COLUMN OBJECT_NAME FORMAT a30COLUMN EVENT FORMAT a30  SELECT dba_objects.object_name,         dba_objects.object_type,         active_session_history.event,         SUM (active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time    FROM v$active_session_history active_session_history, dba_objects   WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE         AND active_session_history.current_obj# = dba_objects.object_idGROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.eventORDER BY 4 DESC;

7. Search for historical SQL statements within a specified time range

-- Note that this query is affected by AWR snapshot-related parameters -- filename: top_ SQL _in_spec_time. SQL -- top sqls elaps time and CPU time in a given time range .. -- X. elapsed_time/1000000 => from micro second to second -- X. elapsed_time/1000000/X. executions_delta => how many times the SQL ranset pause onset pause 'Press return to contine' set linesize 180col SQL _text format a80 wrap select SQL _text, dhst. SQL _id, round (X. elapsed_time/1000000/x.exe cutions_delta, 3) elapsed_time_sec, round (X. cpu_time/1000000/x.exe cutions_delta, 3) cpu_time_sec, X. elapsed_time, X. cpu_time, executions_delta as exec_delta from dba_hist_sqltext dhst, (select DHSS. SQL _id, sum (DHSS. cpu_time_delta) cpu_time, sum (DHSS. elapsed_time_delta) elapsed_time, Case sum (dhss.exe cutions_delta) When 0 then 1 else sum (dhss.exe cutions_delta) end as executions_delta from dba_hist_sqlstat DHSS where DHSS. snap_id in (select snap_id from region where region> = to_date ('& input_start_date', 'yyyymmdd hh24: Mi ') and end_interval_time <= to_date (' & input_end_date ', 'yyyymmdd hh24: mi ') group by DHSS. SQL _id) X where X. SQL _id = dhst. SQL _idorder by elapsed_time_sec DESC;

8. Search for historical SQL statements based on the specified time range and the specified user

-- Note that this query is affected by AWR snapshot-related parameters -- Author: Robinson -- Blog: http://blog.csdn.net/robinson_0612SELECT dbms_lob.substr (SQL _text, 4000, 1) as SQL, round (X. elapsed_time/1000000, 2) elapsed_time_sec, round (X. cpu_time/1000000, 2) cpu_time_sec, x.exe cutions_delta as exec_num, round (X. elapsed_time/1000000)/x.exe cutions_delta, 2) As exec_time_per_query_sec from dba_hist_sqltext dhst, (select DHSS. SQL _id, sum (DHSS. cpu_time_delta) cpu_time, sum (DHSS. elapsed_time_delta) elapsed_time, Case sum (dhss.exe cutions_delta) When 0 then 1 else sum (dhss.exe cutions_delta) end as executions_delta -- DHSS. executions_delta = No of queries execution (per hour) from dba_hist_sqlstat DHSS where DHSS. snap_id in (select snap_id from region where region> = to_date ('& input_start_date', 'yyyymmdd hh24: Mi ') and end_interval_time <= to_date (' & input_end_date ', 'yyyymmdd hh24: mi ') and DHSS. parsing_schema_name like upper ('% & input_username %') group by DHSS. SQL _id) X where X. SQL _id = dhst. SQL _idorder by elapsed_time_sec DESC;

9. Number of times SQL statements are executed

-- Exe_delta indicates the number of times that the cluster grows in a specified period of time -- filename: SQL _exec_num. SQL -- how many times a query executed? Set linesize 180 set verify off select to_char (S. begin_interval_time, 'yyyymmdd hh24: MI: ss'), SQL. SQL _id as SQL _id, SQL .exe cutions_delta as exe_delta, SQL .exe cutions_total from dba_hist_sqlstat SQL, dba_hist_snapshot s where SQL _id = '& input_ SQL _id' and S. snap_id = SQL. snap_id and S. begin_interval_time> to_date ('& input_start_date', 'yyyymmdd hh24: Mi ') and S. begin_interval_time <to_date ('& input_end_date', 'yyyymmdd hh24: Mi ') order by S. begin_interval_time;

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan