optdiagる
０．更新履歴
- 2002.05.15 新規作成
１．はじめに
このドキュメントでは，Sybaseのメンテナンス用コマンドである，optdiagの使い方について説明する．
２．optdiagとは
オプティマイザ統計値を表示したり，更新された統計値をシステムテーブルに入れる．
今回は，reorgが必要かどうかを確認するのが目的．
３．データベース全ての統計情報を取ってみる
- 次のように，コマンドを実行し，データベースにある全てのオブジェクトの統計情報を表示させる．
bash$ optdiag statistics model -Usa -P -Spoweredge -o model.opt
bash$ cat model.opt
Server Message: - Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and se
rver character set 'sjis'.
Server Message: - Msg 2411, Level 10, State 1:
No conversions will be done.
Server Message: - Msg 2415, Level 10, State 1:
Due to previous errors, the language for this session is being forced to 'us_english'.
OptDiag/11.9.2.6/1287/P/SWR 10032 ROLLUP/Linux Intel/Linux 2.2.5-15 i586/OPT/Tue Mar
12 00:53:10 2002
Adaptive Server Enterprise/11.9.2.6/1287/P/SWR 10032 ROLLUP/Linux Intel/Linux 2.2.5-
15 i586/OPT/Mon Mar 11 23:46:34 2002
Server name: ""
Specified database: "model"
Specified table owner: not specified
Specified table: not specified
Specified column: not specified
Table owner: "dbo"
Table name: "sysalternates"
Statistics for index: "sysalternates" (clustered)
Index column list: "suid"
Data page count: 1
Empty data page count: 0
Data row count: 0.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 0.0000000000000000
OAM + allocation page count: 0
First extent leaf pages: 0
Data row size: 6.0000000000000000
Index height: 1
Derived statistics:
Data page cluster ratio: 1.0000000000000000
No statistics for remaining columns: "altsuid"
(default values used) "suid"
bash$ |
- このサンプルでは，MODELデータベースの統計情報を，model.optファイルに保存している．
４．特定のテーブルの統計情報を表示させてみる．
- テーブルを指定して，統計情報を参照する．
bash$ optdiag statistics model..syscolumns -Usa -P -Spoweredge -o model.syscolumns.opt
bash$ cat model.syscolumns.opt
Server Message: - Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and se
rver character set 'sjis'.
Server Message: - Msg 2411, Level 10, State 1:
No conversions will be done.
Server Message: - Msg 2415, Level 10, State 1:
Due to previous errors, the language for this session is being forced to 'us_english'.
OptDiag/11.9.2.6/1287/P/SWR 10032 ROLLUP/Linux Intel/Linux 2.2.5-15 i586/OPT/Tue Mar
12 00:53:10 2002
Adaptive Server Enterprise/11.9.2.6/1287/P/SWR 10032 ROLLUP/Linux Intel/Linux 2.2.5-1
5 i586/OPT/Mon Mar 11 23:46:34 2002
Server name: ""
Specified database: "model"
Specified table owner: not specified
Specified table: "syscolumns"
Specified column: not specified
Table owner: "dbo"
Table name: "syscolumns"
Statistics for index: "syscolumns" (clustered)
Index column list: "id", "number", "colid"
Data page count: 8
Empty data page count: 0
Data row count: 100.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 1.0000000000000000
OAM + allocation page count: 0
First extent leaf pages: 0
Data row size: 190.0000000000000000
Index height: 1
Derived statistics:
Data page cluster ratio: 1.0000000000000000
No statistics for remaining columns: "cdefault"
(default values used) "colid"
"domain"
"id"
"length"
"name"
"number"
"offset"
"prec"
"printfmt"
"remote_name"
"remote_type"
"scale"
"status"
"type"
"usertype"
Optdiag succeeded.
bash$
|
インストールしたてのDBだし，modelデータベースなので，なんにも情報が無い．
