Oracleリファレンス

2011年3月25日金曜日

自動アナライズの実行時刻を変更する

Oracleは定期的にアナライズをすることにより、実行計画の精度を上げるのが基本です。
10gではGATHER_STATS_JOBという自動アナライズ機能がデフォルトでONになっています。
とはいえ、たびたびアナライズをするとパフォーマンスダウンにつながるため、 GATHER_STATS_JOBは次のオブジェクトに対して実施します。

統計情報を取得していないオブジェクト
統計情報が失効（レコードが10%が変更された）オブジェクト

私の担当Webサイトでは、ジョブスケジューリングの設定変更をしています。
というのは、デフォルト設定のままだと平日22時および土曜日0時にしか実行されないためです。
担当サイトの高負荷時間帯は毎日22～25時なので、負荷の低い毎日14時に実行しています。

まず、GATHER_STATS_JOBがMAINTENANCE_WINDOW_GROUPというジョブグループで動いていることを確認します。

col JOB_NAME for a30
col SCHEDULE_NAME for a40
select JOB_NAME ,SCHEDULE_NAME from DBA_SCHEDULER_JOBS;

JOB_NAME                       SCHEDULE_NAME
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP
GATHER_STATS_JOB               MAINTENANCE_WINDOW_GROUP
FGR$AUTOPURGE_JOB
PURGE_LOG                      DAILY_PURGE_SCHEDULE
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB                MAINTENANCE_WINDOW_GROUP

次に、MAINTENANCE_WINDOW_GROUPのスケジュール内容を確認します。

set pages 10000
set lines 120
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a80
col DURATION for a30
select WINDOW_NAME ,REPEAT_INTERVAL ,DURATION from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          REPEAT_INTERVAL
-------------------- --------------------------------------------------------------------------------
DURATION
------------------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW       freq=daily;byday=SAT,SUN;byhour=0;byminute=0;bysecond=0
+000 48:00:00

設定は次のようにします。

exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=15;byminute=0; bysecond=0');
exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','duration','+000 05:00:00');

exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval','freq=daily;byday=SAT,SUN;byhour=15;byminute=0;bysecond=0');
exec DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','duration','+000 05:00:00');

実行されたかどうかは、スケジューラの履歴で確認できます。
なお、この例はRAC環境なのでインスタンス単位で表示されます。

set pages 10000
set lines 120
col job_name for a25
col status for a10
col START_DATE for a20
col END_DATE for a20
SELECT 
  TO_CHAR(ACTUAL_START_DATE, 'YYYY/MM/DD HH24:MI:SS') AS "START_DATE",
  TO_CHAR(LOG_DATE, 'YYYY/MM/DD HH24:MI:SS') AS "END_DATE",
  JOB_NAME,STATUS,INSTANCE_ID
 FROM DBA_SCHEDULER_JOB_RUN_DETAILS
 WHERE JOB_NAME in ('AUTO_SPACE_ADVISOR_JOB','GATHER_STATS_JOB')
 ORDER BY ACTUAL_START_DATE,JOB_NAME;

 START_DATE           END_DATE             JOB_NAME                  STATUS     INSTANCE_ID
-------------------- -------------------- ------------------------- ---------- -----------
2011/02/23 14:00:02  2011/02/23 14:05:19  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/23 14:00:02  2011/02/23 14:09:55  GATHER_STATS_JOB          SUCCEEDED            2
2011/02/24 14:00:02  2011/02/24 14:05:39  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/24 14:00:02  2011/02/24 14:10:30  GATHER_STATS_JOB          SUCCEEDED            2
2011/02/25 14:00:01  2011/02/25 14:11:04  GATHER_STATS_JOB          SUCCEEDED            1
2011/02/25 14:00:02  2011/02/25 14:05:38  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            2
2011/02/26 14:00:00  2011/02/26 14:09:39  GATHER_STATS_JOB          SUCCEEDED            1
2011/02/26 14:00:00  2011/02/26 14:05:40  AUTO_SPACE_ADVISOR_JOB    SUCCEEDED            1

2011年3月17日木曜日

実行計画（EXPLAIN）を取得する

クエリの実行計画を取得するには2ステップ必要です。

(1)explainしてその結果を保存する
(2)保存結果を表示する

まずexplainすると、その結果はPLAN_TABLEに保存されます。

SQL> explain plan for select user_id from T01_AQUA1_APPITEM_LIMIT1;

Explained.

結果は、標準スクリプトでテーブル内容を表示できます。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4246935464

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |  7421K|    42M|  9167   (2)| 00:01:50 |
|   1 |  TABLE ACCESS FULL| T01_AQUA1_APPITEM_LIMIT1 |  7421K|    42M|  9167   (2)| 00:01:50 |
----------------------------------------------------------------------------------------------

表関数がサポートされている9i（9iR2？）以上は、DBMS_XPLANパッケージでさらに詳細な情報を表示することもできます。
3つ目の引数を'ALL'にするとパラレルクエリやカラム情報も表示します。

SQ> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4246935464

----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |  7421K|    42M|  9167   (2)| 00:01:50 |
|   1 |  TABLE ACCESS FULL| T01_AQUA1_APPITEM_LIMIT1 |  7421K|    42M|  9167   (2)| 00:01:50 |
----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T01_AQUA1_APPITEM_LIMIT1@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "USER_ID"[NUMBER,22]

2011年3月10日木曜日

UNDO表領域を拡張する

表領域の自動拡張設定をしていれば自動的に拡張されますがパフォーマンスが落ちるので適度なサイズにした方がいいです。
注意として次の2つ。

・UNDO表領域のデータファイルは1つだけ
・UNDO表領域は縮小できない（ことが多い）
・UNDO表領域の最大サイズは32GB

まずデータファイル名を確認します。

set pages 10000
set lines 120
col NAME for a40
col bytes for 999,999,999,999
select file#, bytes, name from v$datafile where upper(name) like '%UNDO%';

     FILE#            BYTES NAME
---------- ---------------- ----------------------------------------
         2   11,188,305,920 /DATA/DATABASE/UNDOTBS101.DBF

サイズを拡張します。

ALTER DATABASE DATAFILE '/DATA/DATABASE/UNDOTBS101.DBF' RESIZE 16G;

2011年3月7日月曜日

オブジェクトの依存関係を確認する

ビューが参照しているテーブルをdropすると、そのビューはINVALIDになります。
Oracleはオブジェクト同士の依存関係を管理しているためです。

set pages 10000
set lines 120
col REFERENCED_NAME for a30
col TYPE for a20
col NAME for a30
select referenced_name,type,name from user_dependencies
where referenced_name in ('M01_DOWNLOADITEM','M01_MACHINE')
order by referenced_name,type,name;

実行結果は次の通り。
REFERENCED_NAMEが参照されているオブジェクト、NAMEが影響を受ける(INVALIDになりうる)オブジェクトです。

REFERENCED_NAME                TYPE                 NAME
------------------------------ -------------------- ------------------------------
M01_DOWNLOADITEM               FUNCTION             L_ITEMPUBLISHLOG
M01_DOWNLOADITEM               VIEW                 V_CATEGORYLIST
M01_MACHINE                    VIEW                 V_CATEGORYLIST

共有プールのサイズ変更履歴を確認する

例えばWebリクエストと分析系バッチなど、特性が違いすぎる処理を実行しているとします。
自動共有メモリ管理（ASMM）を使っている場合、Oracleは共有プールとデータベースバッファの最適化に迷ってしまうことがあります。
特性の異なる最適化を繰り返してしまうのは、本当の意味で最適化とは言えません。

メモリ（SGA）のリサイズ結果を判断の1つにすることができます。

set time on 
set pages 10000
set lines 120
col component for a25
col oper_type for a12
col parameter for a25
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select component,oper_type,parameter,modsize,end_time from (
  select row_number() over (order by end_time desc) as rn,
    component,oper_type,parameter,(final_size-initial_size) as modsize,end_time
  from v$SGA_RESIZE_OPS
) where rn<=20;

実行結果は次の通り。 OPER_TYPEは増減（GROW/SHRINK）です。頻度や量（MODSIZE）を確認するといいと思います。

COMPONENT                 OPER_TYPE    PARAMETER                    MODSIZE END_TIME
------------------------- ------------ ------------------------- ---------- -------------------
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 04:14:31
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 04:14:31
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 03:01:26
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 03:01:26
DEFAULT buffer cache      SHRINK       db_cache_size             -100663296 2011/03/04 18:57:11
shared pool               GROW         shared_pool_size           100663296 2011/03/04 18:57:11
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/04 04:32:11
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/04 04:32:11
DEFAULT buffer cache      SHRINK       db_cache_size             -201326592 2011/03/03 16:12:45
shared pool               GROW         shared_pool_size           201326592 2011/03/03 16:12:45

REDOログスイッチの完了時刻を確認する

更新処理によりREDOログファイルがいっぱいになると、REDOログファイルはローテートされます
これをログスイッチと呼びます。
1時間に1度を目処に考えていますが、頻繁に発生するとパフォーマンスが落ちるのでチューニングが必要です。

ログスイッチのタイミングはアラートログから調べることができます。

$ grep -B1 "LGWR switch" $ORACLE_BASE/admin/sss01/bdump/alert_sss011.log | tail -1000 | grep "Mar  6"

Sun Mar  6 00:29:08 2011
Sun Mar  6 02:07:04 2011
Sun Mar  6 02:07:43 2011
Sun Mar  6 07:31:04 2011
Sun Mar  6 07:32:33 2011
Sun Mar  6 13:57:04 2011
Sun Mar  6 13:58:38 2011
Sun Mar  6 18:57:06 2011
Sun Mar  6 18:58:25 2011

ログスイッチはロックによる待機を最小限にするため、次の順に処理します。

1)REDOログファイルのローテート
2)古いREDOログファイルをアーカイブREDOログファイルとして書き出す（アーカイブログモード）

アーカイブログモードでログスイッチが高頻度すぎると、ディスクI/Oが発生してパフォーマンスが低下します。
アーカイブREDOログファイルへの書き出しが完了しないと、そのREDOファイルは次回REDOログとして利用できません。
最悪の場合、REDOログファイルのローテートが1周してもアーカイブREDOログファイルへの書き出しが完了しません。
そうなると、利用出来るREDOログがなくなるため、ローテート待ちでデータベースがロックします。

注意として、アラートログの時刻はローテートが完了した時刻です。
アーカイブREDOログの書き出し完了時刻もチェックするといいと思います。

set pages 10000
set lines 100
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select SEQUENCE#,COMPLETION_TIME from (
  select row_number() over (order by COMPLETION_TIME desc) as rn,
    SEQUENCE#, COMPLETION_TIME from v$archived_log
) where rn<=10

 SEQUENCE# COMPLETION_TIME
---------- -------------------
      2750 2011/03/07 00:21:59
      2656 2011/03/06 18:58:26
      2749 2011/03/06 18:58:25
      2748 2011/03/06 18:58:24
      2655 2011/03/06 18:58:22
      2747 2011/03/06 13:58:39
      2654 2011/03/06 13:58:39
      2746 2011/03/06 13:58:36
      2653 2011/03/06 13:58:32
      2652 2011/03/06 07:32:34

2011年3月4日金曜日

システム権限とオブジェクト権限を確認する

Oracleにはシステム権限とオブジェクト権限の2種類あります。
権限をロールとしてひとまとめにすることもでき、ロールにロールを付与することもできます。
注意として、権限だけではデフォルト表領域以外は、スキーマの表領域へのアクセス制限を確認する設定が必要です。
また、grantはロールと権限を混在して記述できません。
もっとも注意しなければならないのは、ストアド内部ではロールの権限は無視されることです。

スキーマやロールに付与したシステム権限を確認します。

set pages 10000
set lines 100
col grantee for a20
col privilege for a30
select grantee,privilege from dba_sys_privs
where grantee='SSS01DBUSER'
order by grantee,privilege;

GRANTEE              PRIVILEGE
-------------------- ------------------------------
SSS01DBUSER          ALTER ANY TABLE
SSS01DBUSER          CREATE ANY INDEX
SSS01DBUSER          CREATE ANY MATERIALIZED VIEW
SSS01DBUSER          CREATE ANY PROCEDURE
SSS01DBUSER          CREATE ANY SEQUENCE
SSS01DBUSER          CREATE ANY TABLE
SSS01DBUSER          CREATE ANY TRIGGER
SSS01DBUSER          CREATE ANY VIEW
SSS01DBUSER          CREATE PROCEDURE
SSS01DBUSER          CREATE SEQUENCE
SSS01DBUSER          CREATE SESSION
SSS01DBUSER          CREATE TABLE
SSS01DBUSER          CREATE TRIGGER
SSS01DBUSER          DELETE ANY TABLE
SSS01DBUSER          DROP ANY INDEX
SSS01DBUSER          DROP ANY MATERIALIZED VIEW
SSS01DBUSER          DROP ANY PROCEDURE
SSS01DBUSER          DROP ANY SEQUENCE
SSS01DBUSER          DROP ANY TABLE
SSS01DBUSER          DROP ANY TRIGGER
SSS01DBUSER          DROP ANY VIEW
SSS01DBUSER          EXECUTE ANY PROCEDURE
SSS01DBUSER          GLOBAL QUERY REWRITE
SSS01DBUSER          INSERT ANY TABLE
SSS01DBUSER          RESUMABLE
SSS01DBUSER          SELECT ANY SEQUENCE
SSS01DBUSER          SELECT ANY TABLE
SSS01DBUSER          UPDATE ANY TABLE

スキーマやロールに付与したロールを確認します。

set pages 10000
set lines 100
col grantee for a20
col granted_role for a20
select grantee,granted_role from dba_role_privs
where grantee='SSS01DBUSER'
order by grantee,granted_role;

GRANTEE              GRANTED_ROLE
-------------------- --------------------
SSS01DBUSER          SSS01APP
SSS01DBUSER          CONNECT

スキーマやロールに付与したオブジェクト権限を確認します。

set pages 10000
set lines 100
col grantee for a20
col owner for a10
col table_name for a30
col privilege for a20
select grantee,owner,table_name,privilege from dba_tab_privs
where grantee like 'SSS01DBUSER'
order by grantee,table_name,privilege;

GRANTEE      OWNER      TABLE_NAME      PRIVILEGE
------------ ---------- --------------- -----------
SSS01DBUSER  SYS        DBMS_LOCK       EXECUTE

ログインしているスキーマのシステム権限を確認します。

set pages 10000
set lines 100
col privilege for a30
select privilege from user_sys_privs order by privilege;

登録: 投稿 (Atom)

このブログを検索

2011年3月25日金曜日

2011年3月17日木曜日

2011年3月10日木曜日

2011年3月7日月曜日

2011年3月4日金曜日