ORA-01555错误浅析(3)

核心提示： Optimizer goal: CHOOSEParsing user id: 30... ...SELECT QIDFROMCSQUE QUE, ASTP STP WHEREQUE.CDE IN (:B3, :B2) AND QUE.TID =STP.TID AND STP.ACDE =

Optimizer goal: CHOOSE

Parsing user id: 30

********************************************************************************

... ...

SELECT QID

FROM

CSQUE QUE, ASTP STP WHERE

QUE.CDE IN (:B3, :B2) AND QUE.TID =

STP.TID AND STP.ACDE = :B1 ORDER BY

QUE.CDE, QUE.DT

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2682 0.41 0.54 7557 10968 0 2680

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

total 2685 0.41 0.54 7557 10968 0 2680

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 31 (recursive depth: 1)

********************************************************************************

... ...

7 user SQL statements in session.

41 internal SQL statements in session.

48 SQL statements in session.

********************************************************************************

Trace file: prod_ora_29225.trc

Trace file compatibility: 9.02.00

Sort options: default

1 session in tracefile.

7 user SQL statements in trace file.

41 internal SQL statements in trace file.

48 SQL statements in trace file.

20 unique SQL statements in trace file.

8626 lines in trace file.

从trace文件中，发现有一条语句性能相当差，通过对这条语句做SQL Trace，发现它的consistent gets达到80万!

于是对该语句进行优化，调整了它的写法，并建立了缺少的索引(优化过程略)。最终将consistent gets数量降低到了5000。

重新安排上线，经过一周的观察，1555错误没再发生。

其实这个案例的解决是比较简单的，最终的处理就是将一条语句进行优化。

系列文章：

ORA-01555错误浅析(1)

ORA-01555错误浅析(2)