Oracle Database Monitoring & Performance SQL Queries

Oracle veritabanı yöneticileri için günlük operasyonlarda ihtiyaç duyulan temel izleme ve performans analiz sorgularını bu yazıda derledim. Bu sorgular, aktif oturumları, tablo alanı kullanımını, kilitlenmeleri ve performans sorunlarını tespit etmek için kullanışlıdır.

ACTIVE SESSION QUERY

set echo off
set linesize 95
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a8 trunc
col osuser form a7 trunc
col machine form a20 trunc head "Client|Machine"
col program form a15 trunc head "Client|Program"
col login form a11
col "last call" form 9999999 trunc head "Last Call|In Secs"
col status form a6 trunc

select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
substr(program||module,1,15) program,substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,
last_call_et "last call",status
from gv$session where status='ACTIVE'
order by 1
/

Bekleyen sorunlu bir sql varsa count unu ve hangi event te bekleme yapıyor görmek için

SELECT EVENT,COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE' AND TYPE <> 'BACKGROUND' GROUP BY EVENT ORDER BY 2 DESC;

SELECT inst_id, event, count(*) AS SAYI
FROM gv$session_wait
WHERE wait_time = 0
  AND event NOT IN (SELECT name FROM v$event_name WHERE wait_class = 'Idle')
GROUP BY inst_id, event
ORDER BY count(*) DESC;

Active sessionda genel olarak uzun süre bekleyen sql leri zamanlarını ve sql ifadelerini görmek için aşağıdaki sql ifade kullanılabilir. Bazı alanlar yorum olarak işaretlenmiştir ihtiyaç halinde yorum satırı kaldırılarak faklı amaçlar için kullanılabilir.

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','frkdba') 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;
/

Active Session ile Load Avarage Hesaplama.

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

Historical Active Session Hesabı

 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('03.04.2025 02:00','dd.mm.yyyy hh24:mi:ss') AND to_date('04.04.2025 09:45','dd.mm.yyyy hh24:mi:ss')
  2    3    4    5  --and a.sample_time BETWEEN ('04/04/2025 08:50:00:000') and ('04/04/2025 10:40:00:000')
  6  --and a.session_id = 4843
  7  --and b.sql_text not like 'SELECT%'
  8  order by a.sample_time;
/

2. TABLESPACE ALANLARI

set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUMN used_of_max_pct format 999.99 heading 'UsedOfMax%'

WITH tbs_auto AS (
  SELECT DISTINCT tablespace_name, autoextensible
  FROM dba_data_files
  WHERE autoextensible = 'YES'
),
files AS (
  SELECT tablespace_name, COUNT(*) tbs_files,
         SUM(bytes / 1024 / 1024) total_tbs_bytes
  FROM dba_data_files
  GROUP BY tablespace_name
),
fragments AS (
  SELECT tablespace_name, COUNT(*) tbs_fragments,
         SUM(bytes) / 1024 / 1024 total_tbs_free_bytes,
         MAX(bytes) / 1024 / 1024 max_free_chunk_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
),
AUTOEXTEND AS (
  SELECT tablespace_name, SUM(size_to_grow) total_growth_tbs
  FROM (
    SELECT tablespace_name, SUM(maxbytes) / 1024 / 1024 size_to_grow
    FROM dba_data_files
    WHERE autoextensible = 'YES'
    GROUP BY tablespace_name
    UNION
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 size_to_grow
    FROM dba_data_files
    WHERE autoextensible = 'NO'
    GROUP BY tablespace_name
  )
  GROUP BY tablespace_name
)
SELECT 
  c.instance_name,
  a.tablespace_name Tablespace,
  CASE tbs_auto.autoextensible
    WHEN 'YES' THEN 'YES'
    ELSE 'NO'
  END AS autoextensible,
  files.tbs_files files_in_tablespace,
  files.total_tbs_bytes total_tablespace_space,
  (files.total_tbs_bytes - fragments.total_tbs_free_bytes) total_used_space,
  fragments.total_tbs_free_bytes total_tablespace_free_space,
  ROUND(((files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes) * 100) total_used_pct,
  ROUND((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100) total_free_pct,
  AUTOEXTEND.total_growth_tbs AS max_size_of_tablespace,
  ROUND(((files.total_tbs_bytes - fragments.total_tbs_free_bytes) / AUTOEXTEND.total_growth_tbs) * 100, 2) AS used_of_max_pct
FROM 
  dba_tablespaces a,
  v$instance c,
  files,
  fragments,
  AUTOEXTEND,
  tbs_auto
WHERE 
  a.tablespace_name = files.tablespace_name
  AND a.tablespace_name = fragments.tablespace_name
  AND a.tablespace_name = AUTOEXTEND.tablespace_name
  AND a.tablespace_name = tbs_auto.tablespace_name(+)
ORDER BY used_of_max_pct DESC;
/

TEMP TABLESPACE KULLANIMI tablespace ile başladık onunla devam edelim

SELECT 
  a.tablespace_name tablespace,
  d.TEMP_TOTAL_MB,
  ROUND(SUM(a.used_blocks * d.block_size) / 1024 / 1024, 2) TEMP_USED_MB,
  ROUND(d.TEMP_TOTAL_MB - SUM(a.used_blocks * d.block_size) / 1024 / 1024, 2) TEMP_FREE_MB,
  ROUND((SUM(a.used_blocks * d.block_size) / 1024 / 1024) / d.TEMP_TOTAL_MB * 100, 2) TEMP_USED_PCT,
  ROUND((d.TEMP_TOTAL_MB - SUM(a.used_blocks * d.block_size) / 1024 / 1024) / d.TEMP_TOTAL_MB * 100, 2) TEMP_FREE_PCT
FROM 
  v$sort_segment a,
  (
    SELECT 
      b.name, 
      c.block_size, 
      SUM(c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
    FROM 
      v$tablespace b, 
      v$tempfile c
    WHERE 
      b.ts# = c.ts#
    GROUP BY 
      b.name, 
      c.block_size
  ) d
WHERE 
  a.tablespace_name = d.name
GROUP BY 
  a.tablespace_name, 
  d.TEMP_TOTAL_MB;

Hangi session ve user hangi tempin ne kadarını kullanıyor

SET LINESIZE 500
SET FEEDBACK OFF

COL username FORMAT A12
COL tablespace FORMAT A20
COL sid FORMAT 99999
COL serial# FORMAT 99999
COL osuser FORMAT A15
COL last_call_et FORMAT 999999
COL sql_hash_value FORMAT 999999999999
COL program FORMAT A30
COL tablespace_name FORMAT A25        
COL total_extensible_mbytes FORMAT 999,999,999
COL tablespace_size FORMAT 999,999,999
COL free_size FORMAT 999,999,999
COL used_size FORMAT 999,999,999
COL extensible_free_size FORMAT 999,999,999
COL I FOR 999
COL used_pct FORMAT 999.99

WITH temp_total AS (
  SELECT 
    SUM(c.bytes) / 1024 / 1024 AS total_temp_mb
  FROM 
    v$tempfile c
)
SELECT
  s.inst_id AS I,
  s.sid,
  s.status,
  SUBSTR(s.username, 1, 10) AS username,
  u.tablespace,
  TRUNC(u.blocks * t.value / 1024 / 1024) AS mbytes,
  ROUND(TRUNC(u.blocks * t.value / 1024 / 1024) / temp_total.total_temp_mb * 100, 2) AS used_pct,
  p.qcsid
FROM
  gv$session s
JOIN gv$sort_usage u
  ON s.saddr = u.session_addr AND s.inst_id = u.inst_id
LEFT JOIN gv$px_session p
  ON s.sid = p.sid AND s.inst_id = p.inst_id
JOIN v$parameter t
  ON t.name = 'db_block_size'
CROSS JOIN temp_total
ORDER BY u.tablespace, s.username, s.status, mbytes DESC;
/

COL used_pct FORMAT 999.99

WITH temp_total AS (
  SELECT 
    SUM(c.bytes) / 1024 / 1024 AS total_temp_mb
  FROM 
    v$tempfile c
)
SELECT
  u.tablespace AS tablespace_name,
  s.username,
  SUM(TRUNC(u.blocks * t.value / 1024 / 1024)) AS mbytes,
  ROUND(
    SUM(TRUNC(u.blocks * t.value / 1024 / 1024)) / temp_total.total_temp_mb * 100, 2
  ) AS used_pct
FROM
  v$session s
JOIN v$sort_usage u
  ON s.saddr = u.session_addr
LEFT JOIN v$px_session p
  ON s.sid = p.sid
JOIN v$parameter t
  ON t.name = 'db_block_size'
CROSS JOIN temp_total
GROUP BY u.tablespace, s.username, temp_total.total_temp_mb
ORDER BY u.tablespace, s.username;

Fast-Start Rollback ve UNDO kullanımları ik olarak Fast-Start. ROlbacjk olan işimiz var mı?

select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,
UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100
from gv$fast_start_transactions;

UNDO Tablespace

SELECT 
    a.tablespace_name,
    ROUND(SIZEMB / 1024, 2) SIZE_GB,
    ROUND(USAGEMB / 1024, 2) USED_GB,
    ROUND((SIZEMB - USAGEMB) / 1024, 2) FREE_GB,
    ROUND((USAGEMB / SIZEMB) * 100, 2) PCT_USED,
    ROUND(((SIZEMB - USAGEMB) / SIZEMB) * 100, 2) PCT_FREE
FROM 
    (
        SELECT 
            SUM(bytes) / 1024 / 1024 SIZEMB, 
            b.tablespace_name
        FROM 
            dba_data_files a, 
            dba_tablespaces b
        WHERE 
            a.tablespace_name = b.tablespace_name
            AND b.contents = 'UNDO'
        GROUP BY 
            b.tablespace_name
    ) a,
    (
        SELECT 
            c.tablespace_name, 
            SUM(bytes) / 1024 / 1024 USAGEMB
        FROM 
            dba_undo_extents c
        WHERE 
            status <> 'EXPIRED'
        GROUP BY 
            c.tablespace_name
    ) b
WHERE 
    a.tablespace_name = b.tablespace_name;

Hengi session hangi undoyu ne kadar kulanıyor

SELECT
    s.inst_id AS instance,
    s.sid,
    s.serial#,
    s.username,
    t.used_urec      AS used_undo_records,
    t.used_ublk      AS used_undo_blocks,
    drs.segment_name AS rollback_segment,
    drs.tablespace_name
FROM 
    gv$session s
JOIN 
    gv$transaction t ON s.saddr = t.ses_addr AND s.inst_id = t.inst_id
JOIN 
    gv$rollstat rs ON t.xidusn = rs.usn AND t.inst_id = rs.inst_id
JOIN 
    dba_rollback_segs drs ON drs.segment_id = rs.usn
ORDER BY 
    s.inst_id, s.sid;

En Çok REDO üreten session ve sql_id leri

set lines 2000
set pages 1000
col inst_id for 99 heading 'Inst'
col sid for 99999
col serial# for 99999
col name for a09
col username for a14
col program for a21
col module for a25
col redo_mb for 999999.99

SELECT 
    s.inst_id,
    s.sid,
    sn.serial#,
    n.name,
    ROUND(s.value / 1024 / 1024, 2) AS redo_mb,
    sn.username,
    sn.status,
    SUBSTR(sn.program, 1, 21) AS program,
    sn.type,
    sn.module,
    sn.sql_id
FROM 
    gv$sesstat s
JOIN 
    gv$statname n ON n.statistic# = s.statistic# AND s.inst_id = n.inst_id
JOIN 
    gv$session sn ON sn.sid = s.sid AND sn.inst_id = s.inst_id
WHERE 
    n.name = 'redo size'
    AND s.value != 0
ORDER BY 
    redo_mb DESC;

Veritabanı LOCK Kontrolleri
Birbirini LOCK yapan User ve Sessionlar kim kimi blokluyor

SELECT
   (SELECT username FROM gv$session WHERE sid = a.sid AND inst_id = a.inst_id) AS blocker,
   a.inst_id AS blocker_inst,
   a.sid AS blocker_sid,
   'is blocking' AS relation,
   (SELECT username FROM gv$session WHERE sid = b.sid AND inst_id = b.inst_id) AS blockee,
   b.inst_id AS blockee_inst,
   b.sid AS blockee_sid
FROM
   gv$lock a,
   gv$lock b
WHERE
   a.block = 1
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2
   AND a.inst_id = b.inst_id
ORDER BY
   a.inst_id, a.sid;

Hangi object üzerinde ne tür bir lock var

Kodu	Lock Mode Adı	Açıklama
0	None	Lock alınmamış veya geçici durum. Genellikle anlam ifade etmez.
1	Null	Nesneye erişim var ama veri değiştirme hakkı yok. Genellikle bekleme durumu.
2	Row Share (SS)	Diğer oturumlar da bu tabloya DML yapabilir. En düşük seviyeli DML lock’tur.
3	Row Exclusive (SX)	DML işlemi yapan bir oturumdur. Başkaları da okuma yapabilir ama bazı DDL’leri engeller.
4	Share	Başka kullanıcılar sadece Row Share alabilir ama DML yapamaz.
5	Share Row Exclusive (SSX)	Hem Share hem Row Exclusive özellikleri taşır. Daha kısıtlayıcıdır.
6	Exclusive	Yalnızca bu oturum erişebilir. Diğer tüm erişim türlerini engeller. En güçlü lock’tur.

col inst_id         head 'Inst'          for 99
col session_id      head 'Sid'           for 9999
col object_name     head "Table|Locked"  for a30
col oracle_username head "Oracle|Username" for a10 truncate
col os_user_name    head "OS|Username"   for a10 truncate
col process         head "Client|Process|ID" for 99999999
col mode_held       for a20

SELECT 
    lo.inst_id,
    lo.session_id,
    lo.oracle_username,
    lo.os_user_name,
    lo.process,
    do.object_name,
    DECODE(
        lo.locked_mode,
        0, 'None',
        1, 'Null',
        2, 'Row Share (SS)',
        3, 'Row Excl (SX)',
        4, 'Share',
        5, 'Share Row Excl (SSX)',
        6, 'Exclusive',
        TO_CHAR(lo.locked_mode)
    ) AS mode_held
FROM 
    gv$locked_object lo,
    dba_objects do
WHERE 
    lo.object_id = do.object_id
ORDER BY 
    lo.inst_id, lo.session_id, do.object_name;

Row Lock hangi ifadeden kaynaklanıyor görmek için

SELECT sid, sql_text 
FROM v$session s, v$sql sq 
WHERE sid IN (
  SELECT sid 
  FROM v$session 
  WHERE state = 'WAITING' 
    AND wait_class != 'Idle' 
    AND event = 'enq: TX - row lock contention'
) 
AND (sq.sql_id = s.sql_id OR sq.sql_id = s.prev_sql_id);

PID, SID ve SESSION Tespitleri

SID den OS PID Bulma

set lines 123
col USERNAME for a15
col OSUSER for a8
col MACHINE for a15
col PROGRAM for a20

select b.spid, a.username, a.program , a.osuser ,a.machine, a.sid, a.serial#,
a.status from gv$session a, gv$process b
where addr=paddr(+) and sid=&sid;

OS PID den SID Bulma

col sid format 999999
col username format a20
col osuser format a15

select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

Bir SID e ait SQL Text ifadesini getirmek

col sql_text form a80
set lines 120

select sql_text from gv$sqltext where hash_value=
(select sql_hash_value from gv$session where sid=&1 and inst_id=&inst_id)
order by piece
/

Bir session KILL etmek için ifade üreten SQL, sql_id , User veya evente göre filtre edilebilir.

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;'
FROM gv$session
WHERE sql_id = 'b4fbuprqmgkza' 
--AND username LIKE 'FCEVIK'
--AND status = 'ACTIVE'
--AND event LIKE '%seq%';

En Hızlı Modül bazlı veya diğer filtereler ekleyerek , OS PID üzerinden kill ifadedesi veren SQL, çıkan sql linux üzerinde çalıştırılarak hiç beklemeden anında kill yapılabilir.

select 'kill -9 '||spid,sid from v$process,v$session where 
  v$process.addr = v$session.paddr
  and addr in 
  (
  select paddr from  v$session, v$sqlarea
  where v$session.sql_id = v$sqlarea.sql_id 
  and v$session.status='ACTIVE'
 -- and event like '%single%'
 --and v$session.SQL_ID='fq83ypvu11d8x'
  --and username='IDBA'
  --and v$session.module='MODULE123'
  and sid=1453
   )

Oracle Database Monitoring & Performance SQL Queries

ORACLE KULLANICI VE ROL YETKİLERİ

ORACLE ONLINE TABLE REDEFINATION (DBMS_REDEFINATION)

Bir yanıt yazın Yanıtı iptal et

Popüler Gönderiler

SQL PLAN FIX -1 (BASELINE)

Installation Kibana and Register Elasticsearch to New Kibana

CREATE TABLE AND DATA TYPE

Önemli

Oracle Database Constraint Management (Enable & Disable)

FILE TO BLOB PLSQL

PL-SQL BLOB ALANDAKI RESIM DATASINI JPG DOSYASINA DONÜŞTÜREN PROCEDUR (BLOB TO JPG)

Son Yazılar

Recent Comments

Press ESC to close

Oracle Database Monitoring & Performance SQL Queries

Bir yanıt yazın Yanıtı iptal et

Popüler Gönderiler

Önemli

Son Yazılar

Recent Comments