Friday, July 31, 2009

Function-Based IndexとOr-Expansionの制約

下のリンクに悲しい話があります。


http://forums.oracle.com/forums/message.jspa?messageID=3661603


  1. Standardエディションを使っているし
  2. Standardエディションは1)Bitmap Indexとその兄弟と言える2)Index Combinationを支援しません。
  3. Function-based Indexを使っているし
  4. OR Predicateを使っています。
  5. 一番大きい問題はSQL文章を変えることができないと言うことです。Softwareに含まれているQueryと言う理由で。。。


私がなぜこれを悲しい話と言うのかは下のデモを見れば理解できるんでしょう。


1. オブジェクトを作ります。


drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1
select level, level, level
from dual
connect by level <= 100000;

create index t1_n1 on t1(c1+1); -- function-based index
create index t1_n2 on t1(c2+1); -- function-based index
create index t1_n3 on t1(c1); -- normal index
create index t1_n4 on t1(c2); -- normal index

exec dbms_stats.gather_table_stats(user, 't1');


2.Index Combinationが適用できる場合は次のようにとても効率的な実行計画を作ります。

alter session set "_b_tree_bitmap_plans" = true;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 48 | 2 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS | | | | |
| 3 | BITMAP OR | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 5 | INDEX RANGE SCAN | T1_N1 | | | 1 (0)|
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 7 | INDEX RANGE SCAN | T1_N2 | | | 1 (0)|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("C1"+1=1)
7 - access("C2"+1=1)



3. でも、Index Combinationが非活性化すれば?

alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 99 (6)|
|* 1 | TABLE ACCESS FULL| T1 | 2 | 48 | 99 (6)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C1"+1=1 OR "C2"+1=1)


驚くべきことにFull Table Scanを選択してしまいます。


4.Function-based Indexではない一般Indexの場合には次善の策でOr-Expansionを選択します。これだけでも十分に効率的です。


explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30 | 4 (0)|
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T1_N4 | 1 | | 1 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 5 | INDEX RANGE SCAN | T1_N3 | 1 | | 1 (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("C2"=1)
4 - filter(LNNVL("C2"=1))
5 - access("C1"=1)


5. では、次のようにヒントを通じて完璧に制御すればどうでしょうか。

explain plan for
select
/*+
INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" "T1_N1")
INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" "T1_N2")
USE_CONCAT(@"SEL$1" 8) */
*
from t1
where c1+1 = 1 or c2+1 = 1
;

----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 195 (4)|
| 1 | CONCATENATION | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 24 | 98 (5)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 24 | 98 (5)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("C2"+1=1)
3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))


やはり無駄です。


なぜこんな現状が起きるんでしょうか。下の文章によく説明されています。

http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464


불행하게도 Oracle은 Or-Expansion과 Funtion-based Index를 같이 사용하지 못합니다. 따라서 이 경우에는 Full Table Scan이 가장 효율적인 실행 계획이 되어 버린 셈입니다.
すなわち、オラクルはOr-ExpansionとFunction-based Indexを一緒に使えません。従って、この場合にはFull Table Scanが一番効率的な実行計画になってしまいました。


6. 試しできるトリックの一つは手動でStored Outlineを作るのです。でも、この場合にはそれさえも不可能です。ヒントで制御できないからです。


7. 最後のトリックはAdvanced Query Rewriting機能を使ってSQL文章自体を変えてしまうということです。ただし、この機能は次のような制約を持ってあります。


  • やはりEnterpriseエディション
  • Select文章だけ支援します。
  • バインド変数は支援しません。

上の簡単なデモの場合にはこの機能を使って解決できません。

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
name => 'rewrite1',
source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
validate => false,
rewrite_mode => 'text_match');
end;
/

alter session set query_rewrite_integrity = trusted;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30 | 4 (0)|
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T1_N4 | 1 | | 1 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 5 | INDEX RANGE SCAN | T1_N3 | 1 | | 1 (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("C2"=0)
4 - filter(LNNVL("C2"=0))
5 - access("C1"=0)

しかし、実際の本番環境では使用するのにはあまり制約が多いと思います。

Thursday, July 30, 2009

Function Based Indexと怪しいFilter Predicate

次のような面白い質問をいただきました。

1.
次の結果をご覧ください。特に索引範囲スキャンのFilter Predicate部分に注意してください。

drop table t1 purge;

create table t1 (
c1 varchar2(10),
c2 varchar2(10),
c3 varchar2(10),
c4 varchar2(10),
c5 varchar2(10)
);

insert into t1
select mod(level, 2), mod(level, 2), mod(level, 2), mod(level, 2), level
from dual
connect by level <= 1000
;

exec dbms_stats.gather_table_stats(user, 't1');

create index t1_n1 on t1(c1, c2, nvl(c3,'x'), c4);

explain plan for
select /*+ index(t1 t1_n1) */
*
from t1
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)|
|* 2 | INDEX RANGE SCAN | T1_N1 | 4 | | 2 (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C5"=:B5)
2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
NVL("C3",'x') IS NOT NULL)
filter("C4"=:B4)

これまではどんな問題もありません。範囲PredicateのNVL("C3",'x')>=:B3のおかげで"C4"=:B4条件がFilter Predicateで使われました。

2. いま二番目のFunction Based Indexを生成します。次の結果を見てください。変なFilter Predicateが付いているのを気づきましたか - SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3)。そして、このPredicateのかげで予測行件数が4から1に変わってしまいました。


create index t1_n2 on t1(c1, c2, substr(c4,1,3));

explain plan for
select /*+ index(t1 t1_n1) */
*
from t1
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)|
|* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 2 (0)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C5"=:B5)
2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
NVL("C3",'x') IS NOT NULL)
filter("C4"=:B4 AND SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3))

SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3)だと? このPredicateがどうして追加されたのでしょうか。なぜオラクルが二番目の索引が含んでいる表現を一番目の索引に使用しているのでしょうか。

多分もう正答が分かっているでしょう。はい、Function Based IndexはHidden列を作ります


3. 10053トレースに対してDiffをしてみれば手軽にその違いが分かることができます。


-- With 1 function index -- With 2 function indexes
SINGLE TABLE ACCESS PATH | SINGLE TABLE ACCESS PATH
Column (#1): C1(VARCHAR2) | Column (#1): C1(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#2): C2(VARCHAR2) | Column (#2): C2(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#4): C4(VARCHAR2) | Column (#7): SYS_NC00007$(VARCHAR2) NO STATISTICS
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 6.00 NDV: 0 Nulls: 0 Density: 0.0000e+00
Column (#5): C5(VARCHAR2) | Column (#4): C4(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#6): SYS_NC00006$(VARCHAR2) NO STAT| Column (#5): C5(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Table: T1 Alias: T1 | Column (#6): SYS_NC00006$(VARCHAR2) NO STATISTICS
Card: Original: 0 Rounded: 1 Computed: 0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
kkofmx: index filter: | Table: T1 Alias: T1
"T1"."C4"=:B1 AND | Card: Original: 0 Rounded: 1 Computed: 0.00 N
"T1"."C5"=:B2 AND | kkofmx: index filter:
NVL("T1"."C3",'x')>=:B3 | SUBSTR("T1"."C4",1,3)=SUBSTR(:B1,1,3) AND
Access Path: index (IndexOnly) | "T1"."C4"=:B2 AND "T1"."C5"=:B3 AND
Index: T1_N1 | NVL("T1"."C3",'x')>=:B4
resc_io: 0.00 resc_cpu: 200 | kkofmx: index filter:
ix_sel: 9.0000e-007 ix_sel_with_filters: | "T1"."C4"=:B1 AND
Cost: 0.00 Resp: 0.00 Degree: 1 | "T1"."C5"=:B2 AND
Best:: AccessPath: IndexRange Index: T1_N1 | NVL("T1"."C3",'x')>=:B3
Cost: 0.00 Degree: 1 Resp: 0.00 Ca| Access Path: index (IndexOnly)
| Index: T1_N1
| resc_io: 0.00 resc_cpu: 200
| ix_sel: 9.0000e-007 ix_sel_with_filters: 9.0000
| Cost: 0.00 Resp: 0.00 Degree: 1
| Best:: AccessPath: IndexRange Index: T1_N1
| Cost: 0.00 Degree: 1 Resp: 0.00 Card: 0.
|


二番目のFunction Based Indexによって、SYS_NC00007$(SUBSTR("T1"."C4",1,3))が作られたのがわかります。一番目の索引がT1.C4列を含んでいるから、この条件がFilter Predicateで使われたのです。

一般的には、この現状は問題になりません。しかし、予測行件数が低くなるから、特定な状況では問題を起こす可能性もあります。

Monday, July 27, 2009

ORA-4030と遊ぶこと

ORA-4030エラーは昔からよく知られているもので、今頃なら初級DBAとしてもよほど深い知識を持っていなければならないはずです。でも、現実は私の期待とは違います。これが私がこの記事を書いている理由です。ORA-4030エラーをより楽しく扱えるように助けてくれるのがこの文の目的です。

まず、この定義をご覧ください。

04030
"out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory has been exhausted
// *Action:

オラクルははっきりとORA-40404エラーがOSのプロセスメモリーの問題だと宣言しています。でも、これは五十パーセントだけの真実だと思います。

私が知る限り、ORA-4030の原因には普通三つぐらいがあります。

  1. OSのメモリー設定が低い。
  2. メモリーリークバグがいる。
  3. アプリがあまり多いオブジェクトを割り当てする。

Unixシステムでは、低いメモリー設定がORA-4030エラーを起こす場合があります。これに対する手軽な対応は設定値を高めることです。Unlimited値がよく使われます。

prompt> ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory       (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited


Windows32システムではプロセスメモリーの最高値は1.5Gぐらいです。フィジカルRAMサイズとは関係なく、プロセスメモリーのサイズはこのサイズ以上の値を取ることができません。こんな理由で、Windows64システムをお勧めする場合がたくさんあります。

大きいサイズのメモリーを使えるように設定したにもかかわらずORA-4030エラーが続けば、原因は異なる二つにあります。1)オラクルのバグ、2)アプリが多すぎるオブジェクトを生成しているかも知れません。

もっと詳細な分析のために、一番自然ながら易しい方法はPGAヒープダンプです。

PGAヒープダンプを実行する方法を説明する前に、ORA-4030エラーが正確にいつ発生するのかを簡単な例で紹介して行きます。
1. 次のように最小のPGA設定値を持っています。

alter system set "_pga_max_size" = 15000000;
alter system set pga_aggregate_target=50m;


2.PL/SQLブロックサイズをPGAの最大サイズより大きく設定しています。ORA-4030エラーは発生しましょうか。

-- case1. this does not cause 4030
set serveroutput on
spool temp.sql

begin
dbms_output.put_line('declare');
for idx in 1 .. 8000 loop
dbms_output.put_line(' v' || idx || ' varchar2(4000) := rpad(''x'',4000);');
end loop;
dbms_output.put_line('begin');
dbms_output.put_line('null;');
dbms_output.put_line('end;');
dbms_output.put_line('/');
end;
/

spool off
@temp

v91 varchar2(4000) := rpad('x',4000);
*
ERROR at line 92:
ORA-06550: line 1753, column 30:
PLS-00123: program too large (Diana nodes)

NAME VALUE
------------------------------ ----------------
session pga memory 2,022,996
session pga memory max 2,022,996

答えはNoです。オラクルはPL/SQLブロックサイズをPGAの最大サイズ以下に制限しています。

3.1.2GサイズのLOBオブジェクトを割り当てしています。ORA-4030エラーは発生しましょうか。

declare
v1 clob;
begin
for idx in 1 .. 1200000 loop
v1 := v1 || rpad('x', 1000, 'x');
end loop;
end;
/

NAME VALUE
---------------------------------------- ----------------
session pga memory 2,547,284
session pga memory max 5,627,476

PL/SQL procedure successfully completed.

今度も答えはNOです。LOBオブジェクトはPGAのサイズをを起こさないように具現しています。ソートに対しても同じ方式で動作します。2Gサイズのデータを15MのPGAでソートするのはもちろん性能が低いのは当然ですけど、ORA-4030エラーは絶対に発生しません。

4. 次のようなトリックはどうでしょうか。ORA-4030エラーは発生しましょうか。

create or replace procedure proc_rec(depth number)
is
v1 varchar2(1000) := rpad('x',1000);
v2 varchar2(1000) := rpad('x',1000);
v3 varchar2(1000) := rpad('x',1000);
v4 varchar2(1000) := rpad('x',1000);
v5 varchar2(1000) := rpad('x',1000);
v6 varchar2(1000) := rpad('x',1000);
v7 varchar2(1000) := rpad('x',1000);
v8 varchar2(1000) := rpad('x',1000);
v9 varchar2(1000) := rpad('x',1000);
v10 varchar2(1000) := rpad('x',1000);
begin
if depth > 0 then
proc_rec(depth - 1);
end if;
end;
/

UKJA@ukja102> exec proc_rec(100);

NAME VALUE
------------------------------ ----------------
session pga memory 2,940,500
session pga memory max 2,940,500

Elapsed: 00:00:00.00

UKJA@ukja102> exec proc_rec(10000);

NAME VALUE
------------------------------ ----------------
session pga memory 111,140,436
session pga memory max 124,575,316

UKJA@ukja102> exec proc_rec(20000);

NAME VALUE
------------------------------ ----------------
session pga memory 220,323,412
session pga memory max 248,307,284

メモリー使用量がPGAの最大サイズを軽く超えるのが分かります。複雑なアプリがこのようなパターンに会えばORA-4030エラーが発生する可能性があると予想できます。

5. PL/SQLコレクションもPGAの最大サイズを超えることができます。

create or replace procedure proc_array(len number)
is
type vtable is table of varchar2(1000);
vt vtable := vtable();
begin
for idx in 1 .. len loop
vt.extend;
vt(idx) := rpad('x',1000,'x');
end loop;
end;
/
UKJA@ukja102> exec proc_array(10000);

NAME VALUE
------------------------------ ----------------
session pga memory 221,896,276
session pga memory max 248,307,284

UKJA@ukja102> exec proc_array(10000);

NAME VALUE
------------------------------ ----------------
session pga memory 220,192,340
session pga memory max 364,961,364

UKJA@ukja102> exec proc_array(1200000);

ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu
call ,pl/sql vc2)

NAME VALUE
------------------------------ ----------------
session pga memory 219,668,052
session pga memory max 1,405,476,436

Windows32システムではプロセスメモリーの最大は1.5Gぐらいです。従って、1,405,476,436でORA-4030エラーガ発生していることです。

6. 一番重要なものはORA-4030エラーが発生した時どのように対応すればいいのかということです。私の一番好きな方法はPGAヒープダンプです。

-- This time
alter session set events
'4030 trace name heapdump level 0x20000001, lifetime 1';

レベル0x20000001のヒープダンプに関してはここに説明しています。

次のステップはトレースファイルをサマリーして、リポートを作成することです。

UKJA@ukja102> @heap_analyze ukja10_ora_5728.trc


HEAP_NAME HSZ
-------------------- ----------
pga heap 1,362.7
koh-kghu call 1,022.8
top uga heap .2
session heap .2
top call heap .1
PLS non-lib hp .0
qmtmInit .0
Alloc environm .0
KSFQ heap .0
Alloc server h .0
koh-kghu sessi .0
callheap .0

12 rows selected.

Elapsed: 00:00:00.09

HEAP_NAME CHUNK_TYPE CNT SZ HSZ HRATIO
-------------------- --------------- -------- ---------- ---------- ------
Alloc environm freeable 3 .0 .0 80.9
Alloc environm recreate 1 .0 .0 14.6
Alloc environm perm 2 .0 .0 4.5
Alloc server h free 6 .0 .0 94.3
Alloc server h perm 2 .0 .0 3.1
Alloc server h freeable 2 .0 .0 2.6
KSFQ heap perm 2 .0 .0 100.0
PLS non-lib hp freeable 12 .0 .0 75.1
PLS non-lib hp free 6 .0 .0 15.9
PLS non-lib hp perm 2 .0 .0 9.0
callheap free 6 .0 .0 77.1
callheap perm 2 .0 .0 22.9
koh-kghu call freeable 65,415 1,022.8 1,022.8 100.0
koh-kghu sessi freeable 4 .0 .0 100.0
pga heap freeable 65,453 1,024.1 1,362.7 75.2
pga heap free 43,616 338.4 1,362.7 24.8
pga heap recreate 6 .0 1,362.7 .0
pga heap perm 28 .2 1,362.7 .0
qmtmInit freeable 12 .0 .0 69.1
qmtmInit free 8 .0 .0 30.9
session heap perm 2 .1 .2 36.8
session heap freeable 333 .1 .2 33.6
session heap free 14 .0 .2 23.0
session heap recreate 8 .0 .2 6.6
top call heap free 2 .1 .1 93.5
top call heap perm 2 .0 .1 .1
top call heap freeable 1 .0 .1 3.1
top call heap recreate 2 .0 .1 3.3
top uga heap recreate 1 .1 .2 33.3
top uga heap free 6 .1 .2 33.4
top uga heap freeable 1 .1 .2 33.3

31 rows selected.

Elapsed: 00:00:00.15

HEAP_NAME OBJ_TYPE CNT SZ HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
Alloc environm 1 .0 .0 17.2
Alloc environm Alloc server h 3 .0 .0 78.4
Alloc environm perm 2 .0 .0 4.5
Alloc server h 8 .0 .0 96.9
Alloc server h perm 2 .0 .0 3.1
KSFQ heap perm 2 .0 .0 100.0
PLS non-lib hp PL/SQL STACK 2 .0 .0 69.3
PLS non-lib hp PLSQL Stack des 2 .0 .0 .2
PLS non-lib hp perm 2 .0 .0 9.0
PLS non-lib hp pl_lut_alloc 1 .0 .0 .4
PLS non-lib hp peihstdep 5 .0 .0 .7
PLS non-lib hp PEIDEF 1 .0 .0 4.2
PLS non-lib hp 6 .0 .0 15.9
PLS non-lib hp pl_iot_alloc 1 .0 .0 .4
callheap 6 .0 .0 77.1
callheap perm 2 .0 .0 22.9
koh-kghu call pl/sql vc2 65,414 1,022.8 1,022.8 100.0 <-- This is it!
koh-kghu call pmucalm coll 1 .0 1,022.8 .0
koh-kghu sessi pl/sql vc2 1 .0 .0 39.9
koh-kghu sessi pliost struct 3 .0 .0 60.1
pga heap KJZT context 1 .0 1,362.7 .0
pga heap external name 1 .0 1,362.7 .0
pga heap KFIO PGA struct 1 .0 1,362.7 .0
pga heap KSFQ heap descr 1 .0 1,362.7 .0
pga heap PLS cca hp desc 1 .0 1,362.7 .0
pga heap KFK PGA 1 .0 1,362.7 .0
pga heap kews sqlstat st 1 .0 1,362.7 .0
pga heap koh-kghu call h 2 .0 1,362.7 .0
pga heap kpuinit env han 1 .0 1,362.7 .0
pga heap joxp heap 2 .0 1,362.7 .0
pga heap kjztprq struct 1 .0 1,362.7 .0
pga heap kopolal dvoid 5 .0 1,362.7 .0
pga heap KSFQ heap 1 .0 1,362.7 .0
pga heap Alloc environm 2 .0 1,362.7 .0
pga heap ldm context 13 .0 1,362.7 .0
pga heap qmtmInit 4 .0 1,362.7 .0
pga heap kgh stack 1 .0 1,362.7 .0
pga heap PLS non-lib hp 3 .0 1,362.7 .0
pga heap Fixed Uga 1 .0 1,362.7 .0
pga heap perm 28 .2 1,362.7 .0
pga heap kzsna:login nam 1 .0 1,362.7 .0
pga heap koh-kghu call 65,415 1,024.1 1,362.7 75.2
pga heap 43,616 338.4 1,362.7 24.8
qmtmInit qmushtCreate 3 .0 .0 44.9
qmtmInit 8 .0 .0 30.9
qmtmInit qmtmltAlloc 6 .0 .0 23.0
qmtmInit qmtmltCreate 3 .0 .0 1.2
session heap perm 2 .1 .2 36.8
session heap 14 .0 .2 23.0
session heap koklug hxctx in 1 .0 .2 .0
session heap koklug hlctx in 1 .0 .2 .0
session heap koddcal dvoid 1 .0 .2 .0
session heap system trigger 1 .0 .2 .0
session heap kxsFrame4kPage 5 .0 .2 12.8
session heap koh-kghu sessio 7 .0 .2 4.9
session heap koh-kghu sessi 6 .0 .2 4.6
session heap kxsc: kkspsc0 12 .0 .2 3.6
session heap kgsc ht segs 266 .0 .2 3.2
session heap PLS non-lib hp 2 .0 .2 2.6
session heap kzctxhugi1 1 .0 .2 2.6
session heap kpuinit env han 1 .0 .2 1.0
session heap kgiob 6 .0 .2 .7
session heap kokl lob id has 1 .0 .2 .6
session heap kxs-heap-p 1 .0 .2 .6
session heap kodpai image 1 .0 .2 .6
session heap kxs-krole 7 .0 .2 .4
session heap session languag 1 .0 .2 .3
session heap Session NCHAR l 1 .0 .2 .3
session heap PLS cca hp desc 2 .0 .2 .2
session heap kokl transactio 1 .0 .2 .2
session heap kokahin kgglk 1 .0 .2 .1
session heap kqlpWrntoStr:st 1 .0 .2 .1
session heap kwqidwh memory 2 .0 .2 .1
session heap kwqaalag 2 .0 .2 .1
session heap kgiobdtb 1 .0 .2 .1
session heap kwqb context me 2 .0 .2 .1
session heap kwqica hash tab 2 .0 .2 .1
session heap kwqmahal 2 .0 .2 .1
session heap kodmcon kodmc 1 .0 .2 .0
session heap kzsrcrdi 1 .0 .2 .0
session heap ksulu : ksulueo 1 .0 .2 .0
top call heap perm 2 .0 .1 .1
top call heap callheap 3 .0 .1 6.4
top call heap 2 .1 .1 93.5
top uga heap session heap 2 .1 .2 66.6
top uga heap 6 .1 .2 33.4

86 rows selected.

Elapsed: 00:00:00.15

HEAP_NAME SUBHEAP CNT SZ HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
Alloc environm ds=04F665B8 2 .0 .0 63.7
Alloc environm 4 .0 .0 36.3
Alloc server h 10 .0 .0 100.0
KSFQ heap 2 .0 .0 100.0
PLS non-lib hp 20 .0 .0 100.0
callheap 8 .0 .0 100.0
koh-kghu call 65,415 1,022.8 1,022.8 100.0
koh-kghu sessi 4 .0 .0 100.0
pga heap ds=05003858 65,414 1,024.1 1,362.7 75.2
pga heap 43,683 338.6 1,362.7 24.8
pga heap ds=04F67B34 1 .0 1,362.7 .0
pga heap ds=04F8D9CC 2 .0 1,362.7 .0
pga heap ds=04FE3470 3 .0 1,362.7 .0
qmtmInit 20 .0 .0 100.0
session heap 356 .2 .2 98.7
session heap ds=07F7EAAC 1 .0 .2 1.3
top call heap ds=083B8E20 1 .0 .1 3.1
top call heap 6 .1 .1 96.9
top uga heap 7 .1 .2 66.7
top uga heap ds=05007600 1 .1 .2 33.3

20 rows selected.

Elapsed: 00:00:00.12


(heap_analyze.sqlはここ)
koh-kghu call pl/sql vc2という項目が見えますか。

KOHはKernel Object Heapという意味で、KGHUはKernel Generic Service for Heap Management(U=UGA or User object)という意味です。すなわち、アプリが多すぎるPL/SQL Varchar2オブジェクトを生成していることが分かります。
(Metalinkノート175982.1を見てください)

現実のORA-4030トラブルシューティングは上のような例よりずっと複雑です。でも、このぐらいの知識があればもう少しおそろしさなしにエラーに対応することができるんでしょう。

Friday, July 24, 2009

Stored Outlineが働かない-オラクル11g

仲間の中の一人が次のようなオラクル11gの非正常的なStored Outlineに対するテスト結果を送りました。

1. オブジェクトを作ります。

create table t1(c1 int, c2 int);

-- c1 = skewed, c2 = normal
insert into t1
select 1, level
from dual
connect by level <= 10000
union all
select 2, level
from dual
connect by level <= 1000
union all
select 3, level
from dual
connect by level <= 100
union all
select 4, level
from dual
connect by level <= 10
union all
select 5, level
from dual
connect by level <= 1;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for columns c1 size skewonly');


2. 次のような実行計画を見えています。オラクル10gR2とオラクル11gが同じ実行計画を持っています。オラクルがT1_N1索引を選択したのに注意してください。

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------


3. 現在の実行計画をTEST_OUTLN4のStored Outlineに貯蔵しますし、オラクルがこのOutlineを使うようにします。

create or replace outline test_outln4
on
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;

alter session set use_stored_outlines = true;

TEST_OUTLN4のOutlineは次のようなヒントで構成されています。

select hint from user_outline_hints
where name = 'TEST_OUTLN4';

-- Oracle 10.2.0.1
HINT
--------------------------------------------------
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

-- Oracle 11.1.0.6
HINT
------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS


4. ここから変なことができます。次のように3番目の索引T1_N3を追加します。

-- t1_n3 index (c1, c2)
create index t1_n3 on t1(c1, c2);


5. オラクル10gR2は私が作ったOutlineをよく従います。

explain plan for
select /*+ gather_plan_statistics */ count(*)
from t1
where c1 = 4
and c2 between 1 and 10;



--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
- outline "TEST_OUTLN4" used for this statement


6. しかし、オラクル11gはOutlineを従わなく、さっきの索引T1_N3を選択してしまいます。もっとおかしいものはオラクル自分は私はTEST_OUTLN4をよく使っていると話しているのです。

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| T1_N3 | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- outline "TEST_OUTLN4" used for this statement


7. 理由は?今は私もよくわかりません。でも、10053トレースが少しの追加的な情報を提供します。

オラクル10gR2のトレースは明らかにOutlineによってINDEXヒントを使っていると記録しています。

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 11111 #Blks: 20 AvgRowLen: 6.00
Index Stats::
Index: T1_N1 Col#: 1
LVLS: 1 #LB: 22 #DK: 5 LB/K: 4.00 DB/K: 4.00 CLUF: 21.00
User hint to use this index -- Look at this part!
Index: T1_N2 Col#: 2
LVLS: 1 #LB: 24 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 2116.00
Index: T1_N3 Col#: 1 2
LVLS: 1 #LB: 28 #DK: 11111 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00

でも、オラクル11gのトレースはOutlineによって与えたヒントに対する情報が全然ありません。

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 11111 #Blks: 20 AvgRowLen: 6.00
Index Stats::
Index: T1_N1 Col#: 1
LVLS: 1 #LB: 22 #DK: 5 LB/K: 4.00 DB/K: 4.00 CLUF: 21.00
Index: T1_N2 Col#: 2
LVLS: 1 #LB: 24 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 2116.00
Index: T1_N3 Col#: 1 2
LVLS: 1 #LB: 28 #DK: 11111 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00
Access path analysis for T1


これはバグなのか、もしくは知られない新機能なのかわかりませんけど。。。私には馬鹿なバグに見えますが。。。

Monday, July 20, 2009

オラクル11gのserial全表スキャンに対するdirect path read非活性化しよう。

これはいい知らせです-オラクル11gは全表スキャンでもdirect path readを具現しています。


しかし、私はこのような一方的な機能が好きではないんです。私の願うのはこの機能を自分の必要によって手軽に制御するのです。すなわち、オラクルを私のコントロールの下に置かなければいけません。


それで、いく時間この機能に対して調査をしましたし、Disableさせる方法を見つけました。 オラクル11gはこれをために、10949イベントを新しく追加しました。

UKJA@ukja116> @oerr 10949
10949
"Disable autotune direct path read for full table scan"
// *Cause:
// *Action: Disable autotune direct path read for serial full table scan.
//

次はこのイベントを利用する簡単な例です。

1.充分に大きい表を作ります。最小の大きさは5 * _small_table_thresholdだと知られています。

UKJA@ukja116> col value new_value sth
UKJA@ukja116> @para small_table
old 9: and i.ksppinm like '%&1%'
new 9: and i.ksppinm like '%small_table%'

NAME VALUE IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
--------------------------------------------------------------------------------
_small_table_threshold 637 TRUE true
deferred
threshold level of table size for direct reads

Elapsed: 00:00:00.04
UKJA@ukja116>
UKJA@ukja116> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

Elapsed: 00:00:00.21
UKJA@ukja116> insert into t1
2 select level, 'x', 'x', 'x'
3 from dual connect by level <= 10 + 5*&sth;
old 3: from dual connect by level <= 10 + 5*&sth
new 3: from dual connect by level <= 10 + 5*637

3195 rows created.

Elapsed: 00:00:01.14
UKJA@ukja116> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja116> @gather t1
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68

2. 10949イベントを使用しなかったケースと使用したケースを10046イベントを通じて見比べます。

-- case#1
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from t1;

-- case#2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10949 trace name context forever, level 1';
select count(*) from t1;

3. 10949イベントが活性化した2番目のケースでは以前のバージョンと同じにdb file scattered read待機イベントが観察されます。

SQL ID : 5bc0v4my7dvr5
select count(*)
from
t1

-- Case #1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.02 0.04
db file scattered read 1 0.02 0.02
direct path read 231 0.29 1.67
SQL*Net message from client 2 0.03 0.03


-- Case #2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 213 0.26 1.77
SQL*Net message from client 2 0.00 0.00


予想できなかった性能問題のためにこの立派な機能を使いたくなければ、10949イベントを覚えてください。

Saturday, July 18, 2009

他のセッションを盗み見しよう。

他のセッションによって変更されたパラメータや診断イベントを盗み見する必要が時々あります。

でも、オラクルは公式的な方法を提供していません。すなわち、動的性能ビューからその値を得ることができません。しかし、私たちには万能のツールがあります-oradebug!

セッション#1が次のようにパラメータとイベントを設定します。

-- session #1
alter session set "_optim_peek_user_binds" = false;
alter session set sql_trace = true;

alter session set events '10046 trace name context forever, level 1';
alter session set events '10053 trace name context forever, level 1';


レベル3のErrorstackは変更されたパラメータとイベントを出力します。

-- session #2
connect /as sysdba
oradebug setospid 9768
oradebug dump errorstack 3

トレースファイルは次のようなセクションを含んでいます。

...
Dump event group for SESSION
10053 trace name CONTEXT level 1, forever
10046 trace name CONTEXT level 1, forever
Dump event group for SYSTEM
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
_optim_peek_user_binds = FALSE
_rowsource_execution_statistics= FALSE
...

または、変更されたパラメータだけが必要なら、次のようなコマンドを実行します。

oradebug dump modified_parameters 1

簡単ながらも有効な方法でしょう。

Wednesday, July 15, 2009

UTL_RAW パッケージでブロックダンプをdecodingする

次のブロックダンプの結果を見てみましょう。

block_row_dump:
tab 0, row 0, @0x1f3d
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 <-- RawにEncodingされている!
col 1: [ 2] 58 31 <-- RawにEncodingされている!
tab 0, row 1, @0x1f46
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 2] 58 32
...
tab 0, row 9, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 0b
col 1: [ 3] 58 31 30
end_of_block_dump

上のようにEncodingされた値はブロックダンプを解析する時に大変不便なことになります。でも、UTL_RAWパッケージを適当に使用すればEncodingされたColumnの値を手軽にDecodingできます。

下の例を御覧なさい。

UKJA@ukja102> create table t1(c1 number, c2 varchar2(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> insert into t1
2 select level, 'X'||level
3 from dual
4 connect by level <= 10
5 ;

10 rows created.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> col f# new_value fno
UKJA@ukja102> col b# new_value bno
UKJA@ukja102>
UKJA@ukja102> select dbms_rowid.rowid_relative_fno(rowid) as f#,
2 dbms_rowid.rowid_block_number(rowid) as b#
3 from t1
4 ;

F# B#
---------- ----------
6 836
6 836
6 836
6 836
6 836
6 836
6 836
6 836
6 836
6 836

10 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja102>
UKJA@ukja102> alter system dump datafile &fno block &bno;
old 1: alter system dump datafile &fno block &bno
new 1: alter system dump datafile 6 block 836

UKJA@ukja102> @decode_block_dump T1 <-- 自動化したスクリプト

block_row_dump:
tab 0, row 0, @0x1f3d
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 means C1 = 1
col 1: [ 2] 58 31 means C2 = X1
tab 0, row 1, @0x1f46
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03 means C1 = 2
col 1: [ 2] 58 32 means C2 = X2
tab 0, row 2, @0x1f4f
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04 means C1 = 3
col 1: [ 2] 58 33 means C2 = X3
tab 0, row 3, @0x1f58
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05 means C1 = 4
col 1: [ 2] 58 34 means C2 = X4
tab 0, row 4, @0x1f61
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06 means C1 = 5
col 1: [ 2] 58 35 means C2 = X5
tab 0, row 5, @0x1f6a
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 07 means C1 = 6
col 1: [ 2] 58 36 means C2 = X6
tab 0, row 6, @0x1f73
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 08 means C1 = 7
col 1: [ 2] 58 37 means C2 = X7
tab 0, row 7, @0x1f7c
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 09 means C1 = 8
col 1: [ 2] 58 38 means C2 = X8
tab 0, row 8, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 0a means C1 = 9
col 1: [ 2] 58 39 means C2 = X9
tab 0, row 9, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 0b means C1 = 10
col 1: [ 3] 58 31 30 means C2 = X10
end_of_block_dump

DECODE_BLOCK_DUMP.SQLスクリプトは次のようです。UTL_RAWパッケージを呼び出す部分を注意して見てください。

define __TABLE_NAME = &1

set serveroutput on

declare
v_varchar2 varchar2(4000);
v_number number;
col_idx number;
col_type varchar2(200);
col_name varchar2(100);
col_value varchar2(4000);

begin
for r in (select column_value as txt from table(get_trace_file1)) loop
dbms_output.put(r.txt);
if regexp_like(r.txt, 'col[[:space:]]+[[:digit:]]+:') then

col_idx := regexp_replace(r.txt, 'col[[:space:]]+([[:digit:]])+: [[:print:]]+', '\1');

select column_name, data_type into col_name, col_type
from user_tab_cols
where table_name = upper('&__TABLE_NAME')
and column_id = col_idx+1
;

col_value := replace(regexp_replace(r.txt, 'col[[:space:]]+[[:digit:]]+:[[:space:]]+\[[[:space:]]+[[:digit:]]\][[:space:]]+([[:print:]]+)', '\1'), ' ', '');
if col_type = 'NUMBER' then
--dbms_stats.convert_raw_value(col_value, v_number);
v_number := utl_raw.cast_to_number(col_value);
dbms_output.put(' means ' || col_name || ' = ' || v_number);
elsif col_type = 'VARCHAR2' then
--dbms_stats.convert_raw_value(col_value, v_varchar2);
v_varchar2 := utl_raw.cast_to_varchar2(col_value);
dbms_output.put(' means ' || col_name || ' = ' || v_varchar2);
end if;
end if;

dbms_output.new_line;
end loop;
end;
/

set serveroutput off

(GET_TRACE_FILE1の定義はここ

もう、もっと楽にブロックダンプを解析することができるでしょう。

Tuesday, July 7, 2009

ファイル番号とブロック番号からオブジェクト情報得る

ファイル番号とブロック番号からオブジェクト情報を得たい。

このような簡単な要求が実際には性能については大変なことになってしまいます。例えば、次のような待機イベントがあるとしてみます。

   1: WAIT #6: nam='db file scattered read' ela= 438472 file#=6 block#=2641 blocks=8
   2: WAIT #6: nam='db file scattered read' ela= 1039 file#=6 block#=833 blocks=8 obj#=90054 tim=878243950382
   3: WAIT #6: nam='db file scattered read' ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=878243957168
   4: WAIT #6: nam='db file scattered read' ela= 815 file#=11 block#=7409 blocks=8 obj#=90054 tim=878243966696
   5: ...

P1(ファイル番号)とP2(ブロック番号)からオブジェクトの名前を得たいとします。どうすればいいでしょうか。一番一般的な方法は次のようにDBA_EXTENTSビューを問い合わせて見るのです。でも、その性能はとても悪いです。

   1: UKJA@ukja102> ed which_obj
   2:  
   3: /*
   4: define __FILE = &1
   5: define __BLOCK = &2
   6: 
   7: select segment_name
   8: from dba_extents
   9: where file_id = &__FILE
  10:   and &__BLOCK between block_id and block_id + blocks - 1
  11:             and rownum = 1
  12: ;
  13: 
  14: set echo on
  15: 
  16: */
  17:  
  18: UKJA@ukja102> @which_obj 6 2641
  19:  
  20: SEGMENT_NAME
  21: --------------------
  22: T1_N1
  23:  
  24: Elapsed: 00:02:43.84
  25:  
  26: Statistics
  27: ----------------------------------------------------------
  28:        4676  recursive calls
  29:           2  db block gets
  30:     4077424  consistent gets
  31:        6492  physical reads
  32:           0  redo size
  33:         418  bytes sent via SQL*Net to client
  34:         400  bytes received via SQL*Net from client
  35:           2  SQL*Net roundtrips to/from client
  36:           5  sorts (memory)
  37:           0  sorts (disk)
  38:           1  rows processed

この性能問題を解決ために、いくつの代案が具現されています。 1)DBA_EXTENTSビューに対して集計表を作る、2)X$BHビューをとても速く問い合わせする、3)ブロックダンプをしてそれからオブジェクトIDを取る。

3番目の方法を応用すれば、次のように完全に自動的にオブジェクトIDを迅速に取ることができます。

   1: UKJA@ukja102> ed which_obj2
   2:  
   3: /*
   4: define __FILE = &1
   5: define __BLOCK = &2
   6: 
   7: alter system dump datafile &__FILE block &__BLOCK;
   8: 
   9: set serveroutput on
  10: 
  11: declare
  12:     v_dba        varchar2(100);
  13:     v_type    varchar2(100);
  14:     v_obj_id        number;
  15:     v_obj_name    varchar2(100);
  16: begin
  17:     for r in (select column_value as t from table(get_trace_file1)) loop
  18:         if regexp_like(r.t, 'buffer tsn:') then
  19:             dbms_output.put_line('------------------------------------------------');
  20:             v_dba := regexp_substr(r.t, '[[:digit:]]+/[[:digit:]]+');
  21:             dbms_output.put_line(rpad('dba = ',20)|| v_dba);
  22:         end if;
  23: 
  24:         if regexp_like(r.t, 'type: 0x([[:xdigit:]]+)=([[:print:]]+)') then
  25:             v_type := substr(regexp_substr(r.t, '=[[:print:]]+'), 2);
  26:             dbms_output.put_line(rpad('type = ',20)|| v_type);
  27:         end if;
  28: 
  29:         if regexp_like(r.t, 'seg/obj:') then
  30:             v_obj_id := to_dec(substr(regexp_substr(r.t,
  31:                             'seg/obj: 0x[[:xdigit:]]+'), 12));
  32:             select object_name into v_obj_name from all_objects
  33:                 where data_object_id = v_obj_id;
  34:             dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
  35:             dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
  36:         end if;
  37: 
  38:         if regexp_like(r.t, 'Objd: [[:digit:]]+') then
  39:             v_obj_id := substr(regexp_substr(r.t, 'Objd: [[:digit:]]+'), 7);
  40:             select object_name into v_obj_name from all_objects
  41:                 where data_object_id = v_obj_id;
  42:             dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
  43:             dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
  44:         end if;
  45: 
  46:     end loop;
  47: 
  48:     dbms_output.put_line('------------------------------------------------');
  49: 
  50: end;
  51: /
  52: 
  53: */
  54:  
  55: UKJA@ukja102> @which_obj2 6 2641
  56: old   1: alter system dump datafile &__FILE block &__BLOCK
  57: new   1: alter system dump datafile 6 block 2641
  58:  
  59: System altered.
  60:  
  61: Elapsed: 00:00:00.01
  62: ------------------------------------------------
  63: dba =               6/2641
  64: type =              FIRST LEVEL BITMAP BLOCK
  65: object_id =         90055
  66: object_name =       T1_N1
  67: ------------------------------------------------
  68: PL/SQL procedure successfully completed.
  69:  
  70: Elapsed: 00:00:00.04

2分以上の遅い作業が0.1秒以下の効率的な作業に変わったことをみています。