DBA İÇİN 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;

--Database Size

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
 , round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
 from (select bytes
 from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
 , (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

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ı? enq:TX – row lock contention, bir session tarafından istenen satrın, başka bir session tarafından tutulduğunu gösterir. iki durumu vardır lmod=6 ve lmod=4. lmod 6 ise update yada delete işlemi bekliyordur, lmode 4 ise unique index ile ilgili bir bekleme mevcuttur.

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 ;

--Peki hangi sql cümlesi bekletiliyor;

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

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') 

--En hızlı module bazli session kill etme

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
   )

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

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

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

  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;

veya;

SELECT * FROM (SELECT substr(sql_text,1,40) sql, parse_calls, executions, hash_value,address FROM V$SQLAREA WHERE parse_calls > 1000 ORDER BY parse_calls DESC) WHERE rownum  <=10 ;

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

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

Bir sql in planının değişip değişmediğini kontrol etmek için;

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

Invalid Objeleri Compile için

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and owner='IDBA'

Güncel Olmayan İstatistik Var mı?

--stale_stats YES ise istatistic güncel değildir
--Tablo İstatistikleri için;

SELECT owner,table_name,  partition_name,  object_type,  last_analyzed, global_stats, stale_stats FROM dba_tab_statistics where stale_stats='YES';

--İndex İstatistikleri için;

SELECT owner,table_name,  object_type,  last_analyzed, global_stats, stale_stats FROM dba_ind_statistics where stale_stats='YES';


İstatistikleri Güncellemek için;

---Tüm schemanın istatistikleri için

BEGIN
  DBMS_STATS.gather_schema_stats ('FRK', degree => 8, options =>'GATHER STALE', cascade   => TRUE);
  commit;
END;
   
---Tablo istatistikleri için;
       
BEGIN
  DBMS_STATS.gather_table_stats (ownname => 'FRK', tabname => 'TBLFRK', estimate_percent => 1, CASCADE => TRUE, DEGREE => 8, no_invalidate => false, granularity => 'GLOBAL AND PARTITION', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
  COMMIT;
END;
/

--Index İstatistikleri için;

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS( ownname => 'FRKDB' , indname => 'IDS_TBL' ,estimate_percent => 4 ,degree => 2 ,granularity => 'ALL', no_invalidate => FALSE);
  COMMIT;
END;
        /

Linux ta En fazla CPU tüketen 10 proses

ps -eo pcpu,pid,user,args|sort -k 1 -r |head -10

Linux en yüksek mb dosyaları bulma

find /  -type f -size +500000k 2>/tmp/1 -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

Linux tüm Local noları kill etme

kill -9 `ps -ef |grep LOCAL=NO|awk '{print $2}'`;

Invalid Objeler;

--İnvalid Objelerin Tespiti

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

--İnvalid Objelerin Derlenmesi (Compile)

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
--veya
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

--Toplu olarak compile

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

--UTL_RECOMP ile
!!!! Compile işlemi tamamlanan kadar job lar disable halde kalır.
!!!!Bu paket yanlızca sysdba yetkisi ile kullanılabilir.
!!!!!DDL işlemleri ile aynı anda çalıştırmak Deadlock a neden olabilir
- Schema Seviyesinde.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');


-- Database Seviyesinde.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

--Upgrade veya PSU Patch Sonrasıda Utlrp.sql VE utlprp.sql
 
--DBMS_UTILITY.compile_schema ile

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);

--Belli Bir Tarih Aralığı için Snap_Id leri veren SQL

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')

-- Belli Bir tarih aralığı || Snap Aralığı için SQL lerin Time ve Memory değerlerini veren SQL

select 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
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 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 213625 and 213632
-- 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 sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = '1jxpzt6v5th54'
order by sql_id, run_time_sec desc;


--- 

select 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 , SQL_PLAN_HASH_VALUE
from (
select
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 , SQL_PLAN_HASH_VALUE
from
(
select 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 , SQL_PLAN_HASH_VALUE
from
dba_hist_active_sess_history
where snap_id between 213745 and 214041
-- 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 sql_id,SQL_EXEC_ID,sql_exec_start ,SQL_PLAN_HASH_VALUE
order by sql_id
)
where sql_id = '5d4gu8fpa2nty'

-- Belli Bir tarih aralığı || Snap Aralığı için SQL lerin Time ve Memory değerlerini ve Waite eventlerini veren SQL

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;


-- Memory Tuning için Memory SGA PGA kullnim istatistiklerini, Free Memory Değerlerini Veren SQL.

select
*
from
(
select
x.inst_id,
x.name,
case when x.name not like '%cpu%' then x.value/1024/1024/1024 else x.value end value
from
(
select go.inst_id, 'cpu physical' name, go.value, 11 order_by from gv$osstat go where go.stat_name='NUM_CPUS' union all
select go.inst_id, 'memory physical' name, go.value, 1 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all
select go.inst_id, 'memory unused' name, go.value-(select sum(gp.value) from gv$parameter gp where go.inst_id=gp.inst_id and gp.name in ('sga_target','memory_target','pga_aggregate_target')) value, 2 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all
select gp.inst_id, gp.name, to_number(gp.value) value,
decode(gp.name,
'cpu_count',12,
'sga_max_size',3,
'sga_target',4,
'pga_aggregate_target',5,
'pga_aggregate_limit',6,
'memory_max_target',7,
'memory_target',8,
'result_cache_max_size',9
) order_by
from gv$parameter gp where gp.name in ('cpu_count','pga_aggregate_limit','pga_aggregate_target','sga_max_size','sga_target','memory_max_target','memory_target','result_cache_max_size')
) x
order by
x.inst_id,
x.order_by
)
union all
select
gi.inst_id,
'PGA record date: '||least(gi.startup_time,(select min(dhs.begin_interval_time) from dba_hist_snapshot dhs where vd.dbid=dhs.dbid and gi.instance_number=dhs.instance_number)) name,
null value
from
gv$instance gi,
v$database vd
union all
select
gi.inst_id,
'average PGA' name,
(select avg(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='total PGA allocated') value
from
gv$instance gi,
v$database vd
union all
select
gi.inst_id,
'maximum PGA' name,
(select max(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='maximum PGA allocated') value
from
gv$instance gi,
v$database vd
union all
select
to_number(null) inst_id,
case when ass.pname='DSTART' then 'System Stats record date: '||ass.pval2 else ass.pname end name,
to_number(decode(ass.pname,'DSTART',null,ass.pval1)) value
from sys.aux_stats$ ass
where
ass.pname in ('DSTART','CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED','MBRC')
union all
select
to_number(null) inst_id,
'Pack License: '||vp.value name,
null value
from
v$parameter vp
where
vp.name='control_management_pack_access'
union all
select distinct to_number(null) inst_id, 'AWR Report last usage date: '||max(dfus.last_usage_date) over () name, null value from dba_feature_usage_statistics dfus where dfus.name='AWR Report' and dfus.dbid in (select vd.dbid from v$database vd) union all
select distinct to_number(null) inst_id, 'AWR Report usages: '||sum(dfus.detected_usages) over () name, null value from dba_feature_usage_statistics dfus where dfus.name='AWR Report' and dfus.dbid in (select vd.dbid from v$database vd);


--Geriye dönük günlük inceleme (proaktif çalisma) ile veritabaninda sql leri listeleyerek planlarina bakip iyilestirme yapabilmemiz için analiz eden sql. Sql leri inceleyip bir veritabaninda coe ile fixleme yaparak degisim takip edilmeli. Iyi yönde etki gösterdiyse fixlenen plan kalici olarak birakilabilir. Times_faster kolonu kaç kat iyi plana fixleyince hizlanacagini gösteriyor , bunun disinda outputta iyi plan ve kötü plan karsilastirmalari gibi birçok detay  mevcut. 

select '@sqlid ' a,a.*,b.sql_text from (
select * from (
WITH snaps
     AS (SELECT /*+  materialize */
               dbid, SNAP_ID
           FROM dba_hist_snapshot s
          WHERE (begin_interval_time BETWEEN sysdate-&&sincedays AND sysdate))
select * from (
SELECT t.*, row_number () over (order by impact_secs desc ) seq#
FROM (
  SELECT DISTINCT   sql_id
                  , execs executions
                  , FIRST_VALUE (plan_hash_value) OVER (PARTITION BY sql_id ORDER BY pln_avg DESC) worst_plan
                  , ROUND (MAX (pln_avg) OVER (PARTITION BY sql_id), 2) worst_plan_et_secs
                  , FIRST_VALUE (plan_hash_value) OVER (PARTITION BY sql_id ORDER BY pln_avg ASC) best_plan
                  , ROUND (MIN (pln_avg) OVER (PARTITION BY sql_id), 2) best_plan_et_secs
                  , ROUND ( (MAX (pln_avg) OVER (PARTITION BY sql_id) - MIN (pln_avg) OVER (PARTITION BY sql_id)) * execs) impact_secs
                  , ROUND (MAX (pln_avg) OVER (PARTITION BY sql_id) / MIN (pln_avg) OVER (PARTITION BY sql_id), 2) times_faster
    FROM (SELECT PARSING_SCHEMA_NAME
                 , sql_id
                 , plan_hash_value
                 , AVG (elapsed_time_delta / 1000000 / executions_delta) OVER (PARTITION BY sql_id, plan_hash_value) pln_avg
                 , SUM (executions_delta) OVER (PARTITION BY sql_id) execs
            FROM DBA_HIST_SQLSTAT h
           WHERE     (dbid, SNAP_ID) IN (SELECT dbid, SNAP_ID FROM snaps)
                 AND NVL (h.executions_delta, 0) > 0)
) t
)
where seq# < 1260
ORDER BY seq#) where times_faster> 4) a, dba_hist_sqltext b
where a.sql_id=b.sql_id
and  worst_plan_et_secs> 1
order by times_faster
/


---Performans problemlerinde sorunlu sql ‘I  incelerken problem tespiti yaptigimiz sql ‘in waitinde 
“SQL Net message from dblink” wait eventini gördügümüzde asagidaki sekilde remote veritabanindaki sql in tesptini yaparak problemin kök nedenine ulasabiliriz. 

select distinct sql_id, object_node "DB_LINK", other "REMOTE_SQL" from v$sql_plan p where p.sql_id = 'b0wkusa4hj4fn';

Active Session History

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

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

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

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;

Alınan Backuplar Ve Süreleri İçin

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;