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 -

Also the last stats collected was on 10/22/2007 10:34:39 PM.

This is the statement that is run weekly for capture of stats: execute dbms_stats.gather_schema_stats(ownname => 'ECOM', granularity => 'ALL', estimate_percent => 30, cascade => TRUE); Received on Sat Oct 27 2007 - 19:22:59 CDT