9

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.

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 self-

tuning 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 transform-

oriented 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 :

Gambar 2. 1 Contoh sintaks Create Table dalam SQL

Gambar 2. 2 Contoh sintaks untuk menambah baris baru dalam SQL

CREATE TABEL Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2));

INSERT INTO Staff VALUES(‘SG16’,’Brown’,8300);

13

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

SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000;

14

SELECT [DISTINCT|ALL] {*|[columnExpression [AS newName] [,…]} FROM TabelName [alias] [,..] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList]

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 :

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:

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 masing-

masing 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

σc (R X S)

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

SELECT COUNT (*) FROM employees;

SELECT SUM (salary) FROM employees;

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 :

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 :

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 :

Gambar 2. 9 Contoh penggunaan AVG dalam SQL

SELECT AVG (salary), department_name FROM employees a JOIN departments b ON a.department_id = b.department_id GROUP BY department_name;

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 :

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 :

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.

SELECT first_name || ‘ ‘ || last_name FROM employees WHERE salary = ( SELECT MIN (salary) FROM employees );

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’ );

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 :

Gambar 2. 12 Contoh penggunaan subquery dengan equality

SELECT * FROM employees WHERE department_id = ( SELECT department_id FROM departments WHERE department_name LIKE ‘Administration’ );

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 :

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.

SELECT first_name || ‘ ‘ || last_name, salary FROM employees WHERE (SELECT AVG (salary) FROM employees) < salary

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.

6

M

s

k

C

2

m

m

d

p

la

d

p

h

d

6. Mengopt

Memonitor d

sebagai conto

kepada basis

CPU atau me

2.2.1.4. A

Active

melakukan p

menunjukkan

data pada w

pada gambar

alu pada bag

database dan

pemanggilan

hanya 1 acti

dan pada bag

Ga

timasi Sistem

dan melaku

oh adalah m

s data Oracle

emory untuk

Average Acti

e session m

pemanggilan

n 4 orang p

waktu yang b

r 2.14 hanya

gian kedua h

n pada bagi

n ke databa

ve session (

gian 3 terdap

ambar 2. 14

m Operasi

ukan optima

mengovercloc

e sehingga O

k melakukan

ive Session

merupakan

n ke dalam b

pengguna dim

berbeda-bed

a user 4 yan

hanya user 4

ian ketiga h

se sehingga

(user 4) pada

pat 3 active s

Pengertian

asi CPU, I/O

ck CPU atau

Oracle dapat

aktivitas di

jumlah dar

basis data at

mana setiap

da. Pada bag

ng melakuk

4 dan 3 yang

hanya user 4

a dapat disi

a bagian ked

session dan s

n dari Active

O dan peng

u memberik

t menggunak

dalam basis

ri sesi yan

tau tidak idl

p pengguna

gian awal y

kan pemangg

g melakukan

4, 3 dan 1

impulkan pa

dua terdapat

seterusnya.

e Session

ggunaan me

kan prioritas

kan lebih ba

data.

ng aktif (se

le). Gambar

mengakses

yang ditunju

gilan ke dat

n pemanggil

yang melak

ada bagian

t 2 active se

29

emory

lebih

anyak

edang

r 2.14

basis

ukkan

abase

an ke

kukan

awal

ession

y

w

p

d

Sebu

yang secara

wait yang da

Gamb

Secar

per detik bes

dilihat pada g

Gam

uah sesi pem

umum dapat

apat dilihat p

bar 2. 15 Pe

ra umum, Or

erta aktivita

gambar 2.16

mbar 2. 16 Ilterten

anggilan ke

t dikategorik

pada gambar

mbagian kok

racle akan m

s yang dilak

6 berikut :

lustrasi aktintu yang dia

basis data te

kan menjadi

r 2.15 beriku

omponen dake basis data

menyimpan s

kukan oleh se

ivitas dari 4ambil oleh O

erdiri dari be

3 hal yaitu :

ut :

ari sebuah sa

etiap sesi da

esi tersebut y

4 sesi selamaOracle per d

eberapa kegi

: CPU, IO d

sesi pemang

alam kurun w

yang dapat

a kurun wakdetik

30

iatan

an

ggilan

waktu

ktu

s

b

g

s

la

m

m

Untuk

secara langsu

batang sepert

Gam

Namu

gambar yang

seperti pada g

Gam

Sehin

angsung Ora

mendapatkan

mudah untuk

k menggamb

ung menggab

ti ditunjukka

mbar 2. 17 D

un jika peng

g dihasilkan t

gambar 2.18

mbar 2. 18 D

ngga untuk m

acle akan me

n sebuah Ave

k dilihat sepe

barkan hal in

bungkan has

an pada gam

Diagram badar

gambaran se

tidak dapat d

8 :

Diagram Badari sesi y

menggambar

engambil beb

erage Active

erti pada gam

ni secara ben

sil tersebut k

mbar 2.17 ber

atang untukri sesi yang a

etiap sesi ini

dibaca karen

atang untukyang aktif (

rkan hal ini d

berapa samp

e Session yan

mbar 2.19

ntuk grafik m

ke dalam seb

rikut :

k menggambaktif

i dilakukan p

na garis yang

k menggamb(per detik)

dengan baik

pel dan mera

ng mana has

maka Oracle

buah diagram

\

barkan aktiv

per detik mak

g terlalu tipis

barkan akti

maka secara

ata-rata nya u

ilnya akan le

31

m

vitas

ka

s

ivitas

a

untuk

ebih

2

p

r

G

Pada

2.20 dimana

pada Tab per

ata jumlah s

Dari gam

dapat dili

dimana p

dengan j

tersebut.

Gambar 2. 1

oracle bentu

diagram bat

rformance ya

sesi yang akt

Gambar 2.

mbar Active A

ihat bagaim

pada gamba

elas pekerja

9 Diagram (dirata-r

uk graph ters

tang ini dapa

ang menggam

tif yang seca

20 GambarEn

Average Sess

mana kinerja

ar Active A

aan apa ya

Batang darrata selama

sebut digamb

at dilihat dar

mbarkan Av

ara default di

r Average Atreprise Ma

sion yang di

basis data

Average Ses

ang paling b

ri Average A15 detik)

barkan seper

ri Oracle Ent

verage Activ

i rata-rata de

ctive Sessionnager

itunjukkan o

dalam kuru

ssion tersebu

banyak dila

Active Sessio

rti pada gam

treprise Man

ve Session (ra

engan 15 det

n pada Ora

oleh Oracle

un waktu ter

ut dapat te

akukan oleh

32

on

mbar

nager

ata-

tik)

cle

maka

rtentu

erlihat

h sesi

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 High-

Load 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

WHERE a.order_no = b.order_no

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:

Gambar 2. 22 Contoh where clause dengan kolom yang

belum ditransformasikan

37

WHERE TO_NUMBER (SUBSTR(a.order_no,INSTR (b.order_no, '.')-1)) =(SUBSTR (a.order_no,INSTR (b.order_no, '.')-1))

AND charcol = numexpr

dibandingkan dengan gambar 2.23 yaitu :

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:

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

AND TO_NUMBER (charcol) = numexpr

numeric. Dalam hal ini, Oracle secara implisit akan mengubah

query gambar 2.24 menjadi seperti query gambar 2.25:

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:

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

SELECT info FROM tabels WHERE ... AND somecolumn BETWEEN DECODE (:loval, 'ALL', somecolumn, :loval) AND DECODE (:hival, 'ALL', somecolumn, :hival);

39

UNION ALL agar indeks dapat digunakan yaitu dengan query seperti

pada gambar 2.27 :

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

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');

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

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 CHO

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

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

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 */

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 :

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 CHO

1 NESTED_LOOPS 5

2 VIEW 3

3 SORT UNIQUE 3

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

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 */

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:

Gambar 2. 30 Query dengan penggunaan IN

*) Keterangan :

Note 4 Penggunaan IN menunjukkan bahwa subquery tidak

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

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 CHO

1 NESTED LOOPS 125

2 VIEW 116

3 SORT UNIQUE 116

4 TABEL ACCESS FULL ORDERS ANA 40

5 TABEL ACCESS BY

INDEXROWID EMPLOYEES ANA 1

6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA

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 :

Gambar 2. 31 Query dengan pengunaan 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*/

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 OPTIONS OBJECT_NAME OPT COST

0 SELECT STATEMENT CHO

1 FILTER

2 TABEL ACCESS BY INDEX ROWID EMPLOYEES ANA 98

3 AND-EQUAL

4 INDEX RANGE SCAN EMP_JOB_IX ANA

5 INDEX RANGE SCAN EMP_DEPARTMENT_IX ANA

6 INDEX RANGE SCAN ORD_SALES_REP_IX ANA 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 :

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.

SELECT /*+ FULL(e) */ e.ename FROM emp e WHERE e.job = 'CLERK';

46

Gambar 2.33 menunjukkan bagaimana cara untuk mengoptimasi urutan

JOIN :

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.

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;

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 :

Gambar 2. 34 Query untuk mengambil jumlah karyawan yang

gajinya lebih kecil dari 2000

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

49

Gambar 2. 35 Query untuk mengambil jumlah karyawan yang

gajinya antara 2000 dan 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 :

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

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

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

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;

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:

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

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;

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 :

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

CREATE INDEX year_idx on all_fact(order_date) LOCAL (PARTITION name_idx1), (PARTITION name_idx2), (PARTITION name_idx3);

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:

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

CREATE INDEX item_idx on all_fact (item_nbr) GLOBAL (PARTITION city_idx1 VALUES LESS THAN (100)), (PARTITION city_idx1 VALUES LESS THAN (200)), (PARTITION city_idx1 VALUES LESS THAN (300)), (PARTITION city_idx1 VALUES LESS THAN (400)), (PARTITION city_idx1 VALUES LESS THAN (500));

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:

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

SELECT job, empno FROM emp WHERE ename = ‘RICH’;

57

dibandingkan dengan proses eksekusi dengan “index fast full scan”. Misalnya

pada contoh berikut :

Gambar 2.42 berikut menunjukkan contoh pembuatan concatenated index:

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 :

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 :

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

CREATE INDEX skip1 ON emp5(job,empno);

SELECT count(*) FROM emp5 WHERE empno = 7900;

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

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 :

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 :

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.

SELECT /*+ index_ss(emp5 skip1) */ count(*) FROM emp5 WHERE empno = 7900;

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

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 :

Gambar 2. 48 Syntax pembuatan index hint

Gambar 2.49 menunjukkan bagaimana cara pengunaan index hint

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

/*+ index(nama_table nama_indeks) */

/*+ index(emp5 skip1) */

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 :

Gambar 2. 50 Contoh Query dimana Optimizer akan menggunakan

full table scan walaupun terdapat index

SELECT product_id, qty FROM product WHERE company_no = 1;

61

Gambar 2.51 menunjukkan hasil eksekusi yang dihasilkan melalui query

gambar 2.50 dengan SQL*Plus :

Gambar 2. 51 Execution plan dari query pada gambar 2.50

Gambar 2.52 menunjukkan penggunaan hint untuk memaksa penggunaan

index oleh optimizer

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 :

Gambar 2. 53 Execution plan dari query pada gambar 2.52

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)

SELECT /*+ index(product company_idx1) */ product_id, qty FROM product WHERE company no = 1;

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)

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.

Gambar 2. 54 Query dimana index tidak digunakan karena

adanya sintaks ‘<>’

SELECT cust_id, cust_name FROM customers WHERE cust_rating <> 'aa';

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 :

Gambar 2. 55 Query dimana index tidak digunakan karena pengunaan sintaks is null

SELECT empno, ename, deptno FROM emp WHERE sal is null;