一時表領域に関するチューニングメモ - 闘うITエンジニアの覚え書き

一時表領域に関するチューニングメモ †

◆チューニング指針
1. メモリソート率を上げる(ディスクソート率を下げる)
2. ディスクソート時に使用される一時表領域の割り当てを変更する
　2.1 用途によってDBユーザを分け、それぞれのユーザ毎に一時表領域を割り当てる
　2.2 同じスキーマのオブジェクトを参照できるように、一方のユーザに権限付与、
　　　及びシノニムの作成を行う(テーブル、シーケンス等)

(参照URL)
http://www.atmarkit.co.jp/fdb/rensai/orasql04/orasql04_2.html
http://www50.tok2.com/home/oppama/oracle-gold-pt.html
http://perfopy.ashisuto.co.jp/samples/atmarkit/atmarkit_part_shorttime6.html
http://otn.oracle.co.jp/forum/thread.jspa?threadID=3001701
http://www.insight-tec.com/mailmagazine/ora3/vol036.html
http://www.insight-tec.com/mailmagazine/ora3/vol044.html
http://www.keynus.co.jp/~uhara/dbms/oracle/size_mng.pdf

===============================================================
1. メモリソート率を上げる(ディスクソート率を下げる)
===============================================================

/*
 共有サーバーか専用サーバーかを確認する
 */
SELECT CASE WHEN SERVER = 'SHARED' THEN '共有サーバー'
            WHEN SERVER = 'DEDICATED' THEN '専用サーバー'
            ELSE '？接続'
       END CASE
  FROM V$SESSION 
 WHERE USERNAME = 'DAISUKE'

/* メモリソート率を求める
  sorts (memory)  メモリー上でソートした回数
  sorts (disk)    ディスク上でソートした回数
  sorts (rows)    ソートされた行の合計数
  ※下記SQLで得られるのは,ORACLEが起動してからのメモリーソート率
  ※メモリーソート率が90%以下の場合は初期化パラメータを調整
*/
SELECT A.VALUE * 100 / (A.VALUE+B.VALUE) MEM_SORT_RATE
 FROM V$SYSSTAT A,V$SYSSTAT B
WHERE A.NAME='sorts (memory)' AND B.NAME='sorts (disk)';

/*
  workarea_size_policy を調べる
*/
select value "workarea_size_policy"
  from V$PARAMETER where name = 'workarea_size_policy'

/*
 共有サーバーの場合、または workarea_size_policy が
 MANUAL の場合は 初期化パラメータ:SORT_AREA_SIZE を調整する
*/

/*
 専用サーバーの場合、または workarea_size_policy が
 AUTO の場合は 初期化パラメータ:PGA_AGGREGATE_TARGET を調整する
*/

　（参照）
　　http://otndnld.oracle.co.jp/document/products/database/oracle9i/901/generic/server/J04110-01.pdf
　　自動PGAメモリー管理　449 / 706

　　http://otn.oracle.co.jp/products/oracle9i/daily/020.html

===============================================================
2. ディスクソート時に使用される一時表領域の割り当てを変更する
===============================================================

　---------------------------------------------------------------
　 2.1 用途によってDBユーザを分け、それぞれのユーザ毎に一時表領域を割り当てる
　---------------------------------------------------------------

　---------------------------------------------------------------
　 2.2 同じスキーマのオブジェクトを参照できるように、一方のユーザに権限付与、及びシノニムの作成を行う
　 ※テーブル、シーケンス等
　---------------------------------------------------------------

/* 現在使用している一時表領域の確認 */
select TEMPORARY_TABLESPACE from USER_USERS;

/* db_block_size, sort_area_size の確認 */
select name, value, value/1024 || 'k' FROM V$PARAMETER where name in ('db_block_size', 'sort_area_size');

/* 必要サイズの算出(initial) 
  ※db_block_size + n * sort_area_size (ここではn=1とした )
*/
select sum(val) "initial_size(単位:K)" from (
	select value/1024 val FROM V$PARAMETER where name = 'db_block_size'
	union
	select value/1024 * 1 val FROM V$PARAMETER where name = 'sort_area_size'
);

/* 必要サイズの算出(datafile)
  ※DBMS_SPACEパッケージを使用した方がよいかも
*/
select max(table_size)/1024 "datafile_size(単位:K)" from (
	select table_name, num_rows * avg_row_len table_size from user_tables
);

/* 一時表領域の作成 */
create tablespace 一時表領域名
datafile '/xxxx/xxxxx/xxxxx/xxxxx.ora' size 算出したdatafileサイズ * 2
default storage (initial 算出したinitialサイズ next 算出したinitialサイズ pctincrease 0) temporary;

/* ユーザの一時表領域を変更する */
ALTER USER ユーザ名 TEMPORARY TABLESPACE 一時表領域名;