[Solved] Count is slow Oracle

EverSQL Database Performance Knowledge Base

Count is slow Oracle

Database type:

this is my query :

SELECT Count(last_upd)
FROM S_CONTACT
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 :

COUNT(LAST_UPD)
---------------
43

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 10.2.0.4.0 - 64bits

Thank you

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX s_contact_idx_last_by_last_name ON S_CONTACT (last_upd_by,LAST_NAME);
The optimized query:
SELECT
        Count(S_CONTACT.last_upd) 
    FROM
        S_CONTACT 
    WHERE
        S_CONTACT.last_upd_by = '0-1' 
        AND S_CONTACT.LAST_NAME <> 'Wait' 
        AND S_CONTACT.last_upd + 1 / 24 > S_CONTACT.SYSDATE - (
            1 / (
                24 * 60
            )
        )

Related Articles



* original question posted on StackOverflow here.