On Apr 2, 11:57 am, Andreas Mosmann <mosm..._at_expires-30-04-2007.news-
group.org> wrote:
> sybrandb schrieb am 02.04.2007 in
> <1175500079.842304.118..._at_l77g2000hsb.googlegroups.com>:
>
>
>
>
>
> > On Apr 2, 9:35 am, Andreas Mosmann <mosm..._at_expires-30-04-2007.news-
> > group.org> wrote:
> >> Hi ng,
>
> >> this is a general question about indexes and queries. I am glad about
> >> any hint. I can not imagine that all experienced Oracle- Gurus only work
> >> with try and error. There must be a strategie.
>
> >> At first: I actually work on Oracle 9.2.0.7 DB.
>
> >> If there is a query like
>
> >> SELECT <any_fields> FROM T1
> >> JOIN T2 ON T2.F1=T1.F2
> >> JOIN T3 ON T3.F1=T1.F3
> >> WHERE T2.FX=<a value> and T3.FX=<a value>
> >> GROUP BY T1.F4
>
> >> 1. What way is to go to find out what index should be created to make
> >> this query faster?
> >> 2. What (except hardware) does the performance depend on?
> >> 3. Is there any place I can read about it? What search string I could
> >> use for google search?
>
> >> Many thanks
> >> Andreas Mosmann
>
> >> --
> >> wenn email, dann AndreasMosmann <bei> web <punkt> de
> > 1 read the explain plan, identify the driving table, and make sure the
> > driving table is correct (ie assuming the driving table is not a look
> > up table) and returns the smallest number of records. So no trial and
> > error involved, but 'know thy application'
>
> Which is the driving table of my query? How can I tell the CBO what
> driving table he has to use? Why should a driving table not be a lookup
> table (if I understand it correct)? Is it possible to explain with the
> example above?
>
> > 2 Primarily on 'know thy application' and reading documentation, and
> > by avoiding putting everything on 1 single disk.
>
> To read Oracle documentation row by row I would need years. Is there a
> place I can read (my English is not the very best)?
>
> > 3 The concept outlined above is explained in great detail in the 'SQL
> > Tuning book' by Dan Tow, published by O'Reilly.
>
> I bought it right now. Thanks.> The books by Cary Millsap, Jonathan Lewis, and Tom Kyte have been
> > recommended here so many times, I won't recommend them further. Anyone
> > here can find their details by searching Google.
>
> searching google is a fine thing but without useful keywords it is more
> interesting reading yellow pages.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Thank you very much
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de- Hide quoted text -
>
> - Show quoted text -
I have been always very bad at reading the SQL92 Join Syntax, so I
don't use it myself, and I regret the day the committee decided to
include it in the standard. It results in unreadable SQL. Period.
Normally CBO would assume the FIRST table in the FROM clause is
driving.
Note: RBO assumed the LAST table was driving, that is why the query
always read
select * from emp, dept
I'm not sure how CBO deals with JOIN clauses, my feeling is the access
paths are always different from normal syntax.
Note: How CBO arrived at it's decisions can be verified using event
10051, resulting in a trace file.
In your particular example it looks like the driving table isn't
limited by any predicate in the WHERE clause. This is definitely bad,
and probably resulted in the PLAN you showed before.
A second general principle is whatever predicate can't be satisfied by
an index will be satisfied by FILTER. This is why dbms_xplan is
extreemly useful.
Satisfied by filter means data is actually fetched in buffer cache.
This is why having a lookup table as driving table is bad. Likely 100
percent of the rows of the lookup table are necessary, and this
results in the real driving table being filtered, instead of using an
index access path.
Example: assume you have a table with customer incidents. This table
needs to be joined with a table with customer configuration info. The
customer is large, has many configuration items. You only need the
open incidents of last week.
You need to make sure you access the open incidents first, as the
configurations table being driving will result in way more IO.
Right now I can't think of any other examples, but I have seen this
issue many times.
So that is why I am saying: Know they data. This is also why
*developers* should tune applications instead of DBAs. My experience
tells me most developers know exactly zilch about statement tuning,
which forces me to reverse engineer the datamodel and application. As
you are probably aware, those developers invariably blame the database
for being slow, where it is usually their fault.
Tuning is not trial and error. Tuning is a craft and sometimes an art.
But I only became good at it, because developers still are refusing to
do their job, and just throw their applications over the fence.
--
Sybrand Bakker
Senior Oracle DBA
Received on Mon Apr 02 2007 - 05:32:10 CDT
