Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Negative Buffer Cache Hit - Help with SQL

Re: Negative Buffer Cache Hit - Help with SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 26 Oct 2007 22:59:34 -0700
Message-ID: <1193464768.517442@bubbleator.drizzle.com>


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

Buffer Cache Hit Ratio is a bad joke exposed multiple times as a total fraud. The number you just posted has me rolling on the floor LOL.

I am at a loss to understand how an Explain Plan that indicates it is retrieving 198 records is taking almost 400K disk reads. Where are these numbers coming from? Are you sure you've got the right stuff copied here?

I may be missing something but it seems to me you are jumping to a solution without identifying a problem. Is there one? What is it other than seeing really big numbers. First place I'd start if I was going to do anything is patch to 9.2.0.8 or move to a currently supported version of the database.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Oct 27 2007 - 00:59:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US