BAB 2 LANDASAN TEORI 2.1 Landasan Teori Umum 2.1.1 Pengertian Data dan Informasi Menurut Whitten, Bentley, dan Dittman (2004, p27), data adalah fakta mentah mengenai orang, tempat, kejadian dan hal-hal yang penting dalam organisasi. Informasi adalah data yang telah diproses atau diorganisasi ulang menjadi bentuk yang berarti. Informasi dibentuk dari kombinasi data yang diharapkan memiliki arti ke penerima. 2.1.2 Pengertian Basis Data Menurut Ramakrishnan dan Gehrke (2005, p4), database adalah kumpulan data, secara khusus, menggambarkan aktivitas dari satu atau lebih organisasi yang berhubungan. Menurut Connolly dan Begg (2005, p15), database adalah kumpulan dari data yang berhubungan secara logika, dan deskripsi dari data ini dirancang untuk memenuhi kebutuhan informasi dalam organisasi. 2.1.2.1 Pengertian DBMS (Database Management System) Menurut
Ramakrishnan
dan
Gehrke
(2005,
p4),
Database
Management System atau DBMS adalah perangkat lunak yang didesain untuk membantu dalam memelihara dan menggunakan koleksi data dalam jumlah yang besar. Penggunaan DBMS adalah untuk menyimpan data dalam file dan menulis aplikasi dengan kode khusus untuk mengaturnya.
9
10 Berdasarkan Ramakrishnan dan Gehrke (2005, p9), penggunaan DBMS untuk mengelola data memiliki beberapa keuntungan antara lain : -
Data Independence : Program aplikasi secara ideal memberikan secara detail representasi data dan penyimpanannya, sedangkan DBMS menyediakan gambaran abstrak dari data dan menyembunyikan detail dari representasi data dan penyimpanannya.
-
Akses data yang efisien : DBMS menggunakan berbagai teknik-teknik untuk menyimpan dan mengembalikan data secara efisien. Fitur ini secara khusus penting jika data disimpan pada penyimpanan eksternal.
-
Integritas dan Keamanan Data : Jika data selalu diakses melalui DBMS, DBMS dapat menjalankan batasan untuk integritas data. Sebagai contoh, sebelum memasukkan data gaji untuk karyawan, DBMS dapat melakukan pemeriksaan apakah anggaran departemen mencukupinya. Selain itu, DBMS juga dapat menjalankan kontrol akses yang berwenang untuk penggunaan data bagi pengguna tertentu.
-
Administrasi Data : Ketika beberapa pengguna saling berbagi data dengan memusatkan pada administrasi data dapat menawarkan peningkatan yang signifikan. Tenaga profesional yang memahami sifat dasar data yang dikelola dan bagaimana kelompok yang berbeda dari pengguna yang menggunakan data tersebut, dapat bertanggungjawab untuk mengatur representasi data untuk meminimalkan redudansi dan tuning penyimpanan data yang baik.
-
Concurrent Access dan Crash Recovery : DBMS melakukan penjadwalan akses data secara bersamaan sehingga pengguna berpikir
11 bahwa data diakses hanya oleh satu pengguna pada waktu tertentu. Selanjutnya, DBMS melakukan proteksi pengguna dari efek kegagalan sistem. -
Mengurangi waktu pengembangan aplikasi DBMS mendukung fungsi penting pada berbagai aplikasi yang mengakses data dalam DBMS. Hal ini berkaitan dengan antarmuka tingkat tinggi terhadap data, memberikan fasillitas pengembangan aplikasi secara cepat. Menurut Dawes, Bryla, Johnson, dan Weishan (2005, p3), Oracle
Database 10g merupakan produk yang mengembangkan grid computing sebagai Database Management System dengan fitur manajemen secara otomatis untuk penyimpanan data dalam basis data, struktur memori selftuning untuk basis data tersebut dan penggunaan web-based untuk mengawasi dan mengatur arsitektur Oracle. Grid computing dikembangkan dengan tujuan proses bisnis yang dapat dijalankan melalui server individual yang berjalan pada aplikasi. Melalui cara ini, aplikasi tidak seutuhnya selalu mengakses server utama dan hal ini dapat mencegah penggunaan perangkat keras yang memuncak. Dengan perbandingan ini, maka Oracle Database 10g seolah dapat dijalankan pada beberapa server yang membuat efisiensi penggunaan sumber daya perangkat keras yang akan digunakan. 2.1.3 Pengertian SQL Menurut Connoly dan Begg (2005, p113), pengertian SQL adalah transformoriented language atau bahasa yang dirancang dengan penggunaan relasi untuk
12 mengubah masukan menjadi keluaran yang dibutuhkan. Sebagai sebuah bahasa, standar internasional SQL menetapkan 2 komponen pokok, yaitu : -
Data Definition Language (DDL) untuk mendefisinikan struktur basis data dan akses kontrol data.
-
Data Manipulation Language (DML) untuk mengembalikan dan memperbarui data.
SQL adalah bahasa yang relatif mudah dipelajari, antara lain : -
SQL merupakan bahasa non-prosedural; tetapkan informasi “apa” yang dibutuhkan daripada “bagaimana” mendapatkan informasi tersebut. Dalam SQL, tidak perlu menetapkan metode untuk mengakses data.
-
Pada dasarnya, SQL adalah free-format, artinya bagian-bagian dari perintah SQL tidak harus diketikkan pada lokasi tertentu pada layar.
-
Struktur perintahnya terdiri dari kata-kata standar dalam bahasa inggris, antara lain CREATE TABEL, INSERT, SELECT. Gambar 2.1, 2.2, dan 2.3 menunjukkan beberapa contoh sintaks SQL yang sering digunakan :
CREATE TABEL Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2)); Gambar 2. 1 Contoh sintaks Create Table dalam SQL INSERT INTO Staff VALUES(‘SG16’,’Brown’,8300);
Gambar 2. 2 Contoh sintaks untuk menambah baris baru dalam SQL
13 SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000; Gambar 2. 3 Contoh sintaks untuk menampilkan baris dalam SQL -
SQL dapat digunakan oleh pengguna dari berbagai kalangan, termasuk DBA (Database Administrator), management personnel, application developer dan end-user lainnya.
2.1.3.1 Data Definition Menurut Connoly dan Begg (2005, p168), Data Definition Language (DDL) memberikan izin untuk menciptakan dan menghapus objek basis data, misalnya skema, tabel, view, indeks dan domain. Gambar 2.4 menunjukkan perintah yang ada di dalam SQL untuk data definition language yaitu : CREATE SCHEMA
DROP SCHEMA
CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN
CREATE TABEL
ALTER TABEL
DROP TABEL
CREATE VIEW
DROP VIEW
Gambar 2. 4 Perintah utama dalam SQL untuk data definition language Perintah-perintah pada gambar 2.4 digunakan untuk menciptakan, mengubah dan menghapus struktur dalam konseptual skema. Sekalipun tidak tercakup dalam standar SQL, berikut ini adalah perintah SQL yang banyak disediakan oleh DBMS, yaitu : CREATE INDEX dan DROP INDEX
14 2.1.3.2 Data Manipulation Data manipulation di dalam SQL mencakup banyak hal mengenai query. Hal-hal yang akan dibahas disini adalah yang terkait dengan query secara umum, yaitu : 1. Penggunaan query dasar Menurut Connoly dan Begg (2005, p117), perintah yang digunakan dalam data manipulation adalah : SELECT : untuk menampilkan hasil query data dalam basis data INSERT : untuk memasukkan data ke dalam basis data UPDATE : untuk memperbarui data dalam basis data DELETE : untuk menghapus data dalam basis data Tujuan perintah SELECT adalah untuk mengembalikan nilai dan menampilkan data dari satu atau lebih tabel dalam basis data. Gambar 2.5 menunjukkan contoh penggunaan select statement dalam SQL : SELECT [DISTINCT|ALL] {*|[columnExpression newName] [,…]} FROM TabelName [alias] [,..] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList]
[AS
Gambar 2. 5 Contoh penggunaan select statement dalam SQL *) Keterangan : SELECT : menetapkan kolom mana yang ingin ditampilkan pada keluaran. FROM : menetapkan tabel atau tabel-tabel yang digunakan.
15 WHERE : menyaring data yang ditampilkan berdasarkan kondisi tertentu. GROUP BY : membentuk kelompok berdasarkan nilai kolom yang diinginkan. HAVING : menyaring kelompok subjek dari kondisi tertentu. ORDER BY : menetapkan urutan untuk keluaran data. Perintah yang sangat baik kemampuannya dalam menampilkan relasi data adalah operasi Selection, Projection dan Join dalam perintah tunggal. - Selection Operasi selection bekerja pada relasi tunggal R dan mendefinisikan relasi yang hanya berisi tuple R yang memenuhi kondisi tertentu (predikat). Misalnya adalah mencari gaji pegawai yang lebih dari 10000. Predikat dapat dihasilkan dari operasi logika AND, OR dan NOT. -
Projection Operasi projection bekerja pada relasi tunggal R dan mendefinisikan relasi yang berisi bagian secara vertikal dari R, mengambil nilai dari atribut yang ditentukan dan menghilangkan duplikasi.
-
Join Operasi join sama halnya dengan operasi cross-product yang melakukan pencarian data yang sama pada kolom yang berkaitan antara 2 tabel dalam query. Dalam memenuhi kondisi query tertentu, penggunaan operasi join lebih baik daripada operasi cross-product dalam efisiensi waktu
dan
pencarian
yang
dilakukan.
Operasi
join
akan
16 mengkombinasikan dua relasi ke bentuk relasi yang baru, yang merupakan operasi dasar dalam relational algebra. Menurut Ramakrishnan dan Gehrke (2005, p107), ada beberapa bentuk dari join, yaitu : •
Condition Joins Penggunaan join yang paling umum adalah bentuk condition join, yang melakukan kondisi seleksi pada cross-product antara 2 relasi (R dan S). Gambar 2.6 menunjukkan ilustrasi dari penggunaan Condition Joins yaitu:
σc (R X S) Gambar 2. 6 Ilustrasi gambar Condition Join Keterangan : c adalah kondisi berdasarkan hasil relasi R dan S dalam mencari data pada kolom yang berkaitan. •
Equijoin Operasi equijoin sama halnya ketika melakukan query dengan penggabungan relasi dengan mencari nilai data yang sama pada kolom yang berkaitan antara kedua relasi tersebut. Diilustrasikan dengan query : R.name1 = S.name2, dimana R dan S masingmasing adalah tabel untuk mencari nilai data yang sama pada kolom name1 pada tabel R dan kolom name2 pada tabel S.
•
Natural Join Operasi natural join adalah operasi equijoin yang memiliki kesamaan dalam semua field yang memiliki nama yang sama
17 dalam tabel R dan tabel S. Dalam hal ini, kita dapat menghilangkan
kondisi
dalam
operasi
join
karena
akan
menghasilkan dua field dengan nama yang sama. Dalam Oracle, perintah SQL akan dianalisis oleh Optimizer, yang akan menentukan langkah-langkah yang paling optimal dalam menjalankan perintah SQL. Berikut ini ada beberapa cara JOIN yang ada pada basis data ORACLE, yaitu : •
Nested Loop Nested Loop adalah sebuah JOIN yang efektif jika subset yang digabungkan berjumlah sedikit dan jika kondisi dalam perintah JOIN efisien untuk menggabungkan 2 tabel tersebut. Cara kerja Nested Loop adalah : 1.
Optimizer menentukan sebuah tabel untuk dijadikan Outer Table.
2.
Tabel yang tersisa dijadikan Inner Table.
3.
Pada setiap baris yang terdapat pada Outer Table, Optimizer akan mengakses semua baris yang terdapat pada Inner Table dengan kondisi yang di spesifikasikan di dalam JOIN.
Nested Loop akan dipilih Optimizer jika memenuhi 2 kondisi, yaitu : -
Jumlah baris pada tabel sedikit.
-
Terdapat kondisi yang optimal untuk mengakses baris pada Inner Table.
18 •
Hash Joins Hash Joins biasanya digunakan untuk mengabungkan data-data yang berjumlah besar. Cara kerja Hash Joins adalah Optimizer membuat sebuah Hash Table berdasarkan predikat JOIN. Setiap tabel di Inner maupun Outer masing-masing dijadikan sebuah kode dengan Hash Function kemudian setiap kode Hash dari Inner akan dibandingkan dengan Hash Kode dari Outer. Apabila kode hash dari Inner dan Outer sama maka akan dilakukan proses pengecekan nilai dari kolom yang pada akhirnya akan dimasukkan ke dalam hasil jika nilai kolomnya sama.
•
Sort Merge Joins Sort Merge Joins biasa digunakan untuk menggabungkan baris dari dua sumber yang tidak mempunyai hubungan. Biasanya Hash Joins mempunyai performa yang lebih baik dari pada Sort Merge Joins. Namun Sort Merge Joins akan bekerja lebih baik daripada Hash Join apabila terdapat kondisi sebagai berikut : -
Baris-baris sudah diurutkan
-
Operasi Pengurutan tidak perlu untuk dilakukan.
Sort Merge Join sangat berguna apabila kondisi JOIN diantara dua tabel bukan berbentuk kondisi sama namun mempunyai bentuk kondisi seperti <, <=, >, atau >=.
19 •
Cartesian Joins Sebuah Cartesian Joins digunakan ketika satu atau lebih tabel tidak mempunyai kondisi penggabungan terhadap tabel lainnya. Optimizer akan menggabungkan setiap baris di tabel pertama dengan setiap baris di tabel lainya untuk menghasilkan sebuah Cartesian Produk dari dua set tersebut.
•
Outer Joins Outer Joins mempunyai proses dimana selain baris yang memenuhi kondisi JOIN yang dimasukkan ke dalam hasil. Outer Joins akan menambahkan baris yang tidak memenuhi kondisi JOIN namun digabung dengan nilai NULL. Terdapat 3 Jenis Outer Join yaitu : -
Right Join Perintah Right Join akan mengembalikan baris-baris yang memenuhi kondisi JOIN dan juga baris di tabel kanan yang tidak memenuhi kondisi JOIN dengan ditambahkan NULL.
-
Left Join Perintah Left Join akan mengembalikan baris-baris yang memenuhi kondisi JOIN dan juga baris di tabel kiri yang tidak memenuhi kondisi JOIN dengan ditambahkan NULL.
-
Full Outer join Full Outer Join adalah sebuah JOIN yang berfungsi sebagai kombinasi dari Left Join dan Right Join dimana selain hasil
20 dari Inner Join dimasukkan ke dalam tabel hasil, baris-baris tabel yang tidak dimasukkan di dalam hasil akan di tambahkan dengan NULL dan dimasukkan ke dalam tabel hasil. •
Nested Loop Outer Join Operasi ini akan digunakan ketika sebuah Outer Join digunakan di antara 2 tabel dimana Outer Join akan mengembalikan baris di Outer Table meskipun tidak memenuhi kondisi JOIN. Hal ini menimbulkan 2 kondisi yaitu : -
Jika baris di Inner Table memenuhi kondisi maka tambahkan kolom di Inner Table ke Outer Table dan tampilkan ke dalam hasil.
-
Jika baris di Inner Table tidak ada yang memenuhi kondisi dengan Outer Table maka tampilkan baris di Outer Table dengan semua kolom di Inner Table diganti dengan NULL.
•
Sort Merge Outer Join Optimizer akan menggunakan Sort Merge untuk Outer Join apabila : -
Jika penggunaan Nested Loop Join tidaklah optimal karena jumlah data yang besar dan tidak adanya kondisi JOIN yang cukup efisien.
-
Jika Optimizer menemukan bahwa penggunaan Sort Merge akan meningkatkan
performa daripada Hash Join karena
21 operasi pengurutan pada Sort Merge tidak perlu untuk dilakukan lagi. Berikut ini adalah cara-cara bagaimana Optimizer menganalisis perintah JOIN yang akan dijalankan dalam query : Untuk mengeksekusi sebuah perintah JOIN maka Optimizer harus mengidentifikan beberapa hal, yaitu : 1. Cara akses perintah JOIN Untuk perintah-perintah yang sederhana, Optimizer harus menentukan cara mengakses yang paling optimal untuk mendapatkan data dari setiap tabel yang di- JOIN. 2. Metode JOIN Setiap ada perintah JOIN maka Optimizer akan menentukan metode JOIN mana yang paling tepat untuk digunakan, baik itu Nested Loop, Sort Merge, Cartesian atau Hash Joins. 3. Urutan Join Untuk mengeksekusi lebih dari 2 tabel maka Optimizer akan melakukan JOIN dua tabel terlebih dahulu. Hasil dari join 2 tabel tersebut akan di JOIN - kan lagi terhadap tabel berikutnya sampai semua tabel selesai di JOIN. 2. Fungsi agregat di dalam query Dalam mengembalikan nilai data, dibutuhkan beberapa perhitungan dalam query, yang dinyatakan oleh fungsi agregat dalam perintah SQL. Berikut ini adalah beberapa fungsi agregat standar dalam SQL, yaitu :
22 a. COUNT Perintah COUNT digunakan untuk menghitung jumlah data dalam sebuah tabel. Gambar 2.7 menunjukkan contoh penggunaan count dalam SQL yang berfungsi untuk menghitung jumlah karyawan : SELECT COUNT (*) FROM employees; Gambar 2. 7 Contoh penggunaan COUNT dalam SQL b. SUM Perintah SUM digunakan untuk mengembalikan total nilai dari kolom
yang
ditetapkan.
Gambar
2.8
menunjukkan
contoh
penggunaan SUM dalam SQL untuk menghitung jumlah dari seluruh gaji karyawan : SELECT SUM (salary) FROM employees; Gambar 2. 8 Contoh penggunaan SUM dalam SQL c. AVG Perintah AVG digunakan untuk mengembalikan rata-rata dari nilai kolom yang ditetapkan. Gambar 2.9 menunjukkan contoh dari perintah AVG untuk menghitung rata-rata dari gaji pegawai yang bekerja pada departemen tertentu : SELECT AVG (salary), department_name FROM employees a JOIN departments b ON a.department_id = b.department_id GROUP BY department_name; Gambar 2. 9 Contoh penggunaan AVG dalam SQL
23 d. MIN Perintah MIN digunakan untuk mengembalikan nilai terkecil dari kolom yang ditetapkan. Gambar 2.10 menunjukkan contoh perintah min untuk mencari nama dari pegawai yang mempunyai gaji paling kecil : SELECT first_name || ‘ ‘ || last_name FROM employees WHERE salary = ( SELECT MIN (salary) FROM employees ); Gambar 2. 10 Contoh penggunaan MIN dalam SQL e. MAX Perintah MAX digunakan untuk mengembalikan nilai yang terbesar dari kolom yang ditetapkan. Gambar 2.11 menunjukkan contoh perintah MAX untuk mencari nama dari pegawai yang mempunyai gaji paling besar : SELECT * FROM employees where salary = ( SELECT MAX (salary) FROM employees a JOIN departments b ON a.department_id=b.department_id WHERE department_name LIKE ‘Administration’ ); Gambar 2. 11 Contoh penggunaan MAX dalam SQL 3. Penggunaan Subquery dalam Query Dalam perintah SQL yang kompleks, sering kali ditemukan adanya query di dalam query. Hal ini disebut dengan subquery atau sering disebut dengan inner perintah SELECT.
24 Ada 3 jenis subquery, yaitu : a. Scalar subquery mengembalikan masing-masing satu kolom dan satu baris sehingga merupakan nilai tunggal. Contohnya adalah ketika ingin mencari gaji terbesar dari semua pegawai yang ada. b. Row subquery akan mengembalikan satu baris dengan banyak kolom. Contohnya adalah ketika ingin membandingkan gaji terbesar dan gaji terkecil semua karyawan. c. Tabel subquery akan mengembalikan banyak kolom dan banyak baris. Berikut ini adalah beberapa penggunaan subquery yang umum ditemui : a. Subquery with equality Penggunaan subquery ini bertujuan untuk mencari data dengan kondisi yang disesuaikan dengan data yang ada pada tabel di dalam subquery. Umumnya digunakan untuk mencari nilai data pada tabel yang lain untuk menyesuaikan data yang ada. Gambar 2.12 menunjukkan contoh perintah SQL untuk mengambil data employees yang berada pada departemen Administration : SELECT * FROM employees WHERE department_id = ( SELECT department_id FROM departments WHERE department_name LIKE ‘Administration’ ); Gambar 2. 12 Contoh penggunaan subquery dengan equality
25 b. Penggunaan subquery dengan fungsi agregat Ada beberapa aturan mengenai subquery dengan fungsi agregat, yaitu : • ORDER BY tidak boleh digunakan di dalam subquery (sekalipun digunakan pada perintah SELECT paling yang luar). • Perintah SELECT dalam subquery harus terdiri dari kolom tunggal, kecuali penggunaan EXISTS dalam subquery yang bersangkutan. • Ketika subquery adalah salah satu antara dua operan yang terlibat dalam perbandingan, maka subquery harus diletakkan di bagian kanan dari perbandingan. Gambar 2.13 menunjukkan contoh penggunaan subquery dengan agregat : SELECT first_name || ‘ ‘ || last_name, salary FROM employees WHERE (SELECT AVG (salary) FROM employees) < salary Gambar 2. 13 Contoh penggunaan subquery dengan agregat 2.2 Landasan Teori Khusus 2.2.1 Tuning Basis Data Ada berbagai cara yang dapat digunakan untuk melakukan tuning, salah satunya adalah tuning basis data. Berikut ini penjelasan singkat mengenai tuning basis data.
26 2.2.1.1.
Pengertian Tuning Basis Data Menurut Ramakrishnan dan Gehrke (2005, p650), pengertian tuning
basis data adalah peningkatan kinerja pada desain basis data secara fisikal yang mencakup relasi dan view sesuai dengan kebutuhan pengguna. 2.2.1.2.
Tujuan Tuning Basis Data Tujuan dari tuning basis data adalah : 1. Mengurangi waktu respon dari sistem ke pengguna akhir. 2. Mengurangi sumber daya yang diperlukan untuk melakukan pengolahan data dalam basis data.
Langkah umum untuk Tuning Basis Data Ada 3 cara untuk melakukan tuning basis data, yaitu : 1. Mengurangi waktu kerja dengan cara yang lebih efisien. Proses ini adalah dapat dilakukan dengan proses optimasi, yaitu mengganti sebuah query dengan query lain dengan fungsi sama namun dengan penggunaan sumber daya yang lebih sedikit dan waktu eksekusi yang lebih cepat. 2. Menyeimbangkan waktu kerja Sistem umumnya bekerja lebih berat pada waktu siang hari dibandingkan malam hari. Di siang hari, sistem melakukan pemrosesan transasi dan pekerjaan yang tidak terlalu penting dilakukan di malam hari (misalnya, proses backup). Oleh karena itu, perlu dilakukan pembagian tugas kerja untuk sistem pada waktu siang dan malam hari. Hal ini akan mengurangi sumber daya yang diperlukan untuk melakukan pekerjaan di siang hari.
27 3. Membuat waktu kerja secara pararel Query
untuk
mengakses
data-data
yang
besar
biasanya
dapat
dipararelkan. Hal ini sangat berguna untuk mengurangi waktu respon pada data-data yang jarang diakses secara bersamaan (Data Warehouse). Namun pada OLTP perlu diperhatikan dimana data-data tersebut memiliki waktu akses bersamaan yang sangat tinggi karena hal ini dapat meningkatkan
penggunaan
sumber
daya
yang
selanjutnya
akan
meningkatkan waktu respon dari program. 2.2.1.3.
Berbagai cara untuk meningkatkan kinerja dari Basis Data
Oracle 1. Merubah desain basis data Kinerja sistem yang lambat biasanya disebabkan dari desain basis data yang kurang baik. Pada desain basis data biasanya seseorang akan melakukan normalisasi ke dalam bentuk 3NF. Bentuk 3NF ini membuat beberapa akses ke basis data menjadi kurang cepat yang dapat dioptimalkan dengan melakukan denormalisasi untuk meningkatkan kinerja dari pengaksesan basis data. 2. Melakukan SQL Tuning Pengalaman menunjukkan bahwa 80% dari permasalahan dari kinerja basis data dapat diselesaikan dengan penggunaan SQL yang optimal sehingga eksekusi query oleh server dapat berjalan dengan cepat dan tidak menghabiskan sumber daya.
28 3. Memory Tuning Dengan menempatkan ukuran buffer yang tepat (untuk waktu menunggu, buffer hit ratios, system swapping dan paging). Maka optimasi basis data dapat dilakukan dengan melakukan pin pada objek-objek yang besar dan sering digunakan ke dalam memory. Hal ini dulakukan untuk mencegah pemanggilan yang terlalu sering (karena bila objek tersebut tidak di-pin maka akan membutuhkan sumber daya komputer yang lebih untuk memasukkan memory). 4. Disk I/O Tuning File-file dalam basis data perlu untuk diukur dan ditempatkan secara tepat di dalam sebuah sistem. Hal ini digunakan untuk mempermudah akses ke dalam file tersebut dimana semakin sulit file tersebut diakses dan semakin besar ukuran file tersebut akan meningkatkan penggunaan sumber daya I/O. Hal yang harus diperhatikan adalah bagaiman kita mengatur tempat dari file-file basis data tersebut agar mudah diakses sehingga meningkatkan kemudahan akses ke dalam file tersebut yang pada akhirnya akan mengurangi penggunaan sumber daya I/O. 5. Menghilangkan Database Contention Database Contention berhubungan dengan database locks, latches dan wait event yang terjadi dalam basis data. Untuk menghilangkan Database Contetion, harus dipelajari tentang ketiga hal tersebut dan menentukan apakah ketiga hal tersebut dapat dihilangkan untuk meningkatkan kinerja dari basis data anda.
29 6. 6 Mengopttimasi Sistem m Operasi Memonitor M d melakuukan optimaasi CPU, I/O dan O dan pengggunaan meemory sebagai s contooh adalah mengovercloc m ck CPU atauu memberikkan prioritas lebih kepada k basiss data Oraclee sehingga Oracle O dapatt menggunakkan lebih baanyak CPU C atau meemory untukk melakukan aktivitas di dalam basis data. 2.2.1.4. 2
A Average Actiive Session jumlah darri sesi yanng aktif (seedang Activee session merupakan m
melakukan m p pemanggilan n ke dalam basis b data attau tidak idlle). Gambarr 2.14 menunjukkan m n 4 orang pengguna p dim mana setiapp pengguna mengakses basis data d pada waktu w yang berbeda-bed b da. Pada baggian awal yang y ditunjuukkan pada p gambarr 2.14 hanyaa user 4 yanng melakukkan pemangggilan ke database laalu pada baggian kedua hanya h user 4 dan 3 yangg melakukann pemanggilan ke database d dann pada bagiian ketiga hanya h user 4, 4 3 dan 1 yang melakkukan pemanggilan p n ke database sehinggaa dapat disiimpulkan paada bagian awal hanya h 1 active session (user ( 4) padaa bagian keddua terdapatt 2 active seession dan d pada baggian 3 terdappat 3 active session s dan seterusnya. s
Gaambar 2. 14 Pengertian n dari Activee Session
30 Sebuuah sesi pemanggilan ke basis data teerdiri dari beeberapa kegiiatan yang y secara umum dapatt dikategorikkan menjadi 3 hal yaitu : CPU, IO dan wait w yang daapat dilihat pada p gambarr 2.15 berikuut :
Gamb bar 2. 15 Pembagian koomponen daari sebuah sesi s pemangggilan k basis dataa ke Secarra umum, Orracle akan menyimpan m setiap sesi daalam kurun waktu w per p detik beserta aktivitas yang dilakkukan oleh seesi tersebut yang y dapat dilihat d pada gambar g 2.166 berikut :
Gam mbar 2. 16 Illustrasi aktiivitas dari 4 sesi selamaa kurun wak ktu terten ntu yang diaambil oleh Oracle O per detik d
31 Untukk menggambbarkan hal inni secara benntuk grafik maka m Oracle secara s langsuung menggabbungkan hassil tersebut ke k dalam sebbuah diagram m batang b sepertti ditunjukkaan pada gam mbar 2.17 berrikut :
\ Gam mbar 2. 17 Diagram D baatang untuk k menggamb barkan aktivvitas darri sesi yang aktif a Namuun jika penggambaran seetiap sesi inii dilakukan per p detik makka gambar g yangg dihasilkan tidak t dapat dibaca d karenna garis yangg terlalu tipiss seperti s pada gambar g 2.188 :
Gam mbar 2. 18 Diagram D Baatang untuk k menggamb barkan aktiivitas dari sesi yang y aktif (per ( detik) Sehinngga untuk menggambar m rkan hal ini dengan d baik maka secaraa laangsung Oraacle akan meengambil bebberapa samppel dan meraata-rata nya untuk u mendapatkan m n sebuah Aveerage Activee Session yanng mana hasilnya akan leebih mudah m untukk dilihat sepeerti pada gam mbar 2.19
32
G Gambar 2. 19 Diagram Batang darri Average Active A Sessioon (dirata-rrata selama 15 detik) Pada oracle bentuuk graph terssebut digambbarkan seperrti pada gam mbar 2.20 2 dimana diagram battang ini dapaat dilihat darri Oracle Enttreprise Mannager pada p Tab perrformance yaang menggam mbarkan Avverage Activve Session (raatarata jumlah sesi s yang akttif yang secaara default dii rata-rata deengan 15 dettik)
Gambar 2. 20 Gambarr Average Active Session n pada Oracle Entreprise Manager Dari gam mbar Active Average A Sesssion yang diitunjukkan oleh o Oracle maka dapat diliihat bagaim mana kinerja basis data dalam kuruun waktu terrtentu dimana pada p gambaar Active Average A Sesssion tersebuut dapat teerlihat dengan jelas pekerjaaan apa yaang paling banyak b dilaakukan olehh sesi tersebut.
33 2.2.2 Teknik SQL Tuning 2.2.2.1 Pengertian SQL Tuning Menurut Immanuel Chan (2008, p11-1), SQL Tuning adalah sebuah proses optimasi dengan cara mengubah perintah-perintah SQL serta menentukan teknik indexing agar SQL tersebut bekerja secara optimal. 2.2.2.2 Langkah-langkah SQL Tuning Langkah pertama yang harus dilakukan untuk melakukan SQL Tuning adalah melakukan identifikasi terhadap High-Load SQL Statements. High-Load SQL Statements adalah query yang membebani server sehingga menyebabkan kinerja sebuah basis data menjadi lambat dan menghabiskan penggunaan sumber daya yang besar dari sistem. Sekalipun optimasi telah dilakukan pada basis data, namun penggunaan SQL yang tidak optimal akan menyebabkan performa basis data menurun. Melakukan identifikasi query ini merupakan aktivitas yang penting dari sebuah proses SQL Tuning, yang telah diotomatisasi dengan fitur ADDM (Automatic Database Diagnostic Monitor) pada Enterprise Manager Oracle 10g. Melakukan identifikasi High-Load SQL Statements merupakan sebuah aktivitas yang penting dari sebuah proses SQL tuning. Oracle 10 g telah melakukan otomatisasi hal ini dengan fitur ADDM (Automatic Database Diagnostic Monitor) atau dengan menggunakan Entreprise Manager untuk mencari High-Load SQL Statements tersebut. Ada 2 cara untuk melakukan identifikasi High-Load SQL Statements r, yaitu dengan menggunakan ADDM dan Top SQL.
34 a. Identifikasi High-Load SQL Statements dengan menggunakan ADDM Secara default, ADDM berjalan secara otomatis setiap jam dan melakukan analisa data yang dikumpulkan dari AWR (Automatic Workload Repository) untuk mengidentifikasi masalah pada kinerja basis data melalui snapshot. Dalam hal ini mencakup High-Load SQL Statements. Ketika terdapat masalah pada kinerja basis data, ADDM akan menampilkan permasalahan tersebut pada halaman ADDM dan memberikan rekomendasi untuk setiap masalah yang ditemukan. Sebagai contoh, ketika sebuah HighLoad SQL Statements ditemukan, ADDM akan memberikan rekomendasi untuk menggunakan SQL Tuning Advisor untuk perintah SQL tersebut. a. Identifikasi High-Load SQL Statements dengan menggunakan Top SQL Selain menggunakan ADDM. Oracle juga menyediakan sebuah fitur untuk melihat High-Load SQL Statements. Fitur yang disediakan oleh Oracle ini adalah sebuah halaman Top Activity yang terdapat pada Entreprise Manager
35 . Pada halaman ini kita dapat melihat High-Load SQL Statements, ditunjukkan pada gambar 2.21 berikut :
Gambar 2. 21 Halaman Top Activity pada Enterprise Manager Pada Enterprise Manager, klik Top Activity yang terdapat di tab Performance. Halaman Top Activity menunjukkan aktivitas yang berjalan pada basis data selama periode 1 jam. Pada bagian Top Activity, akan tampil chart yang menggambarkan kinerja basis data yang diukur dari average active sessions. Chart ini menunjukkan rata-rata sesi yang aktif dengan penggolongan kategori dari aktivitas yang dilakukan oleh setiap sesi tersebut dengan keterangan indikator warna dari aktivitas yang terdapat pada sisi kanan chart tersebut. Pada sisi bawah chart terdapat bagian tabel top SQL yang berisi daftar query yang membebani server dari seluruh kategori. Untuk
36 melihat detail dari query tersebut, klik pada kolom SQL ID di tabel top SQL, akan muncul teks query detail masing-masing top SQL. Untuk melihat perintah SQL pada kategori tertentu, klik tombol kategori yang diinginkan, kemudian akan tampil chart dari Active Sessions yang menunjukkan aktivitas dan top SQL akan diperbarui sesuai dengan waktu refresh yang dipilih oleh pengguna. 2.2.2.3 Restukturisasi Perintah-Perintah SQL Menurut Immanuel Chan (2008, p11-7 s.d 11-17), berikut ini adalah beberapa teknik untuk mengoptimalkan query : - Gunakan Equijoin Sedapat mungkin gunakanlah equijoin, karena perintah equijoin pada kolom yang belum ditransformasikan akan meningkatkan performa basis data dan mempermudah proses tuning. - Gunakan kolom-kolom yang belum ditransformasikan di dalam klausa WHERE Selalu
usahakan
untuk
menggunakan
kolom
yang
belum
ditransformasikan (tidak diberi fungsi seperti substr, to_char, to_number, dsb) pada klausa WHERE, seperti pada where clause pada gambar 2.22: WHERE a.order_no = b.order_no Gambar 2. 22 Contoh where clause dengan kolom yang belum ditransformasikan
37 dibandingkan dengan gambar 2.23 yaitu : WHERE TO_NUMBER (SUBSTR(a.order_no,INSTR (b.order_no, '.')-1)) =(SUBSTR (a.order_no,INSTR (b.order_no, '.')-1))
Gambar 2. 23 Contoh where clause dengan kolom yang ditransformasikan Adanya penggunaan fungsi SQL dalam klausa WHERE dapat membuat komputer menggunakan sumber daya lebih banyak untuk mengolah fungsi tersebut sehingga menyebabkan performa yang menurun karena selain butuh proses untuk menjalankan fungsi tersebut, indeks yang terdapat kolom tersebut tidak akan digunakan oleh optimizer. Jika ingin tetap menggunakan fungsi pada kolom dalam klausa WHERE dan indeks tetap digunakan maka gunakan indeks function-based agar optimizer tetap menggunakan indeks dalam proses eksekusi query tersebu. - Berhati-hatilah dengan tipe data baik secara eksplisit maupun implisit Oracle secara implisit memerlukan proses lebih apabila terdapat jika terdapat sebuah kondisi dimana terdapat perbandingan dari tipe data yang berbeda, dimana Oracle akan langsung mengkonversi tipe data tersebut menggunakan fungsi konversi, seperti contoh pada query gambar 2.24: AND charcol = numexpr Gambar 2. 24 Contoh klausa dimana terdapat implicit conversion Sekilas gambar 2.24 merupakan sintaks yang benar karena gambar 2.24 menggunakan kolom yang tidak diberi fungsi namun charcol mempunyai tipe data varchar dan numexpr mempunyai tipe data
38 numeric. Dalam hal ini, Oracle secara implisit akan mengubah query gambar 2.24 menjadi seperti query gambar 2.25: AND TO_NUMBER (charcol) = numexpr Gambar 2. 25 Implisit conversion yang dilakukan oleh Oracle Hal ini tentu saja dapat membuat indeks tidak digunakan oleh optimizer dan membutuhkan proses lebih untuk menjalankan fungsi konversi tersebut. - Jangan membuat SQL layaknya bahasa prosedural SQL bukanlah bahasa prosedural, jika sebuah SQL dibuat untuk berbagai tugas maka hasilnya akan kurang optimal untuk tugas tertentu. Oleh karena itu, lebih baik gunakanlah banyak perintah untuk setiap fungsi dibandingkan dengan sebuah perintah untuk banyak fungsi. Untuk query yang cukup kompleks, dapat menggunakan perintah yang kompleks menjadi lebih sederhana, seperti pada contoh query gambar 2.26: SELECT info FROM tabels WHERE ... AND somecolumn BETWEEN DECODE (:loval, 'ALL', somecolumn, :loval) AND DECODE (:hival, 'ALL', somecolumn, :hival);
Gambar 2. 26 Query yang kompleks dan menyebabkan indeks tidak terbaca Indeks pada kolom somecolumn tidak akan digunakan oleh Optimizer pada query gambar 2.26 karena ekspresi tersebut menggunakan kolom yang sama di kedua sisi BETWEEN. Jika ingin indeks dapat digunakan oleh Optimizer maka query tersebut dapat diganti dengan menggunakan
39 UNION ALL agar indeks dapat digunakan yaitu dengan query seperti pada gambar 2.27 : SELECT /* change this half of UNION ALL if other half changes */ info FROM tabels WHERE...AND somecolumn BETWEEN :loval AND :hival AND (:hival != 'ALL' AND :loval != 'ALL') UNION ALL SELECT /* Change this half of UNION ALL if other half changes. */ info FROM tabels WHERE...AND (:hival = 'ALL' OR :loval = 'ALL'); Gambar 2. 27 Query yang dioptimasi dengan menggunakan union all agar indeks dapat digunakan oleh optimizer - Penggunaan EXISTS dibandingkan IN untuk subquery Gunakan IN jika selective-predicate berada di dalam subquery dan gunakanlah EXISTS jika selective-predicate berada di dalam parent karena masing-masing penggunaan IN maupun EXISTS mempunyai keuntungannya masing-masing. Berikut ini contoh yang mendemonstrasikan keuntungan dari IN dan EXISTS. Kedua contoh ini menggunakan skema yang sama dengan karakteristik seperti berikut : • Terdapat indeks yang unik di employees.employee_id • Terdapat indeks di field orders.customer_id. • Terdapat indeks di field employees.department_id. • Tabel employee mempunyai 27000 baris • Tabel order mempunyai 10000 baris • Skema HR dan OE, keduanya dianalisa dengan COMPUTE Contoh 1 : penggunaan IN – selective predicate berada di dalam subquery
40 Query ini bertujuan untuk mencari semua pekerja yang melayani pesanan pada id pelanggan 144 Gambar 2.28 menunjukan SQL untuk mencari hal tersebut jika menggunakan perintah EXISTS SELECT e.employee_id,e.first_name,e.last_name,e.salary FROM employees e WHERE EXISTS(SELECT 1 FROM orders o /* Note 1 */ WHERE e.employee_id = o.sales_rep_id /* Note 2 */ AND o.customer_id = 144); /* Note 3 */ Gambar 2. 28 SQL dengan pengunaan perintah EXISTS *) Keterangan : Note 1 Penggunaan EXISTS Note 2 Penggunaan EXISTS merupakan correlated-subquery Note 3 Baris ini menunjukkan bahwa di dalam correlated-subquery ini terdapat sebuah selective-query Tabel 2.1 Execution plan dengan penggunaan EXISTS ID
OPERATION
OPTIONS
OBJECT_NAME
OPT
COST
0
SELECT STATEMENT
1
FILTER
2
TABEL ACCESS
FULL
EMPLOYEES
ANA
155
3
TABEL ACCESS
BY INDEX
ROWID ORDERS
ANA
3
4
INDEX
SCAN
ORD_CUSTOMER_IX
ANA
1
CHO
Tabel 2.1 menunjukkan proses eksekusi (dari V$SQL_PLAN) untuk perintah pada gambar 2.28. Proses eksekusi yang ditunjukkan pada table 2.1 memerlukan full-tabel scan dari tabel employees yang mana
41 menghasilkan banyak row dan kemudian setiap row tersebut difiltrasi dengan tabel order dengan menggunakan indeks. Gambar
2.29
menunjukkan
contoh
perintah
SQL
dengan
menggunakan IN : SELECT e.employee_id,e.first_name,e.last_name,e.salary FROM employees e WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 1 */ FROM orders o WHERE o.customer_id = 144); /* Note 3 */ Gambar 2. 29 SQL dengan penggunaan perintah IN *) Keterangan : Note 3
Baris ini menunjukkan bahwa correlated-subquery yang mengandung seleksi yang tinggi dengan ditandai dengan sintaks customer_id = number
Note 4
Baris ini menunjukkan bahwa digunakan IN. Subquery ini tidak lagi berkorelasi karena IN ini mengganti join di subquery.
Tabel 2.2 Execution plan dengan penggunaan perintah IN ID
OPERATION
OPTIONS
OBJECT_NAME
OPT
COST
0
SELECT STATEMENT
1
NESTED_LOOPS
5
2
VIEW
3
3
SORT
UNIQUE
4
TABEL ACCESS
FULL
ORDERS
ANA
1
5
TABEL ACCESS
BY INDEX
ROWID EMPLOYEES
ANA
1
6
INDEX
UNIQUE SCAN
EMP_EMP_ID_PK
ANA
CHO
3
42 Tabel 2.2 menunjukkan execution plan (dari V$SQL_PLAN) untuk perintah pada gambar 2.29. Optimizer menulis kembali subquery pada gambar 2.29 menjadi sebuah view yang kemudian di-JOIN melalui indeks di tabel employees. Hasil ini menunjukkan bahwa penggunaan IN lebih cepat karena subquery-nya mengandung selective predicate sehingga hanya mengeluarkan beberapa employee_id dan beberapa employee_id tersebut digunakan untuk mengakses employee tabel melalui unique-index. Contoh 2 : menggunakan EXISTS–selective predicate berada pada parent query Penggunaan EXISTS juga dapat menghasilkan query yang optimal. Contohnya jika terdapat sebuah query yang bertujuan untuk menampilkan semua karyawan yang mempunyai ID yang sama dengan ID perwakilan sales tertentu yang bekerja di departemen 80 dan pernah melayani pesanan pelanggan. Gambar 2.30 menunjukkan query dimana pengunaan IN tidaklah optimal:
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary FROM employees e WHERE e.department_id = 80 /*Note 5 AND e.job_id = 'SA_REP' /*Note 6 AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /*Note 4 Gambar 2. 30 Query dengan penggunaan IN *) Keterangan : Note 4
Penggunaan IN menunjukkan bahwa subquery tidak
43 lagi
berkorelasi
karena
penggunaan
IN
menggantikan join. Note 5 and 6 Menunjukkan selective-query. Tabel 2. 3 Execution plan dari pengunaan IN ID
OPERATION
OPTIONS
OBJECT_NAME
OPT
COST
0
SELECT STATEMENT
1
NESTED LOOPS
125
2
VIEW
116
3
SORT
UNIQUE
4
TABEL ACCESS
FULL
5
TABEL ACCESS
CHO
116 ORDERS
ANA
40
EMPLOYEES
ANA
1
EMP_EMP_ID_PK
ANA
BY INDEXROWID 6
INDEX
UNIQUE SCAN
Tabel 2.3 menunjukkan execution plan (dari V$SQL_PLAN) untuk perintah pada gambar 2.30. Proses ini akan membuat optimizer membuat sebuah view yang berisi banyak employee_id karena tidak adanya selective-predicate dan kemudian akan dibandingkan dengan tabel employees melalui indeks yang unik. Gambar 2.31 menunjukan contoh query yang menggunakan EXISTS : SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.department_id = 80 /*Note 5*/ AND e.job_id = 'SA_REP' /*Note 6*/ AND EXISTS (SELECT 1 /*Note 1*/ FROM orders o WHERE e.employee_id =o.sales_rep_id); /*Note 2*/ Gambar 2. 31 Query dengan pengunaan EXISTS
44 *) Keterangan : Note
1
Baris ini menunjukkan penggunaan EXISTS.
Note
2
Baris ini menunjukkan bahwa subquery ini merupakan correlated-subquery.
Note 5 & 6 Baris ini menunjukkan selective-predicate di dalam query tersebut. Tabel 2. 4 Execution plan dari pengunaan EXISTS ID
OPERATION
0
SELECT STATEMENT
1
FILTER
2
TABEL ACCESS
3
AND-EQUAL
4
OPTIONS
OBJECT_NAME
OPT
COST
CHO
BY INDEX ROWID
EMPLOYEES
ANA
INDEX
RANGE SCAN
EMP_JOB_IX
ANA
5
INDEX
RANGE SCAN
EMP_DEPARTMENT_IX
ANA
6
INDEX
RANGE SCAN
ORD_SALES_REP_IX
ANA
98
8
Tabel 2.4 menunjukkan execution-plan (dari V$SQL_PLAN) untuk perintah SQL pada gambar 2.31. Cost penggunaan EXISTS lebih kecil karena 2 buah indeks digunakan untuk mengurutkan parent query sehingga menghasilkan beberapa employee_id kemudian beberapa employee_id tersebut digunakan untuk mengakses tabel orders melalui indeks. - Mengatur cara akses dan JOIN melalui hints Kita dapat mengatur pilihan langkah JOIN, INDEX yang dipakai dan cara mengakses yang dilakukan oleh optimizer dengan cara menggunakan hints di perintah SQL. Contoh nya adalah hints /*+FULL */ untuk
45 memaksa optimizer menggunakan FULL TABEL SCAN meskipun terdapat indeks seperti pada query gambar 2.32 : SELECT /*+ FULL(e) */ e.ename FROM emp e WHERE e.job = 'CLERK'; Gambar 2. 32 Contoh pengunaan HINTS FULL - Hati-hati dalam menggunakan perintah JOIN Dalam query, perintah JOIN dapat menyebabkan efek yang signifikan terhadap performa. Sehingga penggunaan perintah JOIN haruslah sangat diperhatikan karena sebuah penggunaan JOIN yang tidak diperlukan akan menyebabkan performa menurun drastis. Untuk memenuhi tujuan performa, terdapat 3 aturan penting yaitu : •
Hindari FULL TABEL SCAN jika row bisa didapatkan melalui penggunaan indeks.
•
Selalu gunakan indeks yang mengembalikan lebih sedikit baris.
•
Sesuaikan urutan JOIN sehingga tabel yang paling jumlah barisnya paling sedikit akan dieksekusi paling awal oleh optimizer.
46 Gambar 2.33 menunjukkan bagaimana cara untuk mengoptimasi urutan JOIN : SELECT info FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN 100 AND 200 AND b.bcol BETWEEN 10000 AND 20000 AND c.ccol BETWEEN 10000 AND 20000 AND a.key1 = b.key1 AND a.key2 = c.key2;
Gambar 2. 33 Contoh perintah join yang dapat dikerjakan dengan berbagai kondisi Jumlah baris yang akan diperiksa dari tabel a ada 100, sedangkan tabel b dan tabel c mempunyai 10000 baris. Query dengan predikat yang demikian akan menghasilkan 2 hasil yang sangat jauh berbeda, yaitu : Kondisi pertama : Jika proses selective-query pertama kali dilakukan pada tabel b join dengan c, akan membutuhkan 10000 proses, sekalipun terdapat indeks. Hasilnya akan join dengan tabel a yang hanya mempunyai 100 baris, dengan ini akan membutuhkan 100 proses. Kondisi kedua : Jika tabel a di – join - kan dengan tabel b terlebih dulu maka jumlah proses yang digunakan adalah 100 proses yang akan berjalan dengan cepat melalui penggunaan indeks. Kemudian 100 baris yang dihasilkan akan join dengan tabel c yang juga akan memerlukan 100 proses dan akan berjalan dengan cepat karena penggunaan indeks.
47 - Berhati hatilah dalam menggunakan view Dalam menggunakan view pastikan bahwa semua tabel yang digunakan di dalam view tersebut terpakai karena proses JOIN terhadap tabel yang tidak dipakai akan mengurangi performa. Apabila hal tersebut terjadi maka gunakanlah view baru daripada membuat sebuah view yang reusable namun dengan performa yang kurang baik. - Melakukan restrukturisasi indeks. Perfoma dapat ditingkatkan dengan cara melakukan restrukturisasi indeks yang dapat dilakukan dengan cara-cara sebagai berikut : 1.
Menghapus nonselective indeks untuk mempercepat proses DML.
2.
Memberi indeks pada bagian selective predicate pada query.
3.
Cobalah
untuk
mengurutkan
kolom
yang
terdapat
di
concatenated-index. 4.
Menambahkan kolom ke dalam indeks untuk meningkatkan selectivity.
- Mengubah dan menonaktifkan triggers dan constraint Semakin banyak trigger dan contraint yang digunakan maka performa sistem juga akan menurun sehingga penggunaan keduanya haruslah diperhatikan secara tepat.s - Melakukan restrukturisasi data Setelah melakukan restrukturisasi indeks dan perintah SQL, restukturisasi data dapat dilakukan dengan cara berikut : •
Hindari penggunaan GROUP BY di dalam perintah-perintah yang kritikal.
48 •
Cobalah untuk melakukan peninjauan kembali desain basis data yang ada, apakah desain basis data sudah optimal. Jika hasilnya adalah tidak, maka cobalah lakukan beberapa optimasi, misalnya melakukan proses denormalisasi.
•
Gunakanlah partisi jika memungkinkan.
- Menggabungkan multi-scan dengan perintah CASE Penggunaan agregasi dengan fungsi set yang bermacam-macam hanya untuk 1 set data sangat sering dijumpai pada query. Pengambilan 10 fungsi set dengan sintaks yang sama namun dengan kondisi yang berbeda (klausa WHERE) akan memerlukan proses 10 kali scan pada 10 query. Hal ini dapat dihilangkan dengan cara memindahkan kondisi WHERE di setiap scan ke dalam sebuah kolom dengan penggunaan perintah CASE untuk melakukan penyaringan data. Sebagai contoh pada query ini kita akan menghitung jumlah karyawan yang mendapatkan gaji lebih kecil dari 2000, diantara 2000 dan 4000 dan lebih dari 4000 setiap bulannya. Hal ini dapat dilakukan dengan 3 query (3 scan) yang berbeda seperti pada gambar 2.34, 2.35 dan 2.36 : SELECT COUNT (*) FROM employees WHERE salary < 2000; Gambar 2. 34 Query untuk mengambil jumlah karyawan yang gajinya lebih kecil dari 2000
49
SELECT COUNT (*) FROM employees WHERE salary BETWEEN 2000 AND 4000; Gambar 2. 35 Query untuk mengambil jumlah karyawan yang gajinya antara 2000 dan 4000
SELECT COUNT (*) FROM employees WHERE salary>4000; Gambar 2. 36 Query untuk mengambil jumlah karyawan yang gajinya lebih besar dari 4000 3 Query tersebut dapat diefisienkan dengan mengubah query tersebut menjadi sebuah single query dimana setiap hasil yang ingin didapatkan ditaruh didalam sebuah kolom dengan menggunakan CASE untuk kondisinya. Gambar 2.37 adalah contoh penggunaan CASE untuk menghilangkan multiple scan tersebut : SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees;
Gambar 2. 37 Pengunaan CASE untuk menggabungkan MULTISCAN - Gunakan DML dengan klausa RETURNING Gunakanlah perintah INSERT, UPDATE, atau DELETE…RETURNING untuk mengambil dan mengubah data dengan sekali panggil. Teknik ini
50 akan
meningkatkan
perfoma
dengan
cara
mengurangi
jumlah
pemanggilan ke dalam basis data. -
Cobalah menggabungkan beberapa perintah menjadi sebuah perintah yang sederhana. Contoh yang paling dasar dalam hal ini adalah perintah DELETE seperti pada gambar 2.38: BEGIN FOR pos_rec IN (SELECT * FROM order_positions WHERE order_id = :id) LOOP DELETE FROM order_positions WHERE order_id = pos_rec.order_id AND order_position =pos_rec.order_position; END LOOP; DELETE FROM orders WHERE order_id = :id; END;
Gambar 2. 38 Penggunaan sintaks delete yang kurang optimal Perintah diatas akan melakukan penghapusan sebanyak 2 kali yaitu penghapusan data di dalam tabel order_positions kemudian penghapusan data di dalam tabel order. Hal ini tentu saja menghabiskan sumber daya karena perintah tersebut akan disampaikan ke dalam database sebanyak 2 kali. Perintah tersebut dapat diganti menjadi sebuah perintah dengan hanya menambahkan 1 buah constraint yaitu constraint cascade dimana hanya sebuah perintah delete saja yang digunakan yang secara otomatis akan mempercepat kerja dari proses delete ini. 2.2.2.4 Penggunaan Bind Variable Setiap kali sebuah query dikirim ke basis data, teks dari query tersebut akan diperiksa apakah teks dari query tersebut sudah pernah ada di
51 dalam shared pool. Jika tidak ada teks query yang sesuai dengan teks query yang ada di dalam shared pool, maka akan dilakukan hard parse. Sebaliknya, jika teks dari query tersebut sudah ada di dalam shared pool, maka akan dilakukan soft parse. Nilai variabel yang berbeda-beda pada query dapat membuat query tersebut dibaca secara berbeda, sehingga akan dilakukan hard parse. Oleh karena itu, penggunaan bind-variable pada teks query yang sama membuat query dapat digunakan kembali atau dibaca sama dengan query yang pernah dimasukkan sebelumnya. Hanya perubahan nilai dari bind-variable yang berubah-ubah. Tujuan dari penggunaan bind-variable untuk mengisi nilai variabel pada query adalah : •
Terlalu sering memasukkan teks query yang sama (hanya berbeda nilai variabel) hanyalah akan membuang banyak memori.
•
Memasukkan teks query yang sama ke dalam shared pool akan membuat query tersebut secara cepat dieksekusi keluar dari shared pool tersebut.
•
Melakukan parsing untuk query merupakan proses intensif yang membutuhkan sumber daya yang tidak kecil. Mengurangi jumlah hard-parse akan mengurangi jumlah penggunaan CPU.
2.2.3 Tuning dengan Indexing 2.2.3.1 Pengertian Indeks Menurut Ramakrishnan dan Gehrke (2005, p276), indeks adalah struktur data yang mengatur record data pada disk untuk melakukan optimasi bermacam-macam operasi pencarian keterangan. Dengan menggunakan
52 indeks, kondisi pencarian pada record-record dapat dipermudah dengan field kunci pencarian. Cara lainnya adalah membuat indeks tambahan pada kumpulan data, masing-masing dengan kunci pencarian yang berbeda, untuk mempercepat operasi pencarian yang tidak didukung oleh organisasi file. 2.2.3.2 Teknik-teknik Indexing Menurut Immanuel Chan (2008, p2-11), ada berbagai tipe indexing yang dapat dilakukan, antara lain : -
B-Tree Indexes Merupakan teknik indeks yang standar dengan keunggulan untuk primary key dan indeks dengan pemilihan selektif yang tinggi. Indeks dengan teknik B-tree ini dapat digunakan untuk mengembalikan data yang diurutkan berdasarkan indeks pada kolom.
-
Bitmap indexes Teknik ini cocok untuk data dengan kardinalitas yang minimum. Melalui kompresi data, teknik ini dapat menghasilkan row-id dalam jumlah yang besar dengan penggunaan I/O yang minimal. Kombinasi teknik indeks bitmap pada kolom yang tidak diseleksi dapat memberikan efisiensi penggunaan operasi AND dan OR dengan menghasilkan row-id dalam jumlah yang besar dan penggunaan I/O yang minimal. Teknik ini secara khusus efektif dalam query dengan perintah COUNT().
-
Function-based Indexes Teknik ini dapat membuat akses melalui B-tree pada nilai yang diturunkan dari fungsi yang ada pada data dasar. Teknik ini memiliki
53 batasan dengan penggunaan NULL dan membutuhkan penggunaan optimasi query. Teknik function-based indexes ini secara khusus berguna ketika
melakukan
query
pada
kolom-kolom
campuran
untuk
menghasilkan data yang diturunkan atau untuk menanggulangi batasan data yang disimpan dalam basis data. -
Partitioned Indexed Indeks dengan partisi dapat dilakukan dengan 2 cara, yakin partisi indeks global dan partisi indeks secara lokal. Indeks global digambarkan dengan hubungan "one-too-many", dengan satu partisi indeks yang akan dipetakan ke banyak partisi tabel. Global indeks hanya dapat digunakan dengan partisi dengan jangkauan tertentu. Indeks lokal digambarkan dengan pemetaan hubungan "one-to-one" antara partisi indeks dan partisi tabel. Secara umum, indeks lokal mengijinkan pendekatan "divide and conquer" untuk menghasilkan eksekusi perintah SQL dengan cepat. •
Indeks terpartisi secara lokal Dengan menggunakan indeks yang terpartisi secara lokal, DBA dapat mengambil partisi tunggal dari tabel dan indeks secara "offline"
untuk
tujuan
pemeliharaan
(reorganisasi)
tanpa
mempengaruhi partisi tabel dan indeks lain. Dalam indeks partisi secara lokal, nilai kunci dan jumlah partisi indeks akan disesuaikan dengan partisi yang ada pada tabel dasar.
54 Gambar 2.39 menunjukkan contoh pembuatan indeks terpatisi lokal : CREATE INDEX year_idx on all_fact(order_date) LOCAL (PARTITION name_idx1), (PARTITION name_idx2), (PARTITION name_idx3); Gambar 2. 39 Contoh pembuatan indeks terpartisi lokal ORACLE secara otomatis akan menggunakan indeks yang disesuaikan dengan jumlah partisi indeks pada tabel yang bersangkutan. Misalnya pada gambar 2,32, jika dibuat 4 indeks pada tabel all_fact, maka pembuatan indeks ini akan gagal karena jumlah partisi indeks dan partisi tabel tidak sesuai. Pemeliharaan akan mudah dilakukan jika menggunakan partisi indeks ini karena partisi tunggal dapat diambil secara "offline" dan indeks dapat dibangun tanpa mempengaruhi partisi lain di dalam tabel. •
Indeks terpartisi secara global : Penggunaan indeks yang terpartisi secara global digunakan untuk semua indeks, kecuali salah satunya digunakan sebagai kunci pada partisi tabel. Penggunaan indeks yang terpartisi secara global ini sangat berguna implementasinya dalam aplikasi OLTP (Online Transaction Processing) dimana indeks yang lebih sedikit dibutuhkan daripada indeks partisi secara lokal. Kelemahan menggunakan
indeks
global
ini
adalah
sulit
dilakukan
pemeliharaan, karena harus mengubah partisi pada tabel yang
55 bersangkutan. Misalnya salah satu partisi tabel dihapus untuk reorganisasi tabel, maka seluruh indeks global yang berlaku akan terpengaruh, sehingga untuk mendefinisikan indeks global ini harus dipastikan DBA memiliki kebebasan untuk menentukan partisi sesuai dengan indeks yang diinginkan. Gambar 2.40 menunjukkan contoh pembuatan indeks global pada partisi tabel: CREATE INDEX item_idx on all_fact (item_nbr) GLOBAL (PARTITION city_idx1 VALUES (PARTITION city_idx1 VALUES (PARTITION city_idx1 VALUES (PARTITION city_idx1 VALUES (PARTITION city_idx1 VALUES
LESS LESS LESS LESS LESS
THAN THAN THAN THAN THAN
(100)), (200)), (300)), (400)), (500));
Gambar 2. 40 Contoh pembuatan indeks terpartisi global Partisi dengan penggunaan indeks global akan mengurangi penggunaan I/O secara signifikan dan dengan waktu yang singkat apabila pembuatan indeks global ini mempunyai pembagian dengan jarak yang baik. -
Reverse Key Indexes Teknik ini dirancang untuk mengeliminasi indeks yang digunakan saat memasukkan data pada aplikasi. Teknik ini terbatas saat digunakan untuk pembacaan jarak indeks.
2.2.3.3 Concatenated Index Menurut Niemiec (2007, p39), sebuah indeks (single index) dapat terkait dengan beberapa kolom yang diberi indeks, disebut concatenated atau
56 composite index. Oracle 9i memperkenalkan proses eksekusi “skip-scan index access ” sebagai salah satu pilihan bagi optimizer ketika ada concatenated index. Oleh karena itu, perlu diperhatikan ketika menetapkan urutan kolom dalam indeks tersebut. Secara umum, kolom yang pertama kali dituliskan pada pembuatan indeks haruslah merupakan kolom yang paling sering digunakan sebagai selective column pada klausa WHERE. Sebagai contoh terdapat concatenated index pada kolom Empno, Ename, dan Deptno (Empno adalah bagian pertama, Ename adalah bagian kedua dan Deptno adalah bagian ketiga). Gambar 2.41 menunjukkan contoh query dimana concatenated index tidak dibaca karena leading columnnya adalah empno: SELECT job, empno FROM emp WHERE ename = ‘RICH’; Gambar 2. 41 Query dimana kolom pada where clause tidak berupa leading column Dalam hal ini, kolom ename bukanlah kolom pertama yang dinyatakan dalam concatenated index sehingga optimizer tidak akan mengeksekusi indeks yang telah dibuat. Namun pada Oracle 9i, diperkenalkan sebuah proses eksekusi “skip-scan index” yang memungkinkan optimizer menggunakan concatenated index meskipun kolom yang pertama kali dinyatakan dalam indeks tidak terdapat pada klausa WHERE. Optimizer akan tetap akan memilih proses eksekusi yang paling optimal, apakah dengan index skip-scan access, index fast full scan atau dengan full table scan. Jika ada sebuah concatenated index pada sebuah query yang akan dieksekusi, maka proses eksekusi dengan “skip-scan index” akan lebih cepat
57 dibandingkan dengan proses eksekusi dengan “index fast full scan”. Misalnya pada contoh berikut : Gambar 2.42 berikut menunjukkan contoh pembuatan concatenated index: CREATE INDEX skip1 ON emp5(job,empno); Gambar 2. 42 Contoh sintaks untuk membuat concatenated index Gambar 2.43 menunjukkan query dimana concatenated index tidak digunakan karena kolom pada where clause bukanlah leading column : SELECT count(*) FROM emp5 WHERE empno = 7900; Gambar 2. 43 Query dimana concatenated index tidak terbaca karena bukan merupakan leading column Gambar 2.44 menunjukkan hasil eksekusi yang dihasilkan melalui pada query gambar 2.43 dengan SQL*Plus : Elapsed: 00:00:03.13 (Result is a single row…not displayed) Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'SKIP1' (NON-UNIQUE) Statistics 6826 consistent gets 6819 physical reads
Gambar 2. 44 Execution plan dimana optimizer menggunakan fast full scan Menurut Niemiec (2007, p48), jika urutan kolom yang dibuat dalam concatenated index tidak sesuai dengan query yang akan dieksekusi (leading column tidak ada di dalam where clause) maka query di atas dieksekusi dengan fast full scan yang memerlukan waktu eksekusi dalam waktu 3 menit
58 13 detik. Namun kita bisa memaksa optimizer mengabaikan hal itu meskipun leading column tidak berada dalam where clause dengan cara menggunakan hints “skip-scan” dimana cara penggunaannya seperti pada query gambar 2.45 : SELECT /*+ index_ss(emp5 skip1) */ count(*) FROM emp5 WHERE empno = 7900;
Gambar 2. 45 Query dimana digunakan Index Hint untuk memaksa optimizer menggunakan index concatenated. Gambar 2.46 menunjukkan hasil eksekusi yang dihasilkan melalui query di atas dengan SQL*Plus : Elapsed: 00:00:00.56 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5) 1 0 SORT (AGGREGATE) 2 1 INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE) Statistics 21 consistent gets 17 physical reads
Gambar 2. 46 Execution plan dari penggunaan index hint untuk concatenated index Jika query dijalankan dengan proses eksekusi “skip-scan index”, maka waktu yang diperlukan untuk melakukan eksekusi query ini adalah 56 detik. Oleh karena itu, urutan kolom-kolom dalam sebuah concatenated index yang ingin dibuat perlulah diperhatikan. Hal ini sangat mempengaruhi optimizer melakukan proses eksekusi pada query yang bersangkutan.
59 2.2.3.4 Index-hint Menurut Chan, Immanuel (2008 p16-8), index hint dalam sebuah query berguna ketika pengguna ingin “memaksakan” sebuah indeks dieksekusi oleh optimizer. Penggunaan index hint digunakan agar optimizer menggunakan proses eksekusi sebuah query dengan akses melalui indeks yang dinyatakan dalam sebuah index hint. Gambar 2.47 menunjukkan skema bagaimana index hint bekerja :
Gambar 2. 47 Skema Index hint bekerja Syntax pembuatan index hint adalah :
/*+ index(nama_table nama_indeks) */
Gambar 2. 48 Syntax pembuatan index hint Gambar 2.49 menunjukkan bagaimana cara pengunaan index hint /*+ index(emp5 skip1) */ Gambar 2. 49 Cara pengunaan index hint Ketika sebuah index hint digunakan, maka optimizer akan melakukan eksekusi sesuai dengan nama indeks yang dispesifikasikan pada query tersebut. Jika daftar kolom dan indeks bersesuaian, maka indeks tersebut yang akan dieksekusi. Jika indeks tersebut tidak ada, maka indeks yang berkaitan
60 dengan tabel dan kolom yang dimaksud di awal dalam urutanlah yang akan dieksekusi. 2.2.3.5 Penggunaan Indeks yang Tidak Tepat Menurut Niemiec (2007, p40) sebagai analogi, terdapat sebuah tabel “produk” yang mempunyai kolom “company_no”. Perusahaan tersebut hanya memiliki 1 cabang sehingga nilai kolom tersebut dari semua baris di dalam tabel “produk” adalah 1. Jika ada indeks pada kolom tersebut, maka optimizer tidak akan menggunakan indeks tersebut. Hal ini disebabkan proses eksekusi dengan indeks akan memperlambat proses eksekusi dibandingkan proses eksekusi dengan “Table Access Full”. Gambar 2.50 menunjukkan penggunaan Table Access Full (Full Table Scan) meskipun terdapat index : SELECT product_id, qty FROM product WHERE company_no = 1;
Gambar 2. 50 Contoh Query dimana Optimizer akan menggunakan full table scan walaupun terdapat index
61 Gambar 2.51 menunjukkan hasil eksekusi yang dihasilkan melalui query gambar 2.50 dengan SQL*Plus : Elapsed time: 405 seconds (all records are retrieved via a full table scan) OPERATION OPTIONS OBJECT NAME ------------------ -------------- ----------SELECT STATEMENT TABLE ACCESS FULL PRODUCT 49,825 consistent gets (memory reads) 41,562 physical reads (disk reads)
Gambar 2. 51 Execution plan dari query pada gambar 2.50 Gambar 2.52 menunjukkan penggunaan hint untuk memaksa penggunaan index oleh optimizer SELECT /*+ index(product company_idx1) */ product_id, qty FROM product WHERE company no = 1;
Gambar 2. 52 Query dengan sintaks hint untuk memaksa penggunaan index Gambar 2.53 menunjukkan hasil eksekusi yang dihasilkan melalui query gambar 2.52 dengan SQL*Plus : Elapsed time: 725 seconds (all records retrieved using the index on company_no) OPERATION OPTIONS OBJECT NAME ------------------ -------------- ----------SELECT STATEMENT TABLE ACCESS BY ROWID PRODUCT INDEX RANGE SCAN COMPANY_IDX1 4,626,725 consistent gets (memory reads) 80,513 physical reads (disk reads)
Gambar 2. 53 Execution plan dari query pada gambar 2.52
62 Dari kedua perbandingan di atas terlihat bahwa jika sebuah index tidaklah optimal untuk menjalankan sebuah query maka optimizer akan lebih memilih untuk menggunakan “Table Access Full” karena penggunaan index tersebut membuat optimizer harus bekerja lebih dimana selain mencari semua baris dalam tabel, optimizer juga harus mencari baris dalam index yang dispesifikasikan. Menurut Niemiec (2007, p41-42) Ada beberapa hal yang membuat index tidak dibaca oleh optimizer, yaitu : - Penggunaan operator ‘<>’ dan ‘!=’ Indeks hanya dapat digunakan untuk menemukan data yang terdapat di dalam tabel. Setiap terdapat operator not equal di dalam klausa WHERE, indeks yang terdapat di dalam kolom yang direferensikan tidak akan digunakan. Sebagai contoh pada gambar 2.54, terdapat sebuah tabel CUSTOMER dan terdapat indeks di dalam kolom CUST_RATING dan query yang ingin dijalankan adalah untuk mengambil data dimana cust_ratingnya bukan ‘aa’. Proses eksekusi yang dijalankan adalah full table scan walaupun terdapat indeks pada kolom CUST_RATING. SELECT cust_id, cust_name FROM customers WHERE cust_rating <> 'aa'; Gambar 2. 54 Query dimana index tidak digunakan karena adanya sintaks ‘<>’
63
- Penggunaan is null atau is not null Ketika terdapat penggunaan sintaks is null atau is not null di dalam klausa WHERE maka optimizer tidak akan menggunakan indeks tersebut. Hal ini disebabkan karena nilai dari null tidak terdefinisi sehingga tidak ada nilai di dalam basis data yang sama dengan NULL. Gambar 2.55 menunjukkan contoh query yang menyebabkan full table scan walaupun pada kolom sal terdapat indeks karena pengunaan is null dalam klausa WHERE : SELECT empno, ename, deptno FROM emp WHERE sal is null;
Gambar 2. 55 Query dimana index tidak digunakan karena pengunaan sintaks is null
