Negative Buffer Cache Hit

From: ROAL <ralbertson_at_comcast.net>
Date: Fri, 26 Oct 2007 18:33:06 -0700
Message-ID: <1193448786.917987.93840@z9g2000hsf.googlegroups.com>

I have the following SQL that I am trying to determine how best to optimize:

select

t0."ATLYS_ACCOUNT_NUMBER",
t0."CLIENT_DATE",
t0."CLIENT_ID",
t0."AMOUNT",
t0."POST_AMOUNT",
t0."SERVER_DATE",
t0."PREPAID_MDN",
t0."ATLYS_PAYMENT_ID",
t0."METHOD",
t0."VERISIGN_GROUP",
t0."STATUS",
t0."PNREF",
t0."MESSAGE_LOG_SEQ_ID",
t0."LOCATION"

from "PAYMENTS" t0
where t0."SERVER_DATE" >= to_date('09/01/2007 00:00:00','mm/dd/yyyy hh24:mi:ss')

and t0."ATLYS_ACCOUNT_NUMBER" = '1111111111'
and t0."POST_AMOUNT" = 74.31
and t0."ATLYS_PAYMENT_ID" is not NULL

The PAYMENTS table as over 10,000,000 records.

Running Oracle 9.2.0.6 on Sun Solaris 10.

Explain Plan Results:
PLAN_TABLE_OUTPUT

| Id  | Operation                   |  Name          | Rows  | Bytes |

Cost (%CPU)|

|   0 | SELECT STATEMENT            |                |     1 |    96
|     9   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| PAYMENTS       |     1 |    96
|     9   (0)|
|*  2 |   INDEX RANGE SCAN          | PMTS_POST_AMT  |   198 |
|     3   (0)|


-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - filter("T0"."SERVER_DATE">=:Z AND "T0"."ATLYS_ACCOUNT_NUMBER"=:Z AND
"T0"."ATLYS_PAYMENT_ID" IS NOT NULL) 2 - access("T0"."POST_AMOUNT"=TO_NUMBER(:Z))

TKPROF Results:
select

t0."ATLYS_ACCOUNT_NUMBER",
t0."CLIENT_DATE",
t0."CLIENT_ID",
t0."AMOUNT",
t0."POST_AMOUNT",
t0."SERVER_DATE",
t0."PREPAID_MDN",
t0."ATLYS_PAYMENT_ID",
t0."METHOD",
t0."VERISIGN_GROUP",
t0."STATUS",
t0."PNREF",
t0."MESSAGE_LOG_SEQ_ID",
t0."LOCATION"

from "PAYMENTS" t0
where t0."SERVER_DATE" >= to_date('09/01/2007 00:00:00','mm/dd/yyyy hh24:mi:ss')

and t0."ATLYS_ACCOUNT_NUMBER" = '1111111111'
and t0."POST_AMOUNT" = 74.31
and t0."ATLYS_PAYMENT_ID" is not NULL

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        2      0.25       0.50         60        330
0           1

------- ------ -------- ---------- ---------- ---------- ----------

total        4      0.25       0.50         60        330
0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42

Rows Row Source Operation

-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID PAYMENTS

329 INDEX RANGE SCAN PMTS_POST_AMT (object id 62781)

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 db file sequential read 60 0.00 0.00 SQL*Net message from client 2 0.00 0.00

NOTE: App does use bind variables.

This statement runs for every payment that we process to check for duplicate payments. I have discussed options to finding dupes with developers. For now I need to determine how to optimize this approach.

OEM Displays following stats for this SQL 5,521 Disk Reads Per Execution
Buffer Gets per Execution 0.00
Executions 72,145
Disk Reads 398,317,617
Buffer Cache Hit Ratio -39,831,761,600

Options I am considering:
1) Partition the table
2) Place the current partition in KEEP buffer

Any assistance on this is greatly appreciated. Let me know if you need any further details Received on Fri Oct 26 2007 - 20:33:06 CDT

Negative Buffer Cache Hit - Help with SQL