Geçtiğimiz hafta "Oracle Database 11g: Administration Workshop II" eğitimindeyken bir katılımcı arkadaşın sorusunu sizinle paylaşmak ve cevaplamak istiyorum.
Soru: "Indeks clustering factor nedir, ne anlama gelir ve yüksek ya da düşük olduğu zaman ne yapabiliriz?"
Cevap: Index'in tanımlandığı kolonun satır sıralamasının ölçüsüdür (örnek kitap sayfa numaraları). Ne kadar çok satır sıralaması varsa, clustering factor o kadar düşüktür. Peki biz indekslerde ne ararız? Düzgün güncellenmiş ve mümkün olduğunca sıralı mantıksal yapraklar. Bu durumda ne kadar düşük clustering factor, o kadar çok fayda diyebiliriz.
CBO bir plan üzerinde titizlikle çalışırken öncelikle satırlara değil, erişeceği blokların miktarına bakar. Bu, bizim milyarlarca kayıt istememizle ilgili değil, ne kadar çok bloğa erişerek milyarlarca kaydı getireceği ile ilgilidir. Bu durumda index clustering factor, "index scan" yani indeks üzerinden bir plan taraması yaparken devreye girer ve CBO'nun indeks mi yoksa "full table scan" mi kararını almasında etkili olur. CBO zaten FTS yapacaktır ancak indeksin hesaplanan maliyeti daha düşük ise CBO indeksi tercih edecektir. Burada tabii indeks ve tablonun istatistiklerinin ne kadar güncel olup olmadığı da önemlidir. CBO bloklar topluluğu ile çalışacaktır ama bizim tablomuzda bir milyar kayıt varken ona "tablomuzda 1 milyon kayıt var" dersek yalan söylemiş oluruz. Bu yalan da döner dolaşır bizi bulur.
Düşük bir clustering factor değerine sahip bir indeks varsa, tekil satırların bir tabloda daha az blokta tutulduğunu ifade edebiliriz. Dolayısıyla yüksek clustering factor'e sahip bir indekse sahipseniz, indekslenen kolonlarınıza ait satırlar daha fazla dağınık blok dizilişi ile sunulacaktır. Çok kötü, CBO bunu beğenmeyebilir ama FTS yerine kötünün iyisi olsun diyerek yine indeks tercih etme ihtimali de vardır.
Örnek Olay
Veritabanı Blok #1 Veritabanı Blok #2 Veritabanı Blok #3 Veritabanı Blok #4
----------------------------------------------------------------------------------------
O O O O G G G G A A A A N N N N
Yukarıdaki indeksin clustering factor değerinin düşük olduğunu söyleyebilirim çünkü bana sadece 'A' kaydını getir dediğim zaman sırasıyla nerelere bakması gerektiğini biliyor olacak. Ancak;
Veritabanı Blok #1 Veritabanı Blok #2 Veritabanı Blok #3 Veritabanı Blok #4
----------------------------------------------------------------------------------------
O G A N N A G O O A N G N G A O
bu şekilde bir dağılıma sahip indeks varsa clustering factor yüksek olacaktır. Şimdi 'A'ları istiyorum dediğim zaman CBO'nun hangi bloklara erişmesi gerektiği ortada.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, PARTITIONED, BUFFER_POOL
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'INSTANCES_PK';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME BLEVEL
------------------------------ --------------------------- ------------------------------ ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
----------- ------------- ----------------- ---------- ----------- ---------
DEGREE PAR BUFFER_
---------------------------------------- --- -------
INSTANCES_PK NORMAL CODESI 1
4 2211 190 2211 2211 08-JAN-11
1 NO DEFAULT
2211 adet distinct_keys ve num_rows değerine sahip INSTANCES_PK indeksinin clustering factor'ü 190 olarak belirlenmiş. Fena değil.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, PARTITIONED, BUFFER_POOL
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'INSTANCES_PK';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME BLEVEL
------------------------------ --------------------------- ------------------------------ ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
----------- ------------- ----------------- ---------- ----------- ---------
DEGREE PAR BUFFER_
---------------------------------------- --- -------
INSTANCES_PK NORMAL CODESI 1
4 2211 190 2211 2211 01-FEB-11
1 NO DEFAULT
Burada şansımız yerine gitti ve 08.01.11 ile 01.02.11 arasında indeks üzerinde bir farklılık olmadığını gözlemledik.
SQL> SELECT COUNT(*)
2 FROM INSTANCES;
COUNT(*)
----------
2211
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','INSTANCES_PK','AIRCOM')
2 FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX INSTANCES_PK ON INSTANCES
(PRID)
LOGGING
TABLESPACE CODESI
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
SQL> SELECT INSTID, PROGID, MACHINEID
2 FROM INSTANCES
3 WHERE PRID = '102211836';
INSTID PROGID MACHINEID
---------- ---------- ----------
836 211 102
Execution Plan
----------------------------------------------------------
Plan hash value: 1283788961
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INSTANCES | 1 | 22 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INSTANCES_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PRID"='102211836')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
clustering factor'ü düşük olan bir indeks ile oldukça düşük maliyetli sorgulara sahip olabilirsiniz.
SQL> SELECT /*+ FULL (INSTANCES) */ INSTID, PROGID, MACHINEID
2 FROM INSTANCES
3 WHERE PRID = '102211836';
INSTID PROGID MACHINEID
---------- ---------- ----------
836 211 102
Execution Plan
----------------------------------------------------------
Plan hash value: 2279036413
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INSTANCES | 1 | 22 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PRID"='102211836')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Clustering factor'ü arttırabilecek bir diğer faktör ise tablonun sürekli olarak taşınması veya yeniden oluşturulmasıdır. Tabloya girilen her yeni kayıt ile indeks kendine ait olan mantıksal yapraklarını günceller ve daha fazla clustered olma eğilimine girer. Bu alışkanlığı sürdürmeye devam ederseniz ve tablo bir canavara dönüşürse, clustering factor'ü de yerinde tutamazsınız. Yanlış anlaşılmayı önlemek için aşağıda örnekler göstereceğim ve bir tablonun her zaman yeniden yaratılması veya içeriğinin değişmesinin clustering_factor parametresini arttırmadığı ama o yönde olduğunu görebilirsiniz. Tablonun durumu zaten felaket ise baştan yaratılmasının yine zaten kötü olan clustering factor parametresini biraz da olsa aşağı çekebileceğini söyleyebilirim. Yine bilinen yanlışlardan birisi de "index'i rebuild ettim, clustering factor azalabilir". Peki, tablonun içeriği değişti mi? Tablodaki verilerinizin tutulduğu blokların dağınıklığı ve düzeni değişti mi? O zaman indeks rebuild sırasında nasıl ve ne boyutta bir iyileştirme bekleyebilirsiniz?
Şu şekilde bir örnek verirsem clustering factor'ün nasıl artabileceğini daha iyi anlamış olabilirsiniz. Bir tablo düşünün ve içerisinde sırasıyla girdiğiniz 100 kayıt olsun. Bu sıra 1'den 100'e kadar olan değerlere göre yapılmış olsun. Bu aşamadan sonra yeni bir karar alındığını ve 100'den sonra bütün her şeyin dağınık olarak girileceği bilgisi geldi. Daha da kötüsü eskilerin sürekli silineceği ve yenilerinin girileceği bilgisi de verildi. Clustering factor bugüne kadar düşüktü ancak bundan sonra düşük olmayacak!
SQL> conn ogan/password
Connected.
SQL> CREATE TABLE OGAN_DENEME
2 (
3 USERID NUMBER
4 );
Table created.
SQL> BEGIN
2 FOR I IN 1..100
3 LOOP
4 INSERT INTO OGAN_DENEME VALUES (I);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*)
2 FROM OGAN_DENEME;
COUNT(*)
----------
100
SQL> CREATE INDEX IDX_OGAN_DENEME ON OGAN_DENEME (USERID);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('OGAN','OGAN_DENEME',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN;
SQL> SELECT USERID
2 FROM OGAN_DENEME
3 WHERE USERID = '100';
Execution Plan
----------------------------------------------------------
Plan hash value: 2941290445
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OGAN_DENEME | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("USERID"=100)
SQL> SET AUTOTRACE OFF;
SQL> /
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
100 1
--> İndeks üzerinde mantıksal yaprak değişikliğini henüz zorlamadım, sadece rastgele değerleri yine tablonun sonuna ekledim. Şimdi bakalım;
SQL> DELETE OGAN_DENEME
2 WHERE ROWNUM < 101;
100 rows deleted.
SQL> BEGIN
2 FOR I IN 1..1000
3 LOOP
4 INSERT INTO OGAN_DENEME VALUES (DBMS_RANDOM.RANDOM);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> DELETE OGAN_DENEME
2 WHERE ROWNUM < 101;
100 rows deleted.
SQL> BEGIN
2 FOR I IN 1..1000
3 LOOP
4 INSERT INTO OGAN_DENEME VALUES (DBMS_RANDOM.RANDOM);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> DELETE OGAN_DENEME
2 WHERE ROWNUM < 1372;
1371 rows deleted.
SQL> BEGIN
2 FOR I IN 1..112341
3 LOOP
4 INSERT INTO OGAN_DENEME VALUES (DBMS_RANDOM.RANDOM);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> DELETE OGAN_DENEME
2 WHERE ROWNUM < 4122;
4121 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*)
2 FROM OGAN_DENEME;
COUNT(*)
----------
108849
SQL> SELECT NUM_ROWS, CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IDX_OGAN_DENEME';
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
108849 107686
Zavallı IDX_OGAN_DENEME objesinin başına gelenleri görüyorsunuz. Başından 2 tane "delete" operasyonu geçmesine rağmen clustering_factor değeri tavan yaptı ve neredeyse toplam kayıt sayısına eşit seviyeye çıktı. Yukarıdaki örnekte tablomuzda tek bir kolon olduğu için clustering factor değerinin büyük olması aslında CBO'yu çokta fazla ilgilendirmedi. CBO, FTS yerine indeks üzerinden "index range scan" yapmayı hala daha mantıklı buluyor.
SQL> DROP TABLE OGAN_DENEME PURGE;
Table dropped.
SQL> CREATE TABLE OGAN_DENEME
2 AS
3 SELECT * FROM ALL_INDEXES;
Table created.
SQL> SELECT COUNT(*)
2 FROM OGAN_DENEME;
COUNT(*)
----------
3393
--> OGAN_DENEME objesi üzerinde composite bir index yaratıyorum;
SQL> CREATE INDEX IDX_OGAN_DENEME ON OGAN_DENEME (NUM_ROWS, CLUSTERING_FACTOR);
Index created.
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('OGAN','IDX_OGAN_DENEME');
PL/SQL procedure successfully completed.
SQL> SELECT NUM_ROWS, CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IDX_OGAN_DENEME';
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
3332 1696
SQL> DELETE OGAN_DENEME;
3393 rows deleted.
SQL> INSERT INTO OGAN_DENEME
2 SELECT * FROM ALL_INDEXES;
3394 rows created.
SQL> DELETE OGAN_DENEME;
3394 rows deleted.
SQL> INSERT INTO OGAN_DENEME
2 SELECT * FROM ALL_INDEXES;
3394 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('OGAN','IDX_OGAN_DENEME');
PL/SQL procedure successfully completed.
SQL> SELECT NUM_ROWS, CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IDX_OGAN_DENEME';
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
3333 1716
--> Şansımızı biraz daha zorladık ve sürekli aynı şeyleri silmeme ve yeniden insert operasyonu ile tabloya girmeme rağmen clustering factor arttı.
SQL> DELETE OGAN_DENEME;
3394 rows deleted.
SQL> INSERT INTO OGAN_DENEME
2 SELECT * FROM ALL_INDEXES ORDER BY REVERSE(CLUSTERING_FACTOR);
3394 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('OGAN','IDX_OGAN_DENEME');
PL/SQL procedure successfully completed.
SQL> SELECT NUM_ROWS, CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IDX_OGAN_DENEME';
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
3333 1200
--> Clustering factor'ü biraz da olsa düşürebildim.
Bir ufak ama önemli bilgiyi daha vermek istiyorum. Clustering factor bir indeks arama tipi olan "index range scan" için değişken olarak varsayılır. Yani indeks üzerindeki mantıksal yapraklarda yapılacak net bir indeks değer taraması sırasında bloklar üzerindeki dağınık değer dağılımı (yüksek clustering factor) etkili olacaktır.
İyi Clustering Factor;
Kötü Clustering Factor;
İyi çalışmalar dilerim.
Ogan
1 yorum:
Elinize sağlık. Detay gibi görünen ama önemli bir konuya değinmişsiniz. Bu tip ileri düzey konularda yazıların çoğalması dileğiyle. Türkçe kaynakta kurulum makalelerinden öteye geçmek lazım artık.
Yorum Gönder