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: ROAL <ralbertson_at_comcast.net>
Date: Sat, 27 Oct 2007 17:19:12 -0700
Message-ID: <1193530752.105140.206410@o3g2000hsb.googlegroups.com>


On Oct 27, 4:06 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "ROAL" <ralbert..._at_comcast.net> wrote in message
>
> news:1193448786.917987.93840_at_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.
>
> Start with the logic of the query and the indexes that exist
> to support that logic.
>
> > where t0."SERVER_DATE" >= to_date('09/01/2007
> > 00:00:00','mm/dd/yyyyhh24:mi:ss')
> > and t0."ATLYS_ACCOUNT_NUMBER" = '1111111111'
> > and t0."POST_AMOUNT" = 74.31
> > and t0."ATLYS_PAYMENT_ID" is not NULL
>
> This looks like: "find a recent bill for $XXX for customer YYY
> which has not yet been paid".
>
> If you do this a lot, and the typical idea of "recent" is 'last couple of
> months"
> then you seem to need an index (atlys_account_number, post_amount,
> server_date).
>
> At present you are using an index on - or starting with - (post_amount).
> Possibly there are some very commonly occurring amounts which have
> appeared numerous times over the last few years - if so, some of your
> queries go to find lots of row from the table based on amount, then
> discard them because the customer or date is wrong.
>
> Do you have such an index ? If so, you need to figure out why
> Oracle is not using it and prefers the (post_amount) index.
>
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -

All thank you for your time on my problem.

Jonathen, there are other indexes that I neglected to point out and as you indicate my dilema is why is it using the amount column when from my opinion the account number would be optimal.

Here are the other indexes:

INDEX_NAME                     COLUMN_NAME
COLUMN_POSITION UNIQUENES DEGREE INSTANCES
------------------------------ ------------------------------
--------------- --------- ------ ---------- PAYMENT_LOC_IDX
LOCATION                                     1
NONUNIQUE      1      1
PK_PAYMENTS
CLIENT_ID                                       1
UNIQUE             1      1
PK_PAYMENTS
CLIENT_DATE                                  2
UNIQUE             1      1
PK_PAYMENTS                    ATLYS_ACCOUNT_NUMBER
3                 UNIQUE             1      1
PMTS_AMOUNT
AMOUNT                                         1
NONUNIQUE      2      1
PMTS_ATLYS_PMT_ID              ATLYS_PAYMENT_ID
1                NONUNIQUE      2      1
PMTS_POST_AMT                  POST_AMOUNT
1                NONUNIQUE      1      1
PMTS_SERVER_DATE               SERVER_DATE
1               NONUNIQUE       2      1

One of the things I was thinking about today was that the amount columns is defined with parallel and the others are not. Is the optimizer using this index because of this?

As I indicated this query is run for every payment that we process through our middleware. This is their method to determine whether the request for payment being made is a duplicate and therefore it should not process the payment. I just tried a NOPARALLEL hint on the query but the results were the same.

Here are the rates per day
TRUNC(SER COUNT(*)

--------- ----------
20-OCT-07      15539
21-OCT-07       5128
22-OCT-07      18887
23-OCT-07      17289
24-OCT-07      17376
25-OCT-07      20232
26-OCT-07      23809
27-OCT-07      14198

The explain plan and the tkprof results are current I just ran them last night.

Thanks for your help. Received on Sat Oct 27 2007 - 19:19:12 CDT

Original text of this message

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