OracleDBAの技術メモ

最近物覚えが悪くなったので仕事中に調べた後々役立つ情報をメモしております。

[PR]

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

ログオントリガー作成

CREATE OR REPLACE TRIGGER ユーザ名.TRI_SQL_TRACE
ALTER LOGON ON ユーザ名.SCHEMA
    BEGIN
        EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRADE = TRUE;
    END;

これを有効にするにはユーザにALTER SESSION権限が必要。

解除するには、ALTER SESSION権限の剥奪とトリガーの削除が必要。

＜＜ソース＞＞
■特定ユーザのSQLトレース取得
http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=110

Statspack解析方法

1.セッション数チェック

DB Timeは期間内の全てのセッション時間の合計なので、
セッション数が大きく変わると DB Time が変動し各種待機時間の割合に影響する。
そのため、一応確認しておくとよい。

2.アプリケーションの傾向をざっくり確認

Load Profileのチェック

DBのアクティビティの確認になる。
以前の値と合わせてグラフにするとアクティビティの変化がわかりやすい。

3.インスタンスの効率をチェック

Instance Effeciency Perentege のチェック

全ての値が100%に近いことが望ましい。
Buffer Hit % ⇒ キャッシュヒット率はダイレクト・リードが加味されていないので注意。
すなわちBuffer Hit %が高くても、Table Access Full のダイレクト・リードが多発して、
ディスクに負荷がかかっていることがある。

4.Top 5 Event

待機イベントが DB CPU より下回っていればひとまず問題はない。
ただし、下回った状態で変動があるようであれば、パフォーマンス悪化の兆候であることがある。

5.Memory Advisoryをチェック

基本的には大きく変動しないはず。
念のため、変動していない事を確認しておく。

6.実行されているSQL文をチェック

各SQL統計（SQL Statistics）をチェックしておく。
できればAWRレポート以外の手段でSQL実行時間の長いものをチェックしておく。
AWRレポートしかない場合は、頑張って全てのSQL統計を一覧表にする？

＜＜参考＞＞
【セミナー動画/資料】実践!! パフォーマンス・チューニング～Statspack解析～
https://blogs.oracle.com/oracle4engineer/entry/_statspack

インデックスが使用されないケース

No.	ケース	SQLの例	対処方法
1	NULL値の検索	列名 IS NULL	・NULL値を別のデータに置き換える・ビットマップ・インデックスを使用する
2	暗黙の型変換	VARCHAR2列 = 1	・比較するデータ型を列のデータ型に合わせる　　　　VARCHAR2列 = TO_CHAR(1) ・INDEXヒントを使用する　（インデックス列にNOT NULL制約が必要）
3	インデックス列に対して関数や算術を実施	VARCHAR2列 \|\| '様' = '○○様' NUMBER列 * 20 = 10000 substr(VARCHAR2列,1,2) = 'AB'	・関数、演算を右辺（インデックス列でない方）に移動する　　　　VARCHAR2列 = '○○' 　　　　NUMBER列 = 10000/20 　　　　VARCHAR2列 LIKE 'AB%' ・関数インデックスを使用する・INDEXヒントを使用する　（インデックス列にNOT NULL制約が必要）
4	LIKEの中間一致、後方一致	列名 LIKE '%TEST%' 列名 LIKE '%TEST'	・INDEXヒントを使用する　（インデックス列にNOT NULL制約が必要）
5	!=、<>の使用（Not Equals）	列名 != 'A' 列名 <> 'A'	・IN で置き換える（可能な場合）　　　　列名 IN ('B', 'C') ・INDEXヒントを使用する　（インデックス列にNOT NULL制約が必要）

AWRレポート出力方法

(1)AWRレポート

SQL> @?/rdbms/admin/awrrpt.sql

(2)AWR期間比較レポート

SQL> @?/rdbms/admin/awrddrpt.sql

(3)AWR SQLレポート

SQL> @?/rdbms/admin/awrsqrpt.sql

※）SQL*PlusでAUTO TRACEを実施したような統計情報が出ない

V$ACTIVE_SESSION_HISTORY取得

以下をSQL*Plus等から実行し、SPOOLでテキストファイルへ落とし込む。

set linesize 10000
set pagesize 1000
set trimspool on
set long 75
col sample_time for a25
col event for a60
col user_name for a20
col sql_text for a80
select
    dhash.inst_id inst_id
,   dhash.sample_time sample_time
,   dhash.session_id
,   dhash.time_waited time_waited
,   dhash.event event
,   dhash.user_id user_id
,   (select username from dba_users where user_id = dhash.user_id) user_name
,   dhash.sql_id sql_id
,   (select sql_text from gv$sqltext where inst_id = dhash.inst_id and sql_id = dhash.sql_id and piece = 0 and rownum = 1) sql_text
,   dhash.p1 p1
,   dhash.p2 p2
,   dhash.p3 p3
from gv$active_session_history dhash
where to_date('yyyymmddhh24mi', 'yyyymmddhh24mi') <= sample_time
  and sample_time < to_date('yyyymmddhh24mi', 'yyyymmddhh24mi')
;

gv$active_session_history は DBA_HIST_ACTIVE_SESS_HISTORY に置き換えてもよい。

v$active_session_history は保存期間が1時間
DBA_HIST_ACTIVE_SESS_HISTORY は保存期間が1週間（デフォルト AWRと同じ）

ログが大量になる場合があるので、ORDER BY句は付加しないほうがよい。
ORDER BY句を付加しなければソートが発生しないので、比較的ログ出力が早く終わる。
また、ソートが発生する場合は、DB側で全てのレコードをソートしてからの出力となるので、
全ての処理が完了しないと出力されないが、
ソートしなければ順次出力されるので、出力の途中経過も見られる。
その場合、sample_time逆順で出力される。