Tuning PL/SQL Applications
12.3. Fitur-fitur PL/SQL untuk Performance Problems
Setelah mengkoreksi kerusakan yang memperlambat aplikasi, kita dapat menggunakan fitur-fitur dan teknik-teknik PL/SQL berikut ini:
- Tuning PL/SQL Performance dengan Native Dynamic SQL
- Tuning PL/SQL Performance dengan Bulk Binds
- Tuning PL/SQL Performance dengan NOCOPY Compiler Hint
- Tuning PL/SQL Performance dengan RETURNING Clause
- Tuning PL/SQL Performance dengan External Routines
- Meningkatkan PL/SQL Performance dengan Object Types dan Collections
- Compiling PL/SQL Code untuk Native Execution
Ini merupakan fitur-fitur yang mudah digunakan yang dapat meningkatkan performa aplikasi.
12.3.1. Tuning PL/SQL Performance dengan Native Dynamic SQL
Beberapa program (sebuah general-purpose report writer sebagai contoh) harus membangun dan memproses sebuah beragam perintah-perintah SQL saat run time. Sehingga, full text mereka tidak diketahui hingga kemudian. Perintah-perintah seperti ini dapat, dan mungkin akan, berubah dari satu eksekusi ke eksekusi. Sehingga mereka disebut dengan perintah-perintah SQL dynamic.
Tadinya, untuk mengeksekusi perintah-perintah SQL, kita harus menggunakan package DBMS_SQL yang disediakan. Sekarang, bersama PL/SQL, kita dapat mengeksekusi berbagai macam perintah SQL dynamic dengan menggunakan sebuah antarmuka yang disebut dengan native dynamic SQL.
Native dynamic SQL lebih mudah digunakan dan lebih cepat dibandingkan dengan DBMS_SQL package. Dalam contoh berikut ini, kita mendeklarasikan sebuah cursor variable, kemudian menghubungkannya dengan sebuah perintah SELECT dynamic yang menghasilkan baris-baris data dari database table emp:
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN OPEN emp_cv FOR 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal; ... END;
12.3.2. Tuning PL/SQL Performance dengan Bulk Binds
Ketika perintah-perintah SQL dijalankan di dalam sebuah loop menggunakan elemen-elemen collection sebagai bind variables, context switching diantara PL/SQL dan SQL engines dapat memperlambat eksekusi. Sebagai contoh, perintah UPDATE berikut ini dikirim ke SQL engine dengan setiap iterasi dari FOR loop:
DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70, ...); -- department numbers BEGIN ... FOR i IN depts.FIRST..depts.LAST LOOP ... UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i); END LOOP; END;
Dalam kasus ini, jika perintah SQL berpengaruh terhadap empat atau lebih baris-baris data di database, penggunakan bulk binds dapat meningkatkan performa. Sebagai contoh, perintah UPDATE berikut ini dikirim ke SQL engine hanya sekali, dengan keseluruhan nested table:
FORALL i IN depts.FIRST..depts.LAST UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
Untuk meningkatkan performa, kita tulis kembali programs kita sebagai berikut:
- Jika sebuah perintah INSERT, UPDATE, atau DELETE berjalan di dalam sebuah loop dan mereferensi elemen-elemen collection, pindahkan ia kedalam sebuah perintah FORALL.
- Jika sebuah klausa SELECT INTO, FETCH INTO, atau RETURNING INTO mereferensi sebuah collection, masukkan klausa BULK COLLECT.
- Jika memungkinkan, gunakan host arrays untuk melewatkan collections kembali dan seterusnya diantara programs kita dan database server.
- Jika kerusakan dari operasi DML pada sebuah baris data tertentu bukan merupakan sebuah problem yang serius, tambahkan keyword SAVE EXCEPTIONS di dalam perintah FORALL dan laporkan atau bersihkan errors di dalam sebuah loop berikutnya dengan menggunakan atribut %BULK_EXCEPTIONS.
Ini bukan tugas yang sepele. Mereka membuthkan analisa yang sangat cermat dari aliran control program dan ketergantungannya.
12.3.3. Tuning PL/SQL Performance dengan NOCOPY Compiler Hint
Secara default, parameter-parameter OUT dan IN OUT dilewatkan berdasarkan nilai. Yaitu, nilai dari sebuah parameter actual IN OUT di-copy ke parameter formal terkait. Kemudian, jika subprogram tersebut keluar secara normal, nilai-nilai yang diberikan ke parameter-parameter formal di-copy ke parameter-parameter aktual terkait.
Ketika parameter-parameter tersebut menangani struktur-struktur data yang besar seperti collections, records, dan instances of object types, seluruh peng-copy-an ini memperlambat eksekusi dan menggunakan banyak memory. Untuk mencegahnya, kita dapat menentukan NOCOPY hint, yang mengijinkan PL/SQL compiler melewatkan parameter-parameter IN dan IN OUT berdasarkan referensi. Dalam contoh berikut ini, kita meminta compiler untuk melewatkan parameter IN OUT my_unit berdasarkan referensi dibandingkan berdasarkan nilai.
DECLARE TYPE Platoon IS VARRAY(200) OF Soldier; PROCEDURE reorganize (my_unit IN OUT NOCOPY Platoon) IS ... BEGIN ... END;
12.3.4. Tuning PL/SQL Performance dengan RETURNING Clause
Seringkali, aplikasi-aplikasi memerlukan informasi tentang baris-baris data yang terpengaruh oleh sebuah operasi SQL, sebagai contoh, untuk menghasilkan sebuah report atau mengambil sebuah aksi berikutnya. Perintah-perintah INSERT, UPDATE, dan DELETE dapat mengandung sebuah klausa RETURNING, yang menghasilkan nilai-nilai kolom yang terpengaruh ke dalam PL/SQL variables atau host variables. Hal ini mengurangi kebutuhan untuk melakukan SELECT terhadap baris-baris data setelah sebuah insert atau update, atau sebelum sebuah delete. Sebagai hasilnya, network round trips menjadi lebih sedikit, lebih sedikit server CPU time, lebih sedikit cursors, dan lebih sedikit server memory yang dibutuhkan.
Dalam contoh berikut ini, kita melakukan update gaji karyawan dan pada saat yang sama menampilkan nama karyawan dan gaji barunya ke dalam PL/SQL variables.
PROCEDURE update_salary (emp_id NUMBER) IS name VARCHAR2(15); new_sal NUMBER; BEGIN UPDATE emp SET sal = sal * 1.1 WHERE empno = emp_id RETURNING ename, sal INTO name, new_sal; -- Sekarang melakukan perhitungan yang menghasilkan nama dan gaji baru END;
12.3.5. Tuning PL/SQL Performance dengan External Routines
PL/SQL menyediakan sebuah antarmuka untuk memanggil routines yang ditulis dalam bahasa-bahasa lain. Standard libraries yang telah ditulis dan tersedia dalam bahasa-bahasa lain dalam dipanggil dari PL/SQL programs. Hal ini meningkatkan reusability, efficiency, dan modularity.
PL/SQL dikhususkan untuk SQL transaction processing. Beberapa tugas dapat lebih cepat dijalankan dalam bahasa low-level seperti C, yang mana sangat efisien dalam hal machine-precision calculations.
Untuk meningkatkan kecepatan eksekusi, kita dapat menulis kembali computation-bound programs di dalam C. Sebagai tambahan, kita dapat memindahkan programs seperti ini dari client ke server, yang mana mereka akan dieksekusi secara lebih cepat untuk kekuatan perhitungan yang lebih lagi dan lebih sedikit komunikasi melalui network.
Sebagai contoh, kita dapat menulis methods untuk sebiah image object type dalam C, meletakkan mereka di dalam sebuah dynamic link library (DLL), mendaftarkan library tersebut dengan PL/SQL, kemudian memanggilnya dari aplikasi-aplikasi kita. Saat run time, library tersebut di-load secara dinamis dan, untuk keamanan, dijalankan di dalam sebuah address space yang terpisah (diterapkan sebagai sebuah proses terpisah).
12.3.6. Meningkatkan PL/SQL Performance dengan Object Types dan Collections
Collection types dan object types meningkatkan produktifitas kita dengan mengijinkan realistic data modeling. Complex real-world entities dan relationships dipetakan secara langsung ke dalam object types. Dan, sebuah well-constructed object model dapat meningkatkan performa aplikasi dengan cara mengurangi table joins, menurunkan round trips, dan sejenisnya.
Client programs, termasuk PL/SQL programs, dapat mendeklarasikan objects dan collections, melewatkan mereka sebagai parameters, menyimpan mereka di dalam database, menampilkan mereka, dan seterusnya. Juga, dengan mengenkapsulasi operasi-operasi dengan data, object types mengijinkan kita memindahkan kode data-maintenance keluar dari SQL scripts dan PL/SQL blocks ke dalam methods.
Objects dan collections lebih efisien untuk meyimpan dan menampilkan karena mereka dapat dimanipulasi sebagai sebuah keseluruhan. Juga, object support terintegrasi dengan arsitektur database, sehingga ia dapat mengambil keuntungan dari banyak scalability dan performance improvements yang dibangun ke dalam setiap rilis Oracle.
12.3.7. Compiling PL/SQL Code untuk Native Execution
Kita dapat meningkatkan kecepatan PL/SQL procedures dengan meng-compile mereka ke dalam native code yang terletak dalam shared libraries. Procedures tersebut diterjemahkan ke dalam kode C, kemudian di-compile dengan compiler C biasa dan dihubungkan ke dalam Oracle process. Kita dapat menggunakan teknik ini dengan kedua Oracle packages yang telah disediakan tersebut, dan procedures yang kita tulis sendiri. Procedures yang di-compile dengan cara ini bekerja dalam berbagai macam server environments, seperti halnya shared server configuration (sebelumnya diketahui sebagai multi-threaded server).
Karena teknik ini tidak dapat terlalu banyak meningkatkan kecepatan akses perintah SQL yang dipanggil dari PL/SQL, hal ini sangat efektif untuk PL/SQL procedures yang banyak mengandung proses perhitungan yang tidak mengeluarkan banyak waktunya untuk mengeksekusi SQL.
Untuk meningkatkan kecepatan satu atau dua procedures, kita dapat menggunakan teknik:
- Memodifikasi makefile yang telah disediakan dan menambahkan paths dan nilai-nilai lain yang sesuai ke dalam system. Path dari makefile ini adalah $ORACLE_HOME/plsql/spnc_makefile.mk.
- Menggunakan perintah ALTER SYSTEM atau ALTER SESSION, atau mengubah initialization file, untuk mengatur parameter PL/SQL_COMPILER_FLAGS untuk menyertakan nilai NATIVE. Setting default menyertakan nilai INTERPRETED, dan kita harus menghapus keyword ini dari nilai parameter.
- Melakukan compile terhadap satu atau dua procedures, menggunakan datu dari method-method berikut:
- Menggunakan perintah ALTER PROCEDURE atau ALTER PACKAGE untuk meng-compile ulang procedure tersebut atau keseluruhan package tersebut.
- Menghapus procedure tersebut dan menciptakannya lagi
- Menggunakan CREATE OR REPLACE untuk meng-compilasi ulang procedure.
- Menjalankan satu dari script-script SQL*Plus yang melakukan set up kumpulan Oracle-supplied packages.
- Menciptakan sebuah database dengan menggunakan sebuah preconfigured initialization file dengan PLSQL_COMPILER_FLAGS=NATIVE. Selama penciptaan database, script UTLIRP dijalankan untuk melakukan kompilasi ulang seluruh Oracle-supplied packages.
- Untuk meyalinkan bahwa proces tersebut bekerja, kita dapat melakukan query terhadap data dictionary untuk melihat apakah sebuah procedure di-compile untuk native execution. Untuk mengecek apakah procedure yang ada di-compile untuk native execution atau tidak, kita dapat melakukan query terhadap data dictionary views USER_STORED_SETTINGS, DBA_STORED_SETTINGS, dan ALL_STORED_SETTINGS. Sebagai contoh, untuk mengecek status dari procedure MY_PROC, kita dapat memasukkan:
SELECT param_value FROM user_stored_settings WHERE param_name = 'PLSQL_COMPILER_FLAGS' and object_name = 'MY_PROC';
Kolom PARAM_VALUE memiliki sebuah nilai dari NATIVE untuk procedures yang di-compile untuk native execution, dan sebaliknya INTERPRETED.
Setelah procedures di-compile dan dialihkan ke shared libraries, mereka secara otomatis dihubungkan dengan Oracle process. Kita tidak perlu melakukan restart database, atau memindahkan shared libraries ke lokasi berbeda. Kita dapat memanggil kembali dan seterusnya diantara stored procedures, apakah mereka seluruhnya di-compile dalam cara default (interpreted), native execution, atau gabungan keduanya.
Karena setting PLSQL_COMPILER_FLAGS disimpan didalam library unit untuk setiap procedure, procedures yang di-compile untuk native execution di-compile dengan cara yang sama dengan ketika procedure di-compile ulang secara otomatis setelah menjadi invalidated, seperti ketika table tempatnya ia bergantung di-create ulang.
Kita dapat mengontrol kelakukan dari PL/SQL native compilation melalui perintah-perintah ALTER SYSTEM atau ALTER SESSION atau dengan mengeset atau mengubah parameter-parameter ini dalam initialization file:
- PLSQL_COMPILER_FLAGS
- PLSQL_NATIVE_LIBRARY_DIR (tidak dapat di set dengan ALTER SESSION
- untuk alasan keamanan)
- PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
- PLSQL_NATIVE_MAKE_UTILITY
- PLSQL_NATIVE_MAKE_FILE_NAME
Contoh Mengkompilasi sebuah PL/SQL Procedure untuk Native Execution
connect scott/tiger;
set serveroutput on;
alter session set plsql_native_library_dir='/home/orauser/lib';
alter session set plsql_native_make_utility='gmake';
alter session set plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
as
begin
dbms_output.put_line('Hello world');
select sysdate from dual;
end;
Bersamaan ketika procedure di-compile, kita melihat berbagai perintah-perintah compilation dan link sedang dieksekusi. Procedure tersebut segera tersedia untuk dipanggil, dan berjalan sebagai sebuah shared library secara langsung bersama dengan Oracle process.
Batasan-batasan Native Compilation
- Jika sebuah package specification di-compile untuk native execution, body yang terkait harus di-compile dengan setting yang sama.
- Debugging tools untuk PL/SQL tidak menangani procedures yang di-compile untuk native execution.
- Ketika banyak procedures dan packages (biasanya, melebihi 5000) di-compile untuk native execution, sejumlah besar dari shared objects di dalam sebuah single directory dapat mempengaruhi performa sistem. Dalam kasus ini, kita dapat mengatur initialization parameter PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT dalam initialization file sebelum menciptakan database atau meng-compile PL/SQL packages atau procedures. Mengatur parameter ini ke suatu nilai (biasanya sekitar 1000). Kemudian menciptakan subdirectories di bawah directory yang ditentukan di dalam parameter PL/SQL_NATIVE_LIBRARY_DIR. Kita beri nama subdirectories d0, d1, d2… d999, hingga nilai yang ditentukan untuk subdirectory count. Ketika procedures di-compile untuk native execution, DLL secara otomatis didistribusikan diantara subdirectories ini oleh PL/SQL compiler.
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] Silahkan melanjutkan membaca pembahasan Tuning PL/SQL Applications […]