this is my query :

SELECT Count(last_upd)
WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd  +  1/24 > SYSDATE - (1 / (24 * 60));

It takes 84 seconds to return me this result :


I don't understand why it's too much slow for only 43 returned row...

This is the execution plan :

Ecxecution plan

Can anyone help me? this is the V$STATNAME

active txn count during cleanout    1
bytes received via SQL*Net from client  497
bytes sent via SQL*Net to client    14803
calls to get snapshot scn: kcmgss   23
calls to kcmgas 45
cleanout - number of ktugct calls   12
cleanouts and rollbacks - consistent read gets  2
cleanouts only - consistent read gets   10
commit txn count during cleanout    11
consistent changes  450
consistent gets 1458915
consistent gets - examination   458
consistent gets from cache  1458915
CPU used by this session    4485
CPU used when call started  4493
CR blocks created   44
current blocks converted for CR 1
cursor authentications  1
data blocks consistent reads - undo records applied 59
db block changes    12
DB time 8383
dirty buffers inspected 31
enqueue releases    1
enqueue requests    1
execute count   2
free buffer inspected   1442014
free buffer requested   1436612
heap block compress 12
hot buffers moved to head of LRU    25764
immediate (CR) block cleanout applications  12
no work - consistent read gets  1458380
opened cursors cumulative   2
OS Involuntary context switches 18069
OS Maximum resident set size    1956
OS Page faults  4
OS Page reclaims    410
OS System time used 1868
OS User time used   2625
OS Voluntary context switches   63594
parse count (hard)  1
parse count (total) 2
physical read bytes 11768365056
physical read IO requests   55724
physical read total bytes   11768365056
physical read total IO requests 55724
physical read total multi block requests    54675
physical reads  1436568
physical reads cache    1436568
physical reads cache prefetch   1380844
prefetched blocks aged out before use   8
recursive calls 1
redo entries    12
redo size   1272
redo subscn max counts  8
rollbacks only - consistent read gets   40
session logical reads   1458915
session pga memory  -393216
session pga memory max  262144
session uga memory max  299256
shared hash latch upgrades - no wait    4
sorts (memory)  2
sorts (rows)    760
SQL*Net roundtrips to/from client   9
table scan blocks gotten    1458432
table scan rows gotten  16656961
table scans (long tables)   1
transaction tables consistent read rollbacks    4
transaction tables consistent reads - undo records applied  391
user calls  11
user I/O wait time  6172
workarea executions - optimal   5

I'm using Oracle Database 10g Enterprise Edition Release - 64bits

Thank you

  • 1
    Is there any indexes created on the columns used in where clause?
    – atokpas
    Commented Feb 10, 2016 at 17:26

4 Answers 4

  • the query does not return 43 rows but 1 row that contains the number 43
  • the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects.
  • from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table (table scan rows gotten 16656961).
  • for this it has to read about 11G data (physical read total bytes 11768365056) and executes 55,000 read operations (physical read total IO requests 55724). 55,000 reads in 60 seconds (user I/O wait time 6172) means 1.1 ms per read. This is a good value.

You have to decrease the number of row your query has to process. An index may help Oracle to count efficiently the number of rowa you are interested in.

  • The most selective subclause of your where-clause will be the last_upd subclause. So make an index on this column.
  • You should add the columns from the other where-clauses (and the select-clause) to the index, too. So Oracle does not have to lookup each fitting row in that is found by the index in the table to get the last_upd_by and last_name values.
  • The last_upd_by subclause is the next selective clause so it schould be the next column in the index. So you should make an inex on the columns (last_upd, last_upd_by, last_name) in this order.

According to the statistics provided user I/O wait time is 6172 out of db time 8383.

When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


Try creating the following index:

CREATE INDEX s_contact_idx_001
    ON s_contact_lupd_lastupdby_name 
     ( last_upd, last_upd_by, last_name );

You should change the name to correspond to your naming convention and make sure that it is unique.


If your table was ever larger at some point and you did a delete, but did not rebuild the indexes or truncated the table, your queries are going to be slower.

Try rebuilding the indexes, if that does not work export the data and truncate the table.

  • that is completely wrong, ignore this advice.
    – miracle173
    Commented Feb 22, 2017 at 21:40
  • @miracle173 No, the statement itself is not completely wrong! You might have sparse tables with lots of gaps and empty blocks. In case of FTS, even such empty blocks need to be read as well up to High-water-mark to check whether something relevant is in there. Same may happen for sparse Indexes. Commented Jul 21, 2022 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.