This blog post focuses on Oracle performance troubleshooting and Top SQL analysis techniques for DBAs. It includes practical SQL scripts to identify high resource-consuming SQL statements, analyze wait events, monitor memory and execution time usage, detect execution plan changes, and investigate AWR snapshot-based performance issues in Oracle databases.
TOP 10 SQL EVENT
SELECT * FROM (
SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time"
FROM gv$active_session_history h,
gv$event_name e
WHERE h.sample_time < (SELECT MAX(sample_time)
FROM gv$active_session_history)
AND h.sample_time > (SELECT MAX(sample_time) - 1/24
FROM gv$active_session_history)
AND h.event_id = e.event_id
AND e.wait_class <>'IDLE'
GROUP BY h.event
ORDER BY 2 DESC)
WHERE ROWNUM <10;SESSION COUNT, SQL, EVENT, VS….
-- Instance
select INST_ID,count(*) toplam from gv$session where status='ACTIVE' and type != 'BACKGROUND' group by INST_ID;
--Module
select module,INST_ID,count(*) toplam from gv$session where status='ACTIVE' and type != 'BACKGROUND' group by module,INST_ID order by toplam desc;
--Machine
select inst_id,machine,count(*) toplam from gv$session where machine is not null group by machine,inst_id order by toplam desc
--Running SQL count on Instance
select inst_id,count(*) from gv$sql group by inst_id;The SQL queries and SQL IDs that consume the most CPU are:
SELECT ss.SID, se.serial#, ss.VALUE, se.osuser, se.username, se.sql_id, se.prev_Sql_id, logon_time, status FROM v$sesstat ss, v$session se
WHERE
VALUE > 0
AND ss.SID = se.SID
AND status = 'ACTIVE' ORDER BY VALUE DESC
--veya
select * from ( SELECT parsing_schema_name "Owner",
last_load_time,
last_active_time,
executions,
rows_processed,
sql_id,
ROUND (rows_processed / executions) AS "RowCount",
ROUND (cpu_time / executions) AS "CpuTimeCount",
ROUND (buffer_gets / executions) AS "MemoryCount",
ROUND (disk_reads / executions) AS "DiskReadCount",
executions "# of Executions",
(elapsed_time / executions) / 1000000 AS "TimeSpend sec.",
DBMS_LOB.SUBSTR (SQL_FULLTEXT, 4000, 1) AS SqlFullText
FROM gv$sql
WHERE executions > 0
-- AND parsing_schema_name = :schema_name
AND TRUNC (last_active_time) = TRUNC (SYSDATE)
ORDER BY 8 DESC)
where rownum <21 ;Longops query
SET VERIFY OFF PAGESIZE 100 LINESIZE 500
COL SID FORMAT 99999
COL SERIAL# FORMAT 99999
COL OPNAME FORMAT A35
COL USERNAME FORMAT A12
COL OSUSER FORMAT A15
COL TOTALWORK FORMAT 999999999
COL SOFAR FORMAT 999999999
COL PERCENT_COMPLETE HEADING "YUZDE" FORMAT A12
COL START_TIME FORMAT A10
COL "TIME (MINS)" FORMAT 999999
COL "ETF (MINS)" FORMAT 999999
SELECT A.SID, A.SERIAL#, A.OPNAME, B.USERNAME, B.OSUSER
,A.TOTALWORK ,A.SOFAR ,ROUND((A.SOFAR/A.TOTALWORK)*100,2)||'%' AS PERCENT_COMPLETE
,TO_CHAR(A.START_TIME ,'HH24:MI:SS') AS START_TIME
,ROUND((SYSDATE - A.START_TIME)*1440,0) AS "TIME (MINS)"
,ROUND(DECODE((ROUND((A.SOFAR/A.TOTALWORK)*100,2)), 0, NULL, (ROUND((SYSDATE - A.START_TIME)*1440,1)) * 100 / (ROUND((A.SOFAR/A.TOTALWORK)*100,2))),0) AS "ETF (MINS)"
FROM GV$SESSION_LONGOPS A, GV$SESSION B
WHERE A.SID=B.SID
AND A.SERIAL#=B.SERIAL#
AND A.SOFAR <> A.TOTALWORK AND A.TOTALWORK <> 0
ORDER BY A.TOTALWORK DESC;— Finding SNAP_ID values for a given date range
SELECT MIN (snap_id) || ' and ' || MAX (snap_id)
FROM dba_hist_snapshot
WHERE begin_interval_time <=
TO_DATE ('2019/08/26 12:00:00', 'YYYY/MM/DD HH24:MI:SS')
AND begin_interval_time >=
TO_DATE ('2019/08/26 08:00:00', 'YYYY/MM/DD HH24:MI:SS')SQL script that provides Time, Memory and Wait Event details for SQL statements within a specific date range / snap interval
select event,wait_class,sql_id,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time)) run_time_sec,
READ_IO_BYTES,
PGA_ALLOCATED PGA_ALLOCATED_BYTES,
TEMP_ALLOCATED TEMP_ALLOCATED_BYTES
from (
select event,wait_class,
sql_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select event,wait_class,sql_id,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where snap_id between 220563 and 220565
-- sample_time >= to_date ('2019/08/20 08:00:00','YYYY/MM/DD HH24:MI:SS')
-- and sample_time < to_date ('2019/08/20 11:00:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by event,wait_class,sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = 'b0wkusa4hj4fn'
order by sql_id, run_time_sec desc;ACTIVE SESSION HISTORY AND SQL
-- SESSIN WAIT COUNTS
SELECT EVENT,COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE' AND TYPE <> 'BACKGROUND' GROUP BY EVENT ORDER BY 2 DESC;
-- ACTIVE SESSIONS SQL
SET LINESIZE 1000
SET PAGESIZE 1000
COL I_ID FOR 99
COL USERNAME FOR A10
COL OSUSER FOR A8
COL MACHINE FOR A15
COL DBSID FOR 9999
COL DBPID FOR A7
COL APPPID FOR A7
COL STATUS FOR A7
COL EX_TIME_MINs FOR 9999
COL BLCKNG_SE FOR A10
COL SQL_ID FOR A13
COL REMIN_SEC FOR 9999
COL MODULE FOR A10
COL PROGRAM FOR A10
COL WAIT_CLASS FOR A12
COL EVENT FOR A25
COL W_TIME FOR 9999
COL KILL FOR A25
COL OS_KILL FOR A10
COL SQL_FULLTEXT FOR A31
SELECT A.*, SQ.SQL_FULLTEXT FROM
( SELECT S.INST_ID I_ID, SUBSTR(S.USERNAME,1,10) "USERNAME", SUBSTR(S.OSUSER,1,10) "OSUSER", SUBSTR(S.MACHINE,1,15) "MACHINE", S.SID DBSID, P.SPID DBPID, S.PROCESS APPPID, S.STATUS, ROUND(S.LAST_CALL_ET/60, 2) "EX_TIME_MINs"
,CASE WHEN S.BLOCKING_SESSION IS NULL THEN NULL ELSE S.BLOCKING_SESSION || ' @ ' || S.BLOCKING_INSTANCE END AS "BLCKNG_SE"
, S.SQL_ID, ROUND(W.TIME_REMAINING_MICRO/1000000) "REMIN_SEC", SUBSTR(S.MODULE,1,10) "MODULE", S.PROGRAM
, S.WAIT_CLASS, W.EVENT, ROUND(W.WAIT_TIME_MICRO/1000000) "W_TIME"
,'ALTER SYSTEM KILL SESSION '''|| S.SID || ',' || S.SERIAL#|| ',@' || S.INST_ID || ''' IMMEDIATE;' AS KILL
,'kill -9 ' || P.SPID AS OS_KILL
FROM GV$SESSION S
INNER JOIN GV$PROCESS P ON S.PADDR = P.ADDR
INNER JOIN GV$SESSION_WAIT W ON S.SID=W.SID AND S.INST_ID=W.INST_ID
WHERE 1=1
--AND S.USERNAME NOT IN ('SYS','SYSTEM') AND S.USERNAME IS NOT NULL
--AND S.SID in (112)
--AND P.SPID = 6672
--AND S.SQL_ID='18PBS6PW1FDH2'
--AND S.BLOCKING_SESSION IS NOT NULL
--AND s.PROGRAM like '%rman%'
AND S.TYPE <> 'BACKGROUND'
AND S.STATUS = 'ACTIVE'
) A LEFT JOIN GV$SQL SQ ON A.SQL_ID = SQ.SQL_ID AND A.I_ID = SQ.INST_ID
WHERE SQ.USERS_EXECUTING=1
ORDER BY 9 DESC;HISTORICAL ACTIVE SESSIONS SQL
SELECT a.instance_number, a.sample_time, a.blocking_session, a.session_id, a.event, a.session_state, a.SQL_PLAN_OPERATION, a.sql_id, a.machine, b.sql_text
FROM dba_hist_active_sess_history a, gv$sql b
WHERE a.sql_id = b.sql_id
--and a.sample_time BETWEEN to_date('09.10.2018 02:00','dd.mm.yyyy hh24:mi:ss') AND to_date('09.10.2018 09:45','dd.mm.yyyy hh24:mi:ss')
and a.sample_time BETWEEN ('09/07/20199 08:50:00:000') and ('09/07/20199 10:40:00:000')
--and a.session_id = 4843
--and b.sql_text not like 'SELECT%'
order by a.sample_time
LOAD AVERAGE for ACTIVE SESSIONS
SELECT ROUND ((COUNT (ash.sample_id) / ((CAST (end_time.sample_time AS DATE) - CAST (start_time.sample_time AS DATE)) * 24 * 60 * 60)), 2) AS Average_Active_Session
FROM (SELECT MIN (sample_time) sample_time FROM gv$active_session_history ash WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) start_time,
(SELECT MAX (sample_time) sample_time FROM gv$active_session_history WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) end_time,
gv$active_session_history ash
WHERE ash.sample_time BETWEEN start_time.sample_time AND end_time.sample_time
GROUP BY end_time.sample_time, start_time.sample_time;Sql Tuning Advisor Kullanımı sql id için
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '87s8z2zzpsg88',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '87s8z2zzpsg88_tuning_task11',
description => 'Tuning task1 for statement 87s8z2zzpsg88');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');
select dbms_sqltune.report_tuning_task('87s8z2zzpsg88_tuning_task11') from dual;QL script to check whether a SQL execution plan has changed
select distinct sql_plan_hash_value from dba_hist_active_sess_history where sql_id='a77kxmcjfty2w';
select distinct(plan_hash_value) from dba_hist_sql_plan where sql_id='a77kxmcjfty2w';
Bir yanıt yazın