APPLICATION PERFORMANCE

Application Performance

( Resume Pertemuan 7 – DBA )

Nim : 10410100104

Sekarang kita telah membahas kinerja dari sistem dan basis data perspektif, sekarang saatnya untuk mengalihkan perhatian kita terhadap kinerja dari perspektif kode aplikasi. Bab ini berfokus pada tuning dan mengoptimalkan kode aplikasi dan SQL pernyataan, serta memastikan aplikasi berinteraksi dengan DBMS tepat dan efisien. Buruk dibangun dan kode aplikasi dirumuskan menyumbang sebagian besar masalah-kinerja database relasional sebanyak 80% dari masalah kinerja dapat ditelusuri kembali ke aplikasi.

Kode aplikasi buruk dibangun dan dirumuskan menyumbang sebagian besar masalah kinerja database relasional.

Designing Applications for Relational Access

Masalah desain aplikasi yang dibahas dalam Bab 5 dan harus ditinjau kembali ketika kinerja aplikasi menderita. Mungkin aplikasi adalah tidak benar dirancang dan bagian-bagian dari aplikasi, atau mungkin seluruh aplikasi, perlu ditulis ulang. Aplikasi harus dirancang untuk kinerja pada awal-karena mengubah desain aplikasi nantinya adalah baik tidak mungkin, tidak praktis, atau terlalu mahal untuk mengatasi.

Desain masalah untuk memeriksa ketika menderita kinerja aplikasi termasu:

·         Jenis SQL. Adalah jenis yang tepat dari SQL (direncanakan atau tidak direncanakan, dinamis atau statis, tertanam atau berdiri sendiri) yang digunakan untuk aplikasi tertentu?

·         Bahasa Pemrograman. Adalah bahasa pemrograman yang mampu mencapai kinerja yang diperlukan, dan lingkungan bahasa dioptimalkan untuk akses database?

·         desain Transaksi dan pengolahan. Apakah transaksi dalam program benar dirancang untuk menjamin ACID properti, dan apakah program menggunakan prosesor transaksi pilihan tepat dan efisien?

·         Mengunci strategi. Apakah aplikasi memegang salah jenis kunci, atau apakah itu memegang jenis yang tepat dari kunci terlalu lama?

·         COMMIT strategi. Apakah setiap masalah program aplikasi SQL laporan COMMIT untuk meminimalkan dampak penguncian?

·         Batch processing. Apakah program batch yang dirancang tepat untuk mengambil keuntungan dari fitur pemrosesan sekuensial dari DBMS?

·         Pemrosesan online. Apakah aplikasi online yang dirancang untuk mengembalikan informasi yang berguna dan untuk meminimalkan jumlah informasi kembali ke layar pengguna untuk doa tunggal program ini?

Relational Optimization

DBA harus menjadi akrab dengan teknik optimasi yang digunakan oleh masing-masing DBMS dalam organisasi. Tentu saja, pengembang aplikasi harus kode SQL yang efisien dan memahami bagaimana mengoptimalkan SQL, tetapi pada akhirnya itu adalah DBA yang bertanggung jawab untuk kinerja aplikasi database. Dengan demikian, DBA harus terampil dalam SQL coding dan tuning SQL untuk kinerja.

Optimizer adalah jantung dari sebuah sistem manajemen database relasional. Ini adalah mesin inferensi yang bertanggung jawab untuk menentukan strategi terbaik basis data navigasi untuk setiap permintaan SQL yang diberikan. Optimizer adalah mesin inferensi untuk menentukan strategi navigasi basis data.

Pengembang aplikasi menentukan data apa yang dibutuhkan oleh coding pernyataan SQL, DBMS menyediakan informasi tentang di mana data tersebut berada, dan optimizer relasional memutuskan bagaimana efisien menavigasi database. Pengguna akhir tidak memerlukan pengetahuan tentang di mana dan bagaimana data sebenarnya disimpan. Optimizer tahu informasi ini.

Untuk mengoptimalkan SQL, optimizer relasional harus menganalisa setiap pernyataan SQL dengan parsing untuk menentukan tabel dan kolom yang harus diakses. Optimizer juga akan mengakses statistik yang disimpan oleh RDBMS baik dalam sistem katalog atau objek database sendiri. Statistik yang digunakan untuk menentukan metode terbaik untuk mencapai tugas-tugas yang perlu dilakukan untuk memenuhi permintaan SQL. Proses ini disebut optimasi relasional dan ditunjukkan pada Gambar.

Gambar Relational optimization

Optimasi relasional sangat kuat karena memungkinkan permintaan untuk beradaptasi dengan lingkungan database berubah. Optimizer dapat bereaksi terhadap perubahan dengan merumuskan jalur akses baru tanpa memerlukan perubahan aplikasi coding untuk dilaksanakan. Aplikasi ini sehingga dapat menjadi fleksibel sebagai tabel memperluas atau kontrak dalam ukuran, karena indeks yang ditambahkan atau dihapus, dan sebagai database menjadi tidak teratur atau direorganisasi.

Terlepas dari bagaimana data disimpan secara fisik dan dimanipulasi, SQL dapat digunakan untuk mengakses data, dan DBMS akan mengambil keadaan saat ini database ke account untuk mengoptimalkan akses data. Pemisahan kriteria akses dari karakteristik penyimpanan fisik disebut data fisik kemerdekaan.

Pemisahan kriteria akses dari karakteristik penyimpanan fisik disebut data fisik kemerdekaan. Setiap RDBMS memiliki optimizer relasional tertanam yang membuat pernyataan SQL ke jalur akses executable. Selanjutnya, optimizer relasional masing-masing vendor bekerja sedikit berbeda, dengan langkah-langkah yang berbeda dan menggunakan informasi yang berbeda. Namun demikian, inti dari proses ini adalah sama dari DBMS ke DBMS. Optimizer mengurai pernyataan SQL dan melakukan berbagai tahapan optimasi, biasanya melibatkan verifikasi sintaksis dan semantik benar, diikuti dengan analisis permintaan dan perumusan jalur akses untuk memenuhi permintaan tersebut.

Optimizer relasional dapat menyebarkan berbagai jenis strategi yang tersedia untuk DBMS untuk mengoptimalkan pernyataan SQL. Operasi internal dan instruksi yang digunakan oleh masing-masing optimizer DBMS yang dijaga ketat rahasia. Pengoptimalan relasional modern berbasis biaya, yang berarti bahwa optimizer akan mencoba untuk merumuskan jalur akses setiap query yang mengurangi biaya keseluruhan. Untuk berfungsi dengan cara ini, optimizer harus mengevaluasi dan menganalisa berbagai faktor, termasuk perkiraan CPU dan I / O biaya, statistik database, dan pernyataan SQL yang sebenarnya.

CPU and I/O Costs

Sebuah penggunaan optimizer relasional formula dan model untuk memperkirakan biaya mesin untuk menjalankan setiap jalur akses potensi permintaan yang dioptimalkan. Berdasarkan informasi CPU, optimizer dapat tiba di perkiraan kasar dari waktu CPU yang dibutuhkan untuk menjalankan query menggunakan setiap jalur akses dioptimalkan analisis.

Selain itu, optimasi relasional harus memperkirakan biaya penulisan aktual dan pengambilan data. Optimizer memperkirakan biaya I / O untuk query dengan menggunakan serangkaian formula berdasarkan statistik database, efisiensi cache data, dan biaya I / O untuk file kerja menengah. Formula ini menghasilkan faktor filter, yang menentukan biaya relatif I / O dari query.

Database Statistics

Sebuah optimizer relasional adalah penggunaan sedikit tanpa statistik yang akurat tentang data yang disimpan dalam database. Sebuah DBMS relasional menyediakan program utilitas atau perintah untuk mengumpulkan statistik tentang obyek database dan menyimpannya untuk digunakan oleh optimizer (atau oleh DBA untuk memantau kinerja). Misalnya, untuk mengumpulkan statistik dalam DB2, DBA harus menjalankan utilitas RUNSTATS, untuk mengumpulkan statistik di SQL Server perintah STATISTIK UPDATE dikeluarkan. Sebuah optimizer relasional adalah penggunaan sedikit tanpa statistik yang akurat.

DBA harus mengumpulkan statistik diubah setiap kali volume yang signifikan dari data yang telah ditambahkan atau dimodifikasi. Kegagalan untuk melakukannya akan mengakibatkan optimizer mendasarkan perkiraan biaya pada statistik tidak akurat. Hal ini dapat merugikan kinerja query.

Statistik database menyediakan optimizer dengan informasi tentang keadaan tablespace, tabel, kolom, dan indeks. DBMS mengumpulkan informasi statistik seperti:

·         Jumlah baris di tablespace, meja, atau indeks

·         Jumlah nilai-nilai unik yang tersimpan dalam kolom nilai

·         Paling sering terjadi untuk kolom

·         Indeks kepadatan utama (lihat "Kepadatan" sidebar)

·         Detail pada rasio pengelompokan untuk tabel berkerumun

·         Korelasi kolom ke kolom lain

·         negara Struktural indeks atau tablespace

·         Jumlah penyimpanan yang digunakan oleh objek database

Ketika mengeluarkan perintah RUNSTATS, DBA menentukan mana statistik untuk mengumpulkan. Tentu saja, statistik yang tepat yang dikumpulkan bervariasi oleh DBMS ke DBMS; statistik tambahan atau kurang mungkin tersedia dalam sistem database Anda. Kuncinya, meskipun, adalah untuk menjaga statistik seakurat mungkin untuk memastikan optimasi relasional yang efisien dan berguna.

Ketika mengembangkan aplikasi terhadap database tes, statistik untuk data tes tidak akan akurat mencerminkan statistik untuk database produksi. Bila mungkin, DBA harus bekerja dengan tim pengembangan aplikasi untuk membuat script untuk mengisi statistik produksi ke dalam sistem tes. Tergantung pada DBMS, hal ini dapat dicapai dengan pernyataan SQL atau alat pengujian data. Tanpa statistik produksi, DBMS kemungkinan akan memilih jalur akses yang berbeda dalam lingkungan pengujian dibandingkan produksi berpotensi menyebabkan masalah kinerja ketika aplikasi masuk ke status produksi.

Query Analysis

Analisis permintaan scan pernyataan SQL untuk menentukan kompleksitas secara keseluruhan. Rumusan pernyataan SQL adalah faktor yang signifikan dalam menentukan jalur akses yang dipilih oleh optimizer. Kompleksitas query, jumlah dan jenis predikat, kehadiran fungsi, dan keberadaan memesan klausul masuk ke dalam perkiraan biaya yang dihitung oleh optimizer. Semakin kompleks pernyataan SQL, semakin besar usaha analisis permintaan harus lakukan untuk memahami pernyataan SQL. Selama analisis query, optimizer analisis dari aspek pernyataan SQL dan sistem database, yakni seperti contoh berikut:

·         Indeks yang, jika ada, dapat digunakan

·         Berapa banyak predikat (klausa WHERE) harus puas

·         Yang fungsi harus dijalankan

·         Apakah SQL menggunakan OR atau AND

·         Bagaimana DBMS proses setiap komponen dari pernyataan SQL

·         Berapa banyak memori memiliki telah ditugaskan untuk cache data (s) yang digunakan oleh tabel dalam pernyataan SQL

·         Berapa banyak memori yang tersedia untuk menyortir jika query membutuhkan semacam sebuah

Semakin kompleks pernyataan SQL, semakin besar usaha analisis permintaan harus dilakukan.

Density

Density adalah persentase rata-rata nilai-nilai duplikat yang disimpan dalam kolom kunci indeks (s) dan dicatat sebagai persentase. Sebagai contoh, kepadatan 50% berarti bahwa untuk nilai kunci indeks yang diberikan, kita akan mengharapkan untuk kembali 50% dari baris. Persamaan berikut menentukan rata-rata jumlah baris diharapkan akan dikembalikan ketika mengakses tabel dengan indeks:

Informasi ini berguna untuk optimizer karena membantu dalam menentukan ukuran hasil yang ditetapkan dan dengan demikian apakah indeks sangat membantu untuk jalur akses khusus.

Dengan kata lain, analisis permintaan memecah pernyataan SQL menjadi tugas-tugas diskrit yang harus dilakukan untuk mengembalikan hasil query.

Sebagian besar analisis query seleksi indeks. Setelah optimizer menentukan indeks yang tersedia untuk digunakan untuk setiap predikat, akan memutuskan apakah akan menggunakan indeks tunggal, beberapa indeks, atau indeks tidak sama sekali.

Joins

Ketika beberapa tabel diakses, optimizer angka keluar bagaimana untuk menggabungkan tabel dengan cara yang paling efisien. Menggabungkan informasi dari beberapa tabel dikenal sebagai bergabung. Ketika menentukan jalur akses untuk bergabung, optimizer harus menentukan urutan di mana tabel akan bergabung, menghitung estimasi biaya keseluruhan dari setiap jalur akses, dan memilih bergabung metode untuk query tertentu. DBMS dapat menggunakan beberapa metode yang berbeda untuk bergabung dengan tabel. Menggabungkan informasi dari beberapa tabel dikenal sebagai bergabung.

Terlepas dari metode bergabung, DBMS harus membuat beberapa keputusan dan melakukan operasi tertentu. Keputusan pertama adalah memilih meja untuk memproses pertama tabel disebut sebagai tabel luar. Selanjutnya, serangkaian operasi yang dilakukan di atas meja luar untuk mempersiapkan untuk bergabung. Baris dari tabel yang kemudian digabungkan dengan baris dari tabel kedua, disebut tabel batin, seperti yang ditunjukkan pada Gambar 12-2. Serangkaian operasi juga dilakukan di meja bagian dalam sebelum bergabung terjadi, sebagai bergabung terjadi, atau keduanya.

Gambar Joining tables.

Walaupun semua bergabung serupa dalam fungsi, masing-masing bergabung dengan metode bekerja secara berbeda di belakang layar. Mari kita menyelidiki dua metode umum bergabung: nested loop bergabung dan gabungan-pemindaian bergabung.

Nested-loop bergabung bekerja dengan membandingkan baris kualifikasi tabel luar ke meja batin. Sebuah baris kualifikasi diidentifikasi dalam tabel luar, dan kemudian tabel batin dipindai untuk pertandingan. Sebuah baris kualifikasi adalah satu di mana predikat untuk kolom dalam pertandingan tabel. Ketika scan meja batin selesai, baris kualifikasi lain di luar tabel diidentifikasi. Tabel batin dipindai untuk pertandingan lagi, dan seterusnya. Pemindaian diulang dari tabel dalam biasanya dilakukan dengan indeks untuk menghindari yang tidak semestinya biaya I / O. Semakin kecil ukuran tabel batin, baik yang bersarang loop bergabung melakukan, karena lebih sedikit baris perlu dipindai untuk setiap baris kualifikasi tabel luar.

Tipe kedua dari bergabung adalah metode merge-pemindaian bergabung. Dalam merge scan bergabung, tabel yang akan bergabung yang diperintahkan oleh tombol. Pemesanan ini dapat dicapai dengan semacam atau dengan akses melalui indeks. Setelah memastikan bahwa kedua tabel luar dan dalam yang diurutkan dengan benar, setiap meja dibaca secara berurutan, dan bergabung kolom dicocokkan. Selama merge scan bergabung, tidak ada baris baik dari tabel dibaca lebih dari sekali. Merge-Scan bergabung berguna ketika indeks yang tepat tidak tersedia pada salah satu (atau keduanya) dari tabel. Tergantung pada DBMS, metode bergabung lainnya mungkin tersedia.

Join Order

Optimizer ulasan masing-masing bergabung dalam query dan analisis statistik yang tepat untuk menentukan urutan optimal di mana tabel harus diakses untuk mencapai bergabung. Untuk menemukan optimal bergabung akses jalan, optimizer menggunakan built-in algoritma yang berisi pengetahuan tentang bergabung dan volume data. Ini cocok dengan kecerdasan ini terhadap bergabung predikat, database statistik, dan indeks yang tersedia untuk memperkirakan urutan yang lebih efisien. Secara umum, optimizer akan menggunakan algoritma yang meminimalkan jumlah kali tabel batin harus diakses untuk kualifikasi baris tabel luar. Namun, tidak ada pengoptimalan relasional saat ini yang sempurna.

Access Path Choices

Optimizer relasional memiliki banyak pilihan untuk membuat jalur akses SQL. Kita telah membahas bergabung akses jalan, jadi mari kita membahas beberapa jenis umum lainnya dari akses data.

Table Scans

Tabel scan adalah bentuk sederhana dari akses data. Sebuah meja scan dilakukan hanya dengan membaca setiap baris dari tabel. Tergantung pada DBMS, alternatif jenis pemindaian mungkin ada, yang disebut scan tablespace. Tablespace memindai membaca setiap halaman di tablespace, yang mungkin berisi lebih dari satu tabel. Jelas, scan tablespace akan berjalan lebih lambat dari meja scan karena tambahan I / O akan dikeluarkan membaca data yang tidak berlaku. Tabel scan adalah bentuk sederhana dari akses data.

Bentuk lain dari pemindaian scan partisi. Jika DBMS dapat menentukan bahwa data yang akan diakses ada di partisi tertentu dari sebuah tabel multipartition (atau tablespace), dapat membatasi data yang dipindai ke partisi yang sesuai. Scan partisi harus mengungguli scan tabel atau tablespace pemindaian karena jumlah I / O yang dibutuhkan berkurang.

Biasanya, optimizer akan memilih untuk memindai data untuk salah satu alasan berikut:

·         Query tidak dapat puas menggunakan indeks mungkin karena tidak ada indeks yang tersedia, tidak ada predikat sesuai indeks, atau predikat menghalangi penggunaan indeks.

·         Sebuah persentase yang tinggi dari baris dalam tabel memenuhi syarat. Dalam hal ini, menggunakan indeks mungkin akan kurang efisien karena sebagian besar baris data yang perlu dibaca pula.

·         Indeks yang memiliki predikat yang cocok memiliki rasio klaster rendah dan hanya efisien untuk sejumlah kecil data.

·         Tabel sangat kecil sehingga penggunaan indeks benar-benar akan merugikan. Untuk meja kecil, menambahkan akses indeks untuk akses tabel dapat menghasilkan tambahan I / O, bukan kurang I / O.

Untuk membantu kinerja scan, optimizer dapat meminta data yang prefetch. Data prefetch menyebabkan DBMS untuk membaca halaman data secara berurutan ke dalam cache data bahkan sebelum mereka diminta. Pada dasarnya, data yang prefetch adalah mekanisme-ketika membaca-depan scan data yang mendapatkan sekitar untuk meminta data, sudah akan ada di memori. Data prefetch ini berguna untuk meja dan tablespace scan, tetapi dapat praktis untuk semua jenis akses data berurutan. Apakah data prefetch tersedia, serta kapan dan bagaimana digunakan, tergantung pada DBMS. Optimizer dapat memilih untuk menyebarkan ketika jalur akses dirumuskan, atau DBMS dapat memilih untuk mengaktifkan data prefetch saat query sedang dijalankan. Sebagai DBA, Anda harus belajar bagaimana dan mengapa data DBMS tertentu prefetches. Data prefetch ini berguna untuk meja dan tablespace scan.

Indexed Access

Dari sekian banyak keputusan yang harus dibuat oleh optimizer, salah satu yang paling penting bagi kinerja query adalah apakah indeks akan digunakan untuk memenuhi permintaan. Untuk menentukan ini, optimizer harus terlebih dahulu mengetahui apakah indeks ada. Indeks tidak harus didefinisikan sebelum SQL dapat ditulis untuk mengakses kolom-Anda dapat query setiap kolom dari setiap tabel yang dikenal ke database.

Tentu saja, sebelum optimizer relasional akan menggunakan indeks untuk memenuhi permintaan, indeks yang tepat harus sudah ada. Selain itu, setidaknya satu kolom diindeks harus dirujuk dalam sebuah predikat diindeks dalam pernyataan SQL. DBMS tidak mampu menggunakan indeks untuk setiap klausa WHERE. Anda harus belajar apa jenis predikat dapat menggunakan indeks untuk memastikan bahwa indeks yang sesuai diciptakan untuk query dalam aplikasi database Anda. Setiap DBMS memiliki daftar yang berbeda dari apa yang ada, dan apa yang tidak, diindeks. Selanjutnya, apa yang diindeks cenderung berubah dari versi ke versi masing-masing DBMS.

Optimizer relasional dapat memilih untuk menggunakan indeks dalam berbagai cara. Tipe pertama, dan yang paling sederhana, akses diindeks adalah pencarian indeks langsung. Untuk melakukan pencarian indeks langsung, DBMS memulai langkah-langkah berikut:

1.      Nilai dalam SQL predikat dibandingkan dengan nilai-nilai yang disimpan di halaman akar indeks. Berdasarkan perbandingan ini, DBMS akan melintasi indeks ke set terendah berikutnya halaman.

2.      Jika halaman nonleaf menengah ada, halaman nonleaf tepat dibaca, dan nilai tersebut dibandingkan untuk menentukan halaman daun untuk mengakses.

3.      Halaman daun yang sesuai dibaca, halaman daun index berisi pointer (s) dengan data aktual untuk baris kualifikasi.

4.      Berdasarkan pointer (s) dalam entri indeks halaman daun, DBMS membaca halaman data tabel yang sesuai.

Untuk DBMS untuk melakukan pencarian indeks langsung, nilai-nilai harus disediakan untuk setiap kolom dalam indeks. Sebagai contoh, perhatikan pertanyaan berikut:

SELECT last_name, first_name, middle_initial, empno

FROM employee

WHERE position = 'MANAGER'

AND work_code = 1

AND dept = '001000';

Selanjutnya, asumsikan bahwa indeks ada di posisi, work_code, dan dept kolom. DBMS dapat melakukan pencarian indeks langsung menggunakan indeks dan nilai yang diberikan dalam predikat untuk masing-masing kolom. Untuk pencarian indeks langsung terjadi, ketiga kolom harus muncul dalam pernyataan SQL. Jika hanya satu atau dua kolom ini ditetapkan sebagai predikat, pencarian indeks langsung tidak dapat dipilih karena DBMS tidak bisa cocok dengan kunci indeks penuh. Sebaliknya, indeks scan bisa dipilih. Ingat pembahasan sebelumnya meja dan tablespace scan. Indeks scan serupa. Ketika indeks scan dipanggil, halaman daun indeks dibaca secara berurutan, satu demi satu.

Ada dua tipe dasar indeks scan: Indeks pencocokan scan dan scan indeks nonmatching. Sebuah indeks scan pencocokan kadang-kadang disebut posisi absolut. Sebuah indeks scan pencocokan dimulai di halaman akar indeks dan bekerja ke halaman daun dalam banyak cara yang sama seperti pencarian indeks langsung tidak. Namun, karena kunci lengkap indeks tidak tersedia, DBMS harus memindai halaman daun indeks mencari nilai-nilai yang tersedia, sampai semua nilai pencocokan telah diambil. Untuk melihat bagaimana scan indeks pencocokan bekerja, mengikuti panah pada Gambar.

Gambar. Matching index scan.

Ada dua tipe dasar indeks scan: pencocokan dan nonmatching. Untuk memperjelas, mempertimbangkan permintaan sebelumnya dari tabel karyawan, tapi kali ini query recoded tanpa predikat untuk kolom dept. DBMS dapat menggunakan indeks scan yang cocok. Indeks pencocokan scan pertama menempatkan halaman daun pertama dengan nilai yang sesuai untuk posisi dan work_code dengan melintasi indeks mulai dari akar. Namun, bisa ada entri indeks berganda dengan kombinasi nilai-nilai dan nilai-nilai yang berbeda untuk dept. Oleh karena itu, halaman daun akan dipindai secara berurutan sampai tidak ada entri lebih valid ditemukan.

Untuk indeks scan yang cocok untuk diminta, Anda harus menentukan kolom high-order dalam indeks kunci-dengan kata lain, untuk kolom pertama ditentukan dalam DDL indeks. Untuk contoh sebelumnya, kolom high-order adalah kolom posisi. Kolom high-order menyediakan titik awal untuk DBMS untuk melintasi struktur indeks dari halaman ke halaman akar daun yang sesuai.

Pertimbangkan konsekuensi dari tidak menentukan kolom high-order dalam query. Misalnya, kita mengambil query dan menghapus predikat untuk posisi, tapi mempertahankan dua lainnya, meninggalkan pernyataan SQL berikut:

SELECT last_name, first_name, middle_initial, empno

FROM employee

WHERE work_code = 1

AND dept = '001000';

Dalam situasi seperti itu, DBMS dapat menyebarkan indeks scan nonmatching, kadang-kadang disebut sebagai posisi relatif. Ketika titik awal tidak dapat ditentukan karena kolom pertama dalam kunci indeks tidak ditentukan, DBMS tidak dapat menggunakan struktur pohon indeks. Namun, hal itu dapat menggunakan halaman indeks daun, seperti yang ditunjukkan pada Gambar. Sebuah indeks scan nonmatching dimulai dengan halaman daun pertama dalam indeks dan scan halaman daun berikutnya secara berurutan, menerapkan predikat yang tersedia.

Gambar. Nonmatching index scan

Sebuah indeks scan nonmatching dapat lebih efisien daripada tabel atau tablespace memindai, terutama jika halaman data yang harus diakses dalam urutan berkerumun. Tentu saja, sebuah indeks scan nonmatching dilakukan pada indeks nonclustered juga.

Hal ini membawa kita ke berkerumun dibandingkan akses indeks unclustered. Setiap metode di atas untuk akses diindeks dapat digunakan dengan baik indeks berkerumun dan unclustered. Namun, scan indeks yang kemudian harus mengakses halaman data tabel tidak mungkin akan sangat efisien jika data tidak terkelompok: akses index Clustered, karena hasil dari halaman ke halaman daun daun, tidak pernah meminta untuk membaca halaman data yang sama dua kali. Akses indeks Unclustered, bagaimanapun, akan meminta halaman data yang sama beberapa kali karena data tersebut tersebar di seluruh meja. Untuk klarifikasi clustering, lihat pembahasan dan diagram dalam Bab 4 dan 11.

Sebuah teknik akses indeks tambahan yang dapat digunakan adalah skrining indeks. Dengan screening indeks, scan indeks pencocokan dilakukan pada kolom terkemuka indeks multicolumn, dan predikat tambahan diterapkan selama pemindaian. Teknik ini berguna jika beberapa kolom dari indeks multicolumn tidak ditentukan dalam query. Pertimbangkan permintaan sampel lain:

FROM employee

WHERE position = 'MANAGER'

AND work_code = 1

AND salary > 50000.00;

Indeks skrining berguna jika beberapa kolom dari indeks multicolumn tidak ditentukan dalam query. Sekarang asumsikan bahwa indeks telah dibuat pada kolom berikut dengan urutan sebagai berikut: posisi, work_code, dept, dan gaji. Indeks dapat disaring dengan menerapkan indeks pencocokan scan pada posisi dan work_code, dan kemudian scan nonmatching untuk nilai gaji yang ditetapkan lebih besar dari $ 50,000.00, tapi hanya untuk orang-orang baris yang cocok posisi = ‘MANAGER’ dan work_code = 1 predikat.

Salah satu tipe yang paling efisien akses diindeks adalah akses indeks-only, kadang-kadang disebut sebagai indeks yang mencakup. Pertimbangkan lagi permintaan kami hanya diperiksa. Selanjutnya, asumsikan bahwa indeks telah dibuat pada kolom berikut: posisi, work_code, dept, gaji, last_name, dan empno. Dalam skenario seperti itu, DBMS dapat memenuhi permintaan hanya menggunakan indeks karena semua data yang diminta dalam daftar SELECT dan predikat ada di indeks. Tidak ada tambahan I / O ke halaman data tabel diperlukan.

Dengan akses indeks-only, DBMS dapat memenuhi permintaan dengan hanya memindai halaman daun indeks. Sebuah nonmatching indeks-hanya memindai bisa jauh lebih cepat daripada tablespace atau scan meja karena entri indeks umumnya lebih kecil daripada baris tabel, dan akibatnya banyak data yang dibaca dengan masing-masing I / O.

Untuk mendorong akses indeks-only, DBA dapat membebani indeks dengan menambahkan kolom tambahan yang muncul dalam daftar SELECT pernyataan SQL. Melakukan hal tersebut dapat memberikan kinerja pengembalian yang besar, tetapi datang pada biaya kebutuhan penyimpanan disk tambahan karena kolom tambahan sedang diindeks.

Salah satu jenis akhir akses diindeks adalah akses multi-indeks. Dengan akses multi-indeks, DBMS menggunakan lebih dari satu indeks untuk memenuhi jalur akses tunggal. Sebagai contoh, perhatikan variasi lain dari tabel query karyawan kami:

SELECT last_name, empno

FROM employee

WHERE position = 'MANAGER'

AND work_code = 1;

Dengan akses multi-indeks, DBMS menggunakan lebih dari satu indeks untuk memenuhi jalur akses tunggal. Kali ini kita memiliki dua indeks: satu di kolom posisi dan lain pada kolom work_code. Permintaan menetapkan dua predikat, yang masing-masing didukung oleh indeks yang berbeda. Alih-alih memilih untuk menggunakan satu indeks atau yang lain, optimizer relasional bisa menggabungkan dua indeks untuk mengembalikan data yang benar efisien.

Ada dua jenis akses multi-index, tergantung pada apakah predikat yang diikat bersama-sama menggunakan AND atau OR. Beberapa DBMSs hanya mendukung operator AND logis, sedangkan yang lain mendukung kedua AND dan OR. Dalam beberapa kasus, DBA dapat meminimalkan jumlah indeks yang dibuat dengan mengerahkan beberapa indeks tunggal-kolom, bukan dari beberapa indeks multicolumn. DBA harus mendasarkan keputusan ini pada efisiensi DBMS dalam menggunakan akses multi-indeks dan persyaratan kinerja dari aplikasi yang digunakan.

Using Indexes to Avoid Sorts

DBMS mungkin perlu untuk mengurutkan data untuk memenuhi permintaan SQL. Sorting adalah biaya cukup mahal dan harus dihindari jika mungkin. DBA dapat menggunakan indeks untuk menghindari macam dengan menciptakan mereka pada kolom yang perlu diurutkan. Optimizer relasional akan mencoba untuk menggunakan indeks untuk menghindari macam bila memungkinkan. Sorting mungkin terjadi ketika klausul berikut ditentukan:

·         DISTINCT: Bila distinct ini ditentukan DBMS mengharuskan setiap kolom data yang dihasilkan berada dalam urutan sehingga duplikasi baris dapat dihapus dari hasil yang ditetapkan.

·         UNION: Operasi ini memerlukan kolom dalam setiap daftar SELECT yang akan dipesan karena hasil set dapat memiliki duplikasi baris.

·         GROUP BY: Bila pasal ini ditentukan, DBMS memerlukan data yang akan diurutkan oleh kolom yang ditentukan untuk data agregat.

·         ORDER BY: Bila pasal ini ditentukan, DBMS akan memastikan bahwa hasil ditetapkan diurutkan menurut kolom tertentu.

Gunakan indeks untuk menghindari macam dengan menciptakan mereka pada kolom yang perlu diurutkan. Perhatikan pernyataan SQL berikut:

SELECT last_name, first_name, middle_initial, empno, position

FROM employee

WHERE position in ('MANAGER', 'DIRECTOR', 'VICE PRESIDENT')

ORDER BY last_name;

Jika indeks ada di kolom last_name, query dapat menggunakan indeks ini dan menghindari penyortiran. Menggunakan indeks untuk menghindari semacam perdagangan dari biaya CPU tambahan yang dibutuhkan untuk memilah untuk tambahan I / O biaya yang diperlukan untuk akses diindeks. Tentu saja, jika indeks akan digunakan pula, pilihannya adalah no-brainer. Apakah atau tidak menggunakan indeks sebenarnya lebih cepat dari pemindaian data dan pemilahan akan tergantung pada:

·         Jumlah baris kualifikasi

·         Kecepatan karakteristik semacam

·         Index (misalnya, berkerumun atau nonclustered)

Why Wasn't the Index Chosen?

Situasi kadang-kadang muncul di mana Anda berpikir optimizer harus memilih indeks, tapi ternyata tidak. Setiap beberapa alasan dapat menyebabkan optimizer untuk menghindari menggunakan indeks. Konsultasikan daftar berikut cara untuk mendorong seleksi indeks.

·         Apakah query menentukan argumen pencarian? Jika tidak ada predikat menggunakan argumen pencarian, optimizer tidak dapat menggunakan indeks untuk memenuhi permintaan tersebut.

·         Apakah Anda bergabung dengan sejumlah besar tabel? Optimizer dalam beberapa DBMSs dapat menghasilkan hasil rencana permintaan yang tak terduga ketika bergabung dengan sejumlah besar tabel.

·         Apakah statistik saat ini? Jika sejumlah besar data telah dimasukkan, diperbarui, dan / atau dihapus, statistik database harus direbut kembali untuk memastikan bahwa optimizer memiliki informasi up-to-date yang mendasari rencana permintaan nya.

·         Apakah Anda menggunakan prosedur yang tersimpan? Kadang-kadang DBMS menyediakan pilihan di mana prosedur yang tersimpan, sekali dikompilasi, tidak akan merumuskan rencana permintaan untuk eksekusi berikutnya. Anda mungkin perlu mengkompilasi ulang atau reoptimize prosedur yang tersimpan untuk mengambil keuntungan dari up-to-date statistik, indeks baru, atau perubahan database terkait lainnya.

·         Apakah predikat tambahan yang diperlukan? Sebuah klausa WHERE yang berbeda bisa saja memungkinkan optimizer untuk mempertimbangkan indeks yang berbeda.

Hashed Access

Optimizer juga akan mempertimbangkan menggunakan struktur hashing yang ada saat merumuskan jalur akses. Sebuah hash operasinya mirip dengan indeks lookup langsung. Hash yang paling tepat untuk random I / O sejumlah kecil data. Untuk mengambil data didasarkan pada algoritma hashing, DBMS menggunakan rutin mengacak untuk menerjemahkan nilai yang diberikan untuk kunci hash ke lokasi fisik. Algoritma ini akan memberikan offset baris dalam tabel database sebenarnya. Sebuah hash operasinya mirip dengan indeks lookup langsung.

Parallel Access

Optimizer relasional dapat memilih untuk menjalankan query secara paralel. Ketika permintaan paralelisme dipanggil oleh DBMS, beberapa tugas secara simultan dipanggil untuk mengakses data. Tiga jenis dasar paralelisme dapat didukung oleh DBMS:

·         I / O paralelisme memungkinkan konkuren I / O stream harus dimulai untuk query tunggal. Menjalankan paralel I / O tugas secara signifikan dapat meningkatkan kinerja I / O terikat pertanyaan. Memutus akses data untuk query ke konkuren I / O stream dieksekusi secara paralel dapat mengurangi waktu yang telah berlalu keseluruhan untuk query.

·         CPU paralelisme memungkinkan multitasking pengolahan CPU dalam query. Menjalankan CPU paralelisme juga memanggil I / O paralelisme karena setiap mesin membutuhkan CPU sendiri I / O stream-nya. CPU paralelisme terurai query ke dalam beberapa pertanyaan yang lebih kecil yang dapat dieksekusi secara bersamaan pada beberapa prosesor. CPU paralelisme dapat mengurangi waktu yang telah berlalu untuk query.

·         Akhirnya, DBMS dapat menyebarkan sistem paralelisme untuk lebih meningkatkan operasi query paralel. Sistem paralelisme memungkinkan permintaan tunggal untuk dipecah dan lari melintasi beberapa contoh DBMS. Dengan membiarkan satu query untuk mengambil keuntungan dari kekuatan pemrosesan dari beberapa contoh DBMS, waktu yang telah berlalu keseluruhan untuk query yang kompleks dapat dikurangi lebih jauh.

Tiga jenis dasar paralelisme dapat didukung oleh DBMS: I / O, CPU, dan sistem. Memastikan bahwa rencana permintaan yang tepat dirumuskan dengan penggunaan indeks yang benar adalah proses yang memakan waktu, tapi satu yang dapat membayar dividen yang besar dalam bentuk peningkatan kinerja. DBA harus melatih staf pengembangan aplikasi untuk memahami optimasi relasional dan untuk menciptakan SQL optimal. Tentu saja, tanggung jawab jatuh pada pengembang aplikasi untuk kode SQL yang efisien dan logika program. Namun, DBA adalah penjaga kinerja database relasional. Ketika masalah kinerja terjadi, DBA adalah orang yang harus mencari penyebab masalah dan menyarankan solusi untuk mengatasinya. Selain itu, DBA harus melakukan review desain untuk mencari dan tune efisien SQL sebelum jalur akses suboptimal dan program yang bermigrasi ke status produksi.