Tuning PL/SQL Applications

Dengan berjalannya waktu, bahkan performa dari aplikasi-aplikasi yang dedesain dengan baik dapat menurun. Sehingga, tuning secara periodik merupakan bagian penting dari pemeliharaan aplikasi. Pembahasan kali ini menunjukkan kepada kita bagaimana membuat penyesuaian-penyesuaian kecil yang dapat meningkatkan performa. Dengan melakukan tuning terhadap aplikasi-aplikasi, kita dapat memastikan bahwa mereka dapat terus memberikan response time dan throughput yang diperlukan.

Pembahasan kali ini mendiskusikan topik-topik:

• Alasan-alasan untuk PL/SQL Performance Problems
• Mengidentifikasi PL/SQL Performance Problems
• Fitur-fitur PL/SQL untuk Performance Problems

12.1. Alasan-alasan untuk PL/SQL Performance Problems

Ketika sebuah aplikasi berbasis PL/SQL memilik performa buruk, biasanya terjadi disebabkan karena buruknya penulisan perintah-perintah SQL, praktek pemrograman yang buruk, kurangnya perhatian terhadap dasar-dasar PL/SQL, atau kesalahan menggunaan shared memory.

12.1.1. Buruknya Penulisan Perintah-perintah SQL di dalam sebuah PL/SQL Program

Program-program PL/SQL terlihat relatif sederhana karena kompleksitasnya disembunyikan di dalam perintah-perintah SQL, yang melakukan sebagian besar pekerjaan. Hal ini mengapa buruknya penulisan perintah-perintah SQL menjadi alasan utama lambatnya eksekusi program. Jika sebuah program mengandung banyak perintah-perintah SQL yang buruk, penulisan perintah-perintah PL/SQL yang baik tidak akan banyak membantu.

Jika buruknya penulisan perintah-perintah SQL membuat lambat program kita, maka kita perlu menganalisa execution plans dan performa mereka dengan menggunakan yang disebutkan di bawah ini. Kemudian, kita tulis ulang perintah-perintah SQL tersebut. Sebagai contoh, hints terhadap query dapat mengurangi problem-problem seperti full-table scans yang tidak perlu terjadi.

• Perintah EXPLAIN PLAN
• Fasilitas SQL Trace dengan utilitas TKPROF
• Fasilitas Oracle Trace

12.1.2. Praktek Pemrogram yang Buruk

Seringkali, praktek pemrograman yang buruk merupakan sebuah efek samping. Dalam keadaan-keadaan seperti ini, bahkan programmer yang berpengalaman sekalipun bisa menulis kode yang merintangi performa.

Tidak peduli bagaimana sesuainya sebuah bahasa pemrograman untuk suatu tugas tertentu, buruknya penulisan subprograms (sebagai contoh, sebuah function pengurutan atau pencarian yang lambat) dapat meruntuhkan performa. Misalkan subprogram tersebut yang dipanggil sangat sering oleh aplikasi merupakan sebuah lookup function dengan ratusan target yang mungkin. Jika function tersebut dapat ditulis sebagai sebuah hash atau binary search, namun, ditulis sebagai sebuah linear search, keseluruhan performa akan menderita.

Praktek lain yang buruk termasuk mendeklarasikan variables yang tidak pernah digunakan, melewatkan parameter-parameter yang tidak diperlukan ke functions dan procedures, menempatkan inisialisasi atau komputasi di dalam sebuah loop yang tidak diperlukan, dan seterusnya.

12.1.3. Duplikasi Built-in Functions

PL/SQL menyediakan function-function yang telah sangat dioptimasi seperti REPLACE, TRANSLATE, SUBSTR, INSTR, RPAD, dan LTRIM. Jangan mengkodekan versi kita sendiri. Built-in functions lebih efisien. Bahkan ketika sebuah built-in function memiliki lebih banyak kekuatan daripada yang kita perlukan, gunakanlah daripada melakukan hand-coding sebuah subset dari fungsionalitasnya.

12.1.4. Perintah Pengontrol Kondisi yang Tidak Efisien

Ketika mengevaluasi sebuah ekspresi logikal, PL/SQL menggunakan short-circuit evaluation. Yaitu, PL/SQL berhenti mengevaluasi ekspresi segera setelah hasil dapat ditentukan. Sebagai contoh, di dalam ekspresi OR berikut ini, ketika nilai dari sal adalah kurang dari 1500, operand kiri menghasilkan TRUE, sehingga PL/SQL tidak perlu mengevaluasi operand kanan (karena OR menghasilkan TRUE bahkan jika kedua operand tersebut adalah true):

IF (sal < 1500) OR (comm IS NULL) THEN
  ...
END IF;

Sekarang, mari kita perhatikan eskpresi AND berikut ini:

IF credit_ok(cust_id) AND (loan < 5000) THEN
  ...
END IF;

Function Boolean credit_ok selalu dipanggil. Namun, jikta kita mengubah operands dari AND seperti berikut ini

IF (loan < 5000) AND credit_ok(cust_id) THEN
  ...
END IF;

maka function tersebut dipanggil hanya ketika ekspresi loan < 5000 adalah true (karena AND menghasilkan TRUE hanya jika kedua operand-nya bernilai true).

Ide yang sama dapat diterapkan ke perintah-perintah EXIT-WHEN.

12.1.5. Implicit Datatype Conversions

Saat run time, PL/SQL melakukan konversi secara implisit terhadap tipe-tipe data berbeda secara struktural. Sebagai contoh, memberikan sebuah PLS_INTEGER variable ke sebuah NUMBER variable menghasilkan sebuah konversi karena representasi internal mereka adalah berbeda.

Menghindari konversi implisit dapat meningkatkan performa. Mari kita perhatikan contoh di bawah ini. Integer literal 15 direpresentasikan secara internal sebagai sebuah signed 4-byte quantity, sehingga PL/SQL harus mengkonversinya ke sebuah Oracle number sebelum penambahan. Namun, floating-poin literal 15.0 direpresentasikan sebagai sebuah 22-byte Oracle number, sehingga tidak diperlukan konversi.

DECLARE
n NUMBER;
c CHAR(5);
BEGIN
  n := n + 15; -- dikonversi
  n := n + 15.0; -- tidak dikonversi
  ...
END;

Berikut ini contoh lainnya:

DECLARE
c CHAR(5);
BEGIN
  c := 25; -- dikonversi
  c := '25'; -- tidak dikonversi
  ...
END;

12.1.6. Deklarasi Tipe Data Numeric yang Tidak Perlu

Tipe data NUMBER dan subtypes-nya adalah 22-byte, database-format numbers, di desain untuk portabilitas dan skala/posisi yang berubah-ubah, bukan untuk performa. Ketika kita perlu mendeklarasikan sebuah variable integer, gunakan tipe data PLS_INTEGER, yang merupakan tipe numerk paling efisien. Hal ini karena nilai-nilai PLS_INTEGER memerlukan lebih sedikit tempat penyimpanan (storage) dibandingkan dengan nilai-nilai INTEGER atau NUMBER. Juga, operasi-operasi PLS_INTEGER menggunakan machine arithmetic, sehingga mereka lebih cepat daripada operasi-operasi  BINARY_INTEGER, INTEGER, atau NUMBER, yang mana menggunakan library arithmetic.

Selanjutnya, INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, dan SIGNTYPE merupakan constrained subtypes (subtypes yang memiliki batasan). Sehingga, variable-variable mereka memerlukan pengecekan presisi pada saat run time, yang mana bisa berpengaruh terhadap performa.

12.1.7. NOT NULL Constraints yang Tidak Perlu

Dalam PL/SQL, menggunakan constraint NOT NULL mendatangkan sebuah harga performa. Mari kita perhatikan contoh berikut:

PROCEDURE calc_m IS
m NUMBER NOT NULL := 0;
a NUMBER;
b NUMBER;
BEGIN
  ...
  m := a + b;
  ...
END;

Karena m di batasi oleh NOT NULL, nilai dari ekspresi a + b diberikan ke sebuah variable sementara, yang kemudian diperiksa null atau tidak. Jika variable tersebut not null, nilainya diberikan ke m. Selain daripada itu, sebuah exception akan muncul. Namun, jika m tidak diberikan constraint, nilai akan diberikan ke m secara langsung.

PROCEDURE calc_m IS
m NUMBER; -- tanpa constraint
a NUMBER;
b NUMBER;
BEGIN
  ...
  m := a + b;
  IF m IS NULL THEN -- memaksakan constraint secara programatik
    ...
  END IF;
END;

Perlu kita ingat bersama bahwa subtypes NATURALN dan POSITIVEN didefinisikan sebagai NOT NULL. Sehingga menggunakan mereka dapat mendatangkan harga terhadap performa.

12.1.8. Deklarasi Size Variable-variable VARCHAR2

Tipe data VARCHAR2 melibatkan sebuah pertukaran antara penggunaan memory dan efisiensi. Untuk sebuah variable VARCHAR2(>=2000), PL/SQL secara dinamis mengalokasikan hanya memory yang cukup ke nilai aktual. Namun, untuk sebuah variable VARCHAR2(<2000), PL/SQL melakukan pre-alokasi memory yang cukup untuk menampung sebuah nilai ukuran maksimum. Sehingga, sebagai contoh, jika kita memberikan nilai 500-byte yang sama ke sebuah variable VARCHAR2(2000) dan untuk sebuah variable VARCHAR2(1999), yang terakhir menggunakan memory 1499 bytes lebih sedikit.

12.1.9. Kesalahan Penggunaan Shared Memory PL/SQL Program

Ketika kita memanggil sebuah packaged subprogram untuk pertama kali, keseluruhan package di-load ke shared memory pool. Sehingga, pemanggilan selanjutnya tidak lagi memerlukan disk I/O, dan kode program kita dapat berjalan lebih cepat. Namun, jika package tersebut telah keluar dari memory, ia harus di-load ulang jika kita mereferensinya lagi.

Kita dapat meningkatkan performa dengan mengatur shared memory pool secara tepat. Pastikan bahwa ia cukup besar untuk menampung seluruh packages yang sering digunakan, namun tidak terlalu besar sehingga memboroskan memory.

12.1.10. Pinned Package

Cara lain untuk meningkatkan performa adalah dengan menyematkan packages yang sering digunakan, ke dalam shared memory pool. Ketika sebuah package disematkan, ia tidak akan dikeluarkan dari memory oleh algoritma least recently used (LRU) yang secara normal digunakan oleh Oracle. Package tersebut tetap di dalam memory tidak peduli bagaimana penuhnya pool gets atau bagaimana seringnya kita mengakses package tersebut.

Kita dapat menyematkan packages dengan bantuan supplied package DBMS_SHARED_POOL.

12.1.11. Serially Reusable Packages

Untuk membantu kita mengatur penggunaan memory, PL/SQL menyediakan pragma SERIALLY_REUSABLE, yang mengijinkan kita untuk menandai beberapa packages sebagai serially reusable. Kita dapat menandai sebuah package jika state-nya diperlukan hanya untuk durasi satu pemanggilan ke server (sebagai contoh, sebuah OCI memanggil ke server atau sebuah server-to-server RPC).

Global memory untuk package-package seperti ini di satukan di dalam System Global Area (SGA), tidak dialokasikan ke individual users di dalam User Global Area (UGA). Dengan cara itu, area kerja package dapat digunakan kembali. Ketika pemanggilan ke server berakhir, memory dikembalikan ke pool. Setiap kali package tersebut digunakan, public variables-nya diinisialisasi ke nilai-nilai default mereka atau ke NULL.

Jumlah maksimum area kerja yang diperlukan untuk sebuah package adalah jumlah concurrent users dari package tersebut, yang biasanya lebih kecil daripada jumlah logged-on users. Penambahan penggunaan dari SGA memory adalah lebih dari offset oleh pengurangan penggunaan dari UGA memory. Juga, Oracle mengeluarkan area kerja yang tidak sedang digunakan jika ia perlu melakukan reclaim terhadap SGA memory.

Untuk packages tak ber-body, kita meng-kode-kan pragma di dalam package spec dengan menggunakan sintaks berikut:

PRAGMA SERIALLY_REUSABLE;

Untuk packages dengan sebuah body, kita harus menulis kode pragma di dalam spec dan body. Kita tidak dapat menulis kode pragma hanya di dalam body. Contoh-contoh berikut ini menunjukkan bagaimana sebuah public variable di dalam sebuah serially reusable package berjalan menyebrangi batas-batas pemanggilan:

CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/

CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
  pkg1.num := n;
END;

PROCEDURE print_pkg_state IS
BEGIN
  dbms_output.put_line('Num: ' || pkg1.num);
END;
END pkg1;
/

BEGIN
  /* Menginisialisasi package state. */
  pkg1.init_pkg_state(4);
  /* Pada server call yang sama, mencetak print package state. */
  pkg1.print_pkg_state; -- mencetak 4
END;
/

-- subsequent server call
BEGIN
  -- public variable dari package diinisialisasi
  -- ke nilai default secara otomatis
  pkg1.print_pkg_state; -- mencetak 0
END;