ORA-01555错误浅析(3)

2 rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",

3 rs.Shrinks "# Shrinks", rs.Extends "# Extends"

4 from sys.v_$RollName rn, sys.v_$RollStat rs

5 where rn.usn = rs.usn;

Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------

SYSTEM 952 1189 0 0 0 0

RBS_BATCH1 409592 681422 667 .097883544 0 0

... ...

RBS10 204792 478502 10 .002089855 0 0

RBS11 204792 477366 8 .001675863 0 0

RBS12 204792 491070 6 .001221822 0 0

RBS_BATCH2 409592 650088 644 .099063511 0 0

RBS_BATCH3 409592 243849 3 .00123027 0 0

16 rows selected.

注意到RBS_BATCHT1的wait%是0.098%，这个值应该是比较好的一个值。

回过头再来看依法错误的语句：调用ext_pkg.main函数。在程序日志中已经记录下了输入参数，这就比较好办了：作一个trace，看看到底哪条语句的性能最差：

SQL> alter system set events ‘10046 trace name context forever, level 1’;

System altered.

SQL> exec ext_pkg.main('extfiles','50','EAT');

PL/SQL procedure successfully completed.

SQL> alter system set events ‘10046 trace name context off’;

System altered.

用tkprof处理trace文件后检查trace文件：