Oracle slow query

Question

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
********************************************************************************

This blog post may be helpful: https://blogs.oracle.com/optimizer/cardinality-feedback — Harshil Doshi, Oct 02 '17 at 00:13
hi can someone knows optimization well take a look at my problem here https://stackoverflow.com/questions/46510329/group-by-and-group-concat-optimization-mysql-query-without-using-main-pk/46517590#46517590 — shareef, Oct 03 '17 at 11:21

atokpas · Answer 1 · 2017-10-09T06:23:56.237

10

At the first time, it finds all the rows in the buffer cache(see query section), memory IO is faster than disk 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

The second time, the required rows are no longer available, might have flushed due to aging or space required by some other queries, hence the Oracle process has to pull all the rows from disk(see under disk section) which is slower than memory IO. And of course, the second time the query has spent most of the time on db file scattered read due to missing index on the table referenced in the query.

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

DISK

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

edited Oct 09 '17 at 06:23

answered Sep 18 '17 at 05:41

atokpas

3,231
1
11
22

This fluctuation occurs frequently between queries repeatedly.I mean after opening a session (For example a sql window of plsql developer) I run the query and it completed immediately and I run the query again in that session but this time query takes 25 seconds to complete. database has sufficient cache and I don't think blocks of table ages out of cache it this time. – mohsen.b Sep 18 '17 at 06:24
@mohsen.b You can use segment statistics to determine what object the scattered reads were incurred on. – David Aldridge Sep 18 '17 at 08:00
My question: why my query 1 time reads the data from cache and next time reads from disk and so on. I have sufficient cache. – mohsen.b Sep 18 '17 at 08:36
if you are going to get all the rows from the table every time and the table is not too big, consider pinning it to the buffer cache: 1- set the appropriate size for the keep_cache: alter system set db_keep_cache_size = 50m scope=both / 2- put the table in the keep cache: alter table mytable storage( buffer_pool keep) / – Cyrille MODIANO Sep 26 '17 at 13:49
usually query of tables even a big table in third party tools like plsql developer (it returns about 50 first records) completes in miliseconds. but for me for one of my small tables( for example I named it testtab) it takes about 25 seconds( and some times 1 second).it seems my table is not in normal status. i want to know the cause of it. – mohsen.b Sep 27 '17 at 05:54
1

What is the explain plan of Slow Time query? Does the table have an index in the same schema? What are the values of OPTIMIZER_INDEX_COST_ADJ and DB_FILE_MULTIBLOCK_READ_COUNT? – sandman Sep 27 '17 at 06:29

Oracle slow query

1 Answers1

QUERY

DISK