OKLabのタイトルロゴ

<<戻る

Oracle パフォーマンス・チューニングの基礎知識

データベースは、正規化したテーブルを作成し、チューニングをすることで非常に性能の差が出てきます。 チューニングをするための知識は非常に多いですが、ここではチューニングする時に必要なコマンドなどを 説明したいと思います。

診断ユーティリティ

チューニングするためには、いくつかユーティリティが用意されています。

exlpain planのためのテーブルを対象となるスキーマで作成

実行計画を取るためには、あらかじめその値を保持しておくためのテーブルが必要です。 Oracleをインストールしたディレクトリ内にutlxplan.sqlがあるのでこれを実行することで テーブルを作成します。


SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> desc plan_table;
Name Null? Type
----------------------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)

実行計画を表示する

実行計画を表示するためのsqlもutlxpls.sqlとして用意されているのでこれを実行すればよいだけです。

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation       | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT |      | | | |
| 1 | TABLE ACCESS FULL| DUAL | | | |
--------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

テスト用のテーブルを作る

SQL> create table hoge_table (hoge_id number, hoge_name varchar2(8));

Table created.

SQL> alter table hoge_table add constraint pk_hoge primary key(hoge_id);

Table altered.

SQL> insert into hoge_table values (1,'aaa');

1 row created.

SQL> insert into hoge_table values (2,'bbb');

1 row created.

SQL> commit;

実行計画を取ってみる

sql文にexplain plan forをつける事で、実行計画を取ることができます。実際に標準出力に表示されるのではなく、先ほど作った 実行計画用のテーブルに格納されます。ここでは、索引を使った検索をテストしてみます。(INDEX UNIQUE SCANという)

SQL> explain plan for select * from hoge_table where hoge_id = '1';

Explained.

実行計画を表示してみる。

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0  | SELECT STATEMENT | | | | |
| 1  | TABLE ACCESS BY INDEX ROWID| HOGE_TABLE | | | |
|* 2 | INDEX UNIQUE SCAN | SYS_C009089 | | | |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("HOGE_TABLE"."HOGE_ID"=1)

Note: rule based optimization

15 rows selected.

全件検索(TABLE ACCESS FULL)の実行計画を取ってみる

SQL> explain plan for select * from hoge_table;

Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | HOGE_TABLE | | | |
--------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

autotraceによる実行計画と統計情報の取得

sql*plusのautotraceコマンドでも実行計画や統計情報を取得することが出来ます。 autotraceはPLUSTRACEロールを作成し、SYSDBAユーザで実行することが可能です。

SQL> connect okita/okita as sysdba
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
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.

SQL>
SQL> set echo off
SQL> grant plustrace to okita;

Grant succeeded.

SQL> set autotrace on
SQL> select * from dual;

D
-
X


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off;
SQL> select * from dual;

D
-
X

オプティマイザ

オプティマイザには、Rule Base Optimizer(RBO)とCost Base Optimizer(CBO)があります。ヒント文をつかって文レベルのオプティマイザ設定も可能です。 また以下のようなコマンドで設定する方法もあります。

alter session set optimizer_mode={ CHOOSE | RULE | FIRST_ROWS | FIRST_ROWS_n | ALL_ROWS }

統計情報

統計情報の取得


analyze table TABLE_NAME compute statistics
analyze table TABLE_NAME estimate statistics
analyze index TABLE_NAME compute statistics
analyze index TABLE_NAME estimate statistics

統計情報の削除

analyze table TABLE_NAME delete statistices

統計情報の表示

DBMS_STATSパッケージを使ってCBOの統計情報を表示できます。

exec dbms_stats.gether_schema_stats ( ownname => 'aaa' estimate_percent => DBMS_STSTS.AUTO_SAMPLE_SIZE);

統計情報の確認

DBA_TABLES,DBA_TAB_COL_STATISTICS,DBA_INDEXESのテーブルで統計情報を確認することが出来ます。