9 BAB 2 LANDASAN TEORI 2.1 Landasan Teori Umum 2.1.1

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