Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db
Date: Sat, 28 Apr 2012 07:14:39 -0400
Message-ID: <CAGzKQQfuM3cfQueaae8Pe0E4vWsjP8O7F+FKJHy6zG9ZpcY2BQ_at_mail.gmail.com>
Li Li and all,
I am often asked to present SQLTXPLAIN to an enterprise Oracle customer, so I often do a 1hr webcast presentation on this tool: how to install it and how to use its main methods. Next month I am delivering a Webinar (1hr). It is free. If you or someone you know may want to attend, please register following link below. Capacity for this 1hr SQLTXPLAIN Webinar on May 15 is limited, so please register early so you can reserve one connection. Feel free to share this link with your customer(s).
https://oracleaw.webex.com/oracleaw/onstage/g.php?d=590415036&t=a Event Information: Using SQLTXPLAIN to diagnose SQL statements performing poorly
Cheers -- Carlos
On Thu, Apr 26, 2012 at 6:44 PM, Li Li <litanli_at_gmail.com> wrote:
> Thanks everyone for taking your time to reply!! The SQLTXPLAIN tool is
> really cool. Carlos the author of this tool took a quick look at the
> report generated by this tool and was able to quickly locate the
> issue. There was a good plan using the FBI in DEV2 but it was not
> accepted!! Once that's been located, issue resolved.
>
> On Wed, Apr 25, 2012 at 1:26 PM, Carlos Sierra
> <carlos.sierra.usa_at_gmail.com> wrote:
> > Chris,
> >
> > If you try SQLTXPLAIN (SQLT) MOS 215187.1 please use SQLT XTRACT in both
> and
> > send me output zip files. I can do a quick review. Or you can also use
> SQLT
> > COMPARE from both. There are so many things to compare manually. An
> > automated mechanism reduces the human effort and error.
> >
> > Cheers -- Carlos
> >
> > On Wed, Apr 25, 2012 at 1:34 PM, Taylor, Chris David
> > <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> >>
> >> Oracle will often favor a full tablescan when the data you want is
> spread
> >> out across multiple Oracle blocks - so say the table only has 50 Oracle
> >> blocks, and your data occupies 45 of them then it's 'cheaper' to do a
> FTS
> >> since it has to read most of the Oracle blocks anyway.
> >>
> >> That's the point I was trying to find floating around in my head...
> >>
> >>
> >> Chris
> >>
> >> -----Original Message-----
> >> From: Taylor, Chris David
> >> Sent: Wednesday, April 25, 2012 12:09 PM
> >> To: 'Li Li'
> >> Cc: 'oracle-l_at_freelists.org'
> >> Subject: RE: query uses function based index in DEV1 db but does NOT use
> >> it in DEV2 db
> >>
> >> <snip>
> >> There are times where if you've asked for some percentage of a table's
> >> rows, but because that percentage is spread out across the whole table,
> that
> >> Oracle will favor a FTS even when asked for a small percentage of the
> >> table's rows. (I was trying to find an example of this but can't put my
> >> fingers on it at the moment...)
> >> </snip>
> >>
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >>
> >
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 28 2012 - 06:14:39 CDT