BAB 2 LANDASAN TEORI

BAB 2 LANDAS AN TEORI

2.1

Data Data adalah dokumentasi dari fakta-fakta mentah yang mewakili kejadian atau

transaksi yang terjadi di dalam suatu organisasi namun belum terorganisir dan tersusun ke dalam bentuk yang mudah dimengerti dan digunakan untuk kebutuhan organisasi dalam lingkup tertentu. Seperti apa yang disampaikan oleh Turban dan Volonino (2010, p41), data adalah suatu deskripsi dasar dari suatu hal, kejadian, aktivitas, dan transaksi-transaksi yang direkam, diklasifikasikan, tetapi tidak diorganisasikan untuk menyampaikan arti yang khusus atau spesifik. Sedangkan Laudon dan Laudon (2010, p46) menyatakan bahwa data adalah aliran dari fakta-fakta mentah yang merupakan kejadian yang terjadi di dalam organisasi atau lingkungan fisik sebelum diorganisir dan disusun ke dalam bentuk yang bisa dimengerti dan digunakan. Tetapi Jessup dan Valacich (2008, p11) memberikan definisi yang agak berbeda, data adalah fakta mentah yang dicatat, informasi yang belum disusun, seperti kata-kata dan angka.

129

9 Untuk memperjelas pengertian dari data, tabel 2.1 berikut adalah contoh kumpulan data member pada database SM S Community. Tabel 2.1 Kumpulan data member pada database SM S Community

2.2

Informasi Informasi adalah data yang sudah diolah dan disusun ke dalam suatu format atau

hasil dengan menggunakan metode tertentu sehingga lebih berguna dan memiliki makna atau nilai yang mendukung dalam pengambilan keputusan bagi organisasi. Seperti pengertian informasi yang dijelaskan oleh Jessup dan Valacich (2008, p10), informasi adalah data yang telah diformat atau diproses dengan tanda penghubung atau label yang lebih berguna daripada data yang belum diformat. Hal ini berubah menjadi informasi, yang dapat didefinisikan sebagai perwakilan dari kenyataan atau realitas. Serta menurut Laudon dan Laudon (2010, p46), informasi adalah data yang telah dibentuk ke dalam suatu format yang bermakna dan berguna bagi manusia.

10 Untuk memperjelas pengertian dari informasi, gambar 2.1 di bawah ini adalah contoh informasi jumlah poin member pada aplikasi SM S Community.

Gambar 2.1 Informasi jumlah poin member pada SM S Community

2.3

Rich Picture Rich Picture adalah suatu gambaran untuk menjelaskan proses bisnis yang tidak

memiliki standar dalam penggambarannya. Untuk pengertian rich picture yang lebih mendalam, M athiassen (2000, p26) menjelaskan bahwa rich picture adalah gambaran informal yang menampilkan situasi yang dideskripsikan oleh illustrator. Rich picture fokus pada aspek yang penting pada situasi. Rich picture harus memberikan sebuah deskripsi yang luas dari situasi yang memungkinkan beberapa tafsiran alternatif.

11

Gambar 2.2 Rich Picture Sumber: M athiassen (2000), Object Oriented Analysis & Design 2.4

Database Database adalah kumpulan data yang saling berhubungan dan terorganisir untuk

disimpan dalam bentuk tabel, dan dapat dilihat atau ditampilkan kembali, serta membantu organisasi dalam memenuhi kebutuhan informasi. Pengertian database juga dikemukakan oleh Connoly dan Begg (2010, p65), database adalah kumpulan data yang saling berhubungan satu sama lain secara logis dan deskripsi kumpulan data ini dirancang untuk memenuhi kebutuhan informasi suatu organisasi. Namun menurut Turban dan Volonino (2010, p41), database terdiri dari item data yang disimpan dan terorganisir untuk diambil datanya.

12 Untuk memperjelas pengertian database, gambar 2.3 berikut adalah contoh database dalam suatu rumah sakit:

Gambar 2.3 Database 2.5

Normalisasi Normalisasi adalah sebuah upaya untuk memperoleh sebuah database dengan

struktur yang baik (ruang penyimpanan yang efisien) dengan cara menerapkan sejumlah aturan pada setiap tabel yang termasuk dalam database tersebut. Tetapi Connoly dan Begg (2010, p415) memberikan pengertian normalisasi yang agak berbeda yaitu suatu teknik untuk menghasilkan kumpulan hubungan relasional dengan data yang diinginkan, yang sesuai dengan kebutuhan data suatu perusahaan. Tujuan dari normalisasi adalah untuk mengidentifikasi satu set relasi yang sesuai sehingga dapat mendukung kebutuhan data suatu perusahaan. Karakteristik dari satu set relasi yang sesuai meliputi: •

Jumlah minimal atribut yang diperlukan untuk mendukung kebutuhan data perusahaan.

13 •

Atribut yang mempunyai relationship logis (digambarkan sebagai dependensi fungsional) yang ditemukan dalam relasi yang sama.

•

M eminimalkan redundansi, dengan cara masing-masing atribut diwakili hanya sekali, kecuali atribut yang berperan sebagai foreign key, dimana merupakan atribut yang penting untuk di-join atau pada relasi yang berhubungan.

Keuntungan menggunakan database yang memiliki relasi adalah database akan lebih mudah bagi user untuk diakses dan dipelihara datanya, serta diminimalisasi penggunaan media penyimpanan pada komputer.

Normalisas i

Gambar 2.4 Redundansi Data

14

Tahap-tahap normalisasi terdiri dari: a. First Normal Form (1NF) Suatu relasi dimana perpotongan dari setiap baris dan kolom hanya berisi satu nilai. Untuk mengubah tabel unnormalized ke dalam bentuk 1NF, kita mengidentifikasi dan menghapus repeating group di dalam suatu tabel. Sebuah repeating group adalah suatu atribut, atau kelompok atribut, di dalam tabel yang terjadi dengan beberapa nilai untuk suatu kejadian tunggal dari atribut kunci yang ada pada tabel. Suatu tabel dikatakan dalam bentuk 1NF dengan kondisi semua nilai atribut harus atomic (tidak bisa dibagi-bagi lagi) atau dengan kata lain tidak boleh ada atribut yang multivalue. b. Second Normal Form (2NF) Suatu tabel dikatakan memenuhi 2NF jika suatu relasi itu sudah berada dalam bentuk 1NF dan setiap atribut yang bukan kunci (non-primary key) bergantung penuh pada primary key (full functional dependency). Normalisasi 1NF ke 2NF melibatkan penghapusan dependensi parsial. Jika masih ada atribut yang tergantung pada sebagian (partial) primary key, maka akan dihapus atribut parsial tersebut dari relasi dengan menempatkannya pada relasi baru bersama dengan salinan atribut tersebut. c. Third Normal Form (3NF) Suatu tabel dikatakan memenuhi 3NF jika suatu relasi sudah dalam bentuk 1NF dan 2NF serta tidak adanya ketergantungan transitif (dependency transitive). Ketergantungan transitif adalah ketergantungan fungsional antara dua atau lebih atribut bukan primary key. Jika ketergantungan transitif masih

15 ada, maka atribut tersebut akan dihapus dari relasi dengan menempatkannya pada relasi baru bersama salinan atribut tersebut.

2.6

Definisi Performance Performance adalah ukuran kinerja dari suatu objek tertentu seperti mesin dan

aplikasi yang dilihat dari segi efektivitas waktu dengan target tertentu. M enurut Oxford Advanced Learner’s Dictionary, performance berarti sesuatu yang menunjukkan seberapa baik atau buruknya seseorang, perusahaan atau sistem dalam melakukan suatu pekerjaan atau kegiatan.

2.7

Tuning Tuning adalah suatu tindakan untuk menemukan sesuatu yang menjadi penyebab

masalah dan membuat suatu perbaikan yang diperlukan untuk mengurangi dampak dari masalah tersebut. Sesuai dengan pengertian tuning menurut Chan dan Ashdown (2009, p22) yaitu tindakan mengidentifikasikan hambatan-hambatan paling signifikan dan membuat perubahan yang diperlukan untuk mengurangi dampak dari permasalahan tersebut. Ada beberapa faktor yang dapat digunakan untuk mengukur efisiensi: ƒ

Transaction throughput. Yaitu berapa banyak transaksi yang dapat diproses dalam jangka waktu yang diberikan. Contoh: sebanyak 100 transaksi dapat diproses dalam waktu satu jam. Semakin banyak transaksi yang diproses dalam waktu satu jam, semakin tinggi throughput-nya.

16

ƒ

Response time. Yaitu waktu yang digunakan untuk menyelesaikan satu transaksi. Dari titik pandang seorang pemakai, semakin singkat response time-nya semakin baik. Bagaimanapun, ada beberapa faktor yang mempengaruhi response time yang tidak bisa dikontrol oleh perancang seperti pada saat sistem loading. Response time dapat diminimalisasi dengan cara: 1. M engurangi waktu selisih dan waktu tunggu (contention and wait times), terutama waktu tunggu disk I/O. 2. M engurangi jumlah waktu yang diperlukan sumber daya. 3. M enggunakan komponen yang lebih cepat.

ƒ

Penyimpanan disk. Yaitu jumlah media penyimpanan yang dibutuhkan pada suatu disk. Semakin minim penggunaan media penyimpanan, maka akan semakin efisien.

2.8

Jenis-Jenis Tuning Berdasarkan pendapat M ohamed (p10), jenis-jenis tuning dibagi ke dalam 3 jenis

yaitu: 1. Operating system tuning Pada jenis ini lebih fokus pada siklus CPU, alokasi RAM dan jaringan. •

CPU cycles Jumlah dari siklus CPU yang ada dapat memperlambat execution time suatu SQL. Ketika run-queue melebihi jumlah CPU pada server Oracle, CPU menjadi terbebani.

17

•

Available RAM memory Jumlah memori RAM yang tersedia untuk Oracle mempengaruhi kinerja SQL, terutama dalam melakukan data buffer. Penambahan memori RAM akan membantu meningkatkan kinerja pada tingkat tertentu.

•

Network Sejumlah besar traffic Oracle Net akan memberikan kontribusi dalam lambatnya kinerja SQL. Hal ini seharusnya dapat dimonitor dan dikelola dengan baik.

•

Monitor and manage disk I/O Akses objek dan lokasi dari objek tersebut berkaitan langsung dengan penggunaan disk. Penggunaan konfigurasi RAID yang tepat akan membantu menyeimbangkan operasi I/O. Datafile, OS, dan Oracle wait information diperlukan untuk menentukan secara akurat masalah-masalah yang terjadi kemudian mengelola serta menyelesaikan masalah-masalah tersebut.

18 2. Database tuning Pada jenis ini lebih fokus pada parameter init.ora dan space manajemen termasuk reorganisasi user dan pola penggunaan objek. •

Initialization parameter setting Parameter init.ora yang statis setelah instalasi dari Oracle dan perlu dikonfigurasi untuk penggunaan yang spesifik pada kasus database tertentu. Pengaturan parameter ini dapat berubah-ubah seiring munculnya berbagai versi Oracle sehingga harus menyadari akan kemungkinan memodifikasi parameter ini.

•

Space management Dengan menghapus atau mengurangi extent fragment, membangun kembali freelist chain, mengubah parameter dan size objek ketika reorganisasi suatu tabel, dapat meningkatkan kinerja dari database Oracle dengan mengurangi (reduce) I/O dan meningkatkan efisiensi SQL.

•

I/O Optimization Beberapa teknik utama dalam area ini adalah untuk mengoptimalkan ukuran buffer cache, menggunakan keep dan recycle pool yang efektif dan menjaga agar scan tabel yang dilakukan tetap

kecil jika

memungkinkan. Selain itu, ketika mengoptimalkan I/O juga harus memastikan bahwa ada cukup jumlah disk yang dialokasikan untuk workload dan apakah penyebaran I/O sudah merata.

19 3. SQL tuning Pada jenis ini lebih fokus pada penulisan ulang SQL statement, index tuning, atau mengubah session parameter. •

SQL tuning Dengan menulis ulang SQL statement atau penggunaan index untuk mengubah pola akses SQL, akan sangat membantu meningkatkan kinerja SQL.

•

Index tuning Termasuk mengidentifikasi full table scan dan index yang tidak terpakai. M enambahkan index jika dibutuhkan dapat secara signifikan mengurangi waktu yang dihabiskan untuk membaca data yang banyak sedangkan informasi yang diperoleh hanya sedikit.

•

User and object usage patterns Ada banyak user dan pengaturan session yang dapat diaktifkan untuk meningkatkan kinerja. Selain itu, ada banyak cara untuk memanipulasi pola penggunaan objek oleh Oracle, seperti indexing, menulis ulang SQL, lokasi objek, penggunaan tablespace dan setting variabel lain baik secara permanen ataupun sementara.

20 2.9

Manfaat Tuning M enurut Connoly dan Begg (2010, p558), manfaat atau keuntungan yang akan

diperoleh jika melakukan tuning adalah sebagai berikut: 1. Dengan tuning, kita dapat meminimalkan pembelian hardware baru. 2. Tuning memungkinkan untuk menurunkan konfigurasi hardware. Hasilnya tidak terlalu banyak memerlukan hardware tambahan dan biaya penggunaan hardware yang lebih murah sehingga biaya pemeliharaannya juga lebih murah. 3. Sebuah sistem yang di-tuning dengan baik akan menghasilkan response time yang lebih cepat dan throughput yang lebih baik, sehingga membuat pemakai dan perusahaan menjadi lebih produktif. 4. M eningkatnya response time dapat meningkatkan semangat kerja dari para pegawai. 5. M eningkatnya response time dapat menambah tingkat kepuasan pelanggan.

2.10

The Oracle Performance Improvement Method M enurut Chan dan Ashdown (2009, p32-33), metode dalam meningkatkan

kinerja pada database Oracle adalah: 1. M elakukan pemeriksaan standar awal sebagai berikut: a. M endapatkan umpan balik (feedback) dari pengguna. M enentukan

ruang

lingkup kinerja proyek dan tujuan kinerja selanjutnya, dan tujuan kinerja untuk masa depan. Proses ini adalah kunci dalam perencanaan kapasitas masa depan. b. M endapatkan satu set lengkap sistem operasi, database, dan aplikasi statistik dari sistem ketika kinerja dalam keadaan yang baik dan buruk. Jika

21 tidak tersedia, dapatkan apa saja yang tersedia. Kehilangan statistik sama saja dengan kehilangan bukti pada TKP. c. M emeriksa keadaan sistem operasi dari semua komputer yang terlibat dengan pengguna. Dengan pengecekan sistem operasi, carilah perangkat keras

atau

sistem

operasi

sumber

daya

yang

digunakan

sepenuhnya. Daftarkan sumber daya apapun yang digunakan secara berlebihan sebagai suatu gejala yang nantinya akan dianalisis. Selain itu, pastikan mengecek semua perangkat keras tidak menunjukkan error. 2. M emeriksa sepuluh kesalahan umum yang terdapat pada database Oracle (Top Ten Mistakes Found in Oracle Systems), dan menentukan apakah mungkin menjadi masalah. Kemudian mendaftarkan hal tersebut sebagai gejala untuk The Oracle Performance Improvement Method dan selanjutnya dianalisis. 3. M embangun model konseptual tentang apa yang terjadi pada sistem dengan menggunakan gejala-gejala sebagai petunjuk untuk memahami apa yang menyebabkan masalah kinerja. 4. M engusulkan langkah-langkah pemecahan masalah untuk sistem, kemudian implementasikan. 5. M embuat validasi perubahan yang telah dibuat yang memiliki hasil yang diinginkan, dan lihat apakah persepsi kinerja pengguna telah meningkat. Ulangi tiga langkah terakhir sampai tujuan kinerja tersebut dipenuhi atau menjadi tidak mungkin karena kendala lainnya.

22

2.11

Top Ten Mistakes Found in Oracle Systems Berikut adalah sepuluh kesalahan umum yang terdapat pada database Oracle

menurut Chan dan Ashdown (2009, p54): 1. M anajemen koneksi yang kurang baik Aplikasi melakukan koneksi dan menghentikan koneksi setiap kali berinteraksi dengan database. 2. Penggunaan cursor dan shared pool yang kurang baik Kode panjang yang tidak menggunakan cursor menyebabkan hard parsing untuk setiap SQL query. 3. SQL yang kurang baik SQL yang kurang baik artinya SQL yang menggunakan resource lebih banyak dari yang diperlukan untuk kebutuhan suatu aplikasi. 4. Penggunaan inisialisasi parameter yang tidak standar akibat asumsi yang tidak benar. 5. I/O database yang salah Penentuan jumlah disk yang salah karena melakukan konfigurasi beberapa disk berdasarkan disk space bukan berdasarkan bandwith I/O. 6. M asalah pada setting online redo log Ukuran redo log yang kecil mengakibatkan checkpoint akan terus berada pada load yang tinggi pada buffer cache. Jumlah redo log yang terlalu sedikit, menyebabkan file archive tidak dapat disimpan terus sehingga terjadi kemacetan database.

23

7. Serialisasi dari blok-blok data pada buffer cache karena kekurangan dari free list, free list group, transaction slot (initrans), atau segmen rollback yang pendek. 8. Long full table scans Panjangnya full table scan dapat mengindikasikan poor transaction design, missing indexes, atau poor SQL optimization. 9. Banyaknya jumlah recursive (SYS) SQL Banyaknya jumlah recursive SQL yang dieksekusi mengindikasikan kegiatan space management, seperti alokasi extent dan alokasi space. Hal ini berdampak terhadap response time user. 10. Schema Errors dan Optimizer Problems Sebuah aplikasi menggunakan terlalu banyak resource karena schema yang memiliki banyak tabel belum berhasil melakukan migrasi dari implementasi sebelumnya. Hal ini dapat disebabkan oleh missing indexes atau incorrect statistics. Error ini dapat menyebabkan sub-optimal execution plans dan poor interactive user performance.

2.12

SQL Tuning SQL tuning merupakan tindakan mengoptimisasi (query optimization) SQL

statement. Dimana menurut Alapati (2005, p939), query optimization adalah suatu proses dalam memilih strategi eksekusi (execution strategy) yang paling efisien untuk mengeksekusi sebuah query. M enurut Chan dan Ashdown (2009, p161), tujuan dari tuning adalah mengurangi response time bagi end user dari sebuah sistem dan mengurangi sumber daya yang

24 digunakan untuk suatu pekerjaan yang sejenis dan untuk mencapai hal tersebut. Beberapa cara dapat dilakukan menurut Chan dan Ashdown (2009, p384), yaitu: ƒ

Mengurangi Workload SQL Tuning pada umumnya melibatkan pencarian cara yang paling efektif untuk memproses beberapa workload yang sejenis. Hal ini dapat dilakukan dengan mengganti execution plan dari SQL statement tanpa harus mengubah fungsionalitasnya untuk mengurangi pemakaian sumber daya. Sebagai contoh jika ada SQL statement (query) yang sering dijalankan untuk mengakses sebagian kecil dari data dalam tabel, maka SQL statement tersebut dapat dijalankan lebih efisien dengan menggunakan index.

ƒ

Menyeimbangkan Workload Sistem cenderung akan berada dalam kondisi sibuk pada saat jam operasional dan akan sedikit digunakan pada saat malam hari ketika kegiatan operasional sudah tidak dilakukan.

ƒ

Paralelisasi Workload Untuk melakukan query data dalam jumlah yang besar terutama pada data warehouse dapat dilakukan secara paralel untuk mengurangi response time pada data warehouse serta tidak menghentikan kegiatan transaksional secara keseluruhan pada OLTP.

25 Beberapa SQL statement yang dapat mempercepat akses data berdasarkan pendapat Chan dan Ashdown (2009, p389): a. Penggunaan Equijoin Untuk

meningkatkan

efisiensi

SQL

statement,

lebih

baik

menggunakan equijoins jika memungkinkan. SQL statement yang menggunakan equijoins

pada kolom yang tidak ditransformasikan

nilainya lebih mudah untuk dilakukan tuning. b. Hindari mengubah nilai column dalam where clause Lebih baik menggunakan: WHERE a.order_no = b.order_no 

Daripada WHERE TO_NUMBER (SUBSTR (a.order_no, INSTR (b.order_no, ‘ . ’) ‐1)) =  TO_NUMBER (SUBSTR (a.order_no, INSTR (b.order_no, ‘ . ‘) ‐1)) 

c. Hindari penggunaan where clause untuk menggabungkan tabel. Lebih baik menggunakan: SELECT last_name, job_name, department_name  FROM employees a JOIN jobs b    ON a.job_id = b.job_id    JOIN department c    ON a.department_i = c.department_id 

26 Daripada SELECT last_name, job_name, department_name  FROM employees a, jobs b, department c  WHERE a.job_id = b.job_id AND    a.department_id = c.department_id 

Karena untuk menggabungkan beberapa tabel dengan non-join clause (where clause), hasil akan dievaluasi setelah melakukan cross join seluruh tabel. Sedangkan dengan join clause, hasil akan dievaluasi setelah cross join antar 2 tabel. Sehingga dapat mengurangi jumlah row yang akan dicocokkan dengan tabel berikutnya. d. Perhatikan processing time antara delete, insert, dan update. Operasi delete lebih cepat daripada insert dan update karena sebenarnya Oracle tidak menghapus datablock yang terpakai secara permanen, melainkan menandai datablock tersebut bahwa sudah tidak terpakai. Untuk insert, Oracle akan memesan datablock untuk diisi dengan suatu nilai. Sedangkan update merupakan kegiatan yang lebih lama karena memerlukan waktu untuk pencarian data yang hendak diupdate serta mengganti nilainya (terlebih jika melibatkan kalkulasi rumit dalam mengganti nilai).

27 e. Hindari penggunaan data type convertion Contoh: WHERE TO_NUMBER ( char_col ) = num_exp  Keterangan:  Char_col adalah varchar2  Num_exp adalah number 

Code

di

atas

menggunakan

explicit

data

type

convertion

(menyebutkan secara tertulis konversi tipe datanya). Hal ini akan menyebabkan menurunnya performance. Code di atas dapat juga ditulis sebagai berikut: WHERE char_col = num_exp 

Karena Oracle akan mengubah where clause di atas secara implicit menjadi:

WHERE TO_NUMBER (char_col) = num_exp 

f. Hindari penggunaan complex expressions Contoh:  ‐ col1 = NVL (:b1, col1)  ‐ NVL (col1, ‐999) = ...  ‐ TO_DATE(), TO_NUMBER(), TO_CHAR() 

Complex expressions di atas mencegah

SQL Optimizer untuk

memberikan cardinality yang valid dan perubahan pada execution plan dan join method.

dapat menyebabkan

28 Hindarilah: SELECT employee_num, full_name Name, employee_id  FROM mtl_employees_current_view  WHERE (employee_num = NVL (:bl,employee_num))  AND (organization_id=:1)  ORDER BY employee num; 

Lebih baik SELECT employee_num, full_name Name, employee_id  FROM mtl_employees_current_view  WHERE (employee_num =:bl)AND(organization_id=:1)  ORDER BY employee num; 

g. Penggunaan exist dan in untuk subqueries Dalam beberapa kondisi, lebih baik menggunakan in daripada exists. Contoh kasus: •

Jika selective predicate berada di dalam subqueries maka gunakanlah in. Contoh: SELECT e.first_name,e.salary  FROM employees e  WHERE e.employee_id IN    (SELECT o.sales_rep_id     FROM orders o     WHERE o.customer_id = 144); 

•

Jika selective predicate di dalam parent query maka gunakan exists.

29 Contoh: SELECT e.first_name, e.salary  FROM employees e  WHERE e.department_id = 80  AND e.job_id = ‘SA_REP’  AND EXISTS (SELECT 1       FROM orders o  WHERE e.employee_id =       o.sales_rep_id); 

h. Hindarilah mengakses data berulang-ulang untuk beberapa SQL statement yang dapat digabung Contoh: SELECT COUNT(*)  FROM employees  WHERE salary < 2000; 

SELECT COUNT(*)  FROM employees  WHERE salary < 4000; 

SELECT COUNT(*)  FROM employees  WHERE salary BETWEEN 2000 AND 4000; 

Dapat digabungkan menjadi: SELECT COUNT  (CASE WHEN salary<2000    THEN 1 ELSE null END) count_1t_2000,  COUNT  (CASE WHEN salary BETWEEN 2001 AND 4000    THEN 1 ELSE null END) count_btwn_2001 AND 4000,  COUNT  (CASE WHEN salary>4000    THEN 1 ELSE null END)  count_gt_4000  FROM employees; 

30 i. M engendalikan Access Path dan Join Order dengan Hint SELECT e.last_name  FROM employees e  WHERE e.job_id = ‘CLERK’; 

Kita dapat

menggunakan hint dalam

SQL statement untuk

menginstruksikan kepada SQL optimizer bagaimana SQL statement seharusnya dijalankan. Join Order dapat memberikan efek yang sangat signifikan pada performance dalam mengakses data dalam database. Hal-hal yang perlu diperhatikan ketika akan menentukan join order antara lain: ƒ Hindarilah full-table scan jika memungkinkan untuk mendapatkan data yang dibutuhkan melalui sebuah index. ƒ Hindarilah menggunakan index yang menarik 10.000 baris data dari sebuah tabel jika masih bisa menggunakan index lain yang menarik hanya 100 baris data. ƒ Pilihlah join order yang tepat sehingga menghasilkan lebih sedikit baris untuk di-join-kan dengan tabel berikutnya.

31 Berikut rangking access path dari yang paling cepat hingga yang paling lambat: Tabel 2.2 Rangking access path dari tercepat hingga paling lambat Rank Access Path 1 Single ROWID 2 Single row by cluster join 3 Single row by hash cluster key with unique or primary key 4 Single row by unique or primary key 5 Cluster Join 6 Hash cluster key 7 Indeed cluster key 8 Composite key 9 Single-Column indexess 10 Bounded range search on indexed column 11 Unbounded range search on indexed column 12 Sort-merge join 13 Max or Min of indexed column 14 ORDER BY on indexed column 15 Full table scan Pada saat mengeksekusi suatu query, optimizer akan memilih execution plan berdasarkan access path yang tersedia. Jika terdapat lebih dari satu cara untuk mengeksekusi SQL statement, optimizer selalu memilih cara dengan rangking terendah. Biasanya cara dengan rangking yang lebih rendah dieksekusi lebih cepat daripada rangking yang lebih tinggi. Berikut penjelasan mengenai setiap rangkingnya: 1. Single ROWID Access

path

ini

tersedia

jika

pada

statement

where

clause

diidentifikasikan untuk menghasilkan data dengan langsung menunjuk kepada ROWID-nya. Contoh: SELECT * FROM emp WHERE ROWID = ‘AAA7Baa5’; 

32

2. Single row by cluster join Access path ini tersedia untuk statement yang menggabungkan beberapa tabel yang tersimpan pada cluster yang sama dan jika memenuhi kedua kriteria berikut: ƒ

Statement where clause mengandung kondisi yang menyamakan setiap kolom pada cluster key dalam satu tabel dengan kolom pada tabel lain.

ƒ

Statement where clause juga mengandung sebuah kondisi yang menjamin bahwa hasil join hanya menghasilkan satu baris data. Contoh:

SELECT * FROM emp, dept  WHERE emp.deptno = dept.deptno    AND emp.empno = 7900; 

3. Single row by hash cluster key with unique or primary key Access path ini tersedia jika memenuhi kriteria berikut: ƒ

Query statement harus menjamin bahwa output-nya hanya satu baris data.

ƒ

Cluster key sekaligus primary key dari tabel tersebut

ƒ

Statement where clause menggunakan semua kolom hash cluster key dalam menyamakan kondisi antara tabel yang satu dengan yang lain. Contoh: Pada statement di bawah, tabel orders tersimpan di hash cluster dan kolom orderno merupakan cluster key sekaligus primary key dari tabel orders.

SELECT * FROM orders    WEHRE orderno = 1968l; 

33

4. Single row by unique or primary key Access path ini akan tersedia jika statement where clause menggunakan semua kolom yang merupakan unique atau primary key dalam penyamaan kondisi antara tabel yang satu dengan yang lain. Untuk mengeksekusi statement tersebut, Oracle akan melakukan scan index pada unique atau primary key untuk mendapatkan ROWID dan kemudian tabel akan diakses menggunakan ROWID. Contoh: SELECT * FROM emp    WHERE empno = 7900; 

5. Cluster Join Access path ini tersedia jika: ƒ

Statement menggabungkan beberapa tabel pada cluster yang sama

ƒ

Statement where clause mengandung kondisi yang menyamakan setiap kolom cluster key dalam satu tabel yang berhubungan dengan tabel lain. Contoh: Tabel emp dan dept di-cluster pada kolom deptno: SELECT * FROM emp, dept  WHERE emp.deptno = dept.deptno; 

6. Hash cluster key Access path ini hampir sama dengan single row by hash cluster with unique or primary key (access path no. 3), perbedaannya terletak pada cluster key tidak harus merupakan primary key dari tabel tersebut. Sehingga pada contoh di bawah ini, tabel line_items disimpan di cluster

34 dan orderno adalah cluster key namun bukan primary key dari tabel line_items. SELECT * FROM line_items  WHERE orderno = 65118945 

7. Indexed cluster key Access path ini akan tersedia jika statement where clause menggunakan semua kolom dari indexed cluster key untuk menyamakan kondisi satu tabel dengan tabel lain. Sebagai contoh: pada statement di bawah ini, tabel emp tersimpan di indexed cluster dan kolom deptno adalah cluster key: SELECT * FROM emp  WHERE deptno = 08; 

8. Composite key Access path ini akan tersedia jika statement where clause menggunakan semua kolom composite index dalam menyamakan kondisi tabel satu dengan tabel lainnya. Untuk mengeksekusi statement ini, Oracle melakukan range scan pada index untuk mendapatkan ROWID dari selected

row dan

kemudian

mengakses

tabel tersebut

dengan

menggunakan ROWID yang didapatkan. Contoh: Pada statement di bawah ini, terdapat composite index terhadap kolom job dan deptno: SELECT * FROM emp  WHERE job = ‘CLERK’  AND deptno = 26; 

35 9. Single-Column indexes Access path ini tersedia jika statement where clause menggunakan satu atau lebih single colomn index dalam menyamakan kondisi antara tabel yang satu dengan yang lain. Sebagai contoh di bawah ini terdapat sebuah index pada kolom job: SELECT * FROM emp  WHERE job = ‘ANALYST’; 

Contoh lain jika terdapat beberapa single column index (kolom job dan deptno): SELECT * FROM emp  WHERE job = ‘ANALYST’ and deptno = 21; 

10. Bounded range search on indexed column Access path ini tersedia jika statement where clause mengandung kondisi yang menyangkut indexed column dan membatasi range output dengan menggunakan ekspresi sama dengan (=), lebih besar sama dengan dan lebih kecil dari ( > [=] AND < [=]), BETWEEN ...AND, dan ekspresi LIKE ‘c%’. Contoh: SELECT * FROM emp  WHERE sal BETWEEN 2000 AND 3000; 

11. Unbounded range search on indexed column Access path ini tersedia jika statement where clause mengandung kondisi yang menyangkut indexed column dan menggunakan ekspresi: Lebih besar dari >[=] atau kurang dari < [=]. Contoh:

SELECT * FROM emp  WHERE sal > 2000; 

36

12. Sort-merge join Access path ini tersedia untuk statement yang menggabungkan beberapa tabel yang tidak disimpan pada cluster yang sama sehingga Oracle harus melakukan sort-merge join. Pada contoh di bawah ini, tabel emp dan dept tidak disimpan pada cluster yang sama: SELECT * FROM emp, dept  WHERE emp.deptno = dept.deptno; 

13. Max or Min of indexed column Access path ini tersedia untuk select statement dan jika pada kondisi berikut ini: •

Hanya menggunakan fungsi MAX atau MIN pada SELECT statement.

•

Tidak memiliki WHERE atau klausa GROUP BY. Contoh: SELECT MAX(sal) FROM emp; 

14. ORDER BY on indexed column Access path ini tersedia untuk select statement dan jika pada kondisi berikut ini: •

M engandung ORDER BY clause.

•

Terdapat primary key atau NOT NULL constraint, sehingga menjamin kolom yang di-sort tidak mengandung nilai NULL.

37

Contoh: SELECT* FROM emp order by empno; 

15. Full-table scan Access path ini tersedia untuk berbagai jenis SQL statement jika tidak memiliki index atau tidak memenuhi empat belas access path sebelumnya. M isalnya: untuk mengeksekusi query yang bertujuan untuk menghasilkan jumlah data hampir atau seluruh data pada tabel tersebut: SELECT * FROM emp; 

2.13

Execution Plan Berdasarkan pendapat Watt (2005, p172), execution plan menunjukkan jalannya

eksekusi query SQL optimizer. Execution plan output dihasilkan menggunakan EXPLAIN PLAN. Sedangkan menurut website www.akadia.com, execution plan adalah suatu representasi dari access path yang dilalui ketika suatu query dieksekusi di dalam Oracle. Explain plan adalah sebuah tool/alat untuk menampilkan execution plan dari suatu SQL.

38 Pemrosesan query dapat dibagi ke dalam 7 fase yaitu:

1. 2. 3. 4.

5.

6. 7.

Tabel 2.3 Penjelasan fase-fase dalam pemrosesan query Penjelasan Nama fase Syntactic M emeriksa syntax dari suatu query Semantic M emeriksa semua objek yang ada dan yang bisa diakses M enulis ulang query join pada tabel dasar sebagai lawan View Merging (oppose) menggunakan view M enulis ulang query yang kompleks dengan Statement mengubahnya menjadi lebih sederhana dan sesuai, contoh: Transformation subquery merging, in/or transformation M enentukan jalur access path yang optimal untuk suatu query. Dengan Rule Based Optimizer (RBO) akan menggunakan heuristik untuk menentukan access path. Optimization Sedangkan dengan Cost Based Optimizer (CBO) akan menggunakan statistik untuk menganalisis cost relatif yang digunakan untuk mengakses suatu objek. QEP QEP = Query Evaluation Plan Generation QEP Execution QEP = Query Evaluation Plan

Fase pertama sampai dengan fase keenam akan ditangani oleh parser. Fase ketujuh adalah pelaksanaan dari statement. Explain plan sendiri dihasilkan oleh parser. Setelah access path sudah ditetapkan akan disimpan ke dalam library cache bersama dengan statement itu sendiri. Query akan disimpan ke dalam library cache berdasarkan representasi hashed dari query tersebut. Ketika mencari statement di library cache, harus menerapkan algoritma hash pada statement tersebut selanjutnya mencari nilai hash di library cache. Access path ini akan digunakan sampai query tersebut mengalami parse ulang.

39 2.14

Terminology

Nama Row Source

Predicate Tuples Driving Table

Probed Table

2.15

Tabel 2.4 Penjelasan terminology Penjelasan Kelompok row yang digunakan dalam suatu query mungkin select dari objek atau result yang dikembalikan dengan joining 2 row sources awal. Where clause dari suatu query Row (baris) Ini adalah row source yang digunakan sebagai outer table. Jika mengembalikan banyak row maka dapat memiliki pengaruh negatif terhadap semua operasi berikutnya. Ini adalah objek yang dilakukan lookup ke dalam data setelah mengambil data key yang relevan dari driving table.

Cara Oracle Mengakses Data Pada level fisikal, Oracle membaca blok data. Jumlah terkecil data yang dibaca

adalah satu blok Oracle, yang terbesar adalah dibatasi dengan limit sistem operasi (dan multiblok I/O). Secara logika, Oracle mencari data untuk dibaca menggunakan metode berikut:

2.16

•

Full Table Scan (FTS)

•

Index Lookup (unique dan non-unique)

•

Rowid

Hierarki Explain Plan Contoh explain plan sederhana: Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT     [CHOOSE] Cost=1234    TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]   

40

Operasi pertama yang akan dieksekusi dari explain plan adalah TABLE ACCESS FULL LARGE. Statement ini berarti melakukan scan penuh suatu tabel (full table scan) dari tabel LARGE. Ketika operasi ini selesai, maka resultant row source akan dilewati ke level selanjutnya untuk diproses query-nya. Dalam hal ini adalah SELECT STATEMENT yang berada di paling atas dari contoh di atas. [CHOOSE] adalah indikasi dari optimizer_goal untuk query. Hal ini tidak selalu menunjukkan explain plan benar-benar menggunakan goal tersebut. Satu-satunya cara untuk mengkonfirmasi hal ini adalah memeriksa cost yang adalah bagian dari explain plan. Sebagai contoh, query di bawah ini menunjukkan bahwa CBO yang telah digunakan karena ada sebesar 1234 cost pada parameter cost: SELECT STATEMENT     [CHOOSE] Cost=1234 

Namun, explain plan di bawah ini menunjukkan tidak adanya penggunaan RBO karena pada bagian cost tersebut kosong: SELECT STATEMENT     [CHOOSE] Cost= 

Parameter cost merupakan cost perbandingan yang digunakan secara internal untuk menentukan biaya yang terbaik untuk explain plan tertentu. Cost dari statement yang berbeda tidak selalu dapat dibandingkan secara langsung. [:Q65001] menunjukkan bahwa bagian tertentu dari query tersebut sedang dieksekusi secara paralel. Angkanya menunjukkan bahwa operasi tersebut akan diproses oleh parallel query slave sebagai lawan dieksekusi secara serial.

41

[ANALYZED] menunjukkan bahwa objek tersebut telah dianalisis dan ada statistik saat ini yang tersedia untuk digunakan CBO. Tidak ada indikasi tentang “level” dari analisis yang dilakukan.

2.17 ƒ

Metode Akses yang lebih detail Full Table Scan (FTS) Di dalam operasi FTS, seluruh tabel dibaca sampai high water mark (HWM ). HWM menandai blok terakhir pada tabel yang pernah memiliki data. Jika menghapus semua baris maka masih akan bisa dibaca sampai HWM . Truncate melakukan reset HWM kembali pada awal tabel. FTS menggunakan multiblok I/O untuk membaca blok dari disk. M ultiblok I/O dikontrol dengan parameter . Contoh dari explain FTS: SQL> explain plan for select * from dual;  Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT     [CHOOSE] Cost=    TABLE ACCESS FULL DUAL   

ƒ

Index Lookup Data diakses dengan mencari nilai key di dalam index dan mengembalikan rowid. Suatu rowid secara unik mengidentifikasi sebuah baris di dalam data blok tertentu. Blok ini dibaca via single blok I/O. Dalam contoh ini, index digunakan untuk mencari row yang relevan kemudian tabelnya diakses untuk lookup kolom ename (yang tidak termasuk dalam index):

42 SQL> explain plan for  select empno,ename from emp   where empno=10;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=1  TABLE ACCESS BY ROWID EMP [ANALYZED]      INDEX UNIQUE SCAN EMP_I1 

Perhatikan bagian “TABLE ACCESS BY ROWID”. Ini menunjukkan bahwa data tabel tidak sedang diakses melalui operasi FTS melainkan dengan rowid lookup. Dalam hal ini rowid tersebut dibuat dengan pertama kali mencari nilai pada index. Index tersebut diakses oleh suatu operasi “INDEX UNIQUE SCAN” yang akan dijelaskan di bawah. Nama index tersebut adalah EM P_l1. Jika semua data yang dibutuhkan berada dalam index selanjutnya suatu tabel lookup mungkin tidak diperlukan dan yang akan terjadi adalah suatu index tanpa mengakses tabel. Contoh berikut ini semua kolom (empno) berada di index. Perhatikan bahwa tidak ada akses tabel yang terjadi. SQL> explain plan for  select empno from emp where  empno=10;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=1    INDEX UNIQUE SCAN EMP_I1   

Index sendiri sudah memiliki fungsi sorting sehingga tidak diperlukan sorting lagi jika memerlukan order by.

43 SQL> explain plan for select empno,ename   from emp  where empno > 7876   order by empno;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=1  TABLE ACCESS BY ROWID EMP [ANALYZED]    INDEX RANGE SCAN EMP_I1 [ANALYZED] 

Dalam contoh ini index sudah diurutkan sehingga barisnya akan dikembalikan dalam urutan index oleh karena itu tidak memerlukan sorting lagi. SQL> explain plan for  select /*+ Full(emp) */ empno,ename from emp  where empno> 7876 order by empno;  Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=9    SORT ORDER BY      TABLE ACCESS FULL EMP [ANALYZED]  Cost=1 Card=2 Bytes=66   

Karena telah memaksakan FTS, datanya menjadi tidak berurutan sehingga harus melakukan sorting pada data setelah datanya telah diambil (retrieve). Ada 4 metode dari index lookup yaitu sebagai berikut: a. Index unique scan M etode untuk mencari nilai key tunggal melalui index yang unik. Dan selalu mengembalikan nilai tunggal. SQL> explain plan for  select empno,ename from emp   where empno=10;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=1  TABLE ACCESS BY ROWID EMP [ANALYZED]      INDEX UNIQUE SCAN EMP_I1   

44

b. Index range scan M etode untuk mengakses beberapa kolom nilai atau multiple kolom serta dapat digunakan untuk operasi range (misalnya > < <> >= <= between). SQL> explain plan for select empno,ename from emp  where empno > 7876 order by empno;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=1  TABLE ACCESS BY ROWID EMP [ANALYZED]    INDEX RANGE SCAN EMP I1 [ANALYZED] 

Suatu index yang tidak unik akan mengembalikan beberapa nilai atau multiple value untuk predicate seperti ini column1=5 dan akan menggunakan index range scan. SQL> explain plan for   select mgr from emp where mgr = 5    Query plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=1    INDEX RANGE SCAN EMP_I2 [ANALYZED]   

c. Index full scan Dalam keadaan tertentu mungkin dilakukan scanning untuk seluruh index yang berbeda dengan range scan (dimana tidak ada constraint predicate untuk suatu tabel). Full index scan hanya tersedia di CBO karena tidak dapat ditentukan apakah full scan itu ide yang baik atau tidak. Dipilihlah suatu full scan index ketika memiliki statistik yang menunjukkan bahwa akan menjadi lebih efisien daripada tabel full table scan yang disertai sorting.

45

M isalnya melakukan full index scan ketika melakukan scan tak terbatas pada index (unbounded scan of an index) dan ingin data tersebut diurutkan. Optimizer mungkin memutuskan memilih semua informasi dari index dan tidak dilakukan sorting akan lebih efisien daripada melakukan FTS atau Fast Full Index Scan baru kemudian dilakukan sorting. Sebuah index full scan akan melakukan blok I/O sehingga mungkin terbukti menjadi tidak efisien. Index BE_IX merupakan index yang tersambung dengan index pada big_emp(empno, ename). SQL> explain plan for select empno,ename       from big_emp order by empno,ename;  Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=26    INDEX FULL SCAN BE_IX [ANALYZED]   

d. Index fast full scan M elakukan scanning pada semua blok memiliki index. Di Oracle ada beberapa SQL query yang dapat diselesaikan dengan membaca index tanpa menyentuh data tabel. Index Fast Full Scan adalah setara dengan Full Table Scan, tetapi untuk suatu index. Index Fast Full Scan ini membaca menggunakan multiblok read, tetapi result yang dihasilkan tidak dikembalikan secara berurutan. Untuk query yang menggunakan index FFS, kolom tersebut seharusnya didefinisikan Not Null atau setidaknya satu kolom di dalam composite index Not Null.

46 Index Fast Full Scan adalah mekanisme dibalik membuat fast index dan membuat

ulang (recreate).

Index BE_IX

merupakan

sambungan

(concatenated) dari index pada big_emp(empno, ename). SQL> explain plan for select empno,ename   from big_emp;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=1    INDEX FAST FULL SCAN BE_IX [ANALYZED] 

M emilih kolom kedua dari concatenated index: SQL> explain plan for select ename from  big_emp;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT   [CHOOSE] Cost=1    INDEX FAST FULL SCAN BE_IX [ANALYZED]   

ƒ

Rowid Ini adalah metode akses tercepat yang disediakan Oracle dengan hanya mengambil blok yang ditentukan dan mengekstrak row tersebut. M etode ini yang paling banyak dilihat di explain plan sebagai Table access by Rowid. SQL> explain plan for select * from dept   where rowid = ':x';    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=1  TABLE ACCESS BY ROWID DEPT [ANALYZED] 

47 ƒ

Joins Join adalah suatu predicate yang mencoba menggabungkan dua row sources. Di sini hanya dibahas join dua row sources. Langkah-langkah join selalu dilakukan secara serial meskipun row sources yang ada diakses secara paralel. Join order – order/urutan di mana join dilakukan. Join order membuat perbedaan yang signifikan pada cara dimana query dieksekusi. Dengan mengakses row sources pertama tertentu, predicate tersebut mungkin tercapai, tetapi tidak akan tercapai dengan join order yang lain. Hal ini dapat mencegah access path tertentu diambil. Contoh ada concatenated index pada A (a.col1, a.col2). a.col1 adalah leading column. select A.col4  from   A,B,C  where  B.col3 = 10  and    A.col1 = B.col1  and    A.col2 = C.col2  and    C.col3 = 5   

Kondisi di atas dapat digambarkan sebagai berikut: B <---> A <---> col3=10

C col3=5

Sebenarnya ada dua cara untuk menjalankan query yaitu dari B.col3 atau dari C.col3. Harus melakukan full scan pada tabel A untuk bisa mengambil data dari tabel A. Dengan cara tersebut, sepertinya tidak efisien jika tabelnya besar. Jika kita memulai dari tabel B dulu, maka harus menggunakan predicate B.col3=10 (sebagai filter atau lookup key) kemudian akan mengambil nilai B.col1 baru join ke A.col1. Karena leading column (A.col1) sudah terisi, maka

48 concatenated index pada tabel A bisa diaktifkan sehingga memberikan nilai untuk A.col2 dan join ke A. Sedangkan jika mengambil tabel C dulu, hanya akan mendapatkan nilai A.col2 saja sehingga composite index menjadi tidak berfungsi. Hal ini disebabkan karena nilai yang dihasilkan oleh tabel C tidak mengacu pada leading column (A.col1). Jadi, join order yang paling baik adalah BÆAÆC. CBO tetap akan menggunakan cost pada saat memilih access path mana saja. Jika CBO tidak memilih join order BÆAÆC, maka bisa menggunakan hint untuk memicu optimizer agar memilih access path tersebut seperti contoh berikut. select /*+ ordered */ A.col4  from   B,A,C  where  B.col3 = 10  and    A.col1 = B.col1  and    A.col2 = C.col2  and    C.col3 = 5   

Tipe-tipe join:

a. Sort Merge Join (SM J) Row dihasilkan dari row source satu dan kemudian dilakukan sorting. Row dari row source dua kemudian dihasilkan dan dilakukan sorting dengan sort key yang sama dengan row source satu. Row source satu dan dua tidak diakses secara bersamaan. Row yang telah diberi sorting pada dua bagian (row source satu dan dua) kemudian dilakukan merge atau digabung.              MERGE                   /      \              SORT        SORT               |             |          Row Source 1  Row Source 2   

49

Jika row sources sudah diurutkan, maka operasi sort sudah tidak diperlukan selama dua bagian tersebut diurutkan berdasarkan key yang sama. Presorted row sources termasuk kolom index dan row sources yang telah diurutkan dalam langkah sebelumnya. Walaupun penggabungan (merge) dua row sources tersebut dilakukan secara serial, row sources itu sendiri dapat diakses secara paralel. SQL> explain plan for  select /*+ ordered */ e.deptno,d.deptno  from emp e,dept d  where e.deptno = d.deptno  order by e.deptno,d.deptno;    Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT [CHOOSE] Cost=17    MERGE JOIN      SORT JOIN        TABLE ACCESS FULL EMP [ANALYZED]      SORT JOIN        TABLE ACCESS FULL DEPT [ANALYZED] 

Sorting adalah operasi yang mahal, terutama jika dilakukan pada tabel yang besar jumlahnya. Oleh sebab itu, Sort Merge Join tergolong metode join yang kurang efisien. b. Nested Loops (NL) Pertama, mengembalikan semua row dari row sources satu kemudian memeriksa row source dua hanya sekali untuk setiap row yang dikembalikan dari row source satu. Row source 1  ~~~~~~~~~~~~  Row 1 ‐‐‐‐‐‐‐‐‐‐‐‐‐‐       ‐‐ Probe ‐>       Row source 2  Row 2 ‐‐‐‐‐‐‐‐‐‐‐‐‐‐       ‐‐ Probe ‐>       Row source 2  Row 3 ‐‐‐‐‐‐‐‐‐‐‐‐‐‐       ‐‐ Probe ‐>       Row source 2  Row source 1 dikenal dengan outer table  Row source 2 dikenal dengan inner table   

50 Dengan mengakses row source dua berarti sama dengan memeriksa (probing) inner tabel. Supaya nested loop menjadi efisien, sangat penting dimana row source pertama mengembalikan row sedikit mungkin secara langsung untuk mengontrol jumlah probes pada row source kedua. Juga membantu jika metode akses untuk row source dua efisien seperti operasi yang diulang sekali untuk setiap row yang dikembalikan oleh row source satu. QL> explain plan for select a.dname,b.sql from dept a,emp b where a.deptno = b.deptno; Query Plan ------------------------SELECT STATEMENT [CHOOSE] Cost=5 NESTED LOOPS TABLE ACCESS FULL DEPT [ANALYZED] TABLE ACCESS FULL EMP [ANALYZED]

c. Hash Join Dalam hash join, database melakukan full scan pada driving table, membangun RAM tabel hash, kemudian memeriksa (probe) row yang match dengan tabel lainnya. Untuk beberapa tipe SQL tertentu, eksekusi hash join akan lebih cepat daripada nested loop join, tetapi hash join menggunakan sumber daya RAM yang banyak. Optimizer SQL lebih cenderung melakukan invoke hash join dikontrol dengan setting untuk parameter hash_area_size. Semakin besar nilai untuk hash_area_size, semakin banyak hash join yang akan di-invoke oleh optimizer. Hash join dapat diaktifkan dengan parameter HASH_JOIN_ENABLED=TRUE pada init.ora atau session. Default-nya adalah True.

51 SQL> explain plan for  select /*+ use_hash(emp) */ empno  from emp,dept  where emp.deptno = dept.deptno;  Query Plan  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐  SELECT STATEMENT  [CHOOSE] Cost=3    HASH JOIN      TABLE ACCESS FULL DEPT      TABLE ACCESS FULL EMP   

2.18 •

Cara memperoleh explain plan Explain plan for Keuntungan utamanya adalah sebenarnya explain plan for ini tidak menjalankan query (hanya melakukan parsing SQL). Ini berarti explain plan dieksekusi secara cepat. Pada tahap pertama tuning, explain plan memberikan suatu ide dari kinerja potensial query tanpa benar-benar menjalankannya.

•

Autotrace Autotrace dapat dikonfigurasikan untuk menjalankan SQL dan memberikan plan dan setelah itu statistik, atau hanya memberikan explain plan tanpa mengeksekusi query.

•

TKPROF M enganalisis trace file. Berdasarkan pendapat Chan dan Ashdown (2009, p494) execution plan juga

memiliki fasilitas SQL Trace dan TKPROF. M elalui fasilitas SQL Trace dan TKPROF dapat menilai efisiensi suatu SQL statement yang dijalankan pada suatu aplikasi secara

52

akurat. Untuk hasil yang terbaik, gunakan SQL Trace dan TKPROF disertai dengan explain plan daripada menggunakan explain plan saja. Fasilitas SQL Trace menyediakan informasi tentang kinerja setiap SQL statement. SQL Trace ini menghasilkan beberapa statistik untuk setiap statement yaitu: ƒ

Parse, execute, dan perhitungan fetch

ƒ

CPU dan elapsed times

ƒ

Physical reads dan logical reads

ƒ

Jumlah row yang diproses

ƒ

M elesat dari library cache

ƒ

Username dari setiap parse yang terjadi

ƒ

Setiap commit dan rollback

ƒ

Kejadian tunggu (wait event) untuk setiap SQL statement, dan summary untuk setiap trace file.

Jika cursor untuk SQL statement telah close, SQL Trace juga menyediakan row source information yang termasuk: •

Operasi row yang menampilkan execution plan aktual setiap SQL statement.

•

Jumlah row, jumlah consistent reads, jumlah physical reads, jumlah physical writes, dan waktu elapsed untuk setiap operasi pada satu row.

53 Untuk menggunakan fasilitas SQL Trace dan TKPROF dapat mengikuti cara berikut: 1. M engaktifkan fasilitas SQL Trace Dengan cara mengetikkan statement berikut pada SQL*Plus: ALTER SESSION SET SQL TRACE = TRUE; 

Sedangkan untuk menonaktifkan SQL Trace cukup dengan mengetik statement berikut pada SQL*Plus: ALTER SESSION SET SQL TRACE = FALSE; 

SQL Trace secara otomatis akan dinonaktifkan ketika suatu aplikasi disconnect dari Oracle. 2. M elakukan format trace file dengan TKPROF TKPROF sebagai tempat untuk menampung trace file yang dihasilkan fasilitas SQL Trace, dan menghasilkan format file output. TKPROF juga dapat digunakan untuk menghasilkan execution plan. Contoh output dari TKPROF seperti berikut: Tabel 2.5 Contoh output dari TKPROF SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;  call   count      cpu    elapsed     disk    query current    rows  ‐‐‐‐ ‐‐‐‐‐‐‐  ‐‐‐‐‐‐‐  ‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐  ‐‐‐‐‐‐  Parse      1     0.16      0.29         3       13       0       0  Execute    1     0.00      0.00         0        0       0       0  Fetch      1     0.03      0.26         2        2       4      14      Misses in library cache during parse: 1   Parsing user id: (8) SCOTT     Rows     Execution Plan  ‐‐‐‐‐‐‐  ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐   14  MERGE JOIN   4   SORT JOIN   4     TABLE ACCESS (FULL) OF 'DEPT'  14    SORT JOIN   14      TABLE ACCESS (FULL) OF 'EMP' 

54

55 Penjelasannya sebagai berikut: Untuk call value: Tabel 2.6 Penjelasan call value CALL VALUE

Penjelasannya

PARSE

M enerjemahkan pernyataan SQL ke dalam execution plan, termasuk pemeriksaan untuk otorisasi keamanan yang tepat dan memeriksa keberadaan tabel, kolom, dan objek-objek lainnya.

EXECUTE

Pernyataan realisasi execution oleh Oracle. Untuk INSERT, UPDATE, dan pernyataan DELETE, ini memodifikasi data. Untuk pernyataan SELECT, ini mengidentifikasi baris yang dipilih.

FETCH

M engambil baris yang dikembalikan oleh query. Fetch hanya dilakukan pada saat menggunakan pernyataan SELECT.

Untuk statistik SQL Trace: Tabel 2.7 Penjelasan statistic SQL Trace S tatistik SQL Trace Penjelasan COUNT

Berapa kali sebuah pernyataan mengalami parsing, execute, atau fetch.

CPU

Total waktu CPU dalam detik untuk semua parsing, execute, atau fetch untuk pernyataan itu. Nilai ini adalah nol (0) jika TIM ED_STATISTICS tidak diaktifkan.

ELAPSED Total waktu elapsed dalam detik untuk semua parsing, execute, atau fetch untuk pernyataan itu. Nilai ini adalah nol (0) jika TIM ED_STATISTICS tidak diaktifkan. DISK

Jumlah blok data fisik yang dibaca dari datafiles pada disk untuk semua parsing, execute, atau fetch.

QUERY

Jumlah buffer yang diambil dalam mode konsisten untuk semua parsing, execute, atau fetch. Biasanya, buffer akan diambil dalam mode konsisten untuk query.

CURRENT Jumlah buffer yang diambil dalam mode current. Buffer akan diambil dalam mode current untuk pernyataan seperti INSERT, UPDATE, dan DELETE. ROWS

Jumlah baris yang diproses oleh pernyataan SQL. Jumlah ini tidak termasuk baris yang diproses oleh subqueries dari pernyataan SQL.

56 Untuk pernyataan select, jumlah baris yang kembali, muncul untuk langkah fetch. Untuk pernyataan update, delete, dan insert, jumlah baris yang diproses, muncul untuk langkah execute. Khusus untuk row, informasinya ditampilkan bila kursor ditutup. Di SQL * Plus, hanya ada satu kursor pengguna, sehingga setiap pernyataan dieksekusi menyebabkan kursor sebelumnya harus ditutup. Karena itu, jumlah row ditampilkan. PL/SQL memiliki penanganan kursor sendiri dan tidak menutup kursor child ketika kursor parent ditutup. Keluar (atau reconnect) menyebabkan jumlah row akan ditampilkan. Untuk melakukan convert tipe file TKPROF menjadi tipe file t xt agar bisa dibaca, dapat dijalankan pada command prompt, sintaksnya adalah sebagai berikut: Tkprof filename1 filename2 [explain=user/password] 

Penjelasannya sebagai berikut: Tabel 2.8 Penjelasan parameter dalam TKPROF filename1 M enentukan file input, suatu trace file berisi statistik yang dihasilkan oleh fasilitas Trace SQL. File ini dapat berupa trace file yang dihasilkan untuk sesi tunggal, atau sebuah file yang dihasilkan dengan menggabungkan individu trace file dari beberapa sesi. filename2 M enentukan file yang diformat TKPROF untuk menulis output-nya. explain

M enentukan execution plan untuk setiap pernyataan SQL dalam trace file dan menulis execution plan ini ke output file. TKPROF menentukan execution plan dengan mengeluarkan pernyataan execution plan setelah tersambung ke Oracle dengan user dan password yang ditentukan dalam parameter ini. Pengguna khusus harus memiliki hak akses (privileges) CREATE SESSION. TKPROF memakan waktu lebih lama untuk memproses trace file yang besar jika opsi EXPLAIN digunakan.

57 2.19

Hint Oracle Hint adalah suatu sintaks yang disediakan Oracle untuk mempengaruhi execution

plan sehingga bisa lebih cepat lagi kinerjanya dalam pengaksesan data jika execution plan tersebut out of date. Berdasarkan pendapat Cunningham (2008, p2), hint adalah suatu sintaks spesial dalam bentuk comment di dalam pernyataan SQL yang menspesifikasi suatu instruksi atau suatu cost-based optimizer (CBO). Optimizer akan menggunakan hint untuk mempengaruhi atau memaksa pilihannya dari suatu execution plan. Hint dispesifikasikan dalam suatu pernyataan block seperti: {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */  or  {DELETE|INSERT|SELECT|UPDATE} ‐‐+ hint [text] [hint[text]]...

Di mana: Delete, insert, select, atau update adalah suatu keyword query yang memulai suatu pernyataan block. Comment berisi hint harus ada dengan keyword berikut. Tanda (+) adalah tanda tambah yang mengakibatkan Oracle untuk mengartikan comment tersebut sebagai suatu hint. Setelah pembatas comment harus diikuti oleh tanda (+) tanpa spasi. Hint memungkinkan untuk mempengaruhi goal, behavior, metode, dan pilihan yang dipilih optimizer. Behavior optimizer secara default ditentukan oleh informasi seperti statistik pada objek di dalam suatu query, sistem statistik, parameter-parameter session dan parameter database. Biasanya optimizer akan memilih query plan yang telah didesain untuk menyediakan throughput dan efisiensi yang paling baik. Bagaimanapun, respon yang

58

paling cepat mungkin menjadi goal yang lebih penting dibanding throughput untuk suatu aplikasi seperti browser yang dapat melakukan scroll. Contoh berikut ini akan mempengaruhi optimizer untuk memilih plan index yang lebih intensif dengan menspesifikasikan hint FIRST_ROWS: SELECT /*+FIRST_ROWS*/ item_class, item_no, uom,  description, qty  FROM item_table JOIN item_availability  USING (item_no)  WHERE item_class =:INPUT_CLASS  AND qty > 0; 

2.20

Oracle Indexes Seperti pada website www.sagelogix.com, Index adalah struktur data opsional

yang berhubungan dengan tabel dan cluster yang digunakan untuk mempercepat akses row data dan kegunaan lainnya adalah dapat digunakan sebagai mekanisme untuk membuat nilai data lebih unik lagi dalam satu atau lebih kolom. Sedangkan menurut Alapati (2005, p225), index Oracle menyediakan akses yang cepat pada baris tabel dengan menyimpan nilai yang terurut dari kolom yang spesifik, dan menggunakan nilai yang terurut tersebut untuk melihat secara detail ke row tabel yang bersangkutan, hampir sama dengan cara menggunakan index buku untuk secara cepat menemukan apa yang diinginkan. Index memungkinkan untuk mencari suatu row dengan nilai kolom tertentu tanpa harus mencari pada seluruh baris yang ada pada tabel tersebut tetapi cukup hanya dengan sebagian kecil baris yang ada di tabel tersebut. Dengan demikian, penggunaan index yang tepat akan mengurangi disk I/O yang mahal. Kegunaannya dari index yaitu meningkatkan waktu respon dalam pengambilan data untuk suatu query. Kemudian pada saat data berubah, index itu dibuat untuk

59 mempertahankan keunikan nilai data dan mencegah nilai yang tidak unik masuk ke dalam tabel. Index Oracle terdiri dari beberapa tipe yaitu: ™ Unique and nonunique indexes Unique index adalah index yang berdasarkan pada kolom yang unik, biasanya sesuatu yang berkaitan dengan keamanan seperti nomor identitas karyawan. Walaupun dapat membuat unique index secara eksplisit, Oracle merekomendasikan untuk tidak melakukannya. Oracle lebih menyarankan untuk menggunakan unique constraint. Ketika membuat unique constraint pada kolom suatu tabel, Oracle secara otomatis akan membuat unique index ke dalam kolom tersebut. ™ Primary and secondary indexes Primary index adalah unique index pada tabel yang harus selalu memiliki suatu nilai, primary index tidak boleh null. Secondary index adalah index lainnya yang berada pada tabel yang sama yang boleh tidak unik. ™ Composite indexes Composite index adalah index yang mengandung dua atau lebih kolom dari tabel yang sama. Composite index ini juga disebut sebagai concatenated indexes. Composite index khususnya berguna untuk memaksa keunikan pada kolom tabel dalam kasus dimana tidak ada satu kolompun yang bisa mengidentifikasikan suatu baris secara unik. Oracle menyediakan beberapa skema index untuk memenuhi kebutuhan dari tipe aplikasi yang berbeda. Selama fase design, seharusnya memilih tipe index yang tepat setelah melakukan analisis dengan teliti dari kebutuhan-kebutuhan pada aplikasi.