TUNING

SQL Tuning

Bagian terpenting pada tuning performansi database system adalah Tuning SQL statements. Adapun pada tuning SQL terdapat tiga langkah dasar :

1. Identifikasi SQL statements yang memegang andil besar pada sharing workload aplikasi dan system resources, dengan me-review history SQL execution yang telah lampau yang tersedia pada system.
2. Verifikasi bahwa execution plans yang diproduksi oleh query optimizer untuk statement-statement ini berjalan dengan baik.
3. Mengimplementasi corrective actions untuk men-generate execution plans pada peformansi SQL statement yang buruk sehingga menjadi lebih baik.

Ketiga langkah di atas diulang sampai performansi system mencapai tingkat kepuasan atau tidak ada lagi statement yang dapat di-tuning

Tujuan

Tujuan dari tuning sistem adalah untuk mengurangi waktu respon sistem terhadap end user, atau untuk mengurangi resource yang digunakan untuk memproses pekerjaan yang sama. Kita dapat memenuhi kedua tujuan ini dalam beberapa cara:

• Mengurangi Beban Kerja (Reduce the Workload)
• Menyeimbangkan Beban Kerja (Balance the Workload)
• Memparalelkan Beban Kerja (Parallelize the Workload)

a.   Mengurangi Beban Kerja (Reduce the Workload)

SQL Tuning secara umum melibatkan pencarian cara yang lebih efisien untuk memproses beban kerja yang sama. SQL tuning memungkinkan untuk mengubah rencana eksekusi dari suatu statement tanpa mengubah fungsionalitas untuk mengurangi pemakaian resource.Dua contoh bagaimana penggunaan resource dapat dikurangi:

1. Jika suatu query yang biasa dieksekusi perlu untuk mengakses suatu persentase kecil dari data dalam tabel, maka query tersebut dapat dieksekusi dengan lebih efisien dengan menggunakan indeks. Dengan membuat indeks, kita dapat mengurangi jumlah pemakaian resource.
2. Jika seorang user mencari dua puluh baris pertama dari 10000 baris yang diberikan oleh perintah pengurutan yang spesifik, dan jika query (dan perintah pengurutan) dapat dipenuhi oleh indeks, maka user tidak perlu untuk mengakses dan mengurutkan 10000 baris untuk melihat dua puluh baris.

b.  Menyeimbangkan Beban Kerja (Balance the Workload)

Sistem cenderung memiliki penggunaan penuh pada siang hari ketika user yang sebenarnya terhubung ke sistem dan penggunaan yang sedikit di malam hari. Jika noncritical report dan batch job dapat dijadwalkan untuk berjalan pada malam hari dan penggunaan sistem pada siang hari dikurangi, maka hal tersebut dapat membebaskan resource agar dapat digunakan pada program-program yang lebih penting pada siang hari.

c.   Memparalelkan Beban Kerja (Parallelize the Workload)

Query-query yang mengakses sejumlah besar data (typical data warehouse queries) sering dapat diparalelkan. Hal ini sangat berguna untuk mengurangi waktu respon dalam data warehouse yang rendah konkurensinya. Akan tetapi, untuk lingkungan OLTP, yang cenderung tingggi konkurensinya, hal ini dapat merugikan user-user lain dengan meningkatkan penggunaan resource secara keseluruhan pada program.

EXPLAIN PLAN

Explain plan adalah suatu perintah yang digunakan untuk menampilkan perintah eksekusi suatu query.

Query yang dioperasikan yang didukukng oleh explain plan adalah :

• SELECT
• INSERT
• UPDATE
• DELETE

Dengan memanfaatkan explain plan kita bisa melihat langkah eksekusi yang diambil oleh oracle, sehingga kita bisa meningkatkan performansi query kita.

Optimizer mengambil informasi tentang obyek dan tipe dari query yang dijalankan, kemudian memberikan keputusan bagaimana suatu query akan dieksekusi.

ANALYZE

Selalu jalankan command ANALYZE pada table anda untuk memberikan kepastian kalau optimizer akan memperoleh informasi tentang data tersebut. Anda dapat melakukan ANALYZE sebuah table, index, dan bahkan cluster. Ketika anda melakukan analyze suatu table maka oracle akan mengambil informasi berupa data dari obyek tersebut dan informasi untuk memferifikasi blok. Ini adalah perintah yang sangat berguna bukan Cuma untuk memproteksi data, tetapi juga untuk meningkatkan performansi.

SINTAKS ANALYZE

ANALYZE (INDEX | TABLE | CLUSTER)         [USER.] (INDEX [PARTITION (PARTITION NAME) ]                 | TABLE [PARTITION (PARTITION NAME)]                 | CLUSTER ) (  COMPUTE STATISTIC [ FOR CLAUSE] | ESTIMATE STATISTIC [FOR CLAUSE]                              [SAMPLE INTEGER (ROWS | PERCENT)] | DELETE STATISTIC | VALIDATE REF UPDATE | VALIDATE STRUCTURE [CASCADE] | LIST CHAINED ROWS [INTO [USER.] TABLE] )

CONTOH ANALYZE

Berikut contoh aplikasi analyze :

• Pertama buat table emp

SQL> create table emp(

   a number,

   b varchar(10));

Table created.

• Kemudian masukkan sebuah data

SQL> insert into emp values(1,'hallo');

1 row created.

• Lakukan ANALYZE, eksekusi analyze dibawah adalah untuk melakukan validasi terhadap structure table emp.

SQL> analyze table emp validate structure cascade

PLAN TABLE

Sebelum anda bisa menjalankan explain plan anda harus membuat table yang menampung output. Table harus dibuat untuk schema anda sendiri. Table tersebut adalah PLAN_TABLE. Sintaks pembuatan plan_table sudah tersedia di system anda.

• Buka file UTLXPLAN.SQL di %Oraclehome%/rdbms/admin/utlxplan.sql

Berikut sintaksnya :

rem rem $Header: utlxplan.sql 08-may-2004.12:53:19 bdagevil Exp $ xplainpl.sql rem Rem Copyright (c) 1988, 2004, Oracle. All rights reserved.  Rem NAME REM    UTLXPLAN.SQL Rem  FUNCTION Rem  NOTES Rem  MODIFIED Rem     bdagevil   05/08/04  - add other_xml column Rem     bdagevil   06/18/03  - rename hint alias to object_alias Rem     ddas       06/03/03  - increase size of hint alias column Rem     bdagevil   02/13/03  - add plan_id and depth column Rem     ddas       01/17/03  - add query_block and hint_alias columns Rem     ddas       11/04/02  - revert timestamp column to DATE (PL/SQL problem) Rem     ddas       10/28/02  - change type of timestamp column to TIMESTAMP Rem     ddas       10/03/02  - add estimated_time column Rem     mzait      04/16/02  - add row vector to the plan table Rem     mzait      10/26/01  - add keys and filter predicates to the plan table Rem     ddas       05/05/00  - increase length of options column Rem     ddas       04/17/00  - add CPU, I/O cost, temp_space columns Rem     mzait      02/19/98 -  add distribution method column Rem     ddas       05/17/96 -  change search_columns to number Rem     achaudhr   07/23/95 -  PTI: Add columns partition_{start, stop, id} Rem     glumpkin   08/25/94 -  new optimizer fields Rem     jcohen     11/05/93 -  merge changes from branch 1.1.710.1 - 9/24 Rem     jcohen     09/24/93 -  #163783 add optimizer column Rem     glumpkin   10/25/92 -  Renamed from XPLAINPL.SQL Rem     jcohen     05/22/92 -  #79645 - set node width to 128 (M_XDBI in gendef) Rem     rlim       04/29/91 -  change char to varchar2 Rem     Peeler     10/19/88 - Creation Rem Rem This is the format for the table that is used by the EXPLAIN PLAN Rem statement.  The explain statement requires the presence of this Rem table in order to store the descriptions of the row sources. create table PLAN_TABLE (         statement_id       varchar2(30),         plan_id            number,         timestamp          date,         remarks            varchar2(4000),         operation          varchar2(30),         options            varchar2(255),         object_node        varchar2(128),         object_owner       varchar2(30),         object_name        varchar2(30),         object_alias       varchar2(65),         object_instance    numeric,         object_type        varchar2(30),         optimizer          varchar2(255),         search_columns     number,         id                 numeric,         parent_id          numeric,         depth              numeric,         position           numeric,         cost               numeric,         cardinality        numeric,         bytes              numeric,         other_tag          varchar2(255),         partition_start    varchar2(255),         partition_stop     varchar2(255),         partition_id       numeric,         other              long,         distribution       varchar2(30),         cpu_cost           numeric,         io_cost            numeric,         temp_space         numeric,         access_predicates  varchar2(4000),         filter_predicates  varchar2(4000),         projection         varchar2(4000),         time               numeric,         qblock_name        varchar2(30),         other_xml          clob );

• Jalankan UTLXPLAN.SQL anda pada sqlplus

OPERASI

Sekali plan_table telah terbentuk anda bisa melakukan explain plan terhadap query yang kita lakukan.

Lihat langkah berikut :

• Buat plan.sql

SQL> set serveroutput on;

SQL> @plan;

SP2-0310: unable to open file "plan.sql"

SQL> column query_plan format a80

SQL> select

  lpad(' ',2*level)||

  operation||

  ':'||

  options||

  ':'||

  object_name query_plan

  from plan_table

  connect by prior id=parent_id

 start with id=0;

QUERY_PLAN

----------------------------------------------

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

SQL> save plan

Created file plan

• Jalankan query berikut :

SQL> explain plan for

         select * from emp;

Explained.

SQL> @plan;

QUERY_PLAN

----------------------------------------------

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

6 rows selected.

• Perintah plan.sql menghasilkan proses yang dilakukan oleh oracle untuk operasi query kita.

Operasi table acces full emp mendeskripsikan kalo operasi yang dilakukan adalah proses membaca record secara keseluruhan.

Untuk opsi lain lihat explain plan berikut :

SQL> select * from emp;

        A B

--------- ----------

        1 hallo

SQL> insert into emp values(2,'nama saya');

1 row created.

SQL> explain plan for

  2  select * from emp where a=2;

Explained.

SQL> @plan;

QUERY_PLAN

---------------------------------------------------------

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

    TABLE ACCESS:FULL:EMP

12 rows selected.

SQL> Alter table emp

add constraint pk_emp primary key(a);

Table altered.

SQL> commit;

Commit complete.

SQL> select * from emp where a=2;

        A B

--------- ----------

        2 nama saya

SQL> explain plan for

  select * from emp where a=2;

Explained.

SQL> @plan;

QUERY_PLAN

-------------------------------------------

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:BY INDEX ROWID:EMP

      INDEX:UNIQUE SCAN:PK_EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:BY INDEX ROWID:EMP

      INDEX:UNIQUE SCAN:PK_EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

QUERY_PLAN

-------------------------------------------

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:BY INDEX ROWID:EMP

      INDEX:UNIQUE SCAN:PK_EMP

  SELECT STATEMENT::

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:FULL:EMP

      INDEX:UNIQUE SCAN:PK_EMP

    TABLE ACCESS:BY INDEX ROWID:EMP

      INDEX:UNIQUE SCAN:PK_EMP

36 rows selected.

Dari perintah diatas terlihat perbedaan antara penggunaan primary key dengan tidak. Anda jangan terkecoh dengan panjangnya hasil dari explain plan hasil selection setelah pemberian primary key. Tapi pemberian primary key tersebut merupakan sebuah peningkatan performansi pada hasil query kita (terutama jika data besar).

Reviewing the Execution Plan

Ketika kita melakukan tuning (atau writing) sebuah SQL statement pada lingkungan OLTP, tujuannya adalah untuk beralih dari tabel yang memiliki filter yang paling selektif. Ini berarti bahwa ada beberapa baris yang bergabung. Periksa untuk memastikan bahwa jalur pengaksesan telah optimal.

Ketika memeriksa rencana eksekusi optimizer, lihatlah yang berikut ini:

·         Rencana seperti tabel driving memiliki filter terbaik

·         Perintah join pada tiap langkah berarti bahwa angka terkecil pada baris dikembalikan pada langkah selanjutnya (yaitu, perintah join harus merefleksikan, ketika memungkinkan, mengarah filter terbaik yang belum terpakai).

·         Metode join sesuai dengan jumlah baris yang dikembalikan. Sebagai contoh, loop bersarang bergabung melalui indeks mungkin tidak optimal ketika banyak baris dikembalikan.

·         View digunakan secara efisien. Lihat pada urutan SELECT untuk melihat apakah pengaksesan ke view penting.

·         Ada beberapa produk Cartesian yang tidak penting (bahkan dengan tabel kecil).

·         Setiap tabel diakses secara efisien:

Pertimbangkan predikat-predikat pada SQL statement dan jumlah baris pada tabel. Lihat pada aktivitas yang mencurigakan, seperti scan tabel secara penuh dengan sejumlah besar baris, yang memiliki predikat pada klausa where. Tentukan mengapa sebuah indeks tidak digunakan untuk predikat selektif.

Sebuah scan tabel secara penuh tidak berarti tidak efisien. Hal tersebut mungkin efisien untuk melakukan scan tabel penuh pada tabel kecil, atau untuk melakukan scan tabel penuh untuk membuat metode join yang lebih baik (misalnya, hash join) untuk sejumlah baris yang dikembalikan.

Jika ada dari kondisi-kondisi ini yang tidak optimal, maka pertimbangkan untuk merestrukturisasi SQL statement atau indeks-indeks yang tersedia pada tabel.

Rekstruturisasi SQL Statement

Seringkali, menulis ulang SQL statement yang tidak efisien lebih mudah daripada dimodifikasi. Jika Anda mengerti tujuan dari statement yang diberikan, mungkin Anda akan dapat dengan cepat dan mudah menulis sebuah statement baru yang memenuhi kebutuhan.

Membuat Predikat Menggunakan AND dan =

Untuk meningkatkan efisiensi SQL, gunakan equijoin kapanpun memungkinkan. Statement yang menggunakan equijoin pada nilai kolom yang belum ditransformasi adalah yang termudah untuk di-tune.

Hindari Kolom yang Telah Bertransformasi pada klausa WHERE

Gunakan nilai kolom yang belum bertransformasi. Misalnya, gunakan:

WHERE a.order_no = b.order_no

daripada:

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

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

Jangan menggunakan fungsi SQL pada klausa predikat atau klausa WHERE. Ekspresi-ekspresi yang menggunakan kolom, seperti fungsi menyatakan kolom tersebut sebagai argumen, menyebabkan optimizer mengabaikan kemungkinan menggunakan sebuah indeks pada kolom tersebut, bahkan indeks yang unik, hingga terdapat fungsi yang berdasarkan indeks yang dapat terdefinisi yang dapat digunakan.

Hindari ekspresi-ekspresi mixed-mode, dan hati-hati terhadap tipe konversi yang implisit. Ketika Anda ingin menggunakan sebuah indeks pada VARCHAR2 kolom charcol, tapi klausa WHERE seperti berikut:

AND charcol = numexpr

di mana numexpr adalah sebuah ekspresi dari tipe angka (misalnya, 1, USERENV('SESSIONID'), numcol, numcol+0,...), Oracle menerjemahkan ekspresi tersebut ke:

AND TO_NUMBER(charcol) = numexpr

Hindari jenis ekspresi yang kompleks seperti berikut:

·         col1 = NVL (:b1,col1)

·         NVL (col1,-999) = ….

·         TO_DATE(), TO_NUMBER(), and so on

Ekspresi ini mencegah optimizer dari menandai kardinalitas valid atau estimasi selektif dan dapat mempengaruhi rencana dan metode join secara keseluruhan.

Tambahkan predikat yang berlawanan menggunakan teknik NVL().

Misalnya:

SELECT employee_num, full_name Name, employee_id 

  FROM mtl_employees_current_view 

  WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1) 

  ORDER BY employee_num;

Juga:

SELECT employee_num, full_name Name, employee_id 

  FROM mtl_employees_current_view 

  WHERE (employee_num = :b1) AND (organization_id=:1) 

  ORDER BY employee_num;

Ketika Anda perlu menggunakan fungsi SQL pada filter atau predikat join, jangan menggunakannya pada kolom di mana Anda ingin memiliki sebuah indeks; lebih baik gunakan pada sisi berlawanan predikat, seperti pada statement berikut:

TO_CHAR(numcol) = varcol

daripada:

varcol = TO_CHAR(numcol)