RMAN Backup

1) SQL> archive log list;

komutu ile archivelog da olup olmadığını anla.

2) DOS> rman target sys/oracle@orcl

3) Yedekleri e:\vtyedek klasörüne alacağız. 1 backup saklama stratejisi ile backup alacağız. Klasörü RMAN yaratmaz

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'E:\vtyedek\ora_df_%t_%s_%p';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\vtyedek\%F';
CONFIGURE BACKUP OPTIMIZATION ON;

------------------------------------------------------------------------------------------
redundancy 1 yerine bu satırı da verebilirsin: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; -- son 7 gün lük yedeği sakla demek
yedek yeri belirtilirken max dosya boyutu da verilebilir: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'e:\vtyedek\ora_df_%t_%s_%p' maxpiecesize 2G; -10 gb ise 2 gb a böluyor.

show all;

tüm veritabanını yedeğini alalım:

BACKUP DATABASE PLUS ARCHIVELOG;

--crosscheck archivelog all

Veritabanı yedeğinin sağlıklı olup olmadığını, eksik backup dosyası olup olmadığını

RESTORE DATABASE VALIDATE;

komutuyla görebiliriz. Eksik dosya olursa hata mesajı verir ve eksik dosyayı söyler.



İşin ne kadarının bittiğini gösterir:

SQL> SELECT SID, SERIAL#, OPNAME,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
--AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/
----------------------------
create or replace function yedek_yuzde_kac return varchar2 is
yuzde varchar2(20);
begin

select '%'|| ROUND (SOFAR/TOTALWORK*100,2)
into yuzde
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and TOTALWORK <> 0
and SOFAR <> TOTALWORK;
return yuzde;

end;

grant execute
on yedek_yuzde_kac
to public

create public synonym yyk for yedek_yuzde_kac
---------------------


datafile ları tek tek yedekle:

REPORT SCHEMA;
--------------
report obsolete; eski yedekleri listeler

delete noprompt obsolete;

crosscheck backup;

BACKUP VALIDATE DATABASE

----
BACKUP DATAFILE 5;


tablespace düzeyinde yedekle:

BACKUP TABLESPACE EXAMPLE;


incremental backup al:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

datafile düzeyinde incremental backup alabiliriz:

BACKUP INCREMENTAL LEVEL 1 DATAFILE 5;


archivelogları yedekliyoruz:

BACKUP ARCHIVELOG ALL;


backup ihtiyacı olan dosyalar:

REPORT NEED BACKUP;


eski backup dosyalarını göster:

REPORT OBSOLETE;


eski backup dosyalarını sil:

DELETE OBSOLETE;


database ve archivelog yedeklerini validate edelim:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;


recover edilemeyecek olanları gör:

REPORT UNRECOVERABLE;


bugün yedeğinin alındığından eminseniz eski archivelogları boşaltmak için:

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

crosscheck yaparak backupsetlerin yerlerinde olup olmadığına bakabiliriz:

CROSSCHECK BACKUP OF DATABASE;


datafile, tablespace ve controlfile yedekleme bilgilerini görebiliriz:

LIST BACKUP OF DATAFILE 5;

LIST BACKUP OF CONTROLFILE;

Archive log Mode

exp system/oracle@orcl file=c:\exp_hr.dmp owner=hr
possible charset conversion
NLS_LANG

alter user hr identified by hr account unlock;

select * from job_history
drop table job_history purge
flashback table job_history to before drop

create table kopya_emp5 as
select * from employees

select * from kopya_emp5
as of timestamp to_timestamp('06/05/2009 10:10', 'DD/MM/YYYY HH24:MI')
minus
select * from kopya_emp5

alter table employees enable row movement

alter system switch logfile;

select * from v$log;

show parameter undo_retention;

show parameter sessions

select name, value
from v$parameter
where name like '%spfile%'

create spfile from pfile;

archive log list;

shutdown immediate; normal - transactional - abort

grant advisor to hr;

alter system set undo_retention=900 scope=both; spfile - memory

show parameter control_files;

LOG_ARCHIVE_DEST_1=’LOCATION=C:\vtyedek\archives’ - mandatory reopen=300

Archivelog formatı verelim:

LOG_ARCHIVE_FORMAT='ARC_%S_%R_%T.ARC'

Veritabanını arşiv moda geçirmek için:

SQL> shutdown immediate;

İle kapayıyoruz.

SQL> startup mount;

Komutu ile mount modunda açıyoruz.

SQL> alter database archivelog;

Komutu ile veritabanını arşiv moduna geçiriyoruz.

SQL> alter database open;

Komutu ile veritabanını açıyoruz

Şimdi archive loga geçip geçmediğini test etmek için;

SQL> alter system switch logfile;

Komutuyla archive log üretiyoruz. (C:\archives klasöründe archive dosyaları oluşmaya başlayacak. )

select name, log_mode from v$database;

DBA SQL AWR

DIKKAT :

Asagidaki tum SQL komutlari, ORACLE AWR raporlarindan bilgiyi alir. Bu yuzden oncelikle sistem uzerinde AWR raporlarinin calismis oldugundan emin olmalisiniz. Ayrica AWR raporlarinin calisma zamanlari en az yarimsaat olmali ki daha dogru sonuclar alinabilsin.

Oracle 10g uzerinde RAC sistemleri mevcut ise insantce degerlerinden bu gorulebilir. Eger yoksa instance_number sadece 1 olacaktir.

AWR raporlarinin baslangic tarih ve saat ile bitis tarih ve saat degerlerini istediginiz gibi girebilirsiniz. Ozellikle is saatleri arasinda alacaginiz raporlamalar size daha dogru sonuclar verecektir.

/* ###
/* ### Oracle CPU Kullanim Oranlari (CPU yogunlugunun gorulmesi)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database CPU Time Ratio'

group by smr.INSTANCE_NUMBER;



INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

--------------- ---------- ---------- ---------- ---------- ----------

2 0 41.5811655 0 26 18

1 0 34.9719813 0 20 12

=============================================================================================

/* ###
/* ### Oracle Bekleme Oranlari (Oracle sisteminin kullanicilardan is bekleme zaman degerleri)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database Wait Time Ratio'

group by smr.INSTANCE_NUMBER;



INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

--------------- ---------- ---------- ---------- ---------- ----------

2 0 98.7966488 0 92 82

1 0 99.4121159 0 95 88



=============================================================================================

/* ###
/* ### En çok CPU kullanan -yoran- ilk 10 SQL (CPU TIME Degerine göre)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by CPU_TIME desc

)

where rownum<=10;





INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

2 b5hzmkcpbmn23 29148 14241 7619 211526710

1 6ahddxp5un7a3 7 4333 3760 79255773

1 gcfyqusdngbv4 207011 5213 3746 701526739

1 53wbuxzx13ndw 313 67766 2941 92357833

1 b5hzmkcpbmn23 9073 4675 2774 64520393

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 829355xf7xjj4 3513 2592 1356 42605109

1 1z6rfm3gfbzmv 125 27509 1127 29389172

1 gqau08wu3zrgp 286155 13099 981 6029967

=============================================================================================

/* ###
/* ### En uzun süren ilk 10 SQL (Elepsad_time degerine gore):
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by ELAPSED_TIME desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

1 53wbuxzx13ndw 313 67766 2941 92357833

1 1z6rfm3gfbzmv 125 27509 1127 29389172

2 gt1sjbrcc75y7 26497 25423 57 1467588

2 cm273h7pxw8k9 52906 25214 127 1641933

2 af582nx1dpb7y 74522 24179 118 973147

1 17hz8amdfpkth 305 21702 406 4864716

2 4xvr2wj20qjna 5228 20716 357 16711832

1 dbcc5nqf4dyxf 104817 18855 244 6366708

1 d0k0dj5v6yc6p 287381 17005 960 5148445

1 20wbqjfrqmfdx 85300 17001 236 7369135

=============================================================================================

/* ###
/* ### En çok mantiksal okuma yapan ilk 10 SQL (Buffer Gets degerine gore)
/* ###



select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by BUFFER_GETS desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

1 gcfyqusdngbv4 207011 5213 3746 701526739

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 b5hzmkcpbmn23 29148 14241 7619 211526710

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 53wbuxzx13ndw 313 67766 2941 92357833

2 gcc76mbkfzbrg 19962 1347 909 87347052

1 6ahddxp5un7a3 7 4333 3760 79255773

1 b5hzmkcpbmn23 9073 4675 2774 64520393

1 79aunmuktf6sf 8613 1811 956 47733280

2 a859cj7gtwggw 3909566 535 326 43179158

=============================================================================================

/* ###
/* ### En çok DISK okuma yapan ilk 10 SQL (DISK_READS degeri gore)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS,

sum(DISK_READS_DELTA) DISK_READS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1','YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by DISK_READS desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS

--------------- ------------- ---------- ------------ ---------- ----------- ----------

1 53wbuxzx13ndw 313 67766 2941 92357833 4428838

2 33ug56m93d3za 0 12325 285 7445226 3156943

2 3n4p7gcjah2tb 1 11248 228 7907331 2527719

2 fwygkuyv8csvk 1 10730 200 7075954 2191880

1 1z6rfm3gfbzmv 125 27509 1127 29389172 1637314

1 17hz8amdfpkth 305 21702 406 4864716 1245073

2 4xvr2wj20qjna 5228 20716 357 16711832 1176634

1 gdna3tyy8ntu8 379 16587 419 9084651 1144990

1 8acwusvy4jx71 102 11412 497 19688222 894536

2 c0njscvt37djh 1 2404 75 1347772 788968

=============================================================================================

DBA SQL -3 Advanced

/* ### Top 10 by Buffer Gets:#####
/* ### Buffer uzerinden 10000'den fazla bilgi alan ilk 10 sql listesi

set linesize 250
set pagesize 250
SELECT * FROM (SELECT substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10;
/* ### Top 10 by Physical Reads:#####
/* ### Disk uzerinden okuma sayisi 1000'den fazla olan ilk 10 sql listesi.
/* ### Disk uzerinden okuma ne kadar fazla ise performans o kadar kotudur. Bu yuzden bu sql'lerin tekrardan gozden gecirilmesi gerekir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions,-- disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;
/* ### Top 10 by Executions:#####
/* ### sql bazinda calisan sayisi 100'den fazla olan 10 SQL cumleciginin listesi.
/* ### Bir sql ne kadar cok calisiyorsa o sql'lin yaptigi is kullanicilar tarafindan cok kullaniliyor demektir. Bu tur is kurallarini gozden gecirmek tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM (SELECT substr(sql_text,1,40) sql, executions, rows_processed, rows_processed/executions "Rows/Exec", hash_value, address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;
/* ### Top 10 by Parse Calls:#####
/* ### Degiskenlerin parametre olarak gecmedigi tipteki sql'lerin sayisinin 1000'den fazla olan 10 SQL cumleciginin listesi.

set linesize 100
set pagesize 100
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;

/* ### Top 10 by Shareble Memory:#####
/* ### 1048576 byte'dan daha fazla bellek kullanan en ustteki 10 SQL cumleciginin listesi.
/* ### Bir sql calistigi ortamda cok daha fazla bellek tuketmemesi gerekir. Buna dikkat edilmesi tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
/* ### Top 10 by Version Count:#####
/* ### Degisik parametrelerle calistirilan sql'lerin version sayisi 20'den fazla olan 10 SQL cumleciginin listesi.
/* ### Bir sql degisik parametrelerle calistiriliyorsa her defasinda derlenir bu da hem performans hem de bellek acisindan olumsuz durum demektir. BIND variable degerleri kullanarak sql'lerin her defasinda degisik sekilde calistirilmamasina dikkat edilmesi tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10;

DBA SQL - 2

/* ### session toplam ve aktif izleme #####
/* ### oracle ait sessionlar secilmemistir (tipi background olmayanlar)
/* ### sistem uzerinde log on olan toplam kullanıcı ve o an aktif olanlari gosterir
/* ### bu sql, oracle 10g rac sisteminde 2 makina olarak var olan bir sistem icindir.

select count(*) TOPLAM,
(select count(*) from gv$session where inst_id=1 and type != 'BACKGROUND' ) TOPLAMN1,
(select count(*) from gv$session where status = 'ACTIVE' and inst_id=1 and type != 'BACKGROUND' ) AKTIFN1,
(select count(*) from gv$session where inst_id=2 and type != 'BACKGROUND') TOPLAMN2,
(select count(*) from gv$session where status = 'ACTIVE' and inst_id=2 and type != 'BACKGROUND') AKTIFN2
from gv$session group by 1,2,3;
/* ### instance bazinda calisan ACTIVE sql toplamlari ###
/* ### sql cumleciklerinin ilk 70 karakterini ve ayni SQL'lerden 3'den fazla olanlari gosterir
/* ### gv$session tablosu RAC sisteminin tumunu kapsar.

SELECT SUBSTR (sql_text, 1, 70) SQL, COUNT (1) adet, s.inst_id INSTANCE, SYSDATE tarih
FROM gv$session s, gv$sql t
WHERE s.sql_hash_value = t.hash_value
AND sql_hash_value <> 0
AND status = 'ACTIVE'
GROUP BY SUBSTR (sql_text, 1, 70), s.inst_id
HAVING COUNT (1) > 3
ORDER BY adet DESC /* ### sistemde tablolara FULL ACCESS erisim yapan sql'leri ve onu kullananlarinin listesini verir.
/* ### object_owner'da XXXXX ile tablolari yaratan schema name kullanilacaktir.

select sql_text,p.operation,p.options,p.object_name,e.machine,e.username,e.status,s.sql_id,event,sid
from v$sql_plan p , v$sqlarea s , v$session e
where operation='TABLE ACCESS' and options='FULL' and object_owner='XXXXXXXX'
and p.plan_hash_value=s.plan_hash_value
and e.sql_id=s.SQL_Id
order by p.address /* ### sistemde o an calisan ACTIVE SQL'lerin listesi */

select e.sql_id, e.sid, s.sql_text
from v$sqlarea s , v$session e
where s.sql_id = e.sql_id and e.status='ACTIVE' and e.type !='BACKGROUND'
order by 1 /* ### sistemde bugunden 2 gun once logon olmus ve hala ACTIVE olan sessionlarin listesi *
/* ### birsekilde sistemden normal sekilde cikmamis ve sistemde takili kalmis sessionlari bulup ve kill etmek icin kullanilir.
/* ### KILL etmeden once session gercekten aktif olup olmadigini kontrol ediniz !

select s.* from v$session s, v$process p
where s.schemaname='ESASLIVE' and s.status='ACTIVE'
and s.paddr=p.addr
and logon_time/* ### istenilen sessionlari UNIX uzerinden silmek icin isletim sistemindeki PID numarasi ile bulup kill -9 ile dusurmek icin
/* ### v$sql.sql_id='XXXXXXXXXXX' sql_id bilinen durumlar icindir.
/* ### v$session.sid=XXXX session SID biliniyorsa yukaridaki deger yerine kullanilir.

select 'kill -9 '||spid from v$process where addr in
(
select paddr from v$session, v$sql
where v$session.sql_id = v$sql.sql_id
and v$session.status='ACTIVE'
and v$sql.sql_id='79wkdjjz4p693'
and type != 'BACKGROUND'
) ; /* ### istenilen sessionlari ORACLE uzerinden silmek icin SID ve SERIAL# degerlerini alarak ALTER SYSTEM KILL SESSION komutu ile dusurmek icin örnekler...
/* ### bu sql calistirildiktan sonra listenen SQL'leri tekrar secip calistirmak gerekmektedir.

select 'alter system kill session '||''''||SID||','||SERIAL#||''''||';' from v$session
where sql_id='1z6rfm3gfbzmv' -- bu sql_id sahip olan sessionlarin hepsini secer

select 'alter system kill session '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' from v$session where schemaname='XXXXXX' and status='INACTIVE' order by logon_time desc; -- XXXXX schema kullanan, durumu INACTIVE olan ve baglanti zamani eski olan sessionlari listeler. daha sonra bu sessionlari toplu olarak sistemden dusurebilirsiniz. /* ### hash value sunu bildigin sql in çalışan gercek execution planı
/* ### zaman zaman sql'lerin execution planlari farkliliklar gosterebilir.

select distinct id, parent_id, lpad (' ', depth) || operation operation,options, object_name, cost
from v$sql_plan
where hash_value = 1752547357
order by id; /* ### RAC sistemlerinde makinalarda o anda hangi islemlerin oldugunun listesini almak icindir.
/* ### EVENT'lerin sayisina gore buyukten kucuge dogru sirali olarak listelenmistir.

select inst_id,SCHEMANAME,event,count(*)
from gv$session
group by inst_id,SCHEMANAME,event
order by 4 desc

DBA SQL - 1

/* ### Tablolarda alan aciklamalarinin toplu olarak girilmesi #####
/* ### Bazi alan isimleri kullanim amaclarina bagli olarak farkli tablolarda ayni isimle yer alabilir. (CRE_DATE, MOD_USER, FATURA_NO vb.) Bunlara tek seferde aciklama girmek icin asagidaki sql kullanilabilir.
/* ### owner='XXXXXX' sizin islem yapacaginiz schema adini girmeniz gerekecektir.
/* ### ZZZZZZZZZ yerine alan aciklamasini girilmelidir.
/* ### column_name='QQQQQQQQ' yerine istediginiz alan adi girilmelidir.
/* ### Bu sql calistirildiktan sonra listelenen SQL cümlecikleri secilip tekrar calistirilmalidir.

select 'COMMENT ON COLUMN XXXXXXXE.'||table_name||'.'||column_name||' IS '||''''||'ZZZZZZZZZZ'||''''||';'
from dba_col_comments where owner='XXXXXXXX' and comments is null
and column_name='QQQQQQQQ'

/* ### schema'ya ait tablolarin index kullanılip/kullanmadiginin monitor edilmesi ###
/* ### sql cumlecikleri calistikca tablolara ait indexleri kullanirsa indexin monitor degeri YES olur. Boylece o indexin kullanilmis oldugunu anlasilir.

-- scheman'in indexlerinin cikartilmasi ve toplu olarak acilmasi(schemaadi yerine sizin schema adini girmelisiniz)
select 'alter index schemaadi.'||index_name||' monitoring usage;' from dba_indexes where owner=schemaadi order by table_name;

-- monitor'Un acilmasi tek bir index icin (index.adi yerine istediginiz indexin adini giriniz)
alter index "index.adi" monitoring usage;

-- izlenmesi ((schemaadi yerine sizin schema adini girmelisiniz)
select * from v$object_usage where owner=schemaadi;

-- monitor'un kapatilmasi (index.adi yerine istediginiz indexin adini giriniz)
alter index "index.name" nomonitoring usage; /* ### table, index ve partition'larin toplam buyuklukleri
/* ### ozellikle partitionlarin buyukluklerini takip etmek icin kullanilir.

select segment_name,segment_type,bytes/1024 from dba_segments order by bytes desc /* ### Yanlis bir tablespace uzerinde acilan indexlerin dogru tablespace uzerinde rebuild edilmesi
/* ### HEDEFTS dogru tablespace adi
/* ### YANLISTS yanlis kullanilan tablespace adi.
/* ### SCMADI indexleri tasinacak scema adi.

select 'alter index '||owner||'.'||index_name||' rebuild tablespace HEDEFTS ;'
from dba_indexes
where tablespace_name='YANLISTS' and owner='SCHMADI' and index_type='NORMAL'
order by owner /* ### table ve index'lerin initrans değerinin yükseltilmesi
/* ### XXXXXX schemasina ait table ve indexlerin ini_trans degeri 250'den kucuk olanlarin secilmesi.
/* ### Asagidaki sql calistirildiktan sonra listelenen SQL cumlecikleri secilerek tekrar calistirilmalidir.

-- table's of schema
select 'alter table ' || owner || '.' ||table_name || ' initrans 255;'
from dba_tables
where owner in ('XXXXXX','') and ini_trans <250

-- index's of schema
select 'alter index ' || owner || '.' ||index_name || ' initrans 255;'
from dba_indexes
where owner in ('XXXXX','') and ini_trans <250
/* ### Index istatistik degerlerinin kontrol edilmesi.
/* ### Eger kayit sayisi fazla olan tablolarda indexli kolonlarda tekrar edilen deger 1000 den kucukse o zaman bu indexleri tekrar gozden gecirmek gerekir.
/* ### v$session.sid=XXXX session SID biliniyorsa yukaridaki deger yerine kullanilir.
/* ### XXXXXXXXX olarak schema adi girilmelidir.
/* ### burada tablolardaki kayit sayisi 1.000.000 dan fazla olanlar secilmektedir.

select c.TABLE_NAME,c.INDEX_NAME,c.column_name,s.DISTINCT_KEYS,s.NUM_ROWS
from dba_ind_columns c,dba_ind_statistics s,dba_tables t
where c.INDEX_OWNER='XXXXXXXX'
and c.index_NAME=s.index_NAME
and s.OWNER='XXXXXXXX'
and t.OWNER='XXXXXXXX'
and t.table_name=s.TABLE_NAME
and s.DISTINCT_KEYS<1000 and t.num_rows>1000000 and s.partition_name is null
order by 1,4 /* ### SQL PLUS uzerinden calisan sql'in execution plan bilgilerinin alinmasi (Bu ornek UNIX sistemler uzerinden verilmistir.)

-- 1. İzleme işlemini yapacak kullanıcının öncelikle bir PLAN_TABLE tablosuna sahip olması gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altına yer alan utlxplan.sql script’i, izleme işlemini yapacak kullanıcı tarafından SQL*Plus’tan çalıştırılır.
SQL> @utlxplan

-- 2. İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.
SQL> explain plan
set statement_id=’MUSTERI’ for
select MUSTERI_ADI
from MUSTERI
where MUSTERI_NO=1;

-- 3. Plan, aşağıdaki sorgu ile görüntülenir.
SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘
|| options || ‘ ‘ || object_name || ‘ ‘ ||
Decode(id,0, ‘Cost = ‘ || position) “Query Plan”
from PLAN_TABLE
start with id=0 and
statement_id=’MUSTERI’
connect by prior id=parent_id
and statement_id=’MUSTERI’;
/* ### Index analiz edilmesi (SQL PLUS Uzerinden calistirilacaktir.)
/* ### XXXXXX ile schema adi ve "indexadi" ile analiz edilecek index girilmelidir.
/* ### Burada index'in %100 degeri kontrol edilecektir. istenirse bu deger %25, %50 olarak girilebilir.
/* ### Degree => 4 ile paralelizm 4 olarak set edilmistir. sistemdeki cpu sayisina bagli olarak 1,2,4,8 gibi degerler girilebilir.

BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (
OwnName => 'XXXXX'
,IndName => 'indexadi'
,Estimate_Percent => 100
,Degree => 4
,No_Invalidate => FALSE);
END;
/

-- ayni islemin daha basit ve default degerlerle yapiabilecek hali.
ANALYZE INDEX XXXXXX.indexadi COMPUTE STATISTICS ONLINE /

ORACLE VERİTABANI İPUÇLARI

- Büyük tablolarınızı, önemli tablolarınızı, bir instance uzerindeki farkli schema bilgilerini, ayrı table space uzerinde tutun. Ozellikle restore işleminde daha az sorun yaşarsınız. Restore ederken tum DB yerine sadece istediginiz tablespace geri donebilirsiniz.

- tablolarınızı ayri bir tablespace'de indexlerinizi ayri bir tablespace üzerinde tutmanız size bakım, taşıma gibi konularda avantaj getirir.

- ORACLE 10g ile gelen flash ozelligini inceleyin, gerekebilecek durumlar mutlaka olacaktır.

- Tablolarınızdaki satır sayısı arttığında yazdığınız sql'lerin execution planları değişiklik gösterecektir. Arada AWR calistirarak sistemde uzun süren SQL'leri, diskten cok okuma yapan ya da "full access" yapan sql'leri görebilirsiniz.

- Index kullanımının, Tablo satır sayısının %7 ila %10 gibi bir değere erişileceği durumlarda faydalı olacağını bilmelisiniz.

- ORACLE 10g ile gelen "Enterprise Manager" çalışması özellikle makina üzerine yoğunluk olduğunda sorunlu olabiliyor. Bu yüzden özellikle EM üzerinde job tanımlamalarını yapmamanızı bunun yerine DBMS SCHUDULER kullanmanızı öneririm.

- Enterprise Management üzerindeki saat dilim ayarlamaları sorun yaratabileceğini unutmayin. Saatlerin ileri veya geri alınması durumlarında tanımladığınız saatte çalışacak job'lar da sorunlar yaşayabilirsiniz.

- Procedurler uzerinde özellikle tarih ve saat formatlari çalıştığınız PC ile server sistemleri arasında farklı olabileceğinden "ALTER SESSION NLS DATE FORMAT" komutunu kullanmayı alışkanlık haline getirin.

- AWR raporlarının iş saatlerinde yarım saatlik dilimlerde, iş dışı saatlerinde bir saatlik dilimlerde çalışmasını set edin. Unutmayın ki, AWR raporları sistemde bir sorun olduğu anda neden olduğuna dair bilgiyi vermez. Bir zaman dilimi tam olarak bitmeden AWR raporu tamam olmayacaktır. Zaman dilimlerini kısa tutarsanız sistem üzerine yük getirecektir !

- Partition özelliğini kullanmanız size çalıştırdığınız sorgularda performans sağlayacaktır. Ancak partition edilecek alanların nasıl olması gerektiği konusunda titiz davranın. Daha çok hangi alanlar üzerinden sorgulamalar yapıyorsanız o alanlari (genellikle tarih olur) kullanmayı tercih edin.

- Partition tablolarındaki eski partition tablolarının taşınması, düşürülmesinin o kadar kolay olmayacağını unutmayin. Özellikle global index kullanımı mevcutsa indexler tekrardan yaratilacağından bu işlem uzun sürebilir.

- insert, delete işlemleri sonrası zamanla tablolarda performans sorunları yaşayabilirsiniz. Eğer zamanınız ve yeriniz varsa tabloları export ve başka tablespace üzerine import ederek performans artışı sağlayabilirsiniz. Bu işlem öncesi backup almayi unutmayın !

http://www.bilgisite.com/oracle/ora_03.htm

Oracle Performance

select * from sapsr3.TPFYPROPTY
where obj_name='ENQUE/TABLE_SIZE'

SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';


select * from V$SGA_CURRENT_RESIZE_OPS


select * from V$SGA_RESIZE_OPS;


select component, current_size, granule_size from v$sga_dynamic_components;


select * from V$SGA_DYNAMIC_FREE_MEMORY;

OBJECT_NAME = 'COEP~Z02' AND

SELECT
SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE
STATISTIC_NAME IN ('logical reads', 'physical reads');



SELECT SUBSTR(OBJECT_OWNER, 1, 10) OWNER,
SUBSTR(OBJECT_NAME, 1, 25) OBJECT,
SUBSTR(OPERATION, 1, 15) OPERATION,
SUBSTR(OPTIONS, 1, 15) OPTIONS,
COUNT(*) "NUMBER"
FROM V$SQL_PLAN
GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS;


SELECT BYTES FROM V$SGASTAT
WHERE POOL = 'shared pool' AND NAME = 'free memory';

select * from V$ARCHIVE_DEST
-----------------------------
SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';

-------------------------------------
select * from sapsr3.TPFYPROPTY
where obj_name='ENQUE/TABLE_SIZE'

select * from V$ARCHIVE_DESt

select * from v$parameter
where name like '%archive%'

select * from v$_lock

----
abap/buffersize : Program Buffer Size
500MB 000 1500MB 000 kb
enque/table_size
16384 96MB 000
ipc/shm_psize_10
150MB 000 000 600MB 000 000
ipc/shm_psize_40 Size of shared memory segment
124MB 000 000 250MB 000 000 byte

RSPLSE
enque/table_size or abap/shared_objects_size_MB


http://sapkb.blogspot.com/search/label/Oracle
http://benstudycenter.blogspot.com/2008_01_01_archive.html

ORACLE Trace and Dump file Alert sql

create or replace procedure external_alert_log as
path_bdump varchar2(4000);
name_alert varchar2(100);
begin

select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';

select
'alert_' || value || '.log' into name_alert
from
sys.v_$parameter
where
name = 'db_name';

execute immediate 'create or replace directory background_dump_dest_dir as ''' ||
path_bdump || '''';

execute immediate
'create table alert_log_external ' ||
' (line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory background_dump_dest_dir ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' fields terminated by ''#$~=ui$X''' ||
' missing field values are null ' ||
' (line) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ';
end;
/
---------------------------------------------------------------------
begin
external_alert_log;
end;
/
---------------------------------------------------------------------
select * from alert_log_external;


http://www.adp-gmbh.ch/ora/admin/read_alert/index.html

ORACLE Tuning Advisor

ORACLE PERFORMANS İYİLEŞTİRME
#1 - SQL Sorgu Çalışma Planının İzlenmesi
Yazar: Gizem Gürsel
© : www.oracledanismanlik.com ve E-Kitap.org

Her hakkı saklıdır.


Oracle veritabanında çalıştırılan her SQL sorgusu, bir çalıştırma planı (execution plan) doğrultusunda işletilir. Bu plan ile, hangi indekslere (varsa ve uygunsa) erişileceği, hangi tip “join” işlemlerinin gerçekleştirileceğine karar verilir. Çalışma planı, bir yerden bir yere giderken izlenecek birçok yol arasında en hızlı ulaşımı sağlayacak güzergahın seçilmesi olarak da düşünülebilir. Doğru seçilmemiş bir güzergah nedeniyle, 10 dakikada gidilebilecek bir yere 1 saatte ulaşılabilir1.


Neden SQL İzleme-İyileştirme?


İyi çalışan bir sorgu öncelikle kullanıcıya en uygun sürede hizmetin verilmesi için gereklidir. Kimlik Paylaşım Sistemi kullanılarak bir TC kimlik numarası sorgulaması yapılıyorsa, kullanıcının cevabını çok hızlı şekilde alması gerekmektedir. Öte yandan, mevcut donanım kaynaklarının verimli şekilde kullanımı için de sorguların iyi çalışması gerekmektedir. Kötü çalışan bir sorgu; disk, bellek ve CPU açısından da darboğazlara yol açabilmektedir. Sorgular üzerinde bazı hallerde yapılacak ufak rötuşlar bile çok önemli performans kazançları sağlayabilmektedir.


SQL iyileştirme işlemi ile genel olarak şunlar hedeflenmektedir2 :


*

Büyük tablolar üzerindeki gereksiz “full-table” erişimlerin indeksli erişime dönüştürülmesi,
*

Küçük tablolar üzerindeki “full-table” erişimleri cach’lemek,
*

İndeks kullanımının optimum düzeyde olduğunu garantilemek,
*

Optimal JOIN teknikleri kullanmak,
*

Tune complex subqueries to remove redundant access


Ne Zaman SQL İzleme-İyileştirme?


Bir SQL sorgusunun iyileştirilmesini çeşitli koşullar tetikleyebilir. Bunlar arasında kullanıcı tarafından işlemin uzun sürdüğü şeklinde yapılan geri bildirimler olabileceği gibi, yapılan izleme, istatistik toplama çalışmaları kapsamında sorgunun fazla kaynak kullandığının tespit edilmesi de yer alabilir. Üzerinde çalışılması gereken sorguya karar verildiğinde, inceleme ve test çalışmalarının, olabildiği ölçüde uygulamadan ve diğer ara katmanlardan bağımsız olarak gerçekleştirilmesi yararlı olacaktır. Buradaki amaç uygulamanın kendi yapısından kaynaklanan farklı işlemleri devre dışı bırakarak, sadece SQL’in optimizasyonuna yoğunlaşılmasıdır. Bu nedenle, kullanılan izleme araçları ile, sorunlu olduğu düşünülen SQL sorgusu tespit edildikten sonra doğrudan sorgu üzerinde çalışılmalıdır.


Sorgularda dikkat edilmesi gereken bir diğer husus da, “literal” veya “bind” kullanımıdır. “Literal”de, aşağıdaki örnekte olduğu gibi, where koşulu içinde bir değer (value) belirtilmiştir.


SQL> select ad,soyad from calisan where calisan_id=765;


“Bind”da ise aşağıda belirtildiği gibi bir parametre kullanımı vardır (B1)3.


SQL> select ad,soyad from calisan where calisan_id=:B1;



“Bind” ve “literal” kullanımda farklı çalışma planları ortaya çıkabileceğinden, orijinal sorguda hangisi kullanılmışsa, iyileştirme çalışmalarının da bunun üzerinde yoğunlaşması gerekir.


Production ortamındaki veritabanlarını olabildiği ölçüde yansıtan test veritabanları mevcut ise, sorgu iyileştirme çalışmalarının test veritabanlarında yapılması daha sağlıklı olacaktır. Her sorgudan önce buffer cache’in flush edilmesi veya veritabanının kapatılıp açılması ile daha doğru ölçüm değerleri elde edilebilecektir.


SQL İzleme Yöntemleri
1. TRACE Kullanımı


TRACE, bir sorgunun, batch işlemin veya tüm sistemin ölçümü için kullanılabilecek bir yöntemdir. Sistem üzerindeki darboğazların oluştuğu noktaların tespiti için yararlı olan geniş kapsamlı bir yöntemdir. TRACE4;


*

Sorguyu çalıştırır, çalışan sorguyla ilgili olarak istatistik üretir,
*

Uygulama geliştiricilerin sorgunun her bir bölümünü analiz etmesine yardımcı olur.


TRACE ile yapılan izlemede, user_dump_dest initora parametresi ilen belirtilen dizine ora_nnnnn.trc söz diziminde bir trace dosyası oluşturulur.


TRACE’in bulunulan session’dan başlatılması mümkün olduğu gibi, bir başka session’ın çalışması da izlenebilir.


Bulunulan session için TRACE işlemi :


SQL> alter system set timed_statistics=true;

SQL> alter session set max_dump_file_size=20000;


SQL> show parameter user_dump


NAME TYPE VALUE

---------------- ------- --------------------

user_dump_dest string /usr/oracle10/rdbms/log/udump


SQL> alter session set SQL_TRACE true;


SQL> select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;


…..


SQL> select * from dual;


SQL> alter session set SQL_TRACE false;


Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.



Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

=====================

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad='7b4fa270'

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

=====================

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad='7b29b108'

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……


Başka session için TRACE işlemi :


İzlenmesi istenen sesssion’a ait SID ve SERIAL# bilgileri elde edilir (Örnek olarak TEST kullanıcısı seçilmiştir).


SQL> alter system set timed_statistics=true;


SQL> alter session set max_dump_file_size=20000; -- OS block


SQL> select SID, SERIAL# from v$session where username=’TEST’;


SID SERIAL#

---------- ----------

163 110


Elde edilen bu SID ve SERIAL# değerleri, aşağıdaki şekilde kullanılarak trace başlatılır.


SQL>execute dbms_system.set_sql_trace_in_session('163','110',true);



Trace’in sonlandırılması için aynı komut, false parametresi ile çalıştırılır (Aşağıdaki komu işletilmezse izlenen kullanıcının o session’dan çıkana kadar yapacağı sonraki işlemler de aynı trace dosyasına eklenir).


SQL>execute dbms_system.set_sql_trace_in_session('163','110',false);


Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.



Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

=====================

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad='7b4fa270'

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

=====================

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad='7b29b108'

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……


2. EXPLAIN PLAN Kullanımı


EXPLAIN PLAN ile, sorgu çalıştırılmaksızın, Oracle optimizer tarafından izlenecek plan görüntülenir. Bu yöntem, sorgunun çok uzun sürdüğü durumlarda tercih edilebilir. Sorgunun süresine, getireceği kayıtlara ihtiyaç olmadan sadece çalışma planının elde edilmesi için yararlıdır.


1.

İzleme işlemini yapacak kullanıcının öncelikle bir PLAN_TABLE tablosuna sahip olması gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altına yer alan utlxplan.sql script’i, izleme işlemini yapacak kullanıcı tarafından SQL*Plus’tan çalıştırılır.


SQL> @utlxplan


2.

İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.


SQL> explain plan

set statement_id=’MUSTERI’ for

select MUSTERI_ADI

from MUSTERI

where MUSTERI_NO=1;


3.

Plan, aşağıdaki sorgu ile görüntülenir.


SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘

|| options || ‘ ‘ || object_name || ‘ ‘ ||

Decode(id,0, ‘Cost = ‘ || position) “Query Plan”

from PLAN_TABLE

start with id=0 and

statement_id=’MUSTERI’

connect by prior id=parent_id

and statement_id=’MUSTERI’;


Query Plan

----------------------------------------------------------------

SELECT STATEMENT Cost = 220

TABLE ACCESS BY INDEX ROWID MUSTERI

INDEX RANGE SCAN MUSTERI_IDX_NO


Daha basit planlar için aşağıdaki sorgu formatı da kullanılabilir.


SQL> select operation, options, object_name, parent_id

from PLAN_TABLE

where statement_id=’MUSTERI’

order by id;




OPERATION OPTIONS OBJECT_NAME PARENT_ID

-------------------- --------------- --------------- ----------

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1


3. AUTOTRACE Kullanımı


AUTOTRACE özelliği, sorgunun EXPLAIN PLAN’ının yansıra TRACE ve TKPROF’da yer alan istatistiklerin çoğunu (fiziksel okuma sayısı, toplam okuma vb) verir. AUTOTRACE’in aşağıdaki şekilde parametreleri vardır:



SET AUTOTRACE ON


Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir

SET AUTOTRACE ON EXP


Sadece explain plan görüntülenir

SET AUTOTRACE ON STAT


Sadece istatistikler görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)

SET AUTOTRACE TRACE


Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)



SQL> SET AUTOTRACE ON


SQL> select count(name) from scott.emp

where name=’JOHN’;


Count(Name) --- Sorgunun Sonucu

-----------

10


Query Plan --- Sorgunun Çalışma Planı

----------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE

2 1 INDEX (RANGE SCAN) OF ‘EMP_IDX’ (NON_UNIQUE)


Statistics --- Sorguyla İlgili İstatistik

----------


0 recursive calls

0 db block gets

1 consistent gets

1 physical reads

0 redo size

223 bytes sent via SQL*Net to client

274 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disks)

1 rows processed

4. V$SQL_PLAN Kullanımı


İyileştirilmesi istenen sorgunun izlenmesinin yanı sıra, o an çalışmakta olan bir sorgunun çalışma planının alınmasında da kullanılabilecek bir başka yöntem de V$SQL_PLAN view’ının kullanımıdır. Bu yöntemle, TEST adlı kullanıcının çalıştırmakta olduğu sorgunun nasıl izlenebileceği aşağıda örneklenmiştir.


*

SYS kullanıcısı, TEST kullanıcısının SID bilgisini aşağıdaki şekilde alır.


SQL> select SID from v$session where username=’TEST’;


SID

----------

1628


*

Alınan bu SID bilgisi aşağıdaki sorguya parametre olarak verilir.


SQL> select sql_id, sql_child_number from v$session

where sid=1628;


SQL_ID SQL_CHILD_NUMBER

------------- ----------------

9y8jx3vvy33d8 0


TEST kullanıcısının çalıştırmakta olduğu SQL’in değerleri yukarıdak sorgu ile alındıktan sonra, aşağıdaki sorguya parametre olarak verilir.


SQL> set pages 20000

SQL> set lines 120

SQL> column operations format a30

SQL> column object_name format a25

SQL> column options format a15


SQL>


select lpad(' ', level ) || operation operations,

object_name, options, cardinality

from (select * from v$sql_plan

where sql_id='9y8jx3vvy33d8'

and child_number=0)

connect by prior id=parent_id

start with id=0

order by id, position;



OPERATION OPTIONS OBJECT_NAME PARENT_ID

-------------------- --------------- --------------- ----------

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1


*

Çalıştırılan sorgu “bind” kullanıyorsa, bu durumda hangi gerçek değerlerle sorgunun çalıştırıldığı da, aşağıdaki sorgu ile görüntülenir.


SQL>


col name format a10

col value_string format a50


select name, datatype, value_string

from v$sql_bind_capture

where sql_id='9y8jx3vvy33d8' and child_number=0

order by position;


Bind Kullanımı


İzlenen SQL sorgusunda “bind” kullanımı varsa, yapılan iyileştirme çalışmaları kapsamında yine bind kullanımının simule edilmesi gereklidir. “Bind” kullanımında parametrelere nasıl değer aktarılacağı aşağıda örneklenmiştir.


Orijinal sorgunun aşağıdaki gibi A1 ve A2 ile temsil eden bind parametrelerle çalıştırıldığını varsayalım.


select count(*) from MUSTERI

where bolge=:A1 and musteri_adi=:A2;


SQL*Plus’tan yapılacak izleme çalışması öncesinde aşağıdaki şekilde tanımlamalar yapılmalıdır.



SQL> variable A1 number;

SQL> variable A2 varchar2(20);


SQL> execute :A1 := 34;

SQL> execute :A2 := ‘YILMAZ’;


Bu tanımlamaların ardından, sorgunun izlenmesine başlanır. Örnekte AUTOTRACE kullanımı yer almaktadır.


SQL> set autotrace trace exp stat


SQL> select count(*) from MUSTERI

Where bolge=:A1 and musteri_adi=:A2;




Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'MUSTERI' (TABLE)



Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

613 bytes sent via SQL*Net to client

660 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed


DEĞERLENDİRME


SQL izleme yöntemleri arasında yer alan AUTOTRACE ve EXPLAIN PLAN kullanımı, sorgular üzerinde varsayımsal çalışma planı oluşturmaktadır. TRACE ve V$SQL_PLAN ise, sorgu için uygulanan gerçek çalışma planını oluşturur. Bu nedenle TRACE ve SQL$PLAN’ın diğer yöntemlere göre kesin sonuç ürettiği değerlendirmesi yapılabilir. Bununla birlikte, AUTOTRACE, gerek kullanımının kolaylığı gerekse aynı session içinde izlemenin ve sorgu çalıştırma işleminin bir arada yapılabilmesi nedeniyle çoğu durumda tercih edilebilir.



1 Oracle 10g sürümü ile birlikte “rule-based” olarak nitelenen optimizer devre dışı kaldığından bu doküman, “cost-based” optimizer kullanımına göre düzenlenmiştir.

2 Oracle Tuning The Definitive Reference, Donald K.Burleson, Alexey B.Danchenkov, Rampant Techpress, s.555

3 Genel olarak ifade edilmesi gerekirse, bind kullanımı, kullanılan parametre değerleri dışında herşeyi aynı olan sorguların tekrar parse edilmesi gerekliliğini ortadan kaldırır, böylece parse zamanından ve CPU kullanımından tasarruf sağlar.

4 Oracle 9i Performance Tuning Tips&Techniques, Richard J.Niemiec, Oracle Press, s.206.
--------

Burada yer alan bilgi ve belgelerin kullanımı birden çok kişi ve kurumu ilgilendirebilmektedir. Bu nedenle hiçbir biçimde yazılı, görsel ve sayısal ortamlarda çoğaltılmaması gerekmekte yalnızca E-Kitap.org sitesi üzerinden kullanılması gerekmektedir. Buradaki bilgilerin kullanılmasından ötürü çıkabilecek hiçbir zarar E-Kitap.org'u bağlamaz. Kullanıcılar bunu peşinen kabul etmiş sayılırlar.

http://www.e-kitap.org/OraclePerformansIyilestirme_No1.html

ORACLE Trace and Dump file Alert

Oracle Database Alerts;
-Trace and Dump file Alert
root> which sqlplus
root> pwd
root> find . -print|grep –i dbmspool.sql
background_dump_destination
# Cleanup trace files more than 7 days old
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;

Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7

Using Trace Files

This section discusses the following trace file subjects:

• Background Thread Trace Files
• User Thread Trace Files
• Alert File
• Error Call Trace Stack

• $ORACLE_HOME/admin/db_name/bdump on UNIX operating systems
• %ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

The Oracle database creates a different trace file for each background thread. The name of the trace file contains the name of the background thread followed by the extension .trc, such as:

• siddbwr.trc
• sidsmon.trc

sidlckn.trc	Trace file for the Global Cache Service Processes (LMSn). This trace file shows lock requests for other background processes.
sidlmdn.trc	Trace file for the LMDn process. This trace file shows lock requests.
sidlmon.trc	Trace file for the LMON process. This trace file shows the status of the cluster.
sidp00n.trc	Trace file for the parallel execution processes.

The sidalrt.log file is in the directory specified by the BACKGROUND_DUMP_DEST parameter in the initdb_name.ora initialization parameter file. If you do not set the BACKGROUND_DUMP_DEST parameter, the sidalrt.log file is generated in:

• $ORACLE_BASE/admin/db_name/bdump on UNIX operating systems
• %ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

SELECT pid "Oracle Process Id",
       name
    from v$PROCESS, V$BGPROCESS
    where V$PROCESS.addr = V$BGPROCESS.paddr;

SQL>select round(sum(BYTES/1024/1024),2) as "Total SGA in Mb" from v$sgastat;

Total SGA in Mb
---------------


SQL>select round(sum(BYTES/1024/1024),2) as "Used SGA in Mb" from v$sgastat where NAME not like '%free%';

Used SGA in Mb
--------------

select * from v$parameter
where name like '%background_dump_%'
--Oracle free space script
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

-------------------
SELECT a.name, b.tablespace_name,
substr('Free : '||sum(b.bytes)/1024/1024,1,30) File_Size_MB
FROM dba_free_space b, v$database a
GROUP BY b.tablespace_name, a.name
UNION
SELECT a.name, b.tablespace_name,
substr('Total: '||sum(b.bytes)/1024/1024,1,30)
FROM dba_data_files b, v$database a
GROUP BY b.tablespace_name, a.name
ORDER BY 1,2,3
----------------------
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;


CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
-----------------
rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';

ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;


----------------------

Diagnostic Data	Previous Location 10g R2	ADR Location
Database Alert log File	BACKGROUND_DUMP_DEST	ADR HOME/alert /log.xml ADR HOME/trace/alert_.log
Background Process Trace Files	USER_DUMP_DEST	ADR HOME/trace
Foreground Process Trace Files	BACKGROUND_DUMP_DEST	ADR HOME/

-------------------------

ORACLE Alert

select file_name from dba_data_files; ------------------------------------- column file_name format a40 column tablespace format a15 column bytes format 999,999,999 select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name order by BYTES desc; ----------------------------------- alter system set db_create_file_dest='C:\ORA\PRODUCT\10.2.0\DB_1\ORADATA\ORCL\'; create tablespace test;
show parameter background_dump --alert logların yeri

Controlling the Size of Trace Files --MAX_DUMP_FILE_SIZE


ALTER SESSION SET SQL_TRACE TRUE;  -performans kaybına neden olabilir. defaultta false

Use the DBMS_SESSION or the DBMS_MONITOR packages if you want to control SQL tracing for a session.

DBA_THRESHOLDS;


SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

View	Description
DBA_THRESHOLDS	Lists the threshold settings defined for the instance
DBA_OUTSTANDING_ALERTS	Describes the outstanding alerts in the database
DBA_ALERT_HISTORY	Lists a history of alerts that have been cleared
V$ALERT_TYPES	Provides information such as group and type for each alert
V$METRICNAME	Contains the names, identifiers, and other information about the system metrics
V$METRIC	Contains system-level metric values
V$METRIC_HISTORY	Contains a history of system-level metric values

Monitoring Lock;

View	Description
V$LOCK	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS	Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS	Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS	Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch

Process and Session Views;

View	Description
V$PROCESS	Contains information about the currently active processes
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system
V$SESSION	Lists session information for each current session
V$SESS_IO	Contains I/O statistics for each user session
V$SESSION_LONGOPS	Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.
V$SESSION_WAIT	Lists the resources or events for which active sessions are waiting
V$SYSSTAT	Contains session statistics
V$RESOURCE_LIMIT	Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH	Contains statistics for nonparent latches and summary statistics for parent latches

------------------

CPU Alerts

CPU Overload Alert – We use vmstat to monitor for high run queue values and track periods when the Oracle database server is overloaded.

----------------------
display the number of CPUs on their Oracle server:
  

§ Linux Command:

cat /proc/cpuinfo|grep processor|wc –l

§ Solaris Command:

psrinfo -v|grep "Status of processor"|wc –l

§ AIX Command:

lsdev -C|grep Process|wc –l

§ HP/UX Command:

ioscan -C processor | grep processor | wc -l

win; tasklist /S %computername% /SVC /FO CSV > C:\tasklist.csv

RAM Alerts

High RAM Page and scan rate – In UNIX, we constantly monitor for RAM memory high page-in values and high scan rates. overloads and provide our clients with complete reports showing time when the Oracle database server experiences shortages of RAM memory.

vmstat 2

tail -f /var/log/messages

tail -f /var/log/messages | grep failed ile messages log dosyası içinde failed kelimesi arayabilirsiniz.

swapper: page allocation failure. order:0, mode:0×20

benzer mesajlar alıyorsanız sistem RAM i yetersiz demektir. Şu andaki RAM kullanımını görmek içinse:

# free -m
total used free shared buffers cached
Mem: 2010 1844 166 0 12 1290
-/+ buffers/cache: 541 1469
Swap: 3967 0 3967

Total başlığı sistemdeki toplam RAM miktarını, used başlığı ise kullanılan miktarı göteriyor. Ancak buradaki asıl önemli konu cache in ne kadarının kullanıldığı. Çünkü uygulamaların ne kadar hafıza kullandığı bu değerden anlaşılır. En iyi performans için, kullanılan cache miktarı toplam bellekten daha az olmalıdır. (541 <>

İşletim sistemleri RAM canavarlarıdır, sistemde ne kadar çok RAM varsa o kadar geniş geniş çalışırlar. Örneğin benim sistemimdeki 2 GB RAM’in 1.8 GB ı kullanılmış durumda. 1 GB iken de yaklaşık 900 MB. ı kullanımda idi. Yani işletim sistemi RAM kullanımında “ayağını yorganına göre uzatıyor”.

ps -aux komutu ile çalışan uygulamaların sistem kaynağı kullanımını (o an için) görebilirsiniz.

vmstat komutu takas belleği* kullanılıyorsa, ilgili istatistikleri görüntüler. (swap, Takas Bellek, sistem belleğinin yetişmemesi durumunda sabit disk üzerinde bir bölümün RAM bellek gibi kullanılması)

# vmstat 1 2
vmstat 1 2 -> 1 değeri 1 sn aralıkla, 2 ise 2 kez güncel değerleri getirir.

si (swap in) ve so (swap out) değerleri 2–3 interval in ardından 0 a inmeliler. bi (swap e giren byte, byte in) ve bo (byte out) değerleri ise çok büyük değerlerde olmamalı. Sondan bir önceki sütundaki id (CPU idle, cpu nun boşta olma zamanı) değeri ise 100 e ne kadar yakında o kadar iyidir. (İşlemcinin o kadar serbest olduğunu gösterir)
http://www.trsecurity.net/

Disk Alerts

Low free space in archived redo log directory - If your archived redo log directory is becoming full, we e-mail an alert to your DBA so they can add space before the Oracle database hangs.

UNIX mount point - The script checks all UNIX mount points for Oracle, including the UNIX Oracle home directory. Because most Oracle9i databases use the autoextend option for data files, we must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than the specified threshold, an e-mail alert will be sent to the DBA.

Oracle Database Alerts

We use leading-edge technology to monitor every component of your Oracle database, and we offer the most comprehensive and sophisticated Oracle alert monitoring anywhere. We are proud that we detect potential problems before they cause a production outage.

Trace and Dump file Alert – We can immediately detect and e-mail Oracle trace or dump files for fast problem resolution.

Alert log messages - Every minute, we check for new alert log messages and e-mail them to your DBA staff.

Object cannot extend Alert - This report will alert the Oracle DBA whenever an Oracle table or index does not have room to take another extent

Tablespace > 95% free Alert - This report sends an e-mail alert whenever any tablespaces contain less space than specified and the datafile is not using the autoextend option.

Object > nnn extents Alert - This report detects tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined, an e-mail alert will be sent to the DBA.

Hot File Alert - We report on all files whose read or write I/O are greater than (25 percent or 50 percent or 75 percent) of total I/O. This code compares the individual I/O for a file from stats$filestatxs with the overall I/O for the period in stats$sysstat.

Data Buffer Hit Ratio Alert - This report detects those times when the data buffer hit ratio falls below the preset threshold. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools.

High Disk Sorts Alert - We detect whenever a disproportional amount od disk sorts occurs. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it is also useful for ensuring that sort_area_size is set to an optimal level.

I/O Wait Alert - We detect any Oracle files with an excessive amount of wait activity. If the number of I/O waits appears excessive, we investigate the cause of the waits. High I/O waits on files are commonly associated with buffer busy waits, and may be caused by tables with too few freelists or freelist groups.

Buffer Busy Wait Alert - For non-ASSM datafiles, we detect high buffer busy waits.

Redo Log Space Requests Alert - If constantly monitor for high redo log space requests and make appropriate adjustments to the log_buffer parameter. A high number of redo log space requests indicates a high level of update activity, and the Oracle log buffer is having trouble keeping up with the volume of redo log images.

Chained Row Alert - We monitor for continued row fetches greater than 10,000/hr.

Shared Pool Contention Alert - We monitor for enqueue deadlocks that can indicate contention within the shared pool and locking related problems. Enqueue deadlocks are associated with the deadly embrace condition where one task is locking resources and another task that is holding resources requests a lock on the resources of the first task.

Full Table Scan Alert - We can monitor for large-table full-table scans within your library cache to alert you to potential SQL tuning.

Background Wait Alert - We monitor Oracle to find events with high waits. When background events experience more than 100 time-outs/hr, you may have a locking problem.

System Waits Alert - We monitor the Oracle event structures to locate events with excessive wait times. If you experience waits on latch free, enqueue, LGWR waits, or buffer busy waits, you need to locate the cause of the contention.

Library Cache Misses Alert - We monitor for excessive library cache miss ratios. When the library cache miss ratio is too high, we increase the shared_pool_size.

Database Writer Contention alert - We monitor Oracle for high values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 (or your Database Writer checkpoint waits, we look at tuning the database writer processes.

Data Dictionary Miss Ratio Alert - We also monitor the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio.