When I query my table just like this select * from mytable, sometimes (I query the table in PLSQL developer or SQL navigator) query returns results fast and sometimes it takes 25-26 seconds. Of course, this doesn't affect the performance of business transactions.
I traced both status and it gave below results:
Fast Time:
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
********************************************************************************
Slow Time:
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
********************************************************************************