oracle v$sql 慢查询,sql – Oracle慢查询

当我查询我的表就像这样select * from mytable,有时(我查询PLSQL开发人员或SQL导航器中的表)查询返回结果很快,有时需要25-26秒.当然,这不会影响业务交易的表现.

我追踪了两种状态并给出了以下结果：

快速时间：

select *

from

mytable

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.64 1.14 0 169184 0 100

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

total 3 0.64 1.14 0 169184 0 100

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

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

SQL*Net more data to client 40 0.00 0.00

SQL*Net message from client 2 0.00 0.00

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

慢时间：

select *

from

mytable

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 2.91 23.74 169076 169184 0 100

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

total 3 2.91 23.74 169076 169184 0 100

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

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

SQL*Net more data to client 40 0.00 0.00

SQL*Net message from client 2 0.00 0.00

db file scattered read **10686** 0.29 20.20

db file sequential read 6 0.00 0.01

latch: object queue header operation 1 0.00 0.00

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