DBA SCRIPTS – ÇOK KULLANILAN DBA SQL İFADELERİ

Veritabanı İçerisindeki SCHEMA boyutunu öğrenme

SELECT sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments
WHERE owner = UPPER('SCHEMA_NAME');

--Hepsini Görmek için

select owner,sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments group by owner;

Fast Recovery Area (FRA) aktif olarak kullanıyor isek; FRA kullanım oranını veren sql.

select name, 
round(space_limit / 1048576) space_limit_in_mb, 
round(space_used / 1048576) space_used_in_mb, 
round((space_used / 1048576) / (space_limit / 1048576),2)*100 percent_usage
from v$recovery_file_dest;

--

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Birbirini lock yapan user lar var ise onu veren script.

select
   (select username from v$session where sid=a.sid) blocker,
   a.sid,
   ' is blocking ',
   (select username from v$session where sid=b.sid) blockee,
   b.sid
from
   v$lock a,
   v$lock b
where
   a.block = 1
and
   b.request > 0
and
   a.id1 = b.id1
and
   a.id2 = b.id2;

Global Table Lock var mı?

select * from gv$lock 
where id1 in (select p2 from gv$session_wait where event = 'enq: TX - row lock contention') and request=0 and lmode=6 ;

Linux Process ID sinden SID bulan sql;

select sid from gv$session where paddr = (select addr from v$process where spid = 14527 );

SID bilinen bir işlemin Linux Process ID (spid) sini bulan Sql

select spid from v$process where addr = (select paddr from v$session where sid = 382);

Genel Olarak username, sqlid veya event ‘ e göre session kill ifadesini veren sql;

select 'alter system kill session '''|| sid||','||serial#||',@'||inst_id||''';' from gv$session where sql_id='9mw08jfw5a0z4';
-- username like 'FCEVIK' 
-- status='ACTIVE' 
-- event like '%seq%' 

2 Dk dan fazla süredir Inactive olan sessionları kill yapacak olan ifadeyi veren sql;

select s.module,
       'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
  from gv$process p, gv$session s
 where p.addr = s.paddr
   and addr in (select paddr
                  from gv$session a
                 where a.status = 'INACTIVE'
                   and a.last_call_et > 120
                   and type <> 'BACKGROUND') 

SID bilinen session SERIAL bulan sql;

select SERIAL# from v$session where sid=1287

Herhangi bir session kill yapmak için;

alter system kill session 'sid, serial, @inst_id';

TOP 10 Event SQL;

 SELECT * FROM (
  SELECT
    h.event "Wait Event",
    SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time"
    FROM v$active_session_history h,
         v$event_name e
   WHERE h.sample_time < (SELECT MAX(sample_time)
                            FROM v$active_session_history)
     AND h.sample_time > (SELECT MAX(sample_time) - 1/24
                            FROM v$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;

Hangi Instance ta toplan kaç adet session var ?;

--TOPLAM SESSION SAYISI

select INST_ID,count(*) toplam from gv$session  where
--gv$session.status='ACTIVE' and
 
type != 'BACKGROUND' group by INST_ID;

--SESSION LAR HANGI MODULLERDEN GELİYOR

select module,INST_ID,count(*) toplam from gv$session  where 
--gv$session.status='ACTIVE' and
 type != 'BACKGROUND' group by module,INST_ID order by toplam desc;

--HANGI MAKINELERDEN NE KADAR SESSION GELIYOR ?

select inst_id,machine,count(*) toplam from gv$session where machine is not null group by machine,inst_id order by toplam desc

Anlık proses sayısı

SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');

O andaki Toplam Çalışan SQL Sayısı;

select inst_id,count(*) from gv$sql group by inst_id;

Hard Parse Yapan Sqlleri Tespit etmek için;

SQL> column force_matching_signature format 99999999999999999999999
SQL>  select count(1), force_matching_signature from v$sql
group by force_matching_signature
having count(1) > 10
order by 1;  2    3    4

  COUNT(1) FORCE_MATCHING_SIGNATURE
---------- ------------------------
        11       697775134598807240
        12      8906680569877903355
        16      7410675013521489776
        51      2849809912575282532
       173                        0
      4349      9625241933397001575
     13485     15560117443566225365

--Hard Parse Olan sql ifadeleri için v$sql görüntüsünden sql cümlerini görebiliriz.

select  sql_text, sql_id from v$sql where force_matching_signature=15560117443566225365 and rownum<5;

select  sql_text, sql_id from v$sql where force_matching_signature=9625241933397001575 and rownum<5;

SID bilinen bir sessiona ait sql görüntülemek için;

seLect inst_id,(select b.sql_text from gv$sql b  where a.sql_id = b.sql_id and rownum = 1) sql_text, a.sid,a.serial#,a.sql_id
  from gv$session a   where sql_id is not null and   sid=382;

En çok CPU tüketen sql ler ve sql idleri;

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