Common Oracle performance scripts (SQL)

Common Oracle performance-related scripts (SQL) are used in the absence of visual tools to monitor database performance, the following provides several 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 [SQL] -- filename: top_ SQL _by_buffer_gets. SQL -- Identify heavy SQL (Get the SQL with heavy BUFFER_GETS) with the maximum BUFFER_GETS overhead) 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_ge Ts, 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 [SQL] -- 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 3. Search for events that cause overhead of resources in the last 30 minutes [SQL] -- filename: top_event_in_30_min. SQL -- Last 30 minutes result those resources that are in high demand on your system. set linesize 180 COL event FORMAT a60 COL total_wait_time FORMAT 999999999999999999 SELECT active_session_history.event, SUM (active_session_history.wait_time + active_session_history.time_waited) total_wait_time F ROM v $ active_session_history where between sysdate-60/2880 and sysdate and active_session_history.event is not null group by active_session_history.event order by 2 DESC; 4. Find the most-waiting user in the last 30 minutes [SQL] -- filename: top_wait_by_user. SQL -- What user is waiting the most? Set linesize 180 COL event FORMAT a60 COL 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. sid group by ss. sid, ss. username order by 3 DESC; 5. Find the SQL statement that consumes the most resources in 30 minutes [s Ql] -- filename: top_ SQL _by_wait. SQL -- What SQL is currently using the most resources? Set linesize 180 COL SQL _text FORMAT a90 WRAP COL username FORMAT a20 WRAP SET PAGESIZE 200 SELECT * FROM (SELECT sqlarea. SQL _text, dba_users.username, sqlarea. SQL _id, SUM (active_session_history.wait_time + timeout) total_wait_time FROM v $ active_session_history, v $ sqlarea, dba_users WHERE describetween 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. the most waiting object [SQL] -- filename: top_object_by_wait. SQL -- What object is currently causing the highest resource waits? Set linesize 180 COLUMN OBJECT_NAME FORMAT a30 column event format a30 SELECT dba_objects.object_name, dba_objects.object_type, struct, SUM (Bytes + bytes) ttl_wait_time FROM v $ active_session_history, dba_objects WHERE active_session_history.sample_time between sysdate-60/2880 and sysdate and Ctive_session_history.current_obj # = dba_objects.object_id group by dba_objects.object_name, dba_objects.object_type, active_session_history.event order by 4 DESC; 7. Search for historical SQL statements within a specified time range [SQL] -- note that this query is affected by awr snapshot-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. EXECUTION S_DELTA => How many times the SQL ran set pause on set pause 'Press Return To contine' set linesize 180 COL 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, (SELEC T 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 dba_hist_snapshot WHERE begin_interval_time> = TO_DATE ('& input_start_date', 'yyyymmdd HH24: MI ') AND end_interval_time <= _ DATE ('& input_end_date', 'yyyymmdd HH24: MI ') group by dhss. SQL _id) x WHERE x. SQL _id = dhst. SQL _id order by elapsed_time_sec DESC; 8. Search for the historical SQL statement [SQL] based on the specified time range and the specified user. Note that this query is affected BY awr snapshot parameters SELECT 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/1, 100000 0)/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 ', 'yyyymmhhdd 24: MI ') AND dhss. parsing_schema_name like upper ('% & input_username %') group by dhss. SQL _id) x WHERE x. SQL _id = dhst. SQL _id order by elapsed_time_sec DESC; 9. Number of times an SQL statement is executed [SQL] -- exe_delta indicates the number of times it increases within 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;