|
|
仕事ではもっぱらOracleと戯れています。備忘録程度に。
November 04, 2004
Oracleのパフォーマンスチューニングに関する情報、例えばWEB、掲示板、書籍などあるが、その多くには、意外なほど、「迷信」が見られる。迷信、すわなち間違いである。
例えば、多く語られるのが、索引の再構築。索引の再構築を勧める側の一般的な言い分には次の様なものがある:
- 削除されたリーフノードは同じ値が挿入されない限り再利用されない
- 索引のレベルが4を超えた場合は再構築が必要
- CLUSTERING_FACTORが非常に大きい(=テーブルの行数に近い)場合は再構築
- シーケンスから採番した値を主キーに挿入する場合、索引の右側が不均衡となる
- 索引のパフォーマンスを維持するため定期的な再構築が必要
これらは全て「迷信」である。
(ソース: Oracle B-Tree Index Internals / AskTom: Rebuilding Indexes)
索引以外にも次のようなものがある:
- DBバッファキャッシュヒットが90%を切る様であればバッファキャッシュが足りない
- 1つのEXTENTから成る表は最高のパフォーマンスとなる
これらも今ではもう迷信である。
(ソース: That was then, this is now)
Oracleチューニングに関する情報を得た時は、それが信頼するに値するかを十二分に注意し判断しなければならない。怪しい場合はなおさらであるが、信用できる場合でも、可能な限り自分の環境でテストを行い、ベンチマーク試験を行う必要があるだろう。
ところで、これらの迷信の一部は、おそらく、循環参照(または循環引用?)というWEB独特の現象により体系化されたものである。循環参照とは次のようなものである。
- AさんがWEB上に(例えば掲示板などに)、根拠無しのアイデア(索引再作成など)をポストする
- それを見たBさんが、Aさんを引用しそれらしいページ(このサイトのような、笑)を作成する(引用先は明記しない)
- それを見たAさんが、自分のアイデアを正当化するためにBさんを引用する
実際の例が、Circular References (JL Comp)にあるので読んでみると面白いかも。
ちなみに、私が信頼するソースは次のようなもの。このサイトも、下記のサイトを参考にしてます。
あなたがこのサイトを信頼するかどうかは、あなたの判断にお任せします。笑。
追記:
またこの宗教チックなところが、私がOracleに惹かれる要素でもあるかも、、、。
さらに追記: 満月の夜、月に向かって「オラー！オラー！」と叫ぶと脳内パフォーマンスが著しく低下します。
October 15, 2004
Stored Outlineを使用するにあたっての注意点やその他もろもろ。
Stored Outlineが使用されない
SQL Hash Valueが合っていてもStored Outlineが使用されないことはよくある。原因はおそらく次の3つに限られる。
1つは、ハード解析が行われないため。Stored Outlineが使用されるためには、SQLのハード解析が必要である(実行計画を作り直す必要があるため)。USE_STORED_OUTLINESパラメータを変更しても、Shared Poolの全てのSQLがInvalidとなるわけではない。この場合は、Shared PoolのFlush、データベース再起動、参照しているテーブルや索引の統計情報再作成、または、ALTER OUTLINEでカテゴリをダミーへ変更し元に戻すなどでうまくいく(場合がある)。
2つ目は、OUTLN.OL$のキャッシュが古いため。特に、他のデータベースでStored Outlineを作成しEXP、それを本番環境等へIMPした場合によくこれが発生する。この場合は、データベース再起動、参照しているテーブルや索引の統計情報再作成、または、ALTER OUTLINEでカテゴリをダミーへ変更し元に戻すなどでうまくいく(場合がある)。
3つ目は、Oracleのバグ。Metalinkを確認すると結構なバグが報告されている。
ところで、前述の活用編で行ったようなOL$HINTSの入れ替え等を行うと、通常ありえないヒントなどが設定され、Oracleが使用を拒否することはある。入れ替えを行う場合は、必ず同じタイプのSQLで行い、テストも十分行う必要あり。
使ってはいるようだが、動作がおかしい
V$SQL等で確認すると、ちゃんと使用しているようだが、動作がおかしい場合がある。その場合は、OUTLN.OL$からSQL文をコピーしSQL*PlusからAUTOTRACE TRACEONLY EXPLAINで実行し、実行計画を確認してみればよい。バグなどにより、意図したとおりに動かない場合がある。例えば、Semi Join (EXISTS句)を含むSQLで、ALWAYS_SEMI_JOIN = HASHでStored Outlineを作成し、ALWAYS_SEMI_JOIN = NESTED_LOOPSへ変更し実行しても、ハッシュ結合は行ってくれない。かといってネストループでもなく、単純に両テーブル全走査してしまう、、、。要は、最悪な実行計画となる。817で確認。バグ？
インポートする際の注意点
他データベースで作成したStored OutlineをEXPし、本番環境等へIMPするときは、可能であればOL$とOL$HINTSをDROPしてから行う。くれぐれもオリジナルをRENAMEしバックアップなどしないように。RENAMEでは索引の名称は変わらないため、IMPしても索引が作成されず、Stored Outlineが使用されなくなる。
DROPしない場合は、IGNORE=YでのIMPとなるが、エラーメッセージには要注意。
また、インポート後はすぐには使用されない場合もあるので、前述の、Stored Outlineが使用されないを参考。
最後に
Stored Outlineは万能薬ではない。あくまでもヒントでしかありえないため、ロジックの変更等はできない。可能な限り根本原因の修正を行いたい。
October 06, 2004
Oracle 8iから導入された、Stored Outlineの基礎をおさらい。
Stored Outlineとは、短く言えば、SQLの実行計画をデータベースに保存する機能、長く言えば、SQLのハード解析時にOracleが参考とするヒント文セットを作成しそれを保存する機能、となる。保存したアウトライン(Stored Outline)は、データベースパラメータを変更することにより使用可否を決定できる。
Stored Outlineは、一度作成保存されれば、それを使用可能としている限り、通常であれば実行計画が変わってしまうような場合でも、保存された実行計画を使い続ける。ここで言う実行計画が変わってしまうような場合とは、統計情報の再作成、索引追加、オプティマイザの変更、データベースパラメータ(SORT_AREA_SIZEなど)の変更などがある。
この機能を使えば、例えば、オンライン系などのパフォーマンスが最重要視されるようなアプリケーションが発行するSQLに対し一度作成を行っておけば、データ量の変化などがあっても、当初の実行計画のままSQLを実行するので、突然のパフォーマンス悪化を防ぐことができる。ある日突然実行計画が全表走査へ変更してしまうことは、ままある。
Stored Outlineの作成
Stored Outlineの作成には2つ方法がある。1つは、CREATE OUTLINE ON 〜 を使用する方法で、Stored Outlineを作成したいSQLを指定し作成する。まずはじめに、Stored Outlineを作成したいSQLを準備する。このSQLは、後々実際に発行するSQLと一言一句異なってはならない。スペースの数や、改行なども全く同じでなければならない。そして、CREATE OUTLINE文にて作成を行う。
CREATE OUTLINE outline名 ON SQL文;
作成されたStored Outlineは、OUTLN.OL$に保存される。outline名を省略した場合は、Oracleが勝手に名前を付ける。USER_OUTLINES、ALL_OUTLINES、DBA_OUTLINES等で確認できる。
もう1つの作成方法は、CREATE_STORED_OUTLINESパラメータを変更する方法で、変更後そのセッションで実行されたSQLについて、自動でStored Outlineの作成を行う。(システムパラメータを変更した場合は、システムで実行される全てのSQLのStored Outlineを作成する。) この方法は、PL/SQL等に埋め込まれたSQLのStored Outline等を作成する際有利である。
ALTER [ SESSION / SYSTEM ] SET CREATE_STORED_OUTLINES = TRUE;
自動作成を停止する場合は、
ALTER [ SESSION / SYSTEM ] SET CREATE_STORED_OUTLINES = FALSE;
を実行する。
Stored Outlineの使用
作成したStored Outlineを実際に使用するには、USE_STORED_OUTLINESパラメータの変更が必要となる。使用可否は、セッション単位もしくはシステム全体で変更が可能である。
ALTER [ SESSION / SYSTEM ] SET USE_STORED_OUTLINES = TRUE;
使用を停止する場合は、
ALTER [ SESSION / SYSTEM ] SET USE_STORED_OUTLINES = FALSE;
を実行。
作成したStored Outlineが実際に使用されたかを確認するには、USER_OUTLINES (またはALL_OUTLINESかDBA_OUTLINES) のUSED項目を確認。一度でも使用されたならば、USEDとなっている。一度も使用されていないのであれば、UNUSEDとなる。また、そのSQLが実行中、または実行後間もない場合(Shared Poolに該当SQLがある場合)は、V$SQLをHASH_VALUEで検索し、OUTLINE_CATEGORY項目に、作成したStored Outlineのカテゴリ(通常はDEFAULT)が入っていれば、使用していることになる。SQLのハッシュ値(HASH_VALUE)は、Stored Outline名からOUTLN.OL$を検索すれば取得可能(HASH_VALUE項目を参照)。
ところで、Stored Outlineの使用を開始すると、その後全てのハード解析時に、OracleがStored Outlineの存在を確認するため、多少Recursive Callが増える。トレースを取ってみれば分かるが、SQLハッシュ値計算後、OUTLN.OL$を検索している。OUTLN.OL$はほとんどの場合においてキャッシュされているため、パフォーマンスに与える影響は微量なものだが、ハード解析が多く行われる環境では注意が必要である(ただ、その場合の問題はStored Outlineの使用ではなく、ハード解析の多さである場合が一般的)。必要であればOUTLN.OL$とOUTLN.OL$HINTS (9iからはOUTLN.OL$NODESも)をKEEP POOLに入れておいても良いだろう。
Stored Outlineのカテゴリ
Stored Outlineにはカテゴリ属性がある。ここでのカテゴリは、いわばグループのようなもの。通常何も指定しない場合は、カテゴリはDEFAULTとなる。例えば、特定のカテゴリでStored Outlineを作成し、特定のユーザーやセッションだけで使用したり等が可能である。
カテゴリを指定してStored Outlineを作成する場合は、
CREATE OUTLINE outline名 FOR CATEGORY カテゴリ名 ON 〜
や、
ALTER [ SESSION / SYSTEM ] SET CREATE_STORED_OUTLINES = カテゴリ名;
などとする。これらで作成したDEFAULT以外のStored Outlineは、
ALTER [ SESSION / SYSTEM ] SET USE_STORED_OUTLINES = カテゴリ名;
で使用が可能となる。使用した場合は、V$SQLのOUTLINE_CATEGORYにはこのカテゴリ名が入る。
あるStored Outlineのカテゴリを変更したい場合は、
ALTER OUTLINE outline名 CHANGE CATEGORY TO 新カテゴリ名;
とする。DEFAULTへ戻す場合にエラーが出る場合は、"DEFAULT"とダブルクォートしましょう。
参考: Oracle8i Designing and Tuning for Performance - Using Plan Stability
September 14, 2004
前準備も終わったのでインストールを行う。ソフトウェアはOracle Installerで適当にインストール。データベースは、Oracle Database Configuration Assistantを使わず、手作業で構築してみる。
初期パラメータファイル作成
まずは、初期パラメータファイルを作成する。$ORACLE_HOME/dbs/init.oraがテンプレートなので、それを、$ORACLE_BASE/admin/SID/pfile/initSID.oraにコピーし、編集する。また、そのファイルのシンボリックリンクを$ORACLE_HOME/dbs/ に作成しておく。
私の初期パラメータはこんな感じ。SIDはLUPUS。configLUPUS.oraという静的パラメータファイルを別に作り、initLUPUS.oraからインクルードしている。初期パラメータについては、テストのためにめちゃめちゃな値の場合もあるので要注意！
# initLUPUS.ora
# -------------
# Database Buffer and Disk I/O
# ----------------------------
db_block_buffers = 32768 # 32K * 8K = 256M
buffer_pool_keep = (buffers:500, lru_latches:1)
buffer_pool_recycle = (buffers:1000, lru_latches:2)
db_file_multiblock_read_count = 16
db_writer_processes = 1
dbwr_io_slaves = 10
disk_asynch_io = TRUE
db_files = 200
# Shared Pool
# -----------
shared_pool_size = 134217728
shared_pool_reserved_size = 10485760
java_pool_size = 20971520
large_pool_size = 10485780
#lock_sga = TRUE
#mlock_sga = TRUE
dml_locks = 300
# Hash Joins
# ----------
hash_area_size = 10485760
hash_multiblock_io_count = 16
hash_join_enabled = TRUE
# Redo
# ----
log_archive_start = FALSE
# Latch
# -----
db_block_lru_latches = 8
# Chackpoint
# ----------
log_checkpoint_interval = 0
log_checkpoint_timeout = 1800
log_checkpoints_to_alert = TRUE
# Cursor and Library Cache
# ------------------------
cursor_space_for_time = FALSE
session_cached_cursors = 64
open_cursors = 256
# Optimizer
# ---------
optimizer_mode = CHOOSE
optimizer_max_permutations = 79000
optimizer_index_cost_adj = 1
optimizer_index_caching = 99
partition_view_enabled = TRUE
always_anti_join = HASH
# Parallel Query
# --------------
parallel_max_servers = 16
parallel_min_servers = 4
parallel_min_percent = 50
parallel_automatic_tuning = FALSE
# Sort
# ----
sort_area_size = 1048576
sort_area_retained_size = 1048576
sort_multiblock_read_count = 2
# include configuration file
ifile = /oracle/u01/app/oracle/admin/LUPUS/pfile/configLUPUS.ora
# configLUPUS.ora
# ----------------
# This file is to be included in init.ora.
# Parameters in this file are static. Do not change.
db_block_size = 8192
processes = 100
timed_statistics = TRUE
# Make trace files public
_trace_files_public = TRUE
global_names = TRUE
compatible = 8.1.7
control_files = (/oracle/u03/oradata/LUPUS/cntrl1.ctl,\
/oracle/u04/oradata/LUPUS/cntrl2.ctl)
user_dump_dest = /oracle/u01/app/oracle/admin/LUPUS/udump
core_dump_dest = /oracle/u01/app/oracle/admin/LUPUS/cdump
background_dump_dest = /oracle/u01/app/oracle/admin/LUPUS/bdump
db_name = LUPUS
db_domain = ROBIOS.ORG
instance_name = LUPUS
service_names = LUPUS.ROBIOS.ORG
とりあえずの作成ならここまで作りこむ必要はない。静的パラメータだけでよいはず。
データベース作成
インスタンスを立ち上げ、データベースを作成する。
$ ORACLE_SID=LUPUS svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile = "/oracle/u01/app/oracle/admin/
LUPUS/pfile/initLUPUS.ora" <--表示上改行してます
ORACLE instance started.
Total System Global Area 445083324 bytes
Fixed Size 102076 bytes
Variable Size 176005120 bytes
Database Buffers 268435456 bytes
Redo Buffers 540672 bytes
SVRMGR> create database LUPUS
2> maxdatafiles 128
3> maxinstances 1
4> maxlogfiles 8
5> character set JA16EUC
6> national character set JA16EUC
7> datafile '/oracle/u02/oradata/LUPUS/system01.dbf' size 260M
8> autoextend on next 10240K
9> logfile '/oracle/u03/oradata/LUPUS/redo01.log' size 500K,
10> '/oracle/u03/oradata/LUPUS/redo02.log' size 500K;
Statement processed.
SVRMGR> disconnect
SVRMGR> quit
パラメータについて
- maxdatafiles
- 最大データファイル数。コントロールファイルに初期設定される。init.oraのDB_FILESで設定した数まで動的に拡張される。
- maxinstances
- このデータベースをマウントできるインスタンスの最大数。クラスタ作るわけじゃないので1でいいかなと。
- maxlogfiles
- REDOログファイルグループの最大数。今回は2グループ(とはいうもののメンバーは1つ)だが、とりあえず8に設定。
- character sets
- 通常の項目のキャラクタセット。EUCへ設定。
- national character sets
- NCHARなどのNational Character項目のキャラクタセット。同じくEUCへ設定。
- datafile
- システム表領域領域のデータファイルを定義。ディクショナリ類で結構大きくなるので最初から大きめに確保。
- logfile
- REDOログファイルの定義。テスト用データベースなので、グループ設定はしない。本番用であれば、グループ設定を行い、複数のメディアにミラーを作ることで用心。
データディクショナリ作成
$ORACLE_HOME/rdbms/admin/catalog.sqlを実行して、データディクショナリ、動的ビューなどを作成する。
$ ORACLE_SID=LUPUS svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
SVRMGR> connect internal
Connected.
SVRMGR> @/oracle/u01/app/oracle/product/8.1.7/rdbms/admin/catalog.sql
Statement processed.
Statement processed.
- 途中省略 -
Statement processed.
Statement processed.
SVRMGR>
途中、ORA-01342等が幾度も出るが気にしないでよい。全く新規のデータベースなのだから。
システム表領域設定変更
SYSTEM表領域にSTORAGE句を適用する。
上記の続きより
SVRMGR> alter tablespace SYSTEM
2> default storage ( initial 64k next 64k minextents 1
3> maxextents unlimited pctincrease 0);
Statement processed.
SVRMGR> alter tablespace SYSTEM minimum extent 64k;
Statement processed.
ロールバックセグメントの作成
まず表領域を作成する。ここでは表領域をそれぞれ違うマウントポイントに3つ作成し、それぞれに1つ計3つの、ロールバックセグメントを作成してみる。初期サイズは25Mに設定。(3つでは通常は少ないかもしれない。)
上記の続きより
SVRMGR> create tablespace RBS01
2> datafile '/oracle/u02/oradata/LUPUS/rbs01.dbf'
3> size 26279936 reuse
4> autoextend on
5> maxsize 100m
6> extent management local uniform size 1m;
Statement processed.
SVRMGR> create tablespace RBS02
2> datafile '/oracle/u03/oradata/LUPUS/rbs02.dbf'
3> size 26279936 reuse
4> autoextend on
5> maxsize 100m
6> extent management local uniform size 1m;
Statement processed.
SVRMGR> create tablespace RBS03
2> datafile '/oracle/u04/oradata/LUPUS/rbs03.dbf'
3> size 26279936 reuse
4> autoextend on
5> maxsize 100m
6> extent management local uniform size 1m;
Statement processed.
SVRMGR>
表領域はパフォーマンスを考えUNIFORM拡張LMT (Locally Management Tablespaces) にする(最後の行)。LMTの場合、ファイルサイズに64Kのオーバーヘッドを加えておくと余分にExtendしない。25Mアロケートしたいので、サイズは、1024 * 1024 * 25 + 1024 * 64 = 26279936 とする。
ロールバックセグメントを作成し、ONLINEとする。ORA-01552が出る可能性があるので、先にSYSTEM表領域に一時RBSを作成し、ONLINEとしておく。一時RBSは後ほどDROPする。
上記の続きより
SVRMGR> create public rollback segment rbstemp
2> tablespace system;
Statement processed.
SVRMGR> alter rollback segment rbstemp online;
Statement processed.
SVRMGR> create public rollback segment rbs1
2> storage ( initial 1024k next 1024k minextents 25 )
3> tablespace rbs01;
Statement processed.
SVRMGR> create public rollback segment rbs2
2> storage ( initial 1024k next 1024k minextents 25 )
3> tablespace rbs02;
Statement processed.
SVRMGR> create public rollback segment rbs3
2> storage ( initial 1024k next 1024k minextents 25 )
3> tablespace rbs03;
Statement processed.
SVRMGR> alter rollback segment rbs1 online;
Statement processed.
SVRMGR> alter rollback segment rbs2 online;
Statement processed.
SVRMGR> alter rollback segment rbs3 online;
Statement processed.
SVRMGR> alter rollback segment rbstemp offline;
Statement processed.
SVRMGR> drop public rollback segment rbstemp;
Statement processed.
一時表領域の作成
TEMP表領域を作成する。エクステント管理についてはRBSと同様、UNIFORM拡張LMTとする。また、初期エクステントサイズはSORT_AREA_SIZEと同じ値にする。なぜなら、SORTの際ページングが発生すると一時表領域を使用するからである。同じ値であればページングが効率よく行われる。
上記の続きより
SVRMGR> create temporary tablespace TEMP
2> tempfile '/oracle/u02/oradata/LUPUS/temp01.dbf'
3> size 64m reuse
4> autoextend on next 64m maxsize unlimited
5> extent management local uniform size 1m;
Statement processed.
SYSとSYSTEMユーザーの一時表領域を、作成したTEMPに設定する。
上記の続きより
SVRMGR> alter user SYS temporary tablespace TEMP;
Statement processed.
SVRMGR> alter user SYSTEM temporary tablespace TEMP;
Statement processed.
各種スクリプト実行
STANDARDプロシージャ等の作成を行うスクリプトを実行する。実行するスクリプトは、$ORACLE_HOME/rdbms/admin内のcatproc.sql、caths.sql、otrcsvr.sqlと、$ORACLE_HOME/sqlplus/admin内のpupbld.sql。pupbld.sqlはSYSTEMユーザーで実行する。
上記の続きより
SVRMGR> @/oracle/u01/app/oracle/product/8.1.7/rdbms/admin/catproc.sql
Statement processed.
...
SVRMGR> @/oracle/u01/app/oracle/product/8.1.7/rdbms/admin/caths.sql
Statement processed.
...
SVRMGR> @/oracle/u01/app/oracle/product/8.1.7/rdbms/admin/otrcsvr.sql
Statement processed.
...
SVRMGR> connect system/manager
SVRMGR> @/oracle/u01/app/oracle/product/8.1.7/sqlplus/admin/pupbld.sql
Statement processed.
...
TNS名とリスナーの設定
外部から接続できるようにTNS名とリスナーの設定を行う。まずは、$ORACLE_HOME/network/admin/tnsnames.oraから。下記を追加する。
LUPUS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = darkstar)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = LUPUS.ROBIOS.ORG)
)
)
tnsping LUPUSで確認を行う。
$ tnsping LUPUS
TNS Ping Utility for Solaris: Version 8.1.7.0.0 - Production on 14-SEP-2004 19:2
6:33
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=darkstar)(PORT=1521))
OK (10 msec)
次はリスナーの設定。$ORACLE_HOME/network/admin/listener.oraに下記を追加する。
LISTENERLUPUS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = darkstar)(PORT = 1521))
)
)
)
リスナーを起動してみる。
$ lsnrctl start LISTENERLUPUS
LSNRCTL for Solaris: Version 8.1.7.0.0 - Production on 14-SEP-2004 20:21:11
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Starting /oracle/u01/app/oracle/product/8.1.7/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 8.1.7.0.0 - Production
System parameter file is /oracle/u01/app/oracle/product/8.1.7/network/admin/list
ener.ora
Log messages written to /oracle/u01/app/oracle/product/8.1.7/network/log/listene
rlupus.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=darkstar)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=darkstar)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENERLUPUS
Version TNSLSNR for Solaris: Version 8.1.7.0.0 - Production
Start Date 14-SEP-2004 20:21:12
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/u01/app/oracle/product/8.1.7/network/admin/lis
tener.ora
Listener Log File /oracle/u01/app/oracle/product/8.1.7/network/log/liste
nerlupus.log
The listener supports no services
The command completed successfully
うまく起動した。
最後の仕上げ
/var/opt/oracle/oratabに今回構築したデータベースをリストする。こうすることで、oraenvコマンドでこのデータベースが選択できるようになる。また、自動起動、自動シャットダウンの設定も行える。とりあえず自動起動はなしで設定してみる。oratabファイルに下記を追加する。
LUPUS:/oracle/u01/app/oracle/product/8.1.7:N
以上で完成である。一般ユーザー用の表領域などは別途要作成。
September 12, 2004
Sun Ultra 80を手に入れたので、手始めにOralce 8iをインストールしてみる。まずはカーネルパラメータの変更から。
カーネルパラメータ変更
変更すべきは2点。共有メモリパラメータとセマフォパラメータ。 /etc/system に下記を追加する。
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=150
set semsys:seminfo_semmns=250
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
共有メモリ設定(shm*)についてはOracle推奨のデフォルト値で特に問題はない。shmmaxは、システム内全てのOracleデータベースで最大のSGAサイズより大きければよい。上記例であればこのシステムにおいて設定可能な最大のSGAは4Gとなる。(SGAをその最大値に設定する必要はない。また、仮にSGAを4Gに設定する場合でも実メモリは4G以上なければならない。ページングが行われるとデータベースパフォーマンスは著しく悪化する。)
セマフォ設定は、SEMMSL (Max Semaphore Sets) とSEMMNS (Max Semaphores) に注意する。Oracleの推奨では、SEMMSLは、システム内全てのOracleデータベースで最大のPROCESSESに10を加えたものをセット、SEMMNSは、システム内全てのOracleデータベースのPROCESSESの合計+システム内最大PROCESSES+10*データベース数をセットする。
例えば、
SID=ORA01, PROCESSES = 50
SID=ORA02, PROCESSES = 100
SID=ORA03, PROCESSES = 10
というシステムがあれば、
SEMMSL = MAX(PROCESSES) + 10 = 100 + 10 = 110
SEMMNS = SUM(PROCESSES) + MAX(PROCESSES) + 10 * COUNT(INSTANCE)
= 50 + 100 + 10 + 100 + 10 * 3 = 290
となる。
マウントポイントの作成
データベースをインストールするマウントポイントを作成する。基本的には、ソフトウェア用とデータ用と2つあればよい。OFA (Optimal Flexible Architecture) に従えば最低でも4つ必要となる。2つの場合は、/u01、/u02、4つの場合はさらに、/u03、/u04などを追加する。私はルート直下に作るのは気が引けるので、/oracle以下に/u01〜/04まで作成した。
マウントポイントというだけに、それぞれのディレクトリにはそれぞれ違うディスク(スライスではなく物理ディスク)をマウントするのがパフォーマンス上好ましい。最低でも、ソフトウェアとデータの格納ディスクは分けるべきである。ディスクが1つしかなくても、とりあえずマウントポイントは複数作っておき、将来ディスクを増設した際に移行すればよい。そういう意味では、OFAの考え方は非常に良い。
* OFAについてはOracle8i Administrator's Referenceを参照
グループとユーザーの設定
Oracleをインストール/実行するグループとユーザーを追加する。
グループはインストールグループと、データベース管理者グループを作成する。標準では、インストールグループはoinstall、管理者グループはdbaとなる。
ユーザーはOracleを実行するユーザーとなる。oracleなどとすればよい。このユーザーのPrimary GIDはインストールグループを、Secondary GIDは管理者グループを与える。ホームディレクトリはORACLE_HOMEにすればよい(後ほど決定する)。
ユーザープロファイル設定
umaskは022へ設定。
下記環境変数を設定する。
- ORACLE_BASE
- 全てのOracleのベースとなるディレクトリ。Oracle Inventoryなどがインストールされる。推奨は、/u01/app/oracle
- ORACLE_HOME
- Oracleソフトウェアのディレクトリ。推奨は、$ORACLE_BASE/product/Release。8.1.7なら、/u01/app/oracle/product/8.1.7
- ORACLE_SID
- これからインストールするデータベースのSID。
- NLS_LANG
- これからインストールするデータベースの言語設定。文法は、言語_地域.キャラクタセットとなる。言語の部分はメッセージなどの言語になる。地域は、日付や通貨の表示形式を決定する。キャラクタセットは内部データのキャラクタセットとなる。お勧めは、AMERICAN_JAPAN.JA16EUC。これなら日本語が表示できない端末でも文字化けは起こらない。多言語化ならAMERICAN_JAPAN.UTF8だろうが、使用するアプリケーションのUnicode対応に要注意。
とりあえずこれでインストールを開始できる。
September 09, 2004
分析関数の中でも、LAGとLEADは特殊な関数である。
LAGは、現在の行よりも前の行の何れかの項目の値を参照する。LEADは、現在の行よりも後の行の何れかの項目を値を参照する。これらは、通常であれば副問い合わせを行わなければ実現できない。
LAGの例
LAGは、選択した行の中で、現在の行よりも以前の行のデータを取り出す。言い換えれば、データを遅延(lag)して表示する。
各事業所(DEPT)での、各従業員の雇用日の間隔を計算してみる。PREV_HIREDATEは、その従業員より以前に雇用された従業員の直近の雇用日となる。GAPが間隔日数である。
ora817@robios.org>select dept.loc, emp.ename, emp.job, emp.hiredate,
2 lag(emp.hiredate, 1, null) over
3 (partition by dept.loc order by emp.hiredate) as prev_hiredate,
4 emp.hiredate -
5 lag(emp.hiredate, 1, null) over
6 (partition by dept.loc order by emp.hiredate) as gap
7 from scott.emp, scott.dept
8 where emp.deptno = dept.deptno;
LOC ENAME JOB HIREDATE PREV_HIR GAP
------------- ---------- --------- -------- -------- ----------
CHICAGO ALLEN SALESMAN 81-02-20
CHICAGO WARD SALESMAN 81-02-22 81-02-20 2
CHICAGO BLAKE MANAGER 81-05-01 81-02-22 68
CHICAGO TURNER SALESMAN 81-09-08 81-05-01 130
CHICAGO MARTIN SALESMAN 81-09-28 81-09-08 20
CHICAGO JAMES CLERK 81-12-03 81-09-28 66
DALLAS SMITH CLERK 80-12-17
DALLAS JONES MANAGER 81-04-02 80-12-17 106
DALLAS FORD ANALYST 81-12-03 81-04-02 245
DALLAS SCOTT ANALYST 82-12-09 81-12-03 371
DALLAS ADAMS CLERK 83-01-12 82-12-09 34
NEW YORK CLARK MANAGER 81-06-09
NEW YORK KING PRESIDENT 81-11-17 81-06-09 161
NEW YORK MILLER CLERK 82-01-23 81-11-17 67
14行が選択されました。
経過: 00:00:16.01
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0094164' (UNIQUE)
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
17 consistent gets
4 physical reads
0 redo size
1108 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
それぞれの事業所で初めの行のPREV_HIREDATEがブランクなのは、それ以前の行がないからである。この場合は、LAGの3番目の引数(今回はNULL)を表示する。
LEADの例
LEADは、選択した行の中で、現在の行よりも以降の行のデータを取り出す。言い換えれば、データを先取り(lead)して表示する。
従業員SMITHからKING社長まで、直属との上司との給与の差を計算する。MGR_SALが直属の上司の給与、GAPが自分の給与との差である。(FORDは上司のJONESよりも給与が良い！)
ora817@robios.org>select job, ename, hiredate, sal,
2 lead(sal, 1, null) over (order by rownum) as mgr_sal,
3 lead(sal, 1, null) over (order by rownum) - sal as gap
4 from (
5 select rownum, job, ename, hiredate, sal
6 from scott.emp
7 connect by prior mgr = empno
8 start with empno = 7369);
JOB ENAME HIREDATE SAL MGR_SAL GAP
--------- ---------- -------- ---------- ---------- ----------
CLERK SMITH 80-12-17 800 3000 2200
ANALYST FORD 81-12-03 3000 2975 -25
MANAGER JONES 81-04-02 2975 5000 2025
PRESIDENT KING 81-11-17 5000
経過: 00:00:10.02
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 COUNT
3 2 VIEW
4 3 COUNT
5 4 CONNECT BY
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C0094163' (UNIQUE)
7 5 TABLE ACCESS (BY USER ROWID) OF 'EMP'
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0094163' (UNIQUE)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
543 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
KING社長のMGR_SALがブランクなのは、それ以降の行がないからである。この場合は、LEADの3番目の引数(今回はNULL)を表示する。
LAG/LEADの文法
LAG/LEAD ( 項目または式, <Offset>, <Default>) over (
<Partition-Clause>
Order-By-Clause
)
項目または式は、値を取得したい項目、またはそれを組み合わせた式となる。
Offsetでは、LAGの場合は現在行から何行前から、LEADの場合は現在行から何行後からデータを取得するかを指定する。省略した場合は1となる。
Defaultは、境界を越えてしまいデータの取得が行えない場合に、表示する値である。省略した場合はNULLとなる。
LAG/LEADでは、Order-By-Clauseが必須となる。また、Windowing-Clauseは使えない。Partition-Clauseは任意で設定する。
September 01, 2004
Oracle 8.1.6より、分析関数 (Analytic Function) という非常に強力なSQL拡張機能が実装されている。この機能を使えば、今までは副問い合わせを用いてしか可能でなかった問い合わせを、簡単に、そしてスピーディーに実行することができる。
分析関数が通常の集計関数 (Aggregate Function) と大きく異なるのは、通常の関数はグルーピングを行った「集合」に対し値を１つ返すにのに対し、分析関数はグルーピングを行った「同グループ内のそれぞれの行」に対し「同グループ内で、与えられた範囲を基に集計」を行い値を返す(非常に分かり辛いため後述の例を参考下さい、、、)。分析関数ではこのグルーピングされた行集合の中の、与えられた範囲を「窓 (Window)」と呼ぶ。分析関数は、各行において、与えられた窓内で集計を行い値を返すのである。
はじめに一例
ora817@robios.org>select emp.empno, emp.job, emp.ename, emp.sal,
2 sum(sal) over (partition by job order by empno) as cumulative_sum,
3 row_number() over (partition by job order by empno) as seq
4 from scott.emp;
EMPNO JOB ENAME SAL CUMULATIVE_SUM SEQ
---------- --------- ---------- ---------- -------------- ----------
7788 ANALYST SCOTT 3000 3000 1
7902 ANALYST FORD 3000 6000 2
7369 CLERK SMITH 800 800 1
7876 CLERK ADAMS 1100 1900 2
7900 CLERK JAMES 950 2850 3
7934 CLERK MILLER 1300 4150 4
7566 MANAGER JONES 2975 2975 1
7698 MANAGER BLAKE 2850 5825 2
7782 MANAGER CLARK 2450 8275 3
7839 PRESIDENT KING 5000 5000 1
7499 SALESMAN ALLEN 1600 1600 1
7521 SALESMAN WARD 1250 2850 2
7654 SALESMAN MARTIN 1250 4100 3
7844 SALESMAN TURNER 1500 5600 4
14行が選択されました。
経過: 00:00:06.03
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 TABLE ACCESS (FULL) OF 'EMP'
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
1 physical reads
0 redo size
978 bytes sent via SQL*Net to client
287 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
CUMULATIVE_SUMが、各JOBにおけるEMPNO順でのSALの累計、SEQは各JOBにおけるEMPNO順でのシーケンスになっている。
分析関数の文法
Analytic-Function(<Argument>, <Argument>, ...) over (
<Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
Analytic-Function
分析関数には下記の関数がある。一部は9iからの実装である。
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST (9i), FIRST_VALUE, LAG, LAST (9i), LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT (9i), PERCENTILE_DISC (9i), RANK, RATIO_TO_REPORT, REGR_ (Linear Regression) Functions, ROW_NUMBER, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE
Partition Clause
Partition句では、集計を行う窓のグループ分けを行う。丁度、通常の集計関数におけるGROUP BY句に等しい。分析関数は、この句で定義した項目でグルーピングし、それぞれのグループ内の窓で集計を行う。省略した場合は、選択行全てが同一グループとなる。
文法: PARTITION BY 項目 ( , 項目, ... )
Order-By Clause
Order-By句では、グループ内でのデータのソート順を定義する。集計は、この句で定義されたソート順に沿って行われる。
文法: ORDER BY 項目 ( , 項目, ...) ASC/DESC ( NULLS FIRST/LAST )
Windowing Clause
Windowing句では、窓そのものを定義する。窓は範囲である。範囲は、物理的な行での範囲指定と、論理的な値での範囲指定とある。前者はROWS、後者はRANGEで表す。
文法: ROWS/RANGE [範囲指定句]
範囲指定句では、始点と終点を指定する。
文法: BETWEEN [始点] AND [終点]
終点を現在行とし、始点だけ指定することもできる(この場合始点は、UNBOUNDED PRECIDING、CURRENT ROW、N PRECEDINGのみとり得る)。
文法: [始点]
始点/終点には、グループ内先頭のUNBOUNDED PRECEDING、現在点のCURRENT ROW、グループ内後尾のUNBOUNDED FOLLOWING、現在点からN前方のN PRECEDING、現在点からN後方のN FOLLOWING、の5種類がある。Nは、ROWSであれば行数、RANGEであれば値の差となる。グループ内先頭、後尾とは前述したOrder-By句でのソート順における先頭、後尾となる。
Windowing Clauseを省略した場合は、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW となる。すなわち、グループ先頭から現在の行までが窓となる。
例
ora817@robios.org>select *
2 from (
3 select dept.loc, dept.dname, emp.job, emp.ename, emp.sal,
4 dense_rank() over (partition by dept.loc order by sal desc) as rank
5 from scott.emp, scott.dept
6 where emp.deptno = dept.deptno)
7 where rank <= 3;
LOC DNAME JOB ENAME SAL RANK
------------- -------------- --------- ---------- ---------- ----------
CHICAGO SALES MANAGER BLAKE 2850 1
CHICAGO SALES SALESMAN ALLEN 1600 2
CHICAGO SALES SALESMAN TURNER 1500 3
DALLAS RESEARCH ANALYST SCOTT 3000 1
DALLAS RESEARCH ANALYST FORD 3000 1
DALLAS RESEARCH MANAGER JONES 2975 2
DALLAS RESEARCH CLERK ADAMS 1100 3
NEW YORK ACCOUNTING PRESIDENT KING 5000 1
NEW YORK ACCOUNTING MANAGER CLARK 2450 2
NEW YORK ACCOUNTING CLERK MILLER 1300 3
10行が選択されました。
経過: 00:00:06.03
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT PUSHED RANK)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'EMP'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C0094164' (UNIQUE)
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
17 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
318 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
各事業所(DEPT)での、給与上位3位までをリストアップしている。
August 17, 2004
Oracleの関数には、総和(SUM)はあるが総積(全ての値を掛け合わしたもの)はない。平均(AVG)や標準偏差(STDDEV)はあるのに、もっと単純な総積がない。
ないなら作るまでと思いきや、実はもっと単純な方法があった。全くもって、目から鱗であった。
詰まる所、数学的に総積を総和の形へ変換することで計算可能となる。下の数式をご覧頂きたい。
下段のe(自然対数の底、ネピア)の指数が、aとbそれぞれの自然対数の和になっている。値が増えても同じように変換される。
これをSQLで表すと、下記の様になる。
SELECT EXP(SUM(LN(column))) FROM table;
すなわち、columnの各値の自然対数を取り、それを総和し、最終的にeの指数とすれば、総積となる。
しかし、このままではマイナスの数とゼロが含まれた総積がうまく表現できない。そこで、
SELECT
EXP(SUM(LN(ABS(DECODE(column, 0, 1, column))))) *
DECODE(MOD(COUNT(DECODE(SIGN(column), -1, 1, NULL)), 2), 1, -1, 1) *
NVL(MAX(DECODE(column, 0, 0, NULL)), 1)
FROM table;
とすれば、うまく表現可能となる。(1段目では絶対値をとりさらに0の場合は1とし、2段目でマイナスの数をカウントし奇数個であれば全体に-1を掛け、3段目でゼロを検索しあれば全体にゼロを掛ける。最早力ずく、笑)
所で、この方法では計算結果に誤差がでてしまう可能性がある。勘定系での使用は要注意(勘定系でこんな方法そもそも使うわけないか)。
参考: Ask Tom: Mechanism to perform the product of a single column in a multirow table
July 28, 2004
前述した、Stored OutlineのHackを実践してみる。
まずは実験用テーブルを作る。
ora817@robios.org> create table hoge_transactions
2 as
3 select rownum as transaction_id, 'HOGE' as data,
4 4 as status_code
5 from all_objects
6 where rownum <= 100000;
表が作成されました。
経過: 00:00:20.04
ora817@robios.org> create index hoge_transactions_n1
2 on hoge_transactions (status_code);
索引が作成されました。
経過: 00:00:00.08
ora817@robios.org> update hoge_transactions
2 set status_code = 1
3 where transaction_id between 99000 and 99099;
100行が更新されました。
経過: 00:00:00.01
ora817@robios.org> commit;
コミットが完了しました。
経過: 00:00:00.01
ora817@robios.org> analyze table hoge_transactions
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns;
表が分析されました。
経過: 00:00:02.02
これで、Cardinalityが非常に低いコラムを索引対象したテーブルが作成された。ヒストグラムが作成されているため、STATUS_CODE = 1で検索すると、索引検索が行われる。
ora817@robios.org> select *
2 from hoge_transactions
3 where status_code = 1;
TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
99000 HOGE 1
99001 HOGE 1
99002 HOGE 1
- 途中省略 -
99097 HOGE 1
99098 HOGE 1
99099 HOGE 1
100行が選択されました。
経過: 00:00:03.02
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=160
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HOGE_TRANSACTIONS' (Cost
=2 Card=100 Bytes=1600)
2 1 INDEX (RANGE SCAN) OF 'HOGE_TRANSACTIONS_N1' (NON-UNIQUE
) (Cost=1 Card=100)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
5 physical reads
0 redo size
2759 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
しかし、バインド変数を使うと、索引は使用されなくなる。(→理由についてはCBOにおける索引の使用判断 - コストの計算例を参照)
ora817@robios.org> variable status number;
ora817@robios.org> exec :status := 1;
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.00
ora817@robios.org> select *
2 from hoge_transactions
3 where status_code = :status;
TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
99000 HOGE 1
99001 HOGE 1
99002 HOGE 1
- 途中省略 -
99097 HOGE 1
99098 HOGE 1
99099 HOGE 1
100行が選択されました。
経過: 00:00:03.02
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=50000 Bytes=
800000)
1 0 TABLE ACCESS (FULL) OF 'HOGE_TRANSACTIONS' (Cost=39 Card=5
0000 Bytes=800000)
統計
----------------------------------------------------------
0 recursive calls
12 db block gets
257 consistent gets
247 physical reads
0 redo size
2759 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
さて、このようなSQLがOracle ApplicationのProcedureの中にあり、手が出せないとする(実際にこのようなSQLはよくある)。先のOutline Hackでパフォーマンス改善を行う。
まずはProcedureを作る。
ora817@robios.org> create or replace procedure process_hoge_transactions is
2 unprocessed_status constant number := 1;
3 begin
4 for cur in (select *
5 from hoge_transactions
6 where status_code = unprocessed_status)
7 loop
8 dbms_output.put_line(cur.transaction_id || ', ' || cur.data);
9 end loop;
10 end;
11 /
プロシージャが作成されました。
経過: 00:00:00.01
ora817@robios.org> alter session set sql_trace=true;
セッションが変更されました。
経過: 00:00:00.01
ora817@robios.org> exec process_hoge_transactions;
99000, HOGE
99001, HOGE
99002, HOGE
- 途中省略 -
99097, HOGE
99098, HOGE
99099, HOGE
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.08
ora817@robios.org> alter session set sql_trace=false;
セッションが変更されました。
経過: 00:00:00.01
トレースの結果を示す。
********************************************************************************
SELECT *
FROM
HOGE_TRANSACTIONS WHERE STATUS_CODE = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.06 0.07 247 350 12 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.06 0.07 247 350 12 100
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 183 (recursive depth: 1)
********************************************************************************
まず、ターゲットSQLのOutlineを作る。CREATE_STORED_OUTLINESフラグを立てて、Procedureを実行する。
ora817@robios.org> alter session set create_stored_outlines=true;
セッションが変更されました。
経過: 00:00:00.00
ora817@robios.org> exec process_hoge_transactions;
99000, HOGE
99001, HOGE
99002, HOGE
- 途中省略 -
99097, HOGE
99098, HOGE
99099, HOGE
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.09
ora817@robios.org> alter session set create_stored_outlines=false;
セッションが変更されました。
経過: 00:00:00.00
ora817@robios.org> select ol_name, sql_text
2 from outln.ol$;
OL_NAME SQL_TEXT
------------------------------ ----------------------------------------
SYS_OUTLINE_040729043824572 SELECT * FROM HOGE_TRANSACTIONS
WHERE STATUS_CODE = :b1
経過: 00:00:00.02
次に、ターゲットSQLにヒントを埋め込み、索引が正しく使われるようにする。そしてそのSQLのOutlineを作成する。
ora817@robios.org> select /*+ RULE */ *
2 from hoge_transactions
3 where status_code = 1;
TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
99000 HOGE 1
99001 HOGE 1
99002 HOGE 1
- 途中省略 -
99097 HOGE 1
99098 HOGE 1
99099 HOGE 1
100行が選択されました。
経過: 00:00:03.03
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HOGE_TRANSACTIONS'
2 1 INDEX (RANGE SCAN) OF 'HOGE_TRANSACTIONS_N1' (NON-UNIQUE
)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
3 physical reads
0 redo size
2680 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
ora817@robios.org> create outline temp on
2 select /*+ RULE */ *
3 from hoge_transactions
4 where status_code = 1;
アウトラインが作成されました。
経過: 00:00:00.01
先ほどのOutlineと、今作成したOutlineを入れ替える。OL$のHINTCOUNTも忘れずに(値は前もって確認)。
ora817@robios.org> update outln.ol$hints
2 set ol_name = decode(ol_name, 'SYS_OUTLINE_040729043824572', 'TEMP',
3 'TEMP', 'SYS_OUTLINE_040729043824572')
4 where ol_name in ('SYS_OUTLINE_040729043824572', 'TEMP');
13行が更新されました。
経過: 00:00:00.01
ora817@robios.org> update outln.ol$
2 set hintcount = decode(ol_name, 'SYS_OUTLINE_040729043824572', 7,
3 'TEMP', 6)
4 where ol_name in ('SYS_OUTLINE_040729043824572', 'TEMP');
2行が更新されました。
経過: 00:00:00.01
ora817@robios.org> commit;
コミットが完了しました。
経過: 00:00:00.00
これで完了である。試しに、USE_STORED_OUTLINESフラグをオンにし、ヒント埋め込みバージョンを実行してみる。うまくいっていれば、全スキャンが行われるはずである。
ora817@robios.org> alter session set use_stored_outlines=true;
セッションが変更されました。
経過: 00:00:00.00
ora817@robios.org> select /*+ RULE */ *
2 from hoge_transactions
3 where status_code = 1;
TRANSACTION_ID DATA STATUS_CODE
-------------- ---- -----------
99000 HOGE 1
99001 HOGE 1
99002 HOGE 1
- 途中省略 -
99097 HOGE 1
99098 HOGE 1
99099 HOGE 1
100行が選択されました。
経過: 00:00:03.04
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=39 Card=100 Byte
s=1600)
1 0 TABLE ACCESS (FULL) OF 'HOGE_TRANSACTIONS' (Cost=39 Card=1
00 Bytes=1600)
統計
----------------------------------------------------------
48 recursive calls
20 db block gets
264 consistent gets
252 physical reads
600 redo size
2727 bytes sent via SQL*Net to client
292 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
100 rows processed
全スキャンが行われている。次に本題のProcedureを実行し、トレースを取ってみる。
********************************************************************************
SELECT *
FROM
HOGE_TRANSACTIONS WHERE STATUS_CODE = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.01 0.00 3 202 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.01 0.01 3 202 0 100
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 183 (recursive depth: 1)
********************************************************************************
索引が使用され、Disk IOが大幅に減ったのが分かっていただけると思う。
その他:
- PL/SQLで、定数として変数を宣言し、それを検索のキーとして使うのは避けるべきである。定数と宣言してもバインド変数となり、せっかくのヒストグラムが使用されない。上記Procedureで、STAUTS_CODE = 1と素直に書けば、ヒストグラムが使用され、索引検索が行われる。
- ヒント埋め込みバージョンSQLの作成は、今回の場合はヒストグラムがある為あえてRULEヒントを加える必要はない。
- 今回作成した表の、STATUS_CODEのようなCardinalityが非常に低い項目に索引を作成する場合、通常の索引ではなくビットマップ索引を作成すると効果的である。しかし、ビットマップ索引の場合、近いレコードへの同時更新が発生すると、ロックが発生するため注意が必要。
- USE_STORED_OUTLINESフラグを立てた場合、ハード解析時に、そのSQLのOutlineがOL$に定義されているかどうかを調べるため、OL$をHash Valueで検索する。そのため、多少のオーバーヘッドが生じることになる。オーバーヘッドはRecursive Callという形で現れる。このオーバーヘッドについての検証はいずれ。
July 25, 2004
手を入れたいがどうしても入れることができないSQLが多くある。ヒント文を入れるだけで、コストがうんと下がるのであれば、この方法は有効だ。
Oracle 8iよりStored Outlineという、あまり知られていない、しかし非常に有用な機能が追加された。
Stored Outlineとは、Ask Tomのある一稿から引用すると、「the ability for a developer to save an outline, a set of “hints to the server” for executing a specific SQL statement in the database」、とあり、日本語にすれば、「あるSQLを実行する際Oracleが参考とするヒント文、すなわちOutlineを保存する機能」となる。
この機能を使うことで、SQLが実行される際のヒント(というかむしろ実行計画そのもの)を、テーブルに保存することができる。また、USE_STORED_OUTLINESフラグを立てることで、その後は同SQLはそのテーブルに保存されたヒントを基に問い合わせが実行される。
ヒントが保存されるテーブルは、下記の2つのテーブル:
OUTLN.OL$
OUTLN.OL$HINTS
Outlineは、CREATE OUTLINE文で作成できる。作成されるOutlineに名前をつけたい場合は、CREATE OUTLINE Outline名 ON〜とすればよい。省略した場合は、Oracleが勝手に名前をつける。
CREATE OUTLINE ON
SELECT CODE_COMBINATION_ID
FROM GL.GL_CODE_COMBINATIONS
WHERE SEGMENT1 = :SEGMENT1;
これで、GL_CODE_COMBINATIONSをSEGMENT1で検索するSQLのOutlineが作成された。ただ、これだけでは今作成したOutlineは使用されない。Outlineの使用には、USE_STORED_OUTLINESフラグを立てる必要がある。フラグはSYSTEMかSESSIONで立てる。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
これで今後同セッションでは、先ほど作成されたOutlineが、先ほどと同じSQLを実行する場合において、必ず使用されることになる。気をつけなければならないことは、実行するSQLは、CREATE OUTLINEを行ったSQLと一言一句異なってはならない。なぜなら、SQL Hash ValueをキーにOL$を検索するからである。スペース1つ違うだけで違うHash値となってしまい、Outlineは使用されなくなってしまう。
他にも、作成するOutlineをグループ分けし、使用の際グループ単位で使用/未使用を決定する方法などあるがここでは割愛する。次回にでも。
さて、ここまでは普通の解説。これからはOracleがコメントしていない部分を多少Hackする。ここから先はAt Your Own Riskで。何があっても私は責任をもてません。笑。
作成されたOutlineは、OL$とOL$HINTSに保存される。OL$は親情報で、SQL文やHash Valueなどを持つ。OL$HINTSはOL$の子供で、SQLのヒント文を持つ。このOL$HINTSの中身こそが、実行計画そのものである。
さて、Oracle 9iには、Diagnostic Packの中にOutline Editorという、Outlineの中身を変更するGUIツールがあるのだが、8iにはない、、、。こうなれば、直接OL$HINTSを変更するまでである。
とはいうものの、HINT_TEXTの項目に関するOracleのReferenceはない。そこで、別のSQLにてOutlineを作成し、それをオリジナルの物と置き換えるという荒業にて対応することにする。
1) 実行計画を変更したいSQL(ターゲットSQL)のOutlineを作る
先ほど述べたように、SQLは一言一句異なってはならない。Outlineを作成したいSQLをPL/SQL等からコピーし、SQL*PlusにペーストしCREATE OUTLINEする方法は通常あまりうまくいかない。このような場合有効な方法は、CREATE_STORED_OUTLINESフラグの使用である。このフラグを立てた場合、そのセッション(またはシステム)で実行されたSQL全てのOutlineが作成される。このフラグを立て、PL/SQL等の、「ターゲットSQL」が含まれているアプリケーションを実行する。
CREATE_STORED_OUTLINESのフラグの立て方:
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;
これで、ターゲットとなるSQLのOutlineが作成されたはずである。(ごみOutlineも沢山できたはず)
2) ターゲットSQLのヒント文埋め込みバージョンを作る
次に、ターゲットSQLにヒント文を埋め込み、好みの実行計画になるよう調整する。この段階で、ヒント文をいくら埋め込んだ所で好みの実行計画にならない場合、その実行計画にはどこか無理がある。Outlineはあくまでもヒントであり、Oracleは実行時に使用判断を行い使用しないという選択もとり得る。
3) ヒント文埋め込みバージョンのOutlineを作る
ステップ2で作成したSQLのOutlineを、CREATE OUTLINEで作成する。名前は適当に。つけなくてもよい。
4) OL$HINTSの交換
1)で作成したOutlineのOL$HINTSの内容を、3)で作成したものと交換する。1)と3)のOL$HINTSの行数が異なる場合は、OL$のHINTCOUNTの更新も忘れずに。更新に臨んで、USE_STORED_OUTLINESフラグが立っていれば、落とすのが懸命だろう。
交換の方法は、SYSユーザーで下記SQLを実行する。1)で作成したOutlineの名前を「ORIGINALOL」、3)で作成したOutlineの名前を「MODIFIEDOL」とする。
UPDATE OL$HINTS
SET OL_NAME = DECODE(OL_NAME, 'ORIGINALOL', 'MODIFIEDOL',
'MODIFIEDOL', 'ORIGINALOL')
WHERE OL_NAME IN ('ORIGINALOL', 'MODIFIEDOL');
5) USE_STORED_OUTLINESのフラグを立てる
あとは、変更したOutlineが使用されるように、USE_STORED_OUTLINESのフラグを立てるのみ。同一セッションでターゲットSQLが実行されるならALTER SESSIONでいいが、ほとんどの場合は他のセッションのはずなので、ALTER SYSTEMでフラグを立てる。
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;
以上である。あとは、1)でできたゴミOutlinesをDROPすればなお良い。
ちなみに、Outlineが実際に使われているかを確認する方法は、V$SQLを見ればよい。OUTLINE_CATEGORYという項目の値が、OL$のCATEGORYの値と一致すれば、Outlineは正しく使用されている。ブランクであれば、使用されていない。
以上の方法で、手を出したくても出せないSQL(Oracle Applicationやら)の実行計画を、SQLそのものは全く変更することなく、修正することが可能となる。
一番良いのは、そのような修正の必要があるSQLを生み出さないことである、、、。
追記:
同様の記事を探したところ、DBAzine.comに見つけた。
その記事で知ったのだが、Metalinkに同様のNoteがポストされている(Oracleはコメントしていないと思っていた)。92202.1である。(ただ、このNoteでは、OL$のHINTCOUNTの更新について漏れている。)
しかしやはりサポート外であることには変わりはない。
June 19, 2004
前回 (CBOにおける索引の使用判断)の続き。
コスト計算方法
表全走査 (Full Table Scan):
[ブロック数] / [DB_FILE_MULTIBLOCK_READ_COUNT]
索引一意走査 (Index Unique Scan):
([索引階層数] + 1{ROWIDによる表走査}) * [OPTIMIZER_INDEX_COST_ADJ] / 100
索引範囲走査 (Index Range Scan):
([索引階層数] - 1{リーフ分を控除} + [リーフブロック数] * [Filtering Factor] + [Clustering Factor] * [Filtering Factor]) * [OPTIMIZER_INDEX_COST_ADJ] / 100
表/索引情報
表T1の情報:
・行数: 1,000,000
・ブロック数: 1,500
・DB_FILE_MULTIBLOCK_READ_COUNT: 8
項目COL1の情報:
・一意な値: 1,000,000件
・最小値: 1
・最大値: 1,000,000
索引の情報:
・索引階層数: 3
・リーフブロック数: 2,000
・Clustering Factor: 1,500
例1: 値指定問い合わせ
SELECT COL1
FROM T1
WHERE COL1 = 990000;
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * (1 / 1,000,000) + 1,500 * (1 / 1,000,000)) * 100/100
= 2 + 0.002 + 0.0015 = 2.0035
結果、索引走査が行われる。
例2: 下限指定問い合わせ
SELECT COL1
FROM T1
WHERE COL1 > 990000;
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * ((1000000 - 990000) / (1000000 - 1)) + 1,500 * ((1000000 - 990000) / (1000000 - 1))) * 100/100
= 2 + 20+ 15 = 37
結果、索引走査が行われる。
例3: 上限指定問い合わせ
SELECT COL1
FROM T1
WHERE COL1 < 990000;
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * ((990000 - 1) / (1000000 - 1)) + 1,500 * ((990000 - 1) / (1000000 - 1))) * 100/100
= 2 + 1980+ 1485 = 3467
結果、全表走査が行われる。
例4: 範囲指定問い合わせ
SELECT COL1
FROM T1
WHERE COL1 BETWEEN 990000 AND 991000;
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * ((991000 - 990000) / (1000000 - 1)) + 1,500 * ((991000 - 990000) / (1000000 - 1))) * 100/100
= 2 + 2 + 1.5 = 5.5
結果、索引走査が行われる。
表/索引情報 その2
表T2の情報:
・行数: 1,000,000
・ブロック数: 1,500
・DB_FILE_MULTIBLOCK_READ_COUNT: 8
項目COL2の情報:
・一意な値: 2件
・最小値: 1
・最大値: 2
・「1」の件数: 1,000
・「2」の件数: 999,000
・ヒストグラム作成済み (サイズ: 2)
索引の情報:
・索引階層数: 3
・リーフブロック数: 2,000
・Clustering Factor: 1,500
このテーブルは索引付けされた項目のカーディナリティ(濃度)が非常に低い。オンライン系のアプリケーションでは、ステータス管理項目にこのような例が見られる。
COL2にはヒストグラムを作成してある。OracleはCOL2のそれぞれの値の件数を情報として持つことになる。これは、Filtering Factorの値が、COL2の値によって変化することを意味する。
例5: 値指定問い合わせ COL2 = 1の場合
SELECT COL2
FROM T2
WHERE COL2 = 1
COL2の値が1の件数は1,000件である。Filtering Factorは、1000 / (1000000 - 1)となる。
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * (1000 / (1000000 - 1)) + 1,500 * (1000 / (1000000 - 1))) * 100/100
= 2 + 2 + 1.5 = 5.5
結果、索引走査が行われる。
例6: 値指定問い合わせ COL2 = 2の場合
SELECT COL2
FROM T2
WHERE COL2 = 2
COL2の値が2の件数は999,000件である。Filtering Factorは、999000 / (1000000 - 1)となる。
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * (999000 / (1000000 - 1)) + 1,500 * (999000 / (1000000 - 1))) * 100/100
= 2 + 1998 + 1498 = 3498
結果、全表走査が行われる。
例7: バインド変数による値指定問い合わせ
SELECT COL2
FROM T2
WHERE COL2 = :X
バインド変数による問い合わせの場合、Oracleは実行計画を立てる際、:Xの値が未知数のため、ヒストグラムによる件数の推理は行えない。この場合のFiltering Factorは下記の様になる。
Filtering Factor = (行数 / 一意な値の数) / 行数 = 1 / 一意な値の数
よって、T2のCOL2については、
Filtering Factor (COL2) = (1000000 / 2) / 1000000 = 1 / 2 = 0.5
となる。
全表走査のコスト:
1,500 / 8 = 187.5
索引走査のコスト:
(3 - 1 + 2,000 * 0.5 + 1,500 * 0.5) * 100/100
= 2 + 1000 + 750 = 1752
結果、全表走査が行われる。
カーディナリティが非常に低い項目に対するバインド変数を使った問い合わせは、可能な限り避けるべきである。バインド変数を使う必要がある場合は、ヒントの使用やOUTLINEの設定を考えるべきである。
May 24, 2004
CBOにおいては、コストが一番低い方法で実行計画を立てる。この「コスト」とは、そのSQLの物理I/O数に近い。
コストの計算方法
表全走査 (Full Table Scan):
[ブロック数] / [DB_FILE_MULTIBLOCK_READ_COUNT]
索引一意走査 (Index Unique Scan):
([索引階層数] + 1{ROWIDによる表走査}) * [OPTIMIZER_INDEX_COST_ADJ] / 100
索引範囲走査 (Index Range Scan):
([索引階層数] - 1{リーフ分を控除} + [リーフブロック数] * [Filtering Factor] + [Clustering Factor] * [Filtering Factor]) * [OPTIMIZER_INDEX_COST_ADJ] / 100
- ブロック数
- 表を構成するブロックの総数。ALL_TABLES/DBA_TABLESで確認。
- DB_FILE_MULTIBLOCK_READ_COUNT
- 初期パラメータ。全表走査の際1回の読み取りで何ブロックを同時に取得するか。初期値は8。
- 索引階層数
- B*Tree索引(通常の索引)の階層数。例えば3階層であれば、ルート、ブランチ、リーフ、4階層であれば、ルート、ブランチ、ブランチ、リーフとなる。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。
- リーフ数
- B*Tree索引のリーフ総数。最下層ノードの数。
- Filtering Factor
- 全件数に占める、検索取得数の割合。詳細は後述。
- Clustering Factor
- 索引の、それぞれのリーフが参照する表ブロックの総和。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。詳細は後述。
- OPTIMIZER_INDEX_COST_ADJ
- 初期値は100。詳細は後述。
上記式で、表全走査、索引走査それぞれのコストを計算し(索引が複数あればそれらもそれぞれ計算)、少ない方法にて実行計画を立てる。
Filtering Factor
Filtering Factor (FF) は、取得を行おうとしている件数が全件数の何割か、を示す値である。実際に検索を行っていないため、この値は分析により得られた結果を元に推理される。
表と索引分析後、Oracleがその表と索引に対して既に知っていることは、
- 表の全件数
- 項目の最大値と最小値
- 項目の一意な値の数 (ALL_TAB_COLUMNS等のDISTINCT_KEYS)
である。
索引項目をCOL1、またN、Mを定数(バインド変数ではない)としたとき、FFの値は、検索方法により下記のように決まる。
- COL1 = N
- FF = 1 / [COL1の一意な値の数]
- COL1 > N
- FF = (MAX(COL1) - N) / (MAX(COL1) - MIN(COL1))
- COL1 < N
- FF = (N - MIN(COL1)) / (MAX(COL1) - MIN(COL1))
- COL1 between N and M
- FF = (M - N) / (MAX(COL1) - MIN(COL1))
例:
表T1の項目COL1には1から10000までの10000件のデータが一意に保存され、索引付けもされている。このとき、MAX(COL1)は10000、MIN(COL1)は1、COL1の一意な値は10000件である。
COL1 = 5000の時、
FF = 1 / 10000 = 0.0001
COL1 > 9000の時、
FF = (10000 - 9000) / (10000 - 1) = 1000 / 9999 = 0.100... = 0.1
COL1 < 9000の時、
FF = (9000 - 1) / (10000 - 1) = 0.899... = 0.9
COL1 between 2000 and 4000の時、
FF = (4000 - 2000) / (10000 - 1) = 0.200... = 0.2
以上の様に、取得が予想される件数の割合を推理することができる。ただし、例に示したような値の分布が均等である項目では推理値は実際の値に非常に近い(若しくは一致する)が、値の分布が非常に偏っている場合は、推理値は実際の値と大きくかけ離れる場合がある。その場合、適当ではない実行計画を立てる等の不都合が起きる。詳細は後述。
ところで、一意な場合のFFはあまり意味をなさない。コストは結局のところ索引階層数 + 1となり、FFの影響を受けないからである。
Clustering Factor
索引のそれぞれのリーフが参照する表ブロックの総和を示す。意味としては、索引を全スキャンした際、いくつの表ブロックにアクセスする必要があるか、という事。Clustering FactorにFiltering Factorを掛けると、索引レンジスキャンした際に、いくつの表ブロックにアクセスする必要があるか、となる。
Clustering Factorは、索引対象の値が表内で分散している場合(例: 1, 2, 3, 1, 2, 3, 1, 2, 3...)、リーフが参照する表ブロックが増えるため、高くなる。逆に、索引対象の値が表内で偏っている場合(例: 1, 1, 1, 2, 2, 2, 3, 3, 3, 3...)、リーフが参照するブロックが少ないため、低くなる。言葉を変えると、同じブロックに索引対象の同じ値が入っていればいるほど、低くなる。
確認は、索引の分析をした後、ALL_INDEXES/DBA_INDEXESのCLUSTERING_FACTORを調べれば可能。
OPTIMIZER_INDEX_COST_ADJ
索引走査における、DBブロック取得コストを調整するパラメータ。1から100(パーセント)をとる。初期値は100、すなわち、調整はしない。
ある環境にて、索引走査にて取得される索引/表ブロックの多数がキャッシュされており、今後もキャッシュされ続けるとする。この時、索引走査での索引/表ブロック取得が、キャッシュヒットにより平均で通常の1/2の時間で可能であれば、索引走査のコスト算出は調整されるべきである。この場合、OPTIMIZER_INDEX_COST_ADJを50とすることで、1/2に応じた調整が行われる。
OPTIMIZER_INDEX_COST_ADJを使った調整は、索引走査のコストを大きく変化させるため、環境によっては大きなパフォーマンス改善が可能である。ただし、システムパラメータのため、変更の際は十分注意が必要である。
続く...
|
