Oracle

3 Mart 2016

Index Kullanımını Takip Etmek

Yaratılan ama kullanılmayan index'ler sadece alan kaybı yaratmaz. Aynı zamanda DML performansınızı da etkiler. Elbetteki kimse, kullanılmasın diye index oluşturmaz. Fakat zaman içinde gelen sorgular değişebilir; bir zamanlar kullandığınız index'lere artık gerek kalmayabilir. Bu tarz durumlarda karar vermek zor olabiliyor. Index'i yaratan kişiyi bulamıyorsunuz ya da kimse risk almak istemiyor.

Bugün iki farklı yerde bu konu tesadüfen karşıma çıkınca, ufak bir yazı yazmak istedim. Merak etmeyin; index'leri takip edebilmek için yapabileceğimiz şeyler var.

Öncelikle test için RQUSER diye 'DUMMY' bir kullanıcı oluşturacağız ve bu kullanıcı altında index içeren bir de tablo yaratacağız. Akabinde oluşturduğumuz index'i ALTER INDEX ... MONITORING USAGE komutuyla takibe alıyoruz.

-- DUMMY BIR KULLANICI OLUSTURUP, GEREKEN HAKLARI VERIYORUZ
create user rquser identified by welcome1;
grant resource to rquser, unlimited tablespace to rquser;
-- ARDINDAN KULLANICI ALTINDA, BIR TABLO VE BU TABLOYA AIT INDEX YARATILIR
CREATE TABLE RQUSER.DUMMY_TABLE AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX RQUSER.DUMMY_IDX01 ON RQUSER.DUMMY_TABLE( OBJECT_ID );
-- INDEX IZLEME ISLEMI BASLATILIYOR
ALTER INDEX RQUSER.DUMMY_IDX01 MONITORING USAGE;

Aşağıda yazdığım sorguyu kullanarak, index kullanım durumunu görebiliriz.

-- INDEX KULLANIMINI ASAGIDAKI GIBI SORGULAYABILIRIZ
-- 12c ONCESI VERITABANLARI ICIN V$OBJECT_USAGE VIEW'I KULLANILMALIDIR.
set lines 200 trims on
column index_name format a40
column used format a10 HEADING 'USED'
column start_monitoring 

select 	owner||'.'||index_name as index_name, 
		used, 
		start_monitoring, 
		end_monitoring 
from 	DBA_OBJECT_USAGE;

-- INDEX IZLEME AKTIF AMA HENUZ HIC KULLANILMAMIS
-- INDEX_NAME                       USED       START_MONITORING    END_MONITORING    
-- -------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01               NO         03/03/2016 18:24:48

-- INDEX KULLANACAK BIR SORGU CALISTIRIYORUZ
-- SORGU CALISTIKTAN SONRA, ARTIK USED SUTUNUNU YES GEREKIYORUZ
select count(*) from rquser.dummy_table where object_id = 1;

-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              YES        03/03/2016 18:24:48           

-- DILERSEK INDEX MONITOR'LEME ISLEMINI ASAGIDAKI GIBI KAPATABILIRIZ
alter index rquser.dummy_idx01 nomonitoring usage;
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              YES        03/03/2016 18:24:48 03/03/2016 18:36:05

Şimdi de kapattığımız monitoring işlemini tekrar aktive edelim. Tekrar aktivasyon yapıldığında, kullanım durumu 'NO' oluyor. Index'i kullanacak bir sorgu gelene kadar da, USED sütunu aynı şekilde kalıyor.

-- TEKRAR INDEX IZLEME BASLATMAK ISTERSEK
-- AYNI IFADEYI CALISTIRIYORUZ
alter index rquser.dummy_idx01 monitoring usage;
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              NO         03/03/2016 18:38:12          

-- INDEX KULLANACAK SORGUYU TEKRAR CALISTIRIYORUZ
-- SORGU CALISTIKTAN SONRA, ARTIK USED SUTUNUNU YES GEREKIYORUZ
select count(*) from rquser.dummy_table where object_id = 1;
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              YES        03/03/2016 18:38:12

Peki... monitoring'i kapatıp açmadan, sayacımızı sıfırlamak istersek, ne yapabiliriz? Her seferinde NOMONITORING ardından MONITORING yazmak zahmetli olurdu. Bunun yerine, tekrar MONITORING dememiz yeterli oluyor. ( Monitoring Index Usage bağlantısında bu konuyla ilgili açıklama bulabilirsiniz.)

-- EGER USED PARAMETRESINI SIFIRLAMAK ISTERSEK, ASAGIDAKI GIBI YAPABILIYORUZ
alter index rquser.dummy_idx01 monitoring usage;
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              NO         03/03/2016 18:40:01

Veritabanı kapatılıp, açılırsa da, bir kayıp olmuyor.

-- SORGUYU TEKRAR CALISTIRIYORUZ VE INDEX KULLANILIYOR
-- AKABINDE VERITABANINI KAPATIP ACIYORUZ.
select count(*) from rquser.dummy_table where object_id = 1;
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              YES        03/03/2016 18:40:46             

-- VERITABANI KAPATILIP ACILIYOR
CDBA:SQL> alter pluggable database MYPDB close immediate;
CDBA:SQL> alter pluggable database MYPDB open;

-- VERITABANI YENIDE BASLASA DA, DEGERLER KALIYOR.
-- INDEX_NAME                      USED       START_MONITORING    END_MONITORING    
-- ------------------------------- ---------- ------------------- -------------------
-- RQUSER.DUMMY_IDX01              YES        03/03/2016 18:40:46

Diyelim ki, bir süre gözlemledik ve index'in kullanılmadığına karar verdik. Sonraki aşamada ne yapabiliriz? Direkt index'i drop etmek iyi bir seçenek olmayabilir. Kızgın bir kullanıcı çıkıp geldiğinde, sen şurada bekle, ben index'i baştan oluşturayım demek sıkıntılı olacaktır. Veri miktarı büyüdükçe de, bekleme süresi artar. Bunun yerine, index'i hiç bozmadan, erişime kapatabiliriz. Bunun için ALTER INDEX ... INVISIBLE komutunu kullanıp, gerektiğinde de, index'i tekrar görünür hâle getirebiliriz.

-- INDEX INVISIBLE YAPILIYOR
ALTER INDEX RQUSER.DUMMY_IDX01 INVISIBLE;

select count(*) from rquser.dummy_table where object_id = 1;
-- ----------------------------------------------------------------------------------
-- | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-- ----------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT   |             |     1 |     5 |   416   (1)| 00:00:01 |
-- |   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
-- |*  2 |   TABLE ACCESS FULL| DUMMY_TABLE |     1 |     5 |   416   (1)| 00:00:01 |
-- ----------------------------------------------------------------------------------

Index'i gizli hâle aldık ama henüz drop etmedik. Günün birinde bir kullanıcı çıkıp, performans sorunu olduğunu söyledi. Sorunun kapatılan index'ten olduğunu düşünmüyorsunuz; fakat index'i kullanıp, test etmek lâzım. Bu durumda, genele açmadan, sadece kendi oturumunuzdan index'i görülebilir yapabilirsiniz.

SQL> alter session set optimizer_use_invisible_indexes=TRUE;

select count(*) from rquser.dummy_table where object_id = 1;
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| DUMMY_IDX01 |     1 |     5 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Çok hoşuma giden bir diğer özellikle yazımızı burada noktalayalım. Büyük bir tablonuz olduğunu düşünelim. Bu büyük tablo üzerinde bir index yaratmayı düşünüyorsunuz ama gelecek sorguların bunu kullanıp-kullanmayacağından emin değilsiniz. Bu index'i oluşturmadan, optimizer'ın davranışını nasıl kestirebiliriz? Bunun da güzel bir yolu var: Sanal Index yaratmak... Kullanımına gelirsek,

-- ONCELIKLE ASAGIDAKI SORGUNUN PLANINA BAKIYORUZs

select count(*) from rquser.dummy_table where OBJECT_NAME = 'DENEME';
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    25 |   416   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| DUMMY_TABLE |     2 |    50 |   416   (1)| 00:00:01 |
----------------------------------------------------------------------------------

CREATE INDEX RQUSER.DUMMY_IDX02 ON RQUSER.DUMMY_TABLE( OBJECT_NAME ) NOSEGMENT;

-- INDEX BOYUTU KONTROL EDILIYOR
select count(*) from dba_indexes where index_name='DUMMY_IDX02'

alter session set "_use_nosegment_indexes"=true;
select count(*) from rquser.dummy_table where OBJECT_NAME = 'DENEME';
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    25 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| DUMMY_IDX02 |     2 |    50 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Yaratılan index, aslında fiziksel anlamda hiç yaratılmadı. Fakat dictionary tanımı mevcut. Bu nedenle işiniz bittiğinde, isim çakışmaması olmaması için index'i drop etmek doğru olur. Standart bir index drop işleminden hiçbir farkı olmadığını aşağıda göreceksiniz:

SQL> DROP INDEX RQUSER.DUMMY_IDX02;
Index dropped.

Toparlarsak,

Kullanılmadığından şüphelendiğiniz Index'leri, monitoring ile takibe alabilirsiniz.
Belirli bir sürenin sonunda, kullanılmayan index'leri, görünmez (invisible) yapabilirsiniz. Invisible yaptığınızda, index drop edilmeden kullanıma kapatılır.
Yeterli süre sonunda hâlâ şikayet almadıysanız, index'i drop edebilirsiniz. Bu sayede hem yerden kazanır, hem de DML performansını iyileştirirsiniz.
Bir index'i oluşturmanın faydalı olacağını düşünüyor ama oluşturmadan önce simülasyonunu yapmak istiyorsanız, NOSEGMENT ile bunu kolayca gerçekleştirebilirsiniz.

Çağatay ÇEBİ