![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/exadata-hcc.jpg\")
<\/p>\nMerhaba Arkada\u015flar,<\/p>\n
Bu yaz\u0131mda Exadata Storage Server \u0131n muhte\u015fem \u00f6zelli\u011fi olan Hybrid Columnar Comression \u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n
<\/p>\n
<\/p>\n\n
Hybrid Columnar Compression (HCC)<\/strong><\/p>\n Halihaz\u0131rda Basic ve Advance Compression \u00f6zellikleri bulunan Oracle, Exadata makinesiyle beraber yeni bir Compression tekni\u011fi daha ortaya koydu. HCC \u00e7ok y\u00fcksek seviyede veri s\u0131k\u0131\u015ft\u0131rma yaparak \u00f6nemli oranda bir storage kazan\u0131m\u0131n\u0131 beklenin \u00fcst\u00fcnde bir I\/O performans\u0131yla sunmaktad\u0131r.<\/p>\n HCC ile s\u0131k\u0131\u015ft\u0131rma y\u00f6ntemi sadece Exadata da de\u011fil Oracle \u0131n Storage Appliance s\u0131 olan ZFS dede vard\u0131r. ZFS Storage Appliance Genelde backup ama\u00e7l\u0131 kullan\u0131l\u0131r ve bu konuda \u00e7ok ba\u015far\u0131l\u0131d\u0131r. Hem backup al\u0131rken I\/O performans\u0131 iyidir hemde s\u0131k\u0131\u015ft\u0131rma \u00f6zelli\u011fi sayesinde alandanda kar edilmi\u015f oluyor.<\/p>\n <\/p>\n\n <\/p>\n HCC i\u00e7in tercih edilen se\u00e7ene\u011fe g\u00f6re 10 kattan 50 kata kadar s\u0131k\u0131\u015ft\u0131rma yap\u0131l\u0131p Storagedan \u00e7ok ciddi kazan\u00e7 sa\u011flanabilmektedir. HCC datay\u0131 mant\u0131ksal s\u0131k\u0131\u015ft\u0131rma \u00fcnitelerinde saklamak i\u00e7in kolon ve sat\u0131r bazl\u0131 metodlar\u0131 kullanmaktad\u0131r. S\u0131k\u0131\u015ft\u0131rma \u00fcnitesi, her bir blo\u011funda a\u015fa\u011f\u0131da g\u00f6r\u00fcld\u00fc\u011f\u00fc gibi birden fazla blok saklayan bir \u00fcnitedir.<\/p>\n HCC veritaban\u0131nda tablo, partition ve tablespace leri datawarehouse<\/strong> ve archive<\/strong> modda olmak \u00fczere 2 farkl\u0131 y\u00f6ntemle s\u0131k\u0131\u015ft\u0131rabiliyor. Her bir y\u00f6ntemde kendi i\u00e7inde high<\/strong> ve low<\/strong> olmak \u00fczere 2 farkl\u0131 y\u00f6ntem sunar bu y\u00f6ntemler ne kadar storage dan kazand\u0131\u011f\u0131n\u0131 ve performans\u0131n ne seviyede olaca\u011f\u0131n\u0131 belirler.<\/p>\n <\/p>\n\n <\/p>\n <\/p>\n HCC index ve lob segmentlere uygulanamaz. \u00c7ok s\u0131k update g\u00f6ren tablolara HCC nin uygulanmas\u0131 tavsiye edilmez. Sonu\u00e7 olarak s\u0131k\u0131\u015ft\u0131r\u0131lm\u0131\u015f olan bir veri update edildi\u011fi zaman b\u00fct\u00fcn s\u0131k\u0131\u015ft\u0131rma \u00fcnitesi kilitlenecek ve i\u00e7indeki veri s\u0131k\u0131\u015ft\u0131r\u0131lma olmayan yada OLTP olan bir yere ta\u015f\u0131nacakt\u0131r. Datawarehouse ve archive olmak \u00fczere 2 farkl\u0131 s\u0131k\u0131\u015ft\u0131rma y\u00f6ntemi var dedik bunlar\u0131 detayland\u0131ral\u0131m.<\/p>\n S\u0131k\u0131\u015ft\u0131rma i\u015flemi yap\u0131lmadan \u00f6nce en b\u00fcy\u00fck motivasyon Storagedan m\u0131 Performans tanm\u0131 buna karar verilmelidir. Yani Storage taraf\u0131nda hi\u00e7 yer kalmam\u0131\u015f ve y\u00f6netim yeni storage al\u0131m\u0131na m\u00fcsaade etmiyorsa yap\u0131lacak \u015fey Archive Metoduyla s\u0131k\u0131\u015ft\u0131rma yapmakt\u0131r. Burda ben sahada 30 kat\u0131n \u00fcst\u00fcn\u00fc g\u00f6rd\u00fcm, yani 30 TB l\u0131k tablolar\u0131 \u00e7ok rahat 1 TB a indirebilirsiniz ki bu muhte\u015fem bir s\u0131k\u0131\u015ft\u0131rma ve storage kazanc\u0131 demektir. Archive metodu se\u00e7ti\u011finizde s\u0131k\u0131\u015ft\u0131rma \u00e7ok iyi ama performans d\u00fc\u015f\u00fck olur. Archive metodunda Archive Low, Archive High dan daha performansl\u0131d\u0131r, biraz olsun performans isteyenler bunu deneyebilir.<\/p>\n <\/p>\n <\/p>\n\n <\/p>\n <\/p>\n Hem s\u0131k\u0131\u015ft\u0131rma olsun hemde performans \u00e7ok d\u00fc\u015fmesin diyosan\u0131z Query High ve Query Low metodlar\u0131n\u0131 kullanabilirsiniz. Bunlarda da ben sahada 10 kat\u0131n \u00fcst\u00fcnde bir s\u0131k\u0131\u015ft\u0131rma g\u00f6rd\u00fcm yani bu y\u00f6ntemde nearly real time performance dedi\u011fimiz sanki s\u0131k\u0131\u015ft\u0131r\u0131lmam\u0131\u015f gibi performans bulabilirsiniz. Burda da Query High derseniz Query Low dan daha \u00e7ok s\u0131k\u0131\u015ft\u0131rma yapar fakat performans olarak Query Low dan daha d\u00fc\u015f\u00fckt\u00fcr.<\/p>\n <\/p>\n Mevcut bir tabloyu Warehouse query low metoduyla s\u0131k\u0131\u015ft\u0131rmak i\u00e7in a\u015fa\u011f\u0131daki komut kullan\u0131l\u0131r.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI MOVE COMPRESS FOR QUERY LOW;<\/em><\/p>\n Mevcut bir tabloyu Warehouse query high metoduyla s\u0131k\u0131\u015ft\u0131rmak i\u00e7in a\u015fa\u011f\u0131daki komut kullan\u0131l\u0131r.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI MOVE COMPRESS FOR QUERY HIGH;<\/em><\/p>\n Mevcut bir tabloyu Archive low ile s\u0131k\u0131\u015ft\u0131rmak i\u00e7in a\u015fa\u011f\u0131daki komut kullan\u0131l\u0131r.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI COMPRESS FOR ARCHIVE LOW;<\/em><\/p>\n Mevcut bir tabloyu Archive High ile s\u0131k\u0131\u015ft\u0131rmak i\u00e7in a\u015fa\u011f\u0131daki komut kullan\u0131l\u0131r.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI COMPRESS FOR ARCHIVE LOW;<\/em><\/p>\n <\/p>\n <\/p>\n\n <\/p>\n Mevcut bir partition\u0131 s\u0131k\u0131\u015ft\u0131rmak i\u00e7in a\u015fa\u011f\u0131daki komut kullan\u0131l\u0131r.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI MODIFY PARTITION FATURA_01022018 COMPRESS FOR ARCHIVE LOW;<\/p>\n <\/p>\n Compress li tabloyu a\u015fa\u011f\u0131daki komutla compress i iptal edilebilir.<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI NOCOMPRESS;<\/p>\n SQL> ALTER TABLE MEHMET.DEVECI MOVE NOCOMPRESS;<\/p>\n <\/p>\n Hangi tablo yada partition \u0131n nas\u0131l compress lendi\u011fini a\u015fa\u011f\u0131daki komutlarla sorgulayabiliriz.<\/p>\n SQL> SELECT owner,table_name,compress_for FROM dba_tables WHERE compression= ‘ENABLED’;<\/p>\n <\/p>\n SQL> SELECT owner,table_name,partition_name,compress_for FROM dba_tab_partitions WHERE compression= ‘ENABLED’;<\/p>\n SQL> SELECT rowid,owner,object_name FROM sales;<\/p>\n <\/p>\n SQL> SELECT dbms_compression.get_compression_type(‘USER’, ‘TABLENAME’, ‘ROW_ID’) FROM dual;<\/p>\n <\/p>\n <\/p>\n\n <\/p>\n \u015eimdi HCC Compression \u0131 ger\u00e7ek zamanl\u0131 bir test ile g\u00f6relim. \u00d6ncellikle a\u015fa\u011f\u0131daki gibi test ama\u00e7l\u0131 bir tablo olu\u015fturup yakla\u015f\u0131k 160 milyonluk data olu\u015fturuyorum.<\/p>\n Bu test tablosundan s\u0131ras\u0131yla Klasik, OLTP ve Exadata HCC y\u00f6ntemleriyle s\u0131k\u0131\u015ft\u0131r\u0131p insert i\u015flemiyle hem performans hemde s\u0131k\u0131\u015ft\u0131rma ve storage oran\u0131n\u0131 g\u00f6r\u00fcyor olaca\u011f\u0131z.<\/p>\n <\/p>\n\n <\/p>\n Exadata \u00f6ncesinde yada Non Exadata olan ortamlardaki Oracle veritabanlar\u0131nda a\u015fa\u011f\u0131daki klasik y\u00f6ntemle\u00a0 tabloyu s\u0131k\u0131\u015ft\u0131rabiliyoruz.<\/p>\n Klasik y\u00f6ntemle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 8:15 dk da insert etti.<\/p>\n OLTP Compression y\u00f6ntemiyle de a\u015fa\u011f\u0131daki gibi compressleyip ayn\u0131 insert i\u015flemini yap\u0131yoruz.<\/p>\n <\/p>\n\n <\/p>\n <\/p>\n OLTP y\u00f6ntemiyle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 22:52 dk da insert etti.<\/p>\n \u015eimdi Exadata ile gelen HCC y\u00f6ntemiyle s\u0131k\u0131\u015ft\u0131rma yapal\u0131m. \u00d6nce Query Low ile a\u015fa\u011f\u0131daki gibi yapal\u0131m.<\/p>\n Query Low y\u00f6ntemiyle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 07:12 dk da insert etti.<\/p>\n Query High ile compressleme i\u015flemini de a\u015fa\u011f\u0131daki gibi yap\u0131yoruz.<\/p>\n <\/p>\n\n <\/p>\n Query High y\u00f6ntemiyle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 07:56 dk da insert etti.<\/p>\n Archive Low y\u00f6ntemiyle compressleme a\u015fa\u011f\u0131daki gibi yap\u0131yoruz.<\/p>\n Archive Low y\u00f6ntemiyle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 08:16 dk da insert etti.<\/p>\n Archive High y\u00f6ntemiyle compressleme a\u015fa\u011f\u0131daki gibi yap\u0131yoruz.<\/p>\n <\/p>\n\n <\/p>\n Archive High y\u00f6ntemiyle compress lenmi\u015f tabloya 160 milyonluk test tablosunu 31:57 dk da insert etti.<\/p>\n \u015eimdi bu tablolar\u0131n segment size lar\u0131na a\u015fa\u011f\u0131daki gibi bak\u0131yoruz.<\/p>\n Bu sonu\u00e7larda her ne kadar Query High y\u00f6ntemi Archive lar kadar s\u0131k\u0131\u015ft\u0131rm\u0131\u015f olarak g\u00f6r\u00fcnsede \u00e7ok b\u00fcy\u00fck tablolarda veya farkl\u0131 tipte tablolarda fark ettirecektir. Archive y\u00f6ntemi Query y\u00f6ntemine g\u00f6re 2-3 kat daha fazla s\u0131k\u0131\u015ft\u0131rma yapar fakat performans olarak Query y\u00f6ntemi kadar performansl\u0131 de\u011fildir. Yukardaki s\u00fcrelerdende bunu g\u00f6rebilirsiniz.<\/p>\n Sonu\u00e7 olarak \u015funu g\u00f6rd\u00fck ki, 16GB l\u0131k bir tabloyu klasik y\u00f6ntemle s\u0131k\u0131\u015ft\u0131rd\u0131\u011f\u0131m\u0131zda 5500MB a d\u00fc\u015ft\u00fc yani yakla\u015f\u0131k 3 kat s\u0131k\u0131\u015ft\u0131rma yapt\u0131. OLTP y\u00f6ntemle s\u0131k\u0131\u015ft\u0131rma yapt\u0131k 6500MB a d\u00fc\u015ft\u00fc yine ortalama 2-3 kat s\u0131k\u0131\u015ft\u0131rma yapt\u0131.<\/p>\n <\/p>\n\n <\/p>\n <\/p>\n Fakat Exadata n\u0131n HCC sini kulland\u0131\u011f\u0131m\u0131zda Query Low da 2500MB a d\u00fc\u015ft\u00fc\u011f\u00fcn\u00fc g\u00f6rd\u00fck yani yakla\u015f\u0131k 6 kat s\u0131k\u0131\u015ft\u0131rma yapt\u0131. Query High da ise 500MB a kadar indirdi ki buda Ortalama 32 katl\u0131k bir s\u0131k\u0131\u015ft\u0131rma demektir.<\/p>\n Archive low ve High\u00a0 dada 32 katl\u0131k bir s\u0131k\u0131\u015ft\u0131rma yaparak 500MB a indi\u011fini g\u00f6rd\u00fck. Dedi\u011fim gibi bu e\u011fitim ortam\u0131nda haz\u0131rlad\u0131\u011f\u0131m test datas\u0131ndan dolay\u0131 Archive y\u00f6ntemiyle Query High ayn\u0131 s\u0131k\u0131\u015ft\u0131rm\u0131\u015f g\u00f6r\u00fcnsede asl\u0131nda Archive y\u00f6ntemi \u00e7ok daha b\u00fcy\u00fck datalarda her t\u00fcrl\u00fc Query high dan daha \u00e7ok s\u0131k\u0131\u015ft\u0131rma yapmaktad\u0131r.<\/p>\n Bu k\u0131sm\u0131 son derste anlatt\u0131\u011f\u0131m \u00f6rnekteki\u00a0s\u0131k\u0131\u015ft\u0131rma oran\u0131 \u00e7ok muazzam olunca koyal\u0131m dedim. A\u015fa\u011f\u0131daki \u00f6rnekte yukardaki \u00f6rnekler gibi s\u0131k\u0131\u015ft\u0131rma oranlar\u0131 \u00e7ok yak\u0131n \u00e7\u0131kmay\u0131p netle\u015fmesi i\u00e7in \u00e7ok b\u00fcy\u00fck data k\u00fcmesi olu\u015fturdum 1.28 milyar kadar ve bunun s\u0131k\u0131\u015ft\u0131rma sonu\u00e7lar\u0131 a\u015fa\u011f\u0131daki gibidir.<\/p>\n <\/p>\n\n <\/p>\n <\/p>\n <\/p>\n\n <\/p>\n <\/p>\n <\/p>\n\n <\/p>\n <\/p>\n\n <\/p>\n Yukardaki sonu\u00e7lara g\u00f6re Archive High nerdeyse 120 kat s\u0131k\u0131\u015ft\u0131rma yapm\u0131\u015f durumda, Archive low da 80 kata kadar s\u0131k\u0131\u015ft\u0131rma yapm\u0131\u015f durumdad\u0131r. S\u0131k\u0131\u015ft\u0131rma detaylar\u0131 ve bu tablolara yap\u0131lan OLTP s\u00fcrelerini yukarda bulabilirsiniz.<\/p>\n Sonu\u00e7 olarak Performanstan \u00f6d\u00fcn vermeyecek ki\u015filer i\u00e7in bile HCC Query Low ve High y\u00f6ntemi klasik y\u00f6ntemden yukardaki ayn\u0131 insert i\u015fleminde g\u00f6r\u00fcld\u00fc\u011f\u00fc gibi hem daha performansl\u0131 hemde daha \u00e7ok s\u0131k\u0131\u015ft\u0131rma yapmaktad\u0131r.<\/p>\n Son g\u00fcnlerin Esprisiyle kapatay\u0131m bu yaz\u0131y\u0131da. G\u00f6r\u00fcyorsunuz her\u015fey ortada g\u00f6r\u00fcyorsunuz anlatmama gerek yok zaten \ud83d\ude09<\/p>\n Esen kal\u0131n…<\/p>\n <\/p>\n\n <\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/oracle-zfs.jpg\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/exadata-hcc-compression-unit.png\")
<\/p>\n\n
![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/test-table-create.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/classic1.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/oltp.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/query-low.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/query-high.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/archive-low.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/archive-high.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/sonuc3a7.png\")
<\/p>\n![\"Table](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/table-create.png\")
<\/p>\n![\"Table](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/table-create2.png\")
<\/p>\n![\"Classic\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/classic2.png\")
<\/p>\n![\"Query](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/query-low1.png\")
![\"Query](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/query-high1.png\")
<\/p>\n![\"Archive](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/archive-high1.png\")
![\"Sonu\u00e72\"](\"https:\/\/salihdeveci.files.wordpress.com\/2018\/04\/sonuc3a72.png\")
<\/p>\n