DRAFT JUDUL : OPTIMALISASI COST DAN TIME DENGAN SQL TUNING PADA APLIKASI PROFIN Alvian Osalindo Fransiskus Martin Suparto Darudiato Universitas Bina Nusantara
ABSTRAK Salah satu tujuan dalam melakukan SQL Tuning adalah untuk mengoptimalkan cost dan time pada suatu query. Dalam SQL tuning kali ini, menggunakan metode menurut alapati. Adapun metode tersebut dengan cara melakukan penulisan SQL yang efisien, menggunakan Index dengan tepat dan menambahkan Hint Optimizer. Hasil dalam SQL Tuning ini yaitu dapat mengoptimalkan cost dan time yang digunakan. Kata Kunci : SQL, Tuning, Cost, Time One of the purposes in doing SQL Tuning is to optimize the cost and time at a query. SQL tuning in this time, using the method according to Alapati. The method is a way to writing efficient SQL, use the Index to the right and adding Optimizer Hint. This results in a SQL Tuning which can optimize the cost and time used. Keyword : SQL, Tuning, Cost, Time
PENDAHULUAN Dewasa ini data telah menjadi sumber daya yang tidak terpisahkan dalam kelangsungan hidup perusahaan. Sistem aplikasi penerbangan menggunakan data untuk mengelola penerbangan. Sistem aplikasi rumah sakit mengakses data yang dipakai untuk mencari data pasien, dokter, maupun obatobatan. Selain itu, sistem aplikasi perbankan dan asuransi juga menggunakan data untuk mengelola data pelanggan. Optimalisasi kinerja akses data telah menjadi parameter yang wajib dipenuhi di dalam setiap perusahaan. Seiring dengan bertambahnya jumlah data yang kian terus meningkat dan formulasi query yang belum optimal, kinerja akses data akan menjadi lambat. Jika hal ini terjadi, maka akan mengganggu arus lalu lintas proses bisnis pada perusahaan. Akibat jumlah data yang kian terus meningkat dan formulasi query yang belum optimal, maka diperlukan optimalisasi akses data pada basis data. Basis data merupakan teknologi yang digunakan untuk media penyimpanan dan pengelolaan data-data tersebut. Menurut Sukheja dan Singh (2011, p307), pada zaman ini kebutuhan akan basis data sudah sangat mendesak. Hal ini disebabkan karena adanya desentralisasi dalam infrastruktur IT pada suatu perusahaan akibat proses merger, akuisisi, maupun
aplikasi khusus corporate. Oleh karena itu, diperlukan basis data untuk mendukung pengelolaan data-data tersebut. PT VWX menggunakan aplikasi ProFin untuk mengelola seluruh proses keuangan perusahaan secara terpusat. Seiring dengan bertambahnya jumlah data dan formulasi query yang belum optimal, maka proses pengaksesan data pada aplikasi ProFin menjadi lambat. Oleh karena itu PT VWX memerlukan proses peningkatan kinerja akses data. Melalui proses SQL tuning, diharapkan akan mengoptimalkan efisiensi perusahaan sehingga arus lalu lintas proses bisnis pada PT VWX menjadi lebih baik dari sebelumnya. Menurut Karthik, Reddy, dan Vanan (2012, p418), SQL tuning dapat mengurangi tekanan pada basis data ketika melakukan pengeksekusian query dan membuat penggunaan basis data menjadi lebih efisien serta memori yang diperlukan menjadi lebih kecil. Tujuan yang ingin dicapai dalam penelitian ini yaitu untuk menganalisis query yang belum optimal pada aplikasi ProFin dan melakukan SQL tuning pada aplikasi ProFin untuk mengoptimalkan kinerja akses data dengan memprioritaskan pada cost dan time. Adapun manfaat yang dapat diperoleh dari penelitian ini yaitu untuk mengetahui permasalahan dari query yang belum optimal pada aplikasi ProFin, mengurangi cost pada resource yang digunakan ketika mengeksekusi query, mengurangi time yang diperlukan untuk mengakses data sehingga pihakpihak yang terkait dapat lebih cepat dalam mengambil keputusan, dan mengoptimalkan penggunaan sumber daya yang ada tanpa dilakukan penambahan hardware ataupun software.
METODE PENELITIAN Metodologi yang digunakan pada penelitian ini adalah : 1. Metode Analisis a. Pengumpulan Data Untuk mendapatkan data yang akurat mengenai PT. VWX, akan dilakukan observasi di PT. VWX. Selain itu juga, akan dilakukan studi literatur, baik dari buku-buku maupun jurnal-jurnal yang berhubungan dengan SQL tuning untuk dijadikan bahan pembelajaran dalam membuat laporan ini. b. Analisis Sistem Berjalan Melakukan analisis sistem yang berjalan dengan penggunaan rich picture untuk menjabarkan proses bisnis keuangan yang terkait dalam penelitian ini dan penggunaan Entity Relationship Diagram (ERD) untuk menggambarkan konsep logika basis data pada aplikasi ProFin yang akan digunakan dalam SQL tuning. 2. Metode Tuning Metode tuning yang digunakan adalah metode tuning menurut Alapati, antara lain: a. Penulisan SQL yang efisien Melakukan restrukturisasi terhadap penulisan sintaks query yang digunakan untuk menghasilkan kinerja akses data yang lebih optimal berbasis pada cost dan time. b. Penggunaan Index yang tepat Membuat index sesuai dengan kebutuhan untuk menurunkan cost dan mempercepat time sehingga kinerja akses data menjadi lebih optimal. c. Penambahan Hint Optimizer Menggunakan hint optimizer untuk memaksa optimizer dalam menentukan access method yang diinginkan dalam mengeksekusi query sehingga proses pengaksesan data menjadi lebih optimal dari sebelumnya.
HASIL DAN BAHASAN 1. Case View IP_RCP_HDR View IP_RCP_HDR dipakai untuk menampilkan data receipt header yang berdasarkan pada Table PO.RCV_SHIPMENT_HEADERS, Table PO.RCV_SHIPMENT_LINES, dan Table PO.PO_LINES_ALL.
Gambar 1 Tampilan Entity Relationship Diagram Pada Case View IP_RCP_HDR
Sebelum Tuning Tabel 1 Tampilan Query Case View IP_RCP_HDR Sebelum Tuning SELECT RSL.PO_HEADER_ID, SUM (PLA.UNIT_PRICE * RSL.QUANTITY_RECEIVED) RCP_ COST FROM PO.RCV_SHIPMENT_HEADERS RSH, PO.RCV_SHIPMENT_LINES RSL, PO.PO_LINES_ALL PLA WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSL.PO_LINE_ID = PLA.PO_LINE_ID GROUP BY RSL.PO_HEADER_ID; Apabila query yang terdapat pada tabel 1 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 2 untuk Explain Plan dan gambar 3 untuk Query Viewer dan menghasilkan output seperti gambar 4.
Gambar 2 Tampilan Explain Plan Pada Case View IP_RCP_HDR Sebelum Tuning
Gambar 3 Tampilan Query Viewer Pada Case View IP_RCP_HDR Sebelum Tuning
. . .
Gambar 4 Data Grid Pada Case View IP_RCP_HDR Sebelum Tuning Berdasarkan tampilan pada gambar 2, besaran cost secara keseluruhan yang diperlukan untuk mengeksekusi query case View IP_RCP_HDR sebelum tuning sebesar 346.629.728 (CPU cost) dan 2.604 (IO cost). Pada sequence poin 2, terdapat sebuah kondisi Full Table Scan pada Table PO.RCV_SHIPMENT_LINES. Adapun terdapatnya kondisi Full Table Scan pada Table PO.RCV_SHIPMENT_LINES, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 4, terdapat sebuah kondisi Full Table Scan pada Table PO.PO_LINES_ALL. Adapun terdapatnya kondisi Full Table Scan pada Table PO.PO_LINES_ALL, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 6, terdapat fungsi Merge Join untuk Table PO.RCV_SHIPMENT_LINES dan Table PO.PO_LINES_ALL yang dipakai untuk menyeleksi hasil query. Adapun besaran cost yang dihasilkan pada fungsi Merge Join, disebabkan karena adanya fungsi Sorting Join pada Table PO.RCV_SHIPMENT_LINES (lihat sequence poin 3) dan Table PO.PO_LINES_ALL (lihat sequence poin 5). Berdasarkan tampilan pada gambar 3, pada kolom execution time, terlihat bahwa diperlukannya waktu rata-rata sebesar 316 msec untuk menampilkan hasil output secara keseluruhan seperti pada gambar 4.
Setelah Tuning Tabel 2 Query Case View IP_RCP_HDR Setelah Tuning
SELECT /*+ CARDINALITY (RSH 1)*/ RSL.PO_HEADER_ID, SUM (PLA.UNIT_PRICE * RSL.QUANTITY_RECEIVED) RCP_COST FROM PO.RCV_SHIPMENT_HEADERS RSH, PO.RCV_SHIPMENT_LINES RSL, PO.PO_LINES_ALL PLA WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSL.PO_LINE_ID = PLA.PO_LINE_ID GROUP BY RSL.PO_HEADER_ID; Apabila query yang terdapat pada tabel 2 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 5 untuk Explain Plan dan gambar 6 untuk Query Viewer dan menghasilkan output seperti gambar 7.
Gambar 5 Explain Plan Pada Case View IP_RCP_HDR Setelah Tuning
Gambar 6 Query Viewer Pada Case View IP_RCP_HDR Setelah Tuning
. . .
Gambar 7 Data Grid Pada Case View IP_RCP_HDR Setelah Tuning Berdasarkan tampilan pada gambar 5, pada baris select statement, besaran nilai cost yang diperlukan untuk mengeksekusi query case View IP_RCP_HDR setelah tuning sebesar 14.757.441 (CPU cost) dan 43 (IO cost) dimana hasilnya lebih kecil dibandingkan besaran nilai cost sebelum tuning, 346.629.728 (CPU cost) dan 2.604 (IO cost) (lihat gambar 2). Berdasarkan tampilan pada gambar 6, pada kolom execution time, diperlukan waktu rata-rata sebesar 553,33 msec untuk menampilkan hasil output secara keseluruhan dimana hasilnya lebih lambat dibandingkan kondisi sebelum tuning, 316 msec (lihat gambar 3). Hasil dari query case View IP_RCP_HDR setelah tuning dapat dilihat pada gambar 7 dimana hasilnya sama dengan query case View IP_RCP_HDR sebelum tuning (lihat gambar 4).
2. Case View IP_RCP_M View IP_RCP_M dipakai untuk menampilkan data receipt detail yang berdasarkan pada Table PO.RCV_SHIPMENT_HEADERS dan Table PO.RCV_SHIPMENT_LINES.
Gambar 8 Tampilan Entity Relationship Diagram Pada Case View IP_RCP_M Sebelum Tuning Tabel 3 Tampilan Query Case View IP_RCP_M Sebelum Tuning SELECT RSH.RECEIPT_NUM, RSL.QUANTITY_RECEIVED, RSL.PO_HEADER_ID, RSL.PO_LINE_ID FROM PO.RCV_SHIPMENT_HEADERS RSH, PO.RCV_SHIPMENT_LINES RSL WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID; Apabila query yang terdapat pada tabel 3 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 9 untuk Explain Plan dan gambar 10 untuk Query Viewer dan menghasilkan output seperti pada gambar 11.
Gambar 9 Tampilan Explain Plan Pada Case View IP_RCP_M Sebelum Tuning
Gambar 10 Tampilan Query Viewer Pada Case View IP_RCP_M Sebelum Tuning
. . .
Gambar 11 Data Grid Pada Case View IP_RCP_M Sebelum Tuning Berdasarkan tampilan pada gambar 9, besaran cost secara keseluruhan yang diperlukan untuk mengeksekusi query case View IP_RCP_M sebelum tuning sebesar 51.660.200 (CPU cost) dan 882 (IO cost). Pada sequence poin 1, terdapat sebuah kondisi Full Table Scan pada Table PO.RCV_SHIPMENT_HEADERS. Adapun terdapatnya kondisi Full Table Scan pada Table PO.RCV_SHIPMENT_HEADERS, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 2, terdapat sebuah kondisi Full Table Scan pada Table PO.RCV_SHIPMENT_LINES. Adapun terdapatnya kondisi Full Table Scan pada Table PO.PO_RCV_SHIPMENT_LINES, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 3, terdapat fungsi Hash Join untuk Table PO.RCV_SHIPMENT_HEADERS dan Table PO.RCV_SHIPMENT_LINES yang dipakai untuk menyeleksi hasil query. Adapun digunakannya fungsi Hash Join, disebabkan karena tidak terpanggilnya salah satu fungsi Index baik pada Table PO.RCV_SHIPMENT_HEADERS dan Table PO.RCV_SHIPMENT_LINES. Berdasarkan tampilan pada gambar 10, pada kolom execution time, terlihat bahwa diperlukannya waktu rata-rata sebesar 199,67 msec untuk menampilkan hasil output secara keseluruhan seperti pada gambar 11.
Setelah Tuning Tabel 4 Query Case View IP_RCP_M Setelah Tuning SELECT /*+ PARALLEL(RSL 100000) */ RSH.RECEIPT_NUM, RSL.QUANTITY_RECEIVED, RSL.PO_HEADER_ID, RSL.PO_LINE_ID FROM PO.RCV_SHIPMENT_HEADERS RSH, PO.RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID; Apabila query yang terdapat pada tabel 4.1.13 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 4.1.19 untuk Explain Plan dan gambar 4.1.20 untuk Query Viewer.
Gambar 12 Explain Plan Pada Case View IP_RCP_M Setelah Tuning
Gambar 13 Query Viewer Pada Case View IP_RCP_M Setelah Tuning
. . .
Gambar 14 Data Grid Pada Case View IP_RCP_M Setelah Tuning Berdasarkan tampilan pada gambar 12, pada baris select statement, besaran nilai cost yang diperlukan untuk mengeksekusi query case View IP_RCP_M setelah tuning sebesar 30.128 (CPU cost) dan 5 (IO cost) dimana hasilnya lebih kecil dibandingkan besaran nilai cost sebelum tuning, 51.660.200 (CPU cost) dan 882 (IO cost) (lihat gambar 9). Berdasarkan tampilan pada gambar 13, pada kolom execution time, diperlukan waktu rata-rata sebesar 421 msec untuk menampilkan hasil output secara keseluruhan dimana hasilnya lebih lambat dibandingkan kondisi sebelum tuning, 199,67 msec (lihat gambar 10). Hasil dari query case View IP_RCP_M setelah tuning dapat dilihat pada gambar 14 dimana hasilnya sama dengan query case View IP_RCP_M sebelum tuning (lihat gambar 11).
3. Case View IP_RECALC_PR_V View IP_RECALC_PR_V dipakai untuk menampilkan data rekalkulasi untuk nilai purchase request yang berdasarkan pada Table PO.PO_REQUISITION_HEADERS_ALL dan Table APPS.IP_PR_DETIL.
Gambar 15 Tampilan Entity Relationship Diagram Pada Case View IP_RECALC_PR_V
Sebelum Tuning Tabel 5 Tampilan Query Case View IP_RECALC_PR_V Sebelum Tuning SELECT SEGMENT1, ORG_ID FROM (SELECT DISTINCT SEGMENT1, ORG_ID FROM PO.PO_REQUISITION_HEADERS_ALL WHERE AUTHORIZATION_STATUS = 'APPROVED' AND ATTRIBUTE5 = 'BUDGET CLOSED' MINUS SELECT DISTINCT SEGMENT1, ORG_ID FROM PO.PO_REQUISITION_HEADERS_ALL, APPS.IP_PR_DETIL WHERE AUTHORIZATION_STATUS = 'APPROVED' AND ATTRIBUTE5 = 'BUDGET CLOSED' AND SEGMENT1 = PR_NUMBER); Apabila query yang terdapat pada tabel 5 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 16 untuk Explain Plan dan gambar 17 untuk Query Viewer dan menghasilkan output seperti pada gambar 18.
Gambar 16 Tampilan Explain Plan Pada Case View IP_RECALC_PR_V Sebelum Tuning
Gambar 17 Tampilan Query Viewer Pada Case View IP_RECALC_PR_V Sebelum Tuning
Gambar 18 Data Grid Pada Case View IP_RECALC_PR_V Sebelum Tuning Berdasarkan tampilan pada gambar 16, besaran cost secara keseluruhan yang diperlukan untuk mengeksekusi query case View IP_RECALC_PR_V sebelum tuning sebesar 432.750.554 (CPU cost) dan 3.976 (IO cost). Pada sequence poin 1, terdapat sebuah kondisi Full Table Scan pada Table PO.PO_REQUISITION_HEADERS_ALL. Adapun terdapatnya kondisi Full Table Scan pada Table PO.PO_REQUISITION_HEADERS_ALL, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 3, terdapat sebuah kondisi Full Table Scan pada Table PO.PO_REQUISITION_HEADERS_ALL. Adapun terdapatnya kondisi Full Table Scan pada Table PO.PO_REQUISITION_HEADERS_ALL, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 5, terdapat sebuah kondisi Full Table Scan pada Table APPS.IP_PR_DETIL. Adapun terdapatnya kondisi Full Table Scan pada Table APPS.IP_PR_DETIL, disebabkan karena tidak terpanggilnya fungsi Index. Pada sequence poin 7, terdapat fungsi Merge Join untuk Table PO.PO_REQUISITION_HEADERS_ALL dan Table APPS.IP_PR_DETIL yang dipakai untuk menyeleksi hasil query. Adapun besaran cost yang dihasilkan pada fungsi Merge Join, disebabkan karena adanya fungsi Sorting Join pada Table PO.PO_REQUISITION_HEADERS_ALL (lihat sequence poin 3) dan Table APPS.IP_PR_DETIL (lihat sequence poin 5). Pada sequence poin 9, terdapat fungsi operator Minus untuk menyeleksi query. Adapun dipakainya operator Minus menyebabkan adanya fungsi Sorting Unique untuk menyeleksi query pada Table PO.PO_REQUISITION_HEADERS_ALL (lihat sequence poin 2) dan Table PO.PO_REQUISITION_HEADERS_ALL (lihat sequence poin 4) serta Table APPS.IP_PR_DETIL (lihat sequence poin 6). Berdasarkan tampilan pada gambar 17, pada kolom execution time, terlihat bahwa diperlukannya waktu rata-rata sebesar 798 msec untuk menampilkan hasil output secara keseluruhan seperti pada gambar 18.
Setelah Tuning Tabel 6 Query Case View IP_RECALC_PR_V Setelah Tuning SELECT SEGMENT1, ORG_ID FROM (SELECT /*+CARDINALITY(SS 1)*/ DISTINCT SEGMENT1, ORG_ID FROM PO.PO_REQUISITION_HEADERS_ALL SS WHERE AUTHORIZATION_STATUS = 'APPROVED' AND ATTRIBUTE5 = 'BUDGET CLOSED' AND NOT EXISTS( SELECT /*+PARALLEL(S ) MERGE_AJ */ DISTINCT SEGMENT1, ORG_ID
FROM PO.PO_REQUISITION_HEADERS_ALL T, APPS.IP_PR_DETIL S WHERE AUTHORIZATION_STATUS = 'APPROVED' AND ATTRIBUTE5 = 'BUDGET CLOSED' AND SEGMENT1 = PR_NUMBER AND SEGMENT1=SS.SEGMENT1 AND ORG_ID=SS.ORG_ID)) Apabila query yang terdapat pada tabel 6 dieksekusi, maka hasilnya akan terlihat seperti pada gambar 19 untuk Explain Plan dan gambar 20 untuk Query Viewer dan menghasilkan output seperti pada gambar 21.
Gambar 19 Explain Plan Pada Case View IP_RECALC_PR_V Setelah Tuning
Gambar 20 Query Viewer Pada Case View IP_RECALC_PR_V Setelah Tuning
Gambar 21 Data Grid Pada Case View IP_RECALC_PR_V Setelah Tuning Berdasarkan tampilan pada gambar 19, pada baris select statement, besaran nilai cost yang diperlukan untuk mengeksekusi query case View IP_RECALC_PR_V setelah tuning sebesar 206.047.094 (CPU cost) dan 1.019 (IO cost) dimana hasilnya lebih kecil dibandingkan besaran
nilai cost sebelum tuning, 432.750.554 (CPU cost) dan 3.976 (IO cost) (lihat gambar 16). Berdasarkan tampilan pada gambar 20, pada kolom execution time, diperlukan waktu rata-rata sebesar 774,67 msec untuk menampilkan hasil output secara keseluruhan dimana hasilnya lebih cepat dibandingkan kondisi sebelum tuning, 798 msec (lihat gambar 17). Hasil dari query case View IP_RECALC_PR_V setelah tuning dapat dilihat pada gambar 21 dimana hasilnya sama dengan query case View IP_RECALC_PR_V sebelum tuning (lihat gambar 18).
SIMPULAN DAN SARAN Simpulan Berdasarkan penelitian yang telah dilakukan pada aplikasi ProFin, maka simpulan yang dapat ditarik adalah : 1. Melalui SQL tuning, kinerja pengaksesan data pada aplikasi ProFin menjadi lebih optimal dari segi cost dan time. 2. Optimalisasi kinerja pengaksesan data dari segi cost berupa adanya penurunan nilai CPU cost dan IO cost. Penurunan nilai CPU cost dan IO cost merupakan indikator bahwa sumber daya yang diperlukan untuk mengeksekusi query menjadi berkurang. 3. Optimalisasi kinerja pengaksesan data dari segi time berupa adanya penurunan nilai execution time. Penurunan nilai execution time merupakan indikator bahwa waktu yang diperlukan untuk mengeksekusi query menjadi lebih cepat.
Saran Adapun saran-saran yang dianjurkan adalah : 1. Melakukan SQL tuning pada aplikasi lainnya yang digunakan oleh PT. VWX. 2. Melakukan tuning lainnya seperti memory tuning, disk I/O tuning, dan contention tuning agar lebih optimal.
REFERENSI Alapati, S. R. (2009). Expert Oracle Database 11g Administration. New York: Apress. Karthik, P., Reddy, G. T., & Vanan, E. K. (2012). Tuning the SQL Query in order to Reduce Time. IJCSI International Journal of Computer Science, Vol. 9 , 418. Sukheja, D., & Singh, U. K. (2011). A Novel Approach of Query Optimization for Distributed Database Systems. IJCSI International Journal of Computer Science Issues, Vol. 8 , 307.
RIWAYAT PENULIS Alvian Osalindo Lahir di Jakarta pada tanggal 21 agustus 1990. Penulis menamatkan pendidikan s1 di Universitas Bina Nusantara dalam bidang ilmu Sistem Informasi pada tahun 2013. Fransiskus Martin Lahir di kotaTanggerang pada tanggal 4 September 1991. Penulis menamatkan pendidikan S1 di Universitas Bina Nusantara dalam bidang ilmu Sistem Informasi pada tahun 2013.
