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: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 27 Oct 2007 14:29:59 -0000
Message-ID: <1193495399.995525.89800@o3g2000hsb.googlegroups.com>


On Oct 26, 9:33 pm, ROAL <ralbert..._at_comcast.net> 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

Hi Roal,

It could be that other users are using different values for the query, which is causing skewed run times due to bind peeking. In other words, your query values in the example took only 60 disk reads, but another execution required 10,000 or so, resulting in an average of 5521 disk reads per execution.

I would suggest starting (which you may have already done) by making sure stats are up to date and performing a trace with real values when the execution time is poor.

Regards,

Steve Received on Sat Oct 27 2007 - 09:29:59 CDT

Original text of this message

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