Re: Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

From: Brent <bpathakis_at_yahoo.com>
Date: 5 Nov 2002 11:27:05 -0800
Message-ID: <1736c3ae.0211051127.5c0e568a@posting.google.com>

milbratz_at_hotmail.com (william milbratz) wrote in message news:<cee3515e.0211040912.eb5b3c3_at_posting.google.com>...
> Thanks guys for the input. And Mr. Jonathan Lewis, you get the gold
> star as the 'ORDERED' hint effected the quantum difference. (3 minutes
> vs. 1 second). A big woo-hoo!
>
> Some follow up issues :
> 1) The /*+ ORDERED FIRST_ROWS */ hints work fine when I'm querying on
> 'normal' (i.e. btree indexes, nothing fancy...)..but when I used a
> function based index, Oracle doesn't take the hint..i.e. and does its
> own sorting. In addition, I've tried using the 'INDEX' and the
> 'INDEX_ASC' hint. No avail.
>
> i.e. same query as above..
> ORDER BY upper(post.subject)
>
> 2) Likewise I couldn't get Oracle to 'take the hint' if the query
> sorts on the non-primary table, even though though the other column is
> indexed correctly:
> ORDER BY member.last_name
>
> Does this cohere with your individual and collective experiences and
> understandings? Any other steps to take for function based indexes or
> 'non "main table"' sorts?
>
> thanks again.
>
> bill m

  Hi,

    I've had similar problems getting oracle to use indexes and to choose an effecient path.

    One thing you might try assuming:

• your using oracle 8.0 or highter
• you've analyzed tables and indexes (using cbo)

    is to adjust a copuple of settings

     
     * optimizer_index_cost_adj --- I usually set this at 10-20.  It
defaults to 100.
     * optimizer_index_caching -- I usually start with an initial

setting of 50
  and end up setting anywhere from 60-90.

   You can try these using alter session:

   alter session set optimizer_index_cost_adj = 0;

   Note that I would only try these in a test enviroment or when you have some downtime on the db.

   It may not help in all but I've had some dramatic performance increases by playing with these parameters, and have yet to see a case where the defualts were appropriate. Received on Tue Nov 05 2002 - 13:27:05 CST