| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Negative Buffer Cache Hit - Help with SQL
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_NAMECOLUMN_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
 |
 |