Evaluasi Proses Query OLEH Slamet Sn Wibowo Wicaksono

Evaluasi Proses Query OLEH : Slamet Sn Wibowo Wicaksono

Pokok Bahasan Informasi deskriptif apa yang disimpan oleh DBMS dalam katalognya ? Alternatif apa saja yang perlu dipertimbangkan dalam melakukan retrieving baris-baris dari sebuah tabel ? Mengapa DBMS mengimplementasikan beberapa algoritma untuk setiap operasi aljabar ? Faktor-faktor apa yang mempengaruhi kinerja relatif dari algoritma-algoritma yang berbeda ? Apa yang dimaksud dengan rencana evaluasi query dan bagaimana rencana tersebut direpresentasikan ? Mengapa upaya untuk memperoleh sebuah rencana evaluasi query yang baik dianggap penting ? Bagaimana hal ini dapat dilakukan dalam relational DBMS ?

Overview Evaluasi Query Rencana (Plan): Berupa tree dari operator-operator Aljabar Relasional (ops. R. A. ), yang diikuti dengan pilihan algoritma tertentu untuk setiap operator. Setiap operator secara tipikal diimplementasikan dengan menggunakan antar-muka `tarik’ (pull), yaitu bilamana sebuah operator `ditarik’ utk “ouput tuples” berikutnya, maka operator tersebut akan `menarik’ semua input-nya dan melakukan perhitungan terhadap inputs tsb menggunakan operator yang dimaksud. Dua issue utama dalam optimasi query: Utk sebuah query yang diberikan, rencana apa yang diperlukan ? Diperlukan algoritma utk menelusuri ruang rencana guna memperoleh nilai (estimasi) rencana yang termurah. Persoalan: bagaimana mengestimasi biaya suatu rencana? Tujuan ideal: Cari rencana terbaik. Prakteknya: Hindari rencana- rencana terjelek ! Dalam kuliah ini dibahas pendekatan/teknik evaluasi query yang digunakan dalam System R.

Beberapa Teknik Umum Algoritma-algoritma utk mengevaluasi operator-operator relasional menggunakan beberapa ide sederhana berikut secara intensif: Indexing: Dpt menggunakan WHERE conditions utk melakukan retrieve sekumpulan tuples yang relatif sedikit (yang memenuhi kondisi selections atau joins) Iteration: Kadangkala, lebih cepat untuk men-scan semua tuples, walaupun terdapat sebuah index. (Kadangkala, kita dapat men-scan data entries dalam sebuah index dari pada harus men-scan tabel itu sendiri) Partitioning: Dengan menggunakan sorting atau hashing, kita dpt mempartisi input tuples dan menggantikan sebuah operasi yang mahal dengan operasi-operasi serupa yang melibatkan input tuples yang lebih sedikit. Evaluasi query yang dijelaskan dlm bab ini dilakukan dengan memperhatikan teknik-teknik umum di atas !

Katalog Sistem Evaluasi query membutuhkan informasi mengenai relasi dan indexes yang dilibatkan. Untuk ini, Katalog sistem secara tipikal paling tidak harus berisikan: # tuples (NTuples) dan # pages (NPages) utk setiap relasi. # nilai-nilai index key yang berbeda (NKeys) dan # pages (INPages) untuk setiap index (leaf pages utk B+tree). Tinggi index (IHeight), range nilai-nilai key utk setiap index: maksimum (High) dan minimum (Low) untuk setiap tree index. Katalog sistem di-update secara periodik. Proses updating yang dilakukan utk setiap kali terjadi perubahan data akan mempunyai biaya I/O yang tinggi. Untuk ini dapat digunakan aproksimasi, walaupun akan terdapat sedikit inkonsistensi Informasi yang lebih detil (misalnya, histogram dari nilai-nilai dalam beberapa field) kadangkala juga perlu disimpan. Relasi yang akan dijadikan contoh dalam bab ini: Sailors (sid: integer, sname: string, rating: integer, age: real) : 500 pages @ 80 tuples Reserves (sid: integer, bid: integer, day: date, rname: string) : 1000 pages @ 100 tuples

Lintasan Akses (Access Paths) v Sebuah lintasan akses merupakan metode utk melakukan retrieving tuples, berupa: § File scan, atau penelusuran index yang matches (memenuhi) suatu seleksi (dalam suatu query) v Salah satu bentuk seleksi yang sederhana disebut “Conjunctive Normal Form (CNF)” : konjungsi kondisi dalam bentuk “attr op value” (op salah satu dari operator perbandingan <, , =, , >, atau ). v Sebuah tree index matches (kunjungsi dari) terms yang hanya melibatkan attributes dalam prefix dari search key. Contoh: § v Tree index pada <a, b, c> matches seleksi a=5 AND b=3, dan a=5 AND b>6, tetapi bukan b=3 atau b=5 AND c=3 (<a>, <a, b> prefix, tetapi <b>, <a, c> dan <b, c> bukan prefix) Sebuah hash index matches (konjungsi dari) terms yang memiliki sebuah term attribute = value utk setiap attribut dalam search key dari index. Contoh: § Hash index pada <a, b, c> matches a=5 AND b=3 AND c=5; tetapi BUKAN untuk b=3, atau a=5 AND b=3, atau a>5 AND b=3 AND c=5.

Catatan utk Seleksi yang Komplek SELECT …. . FROM …. . WHERE (day<8/9/94 AND rname=‘Paul’) OR bid=5 OR sid=3 Kondisi seleksi harus dikonversi terlebih dahulu ke conjunctive normal form (CNF): (day<8/9/94 OR bid=5 OR sid=3 ) AND (rname=‘Paul’ OR bid=5 OR sid=3) Dalam kuliah untuk bab ini, seleksi hanya dibatasi untuk kondisi yang TIDAK melibatkan OR. Lihat buku referensi lain utk kasus-kasus umum yang melibatkan konjungsi AND dan OR.

Satu Pendekatan utk Seleksi Cari lintasan akses yang paling selektif, gunakan lintasan tsb untuk melakukan retrieval tuples, kemudian lakukan seleski pada terms tersisa yang tidak match dengan index: Most selective access path: Sebuah index atau file scan yang diperkirakan memerlukan jumlah page I/O terkecil. Terms yang match dengan index akan mereduksi jumlah tuples yang diretrieved; terms lainnya digunakan utk membuang beberapa tuples yang tidak memenuhi kondisi, tetapi tidak mempengaruhi jumlah dari tuples/pages yang akan diambil (fetched). Perhartikan day<8/9/94 AND bid=5 AND sid=3. B+ tree index pada day dpt digunakan; kemudian, bid=5 and sid=3 harus dicek utk setiap tuple yang di-retrieved. Hal yang sama, hash index pada <bid, sid> dpt digunakan terlebih dahulu; kemudian day<8/9/94 harus dicek untuk setiap tuple yang di-retrieved.

Penggunaan Index utk Seleksi Biaya seleksi bergantung pada #qualifying tuples, dan clustering. Biaya pencarian qualifying data entries (biasanya kecil) ditambah biaya utk retrieving records (bisa besar tanpa clustering). Sebagai ilustrasi perhatikan contoh query di bawah, dengan asumsi nilai attribut rname terdistribusi secara uniform, dan sekitar 10% dari tuples memenuhi kondisi seleksi (1000 pages, 100 tuples). Dengan menggunakan clustered index, biaya dapat sedikit lebih kecil dari 100 I/Os; tetapi jika unclustered, dapat mencapai 10000 I/Os ! SELECT FROM WHERE * Reserves R R. rname < ‘Abc%’

Proyeksi SELECT DISTINCT FROM R. sid, R. bid Reserves R Bagian yang mahal adalah proses utk membuang duplikasi. Sistem SQL tidak membuang duplikai kecuali jika keyword DISTINCT dinyatakan dalam query. Sorting Approach: Sort pada <sid, bid> dan buang duplikasi. (Dapat dibuat lebih optimal dengan cara membuang informasi yang tidak diiginkan pada saat melakukan proses sorting. ) Hashing Approach: Hash pada <sid, bid> untuk membuat sejumlah partisi. Muat partisi ke dalam memory satu demi satu, kemudian bangun struktur hash dalam memory, dan lakukan eliminasi duplikasi. Jika tersedia index pada R. sid dan R. bid dalam search key, biaya dapat menjadi lebih murah dibandingkan harus melakukan sorting data entries!

Join: Index Nested Loops (INL) FOR setiap tuple r R DO FOR setiap tuple s S DO IF ri = sj THEN tambahkan <r, s> ke dalam hasil Jika tersedia index pada join column dari salah satu relasi (misalkan S), kita dapat mengeksploitasi index utk FOR-LOOP bagian dalam utk memperoleh matching disebut index nested loops join. Biaya: M * ( 1 + p. R * cost untuk mencari matching S tuples) , M = total biaya utk melakukan file scan dari relasi R Utk setiap R tuple, biaya utk menelusuri S index rata-rata sekitar 1. 2 I/Os utk hash index, 2 -4 I/Os utk B+ tree. Biaya utk mencari S tuples (dengan asumsi menggunakan alternatif 2 atau 3 utk data entries) akan bergantung pada clustering. Clustered index: 1 I/O (typical), sedang unclustered: dapat mencapai 1 I/O per matching S tuple.

Contoh: Index Nested Loops Join Hash-index (Alt. 2) pada sid of Sailors (sbg. inner): Scan Reserves: 1000 pages I/Os, 100*1000 tuples. Utk setiap Reserves tuple: 1. 2 I/Os utk mengambil data entry dlm index, ditambah 1 I/O utk mengambil (tepat satu) matching Sailors tuple. Total: 220, 000 I/Os. Hash-index (Alt. 2) pada sid of Reserves (sbg. inner): Scan Sailors: 500 pages I/Os, 80*500 tuples. Utk setiap Sailors tuple: 1. 2 I/Os utk mendapatkan index page bersama dengan data entries, ditambah biaya utk retrieving matching Reserves tuples. Dg asumsi distribusi merata, 2. 5 reservations per sailor (100, 000 / 40, 000). Biaya utk melakukan retrieving menjadi 1 atau 2. 5 I/Os bergantung pada apakah index di-clustered atau tidak. Total 40000 * (1. 2 + 2. 5) ( ? ? I/Os)

Join: Sort-Merge (R i=j S) Sort R dan S pada join column, kemudian gunakan scan utk melakukan ``merge’’ (pada join column. ). Lanjutkan scan dari R hingga current R-tuple >= current S tuple, kemudian lanjutkan scan dari S hingga current S-tuple >= current R tuple; Lakukan langkah tersebut hingga current R tuple = current S tuple. Pada akhir langkah di atas, semua R tuples dg nilai yang sama dlm Ri (current R group) match dengan semua S tuples dg nilai yang sama dlm Sj (current S group); Return output <r, s> untuk semua pasangan dari tuples yang diperoleh. Kemudian lakukan kembali (resume) scanning R dan S. Biaya: dimana M dan N adalah # pages dari masing-masing relasi, dan B adalah # buffer memory pages yang digunakan untuk melakukan partial sorting

Contoh: Sort-Merge Join Sailors Reserves Dengan asumsi bahwa, baik Reserves maupun Sailors dapat di- sorted dlm 2 passes biaya total join sebesar 7500 I/Os: Biaya sorting untuk relasi Reserves: 2*2*1000 = 4000 I/Os Biaya sorting untuk relasi Sailors: 2*2*500 = 2000 I/Os Biaya total: 4000 + 2000 + (1000 + 500) = 7500 I/Os.

Optimasi Query Ruang Rencana (Space of Plans): query optimizer secara tipikal mengenali query dengan memperlakukan query sebagai ekspresi dari kombinasi operator-operator relasional - - Query Parser Parsed Query Relational v Query Optimizer Plan Generator Plan Cost Estimator Catalog Manager Estimated Plan Query Plan Evaluator Optimizer Komersial: § Optmizer yang digunakan oleh berbagai DBMS komersial merupakan modul yang sangat komplek, yang secara tipikal memerlukan 40 hingga 50 manyears untuk pengembangannya

Sepintas tentang System R Optimizer Dampak: Paling banyak digunakan pada saat ini; dapat bekerja dengan baik untuk operasi yang melibatkan < 10 joins. Cost estimation: Approximate art at best. Statistik dipelihara dalam system catalogs dan digunakan untuk mengestimasi biaya operasi dan ukuran hasil operasi. Mempertimbangkan kombinasi biaya CPU dan I/O. Plan Space: Too large, must be pruned. Hanya mempertimbangkan ruang left-deep plans Left-deep plans memungkinkan output setiap operator utk di-pipelined ke dalam operator berikutnya tanpa melakukan penyimpanan dalam relasi sementara. Menghindari penggunaan Cartesian products.

Estimasi Biaya Utk setiap rencana (plan): Harus mengestimasi biaya utk setiap operasi dlm plan tree. Bergantung pada kardinalitas tuples masukan dan luaran. Telah dibahas sebelumnya bagaimana cara mengestimasi biaya operasi (sequential scan, index scan, joins, dll. ) Juga harus mengestimasi ukuran dari hasil untuk setiap operasi dalam tree ! Menggunakan informasi mengenai relasi-relasi yang digunakan. Untuk operasi selections dan joins, terms yang dilibatkan diasumsikan independen satu sama lain.

Estimasi Ukuran dan Faktor Reduksi Perhatikan query block: SELECT attribute list FROM relation list WHERE term 1 AND. . . AND termk # tuples maksimum dlm hasil adalah perkalian dari kardinalitas relasi-relasi yang terdapat dalam FROM clause. Faktor reduksi (RF) yang di asosiasikan dg setiap term merefleksikan dampak dari term dlm mereduksi ukuran hasil. Kardinalitas hasil = # tuples max * perkalian semua RF’s. Asumsi implisit bahwa terms independen satu sama lain! Term: col=value RF: 1/NKeys(I), utk index I pada col Term: col 1=col 2 RF: 1/MAX(NKeys(I 1), NKeys(I 2)) Term: col>value RF: (High(I)-value)/(High(I)-Low(I))

Contoh Skema Relasi Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string) Serupa dengan skema pada bab-bab sebelumnya; kecuali ditambahkan attribut rname utk variasi. Reserves: Setiap tuple mempunyai panjang 40 bytes, 100 tuples per page, 1000 pages. Sailors: Setiap tuple mempunyai panjang 50 bytes, 80 tuples per page, 500 pages.

RA Tree: Contoh Motivasi SELECT S. sname FROM Reserves R, Sailors S WHERE R. sid=S. sid AND R. bid=100 AND S. rating>5 bid=100 Kehilangan beberapa peluang: selections di-`pushed’ lebih awal, tidak memanfaatkan index, dlsb. Sasaran optimasi: Mencari plans yang lebih efisien yang memberikan hasil query yang sama. rating > 5 sid=sid Reserves Cost: 500*1000 I/Os = 500000 I/Os Tanpa optimasi: worst plan ! sname Plan: (On-the-fly) sname bid=100 Sailors rating > 5 (On-the-fly) (Simple Nested Loops) sid=sid Reserves Sailors

Alternative Plans 1 (No Indexes) SELECT S. sname FROM Reserves R, Sailors S WHERE R. sid=S. sid AND R. bid=100 AND S. rating>5 (On-the-fly) sname sid=sid (Scan; write to bid=100 temp T 1) (Sort-Merge Join) rating > 5 (Scan; write to temp T 2) Perbedaan utama: push selects. Reserves Sailors Biaya dari plan: Scan Reserves (1000) + write temp T 1 (10 pages, jika terdapat 100 boats dan diasumsikan terdistribusi secara merata). Scan Sailors (500) + write temp T 2 (250 pages, jika terdapat 10 ratings dan diasumsikan terdistribusi secara merata). Sort T 1 (2*2*10), sort T 2 (2*4*250), merge (10+250) (asumsi tersedia buffer dengan ukuran 5 pages). Total: (1000 +10) + (500+250) + (40+2000+260) = 4060 page I/Os. Jika digunakan Nested-Loops join, biaya join = 10+4*250, biaya total = 2770. (Asumsi tersedia buffer dengan ukuran 3 pages) Jika dilakukan `push’ projections, T 1 hanya memp sid, T 2 hanya memp sid dan sname: T 1 dapat ditempatkan dlm 3 pages, biaya Nested-Loops join akan turun menjadi lebih kecil dari 250 pages, total sekitar 2000.

sname Alternative Plans 2 With Indexes rating > 5 (On-the-fly) Dengan clustered index pada bid pada Reserves, diperoleh 100. 000/100 = 1000 tuples pada 1000/100 = 10 pages. INL dengan pipelining (outer tidak direalisasikan). (On-the-fly) sid=sid (Use hash index; do not write result to temp) bid=100 (Index Nested Loops, with pipelining ) Sailors Reserves – Upaya utk melakukan “projecting out” terhadap fields yang tidak diperlukan dari bagian luar (outer) tidak membantu menurunkan biaya. v Join column sid adalah key untuk Sailors. –Paling banyak satu matching tuple, unclustered index pada sid OK. v Keputusan utk tidak mem-push rating>5 sebelum join didasarkan pada ketersediaan sid index pada Sailors. v Biaya: Selection pada Reserves tuples (10 I/Os); untuk setiap tuple, harus memperoleh matching Sailors tuple (1000*1. 2); shg total 1210 I/Os.

Rangkuman Terdapat beberapa alternatif algoritma evaluasi query untuk setiap operator relasional Sebuah query dievaluasi dengan mengkonversikannya menjadi sebuah tree of operators dan mengevaluasi operators tersebut dalam tree Harus memahami optimasi query yang digunakan dalam DBMS guna memahami secara pernuh dampak kinerja dari suatu desain database (relasi dan indexes) yang dilakukan pads suatu beban kerja dari transaksi (sekumpulan queries) Dua hal utama yang perlu diperhatikan dalam optimasi query: Pertimbangkan satu set alternative plans. Harus memangkas (prune) ruang pencarian; secara tipikal melalui left-deep plans only. Harus mengestimasi biaya dari setiap “plan”. Harus mengestimasi ukuran dari hasil dan biaya dari setiap “plan node”. Issue kunci: Implementasi statistik, indexes, dan operator.