|
|
|
|
Ada kalanya pada saat query di SQL*Plus koq bisa-bisanya hari ini performance-nya mendadak letoy padahal perasaan logikanya udah bener deh. Kalo pake perasaan mah agak susah ngukurnya. Salah satu cara jadul yang sudah merupakan rahasia turun-temurun adalah melakukan benchmark. Cara yang saya lakukan adalah sebagai berikut :
- Connect ke database dengan SQL*Plus :
[topan@isengard ]$ $ORACLE_HOME/bin/sqlplus system/<password>@<connect_string>
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 18 09:34:47 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>
- Jalankan
utlxplan.sql yang terdapat di folder $ORACLE_HOME/rdbms/admin :
SQL> set echo on
SQL> @utlxplan.sql
SQL> rem
SQL> rem $Header: utlxplan.sql 29-oct-2001.20:28:58 mzait Exp $ xplainpl.sql
SQL> rem
SQL> Rem Copyright (c) 1988, 2001, Oracle Corporation. All rights reserved.
SQL> Rem NAME
SQL> REM UTLXPLAN.SQL
SQL> Rem FUNCTION
SQL> Rem NOTES
SQL> Rem MODIFIED
SQL> Rem mzait 10/26/01 - add keys and filter predicates to the plan table
SQL> Rem ddas 05/05/00 - increase length of options column
SQL> Rem ddas 04/17/00 - add CPU, I/O cost, temp_space columns
SQL> Rem mzait 02/19/98 - add distribution method column
SQL> Rem ddas 05/17/96 - change search_columns to number
SQL> Rem achaudhr 07/23/95 - PTI: Add columns partition_{start, stop, id}
SQL> Rem glumpkin 08/25/94 - new optimizer fields
SQL> Rem jcohen 11/05/93 - merge changes from branch 1.1.710.1 - 9/24
SQL> Rem jcohen 09/24/93 - #163783 add optimizer column
SQL> Rem glumpkin 10/25/92 - Renamed from XPLAINPL.SQL
SQL> Rem jcohen 05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)
SQL> Rem rlim 04/29/91 - change char to varchar2
SQL> Rem Peeler 10/19/88 - Creation
SQL> Rem
SQL> Rem This is the format for the table that is used by the EXPLAIN PLAN
SQL> Rem statement. The explain statement requires the presence of this
SQL> Rem table in order to store the descriptions of the row sources.
SQL>
SQL> create table PLAN_TABLE (
2 statement_id varchar2(30),
3 timestamp date,
4 remarks varchar2(80),
5 operation varchar2(30),
6 options varchar2(255),
7 object_node varchar2(128),
8 object_owner varchar2(30),
9 object_name varchar2(30),
10 object_instance numeric,
11 object_type varchar2(30),
12 optimizer varchar2(255),
13 search_columns number,
14 id numeric,
15 parent_id numeric,
16 position numeric,
17 cost numeric,
18 cardinality numeric,
19 bytes numeric,
20 other_tag varchar2(255),
21 partition_start varchar2(255),
22 partition_stop varchar2(255),
23 partition_id numeric,
24 other long,
25 distribution varchar2(30),
26 cpu_cost numeric,
27 io_cost numeric,
28 temp_space numeric,
29 access_predicates varchar2(4000),
30 filter_predicates varchar2(4000));
Table created.
- Agar schema lain dapat mengakses
PLAN_TABLE tanpa harus menggunakan prefix nama schema SYSTEM jalankan perintah berikut :
SQL> create public synonym PLAN_TABLE for PLAN_TABLE;
Synonym created.
- Beri hak akses agar semua schema bisa menggunakannya :
SQL> grant all on PLAN_TABLE to public;
Grant succeeded.
- Sekarang login sebagai
SYSDBA :
SQL> CONNECT sys/<password>@<connect_string> AS sysdba
Connected.
- Jalankan
plustrce.sql yang terdapat di folder $ORACLE_HOME/sqlplus/admin :
SQL> @plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
- Beri hak akses agar semua schema bisa menggunakannya :
SQL> grant all on PLUSTRACE to public;
SQL> grant PLUSTRACE to public;
Grant succeeded.
- Login sebagai
SCOTT dan coba benchmark beberapa query berbeda :
SQL> CONNECT scott/tiger@<connect_string>
Connected.
SQL> set autotrace traceonly
SQL> select * from emp,dept
2 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
238 recursive calls
0 db block gets
60 consistent gets
5 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,emp.DEPTNO from emp,dept
2 where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
2 physical reads
0 redo size
1198 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> set autotrace off
Untuk informasi lebih lanjut bisa mengacu ke dokumentasi Oracle yang terdapat DISINI. Khusus untuk topik ini bisa dibaca DISINI.
|
|
|
|
|
|
