sql - Oracle 慢查询

Question

当我像这样查询我的表时 select * from mytable ，有时(我在 PLSQL developer 或 SQL navigator 中查询表)查询返回结果很快，有时需要 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
********************************************************************************

Answer 1

在第一次找到缓冲区缓存中的所有行(见query部分)，内存IO比磁盘IO快。

query      
---------- 
0          
0         
169076     
-------  

QUERY

Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries

第二次，所需的行不再可用，可能由于老化或某些其他查询所需的空间而刷新，因此 Oracle 进程必须从磁盘中提取所有行(参见 disk 部分)，这比内存IO。当然，第二次查询的大部分时间都花在了 db file scattered read 上。由于缺少查询中引用的表的索引。

disk      
---------- 
0          
0         
169076     
------- 

DISK

Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls