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