Re: Negative Buffer Cache Hit - Help with SQL

"ROAL" <ralbertson_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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html Received on Sat Oct 27 2007 - 15:06:32 CDT