| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: O9i: How to create a matching index to a given query
On Apr 2, 3:11 pm, Andreas Mosmann <mosm..._at_expires-30-04-2007.news-
group.org> wrote:
> sybrandb schrieb am 02.04.2007 in
> <1175509930.482682.227..._at_b75g2000hsg.googlegroups.com>:
>
> > 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.
>
> Is it readable in explain plan? (the most right bottom- node is the
> driving table or anything like that?)
>
> > Note: How CBO arrived at it's decisions can be verified using event
> > 10051, resulting in a trace file.
> Interesting.
> > 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.
>
> So I still did not find the "driving table". In my special situation
> there is only one real table involved, all the other ones are some kinds
> of lookup tables. And, of course, the main table (that shoud be a
> driving table) is strongly limited, the result has about 5 of 500 000
> rows. But whatever I try the CBO prfers a FULL TABLE SCAN.
>
> > 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.
>
> If I understand correct so a lookup table that is small enough to keep
> in buffer completely will cause the whole "main table" as a return?
>
> > 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 right, exactly this is my problem. I sure know the data
> but as a developer I do not know enough to build an efficient query
> (supported by matching indexes) for that one case.
>
> > you are probably aware, those developers invariably blame the database
> > for being slow, where it is usually their fault.
>
> That is not the way I do, I knowthat it is my fault, but I still do not
> know how to solve this.
>
> > Tuning is not trial and error. Tuning is a craft and sometimes an art.
>
> So I should have studied arts? ;)
>
> > But I only became good at it, because developers still are refusing to
> > do their job, and just throw their applications over the fence.
>
> Maybe I will learn doing my job.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Thank you very much, it will take some time to find out why the CBO
> decides to use a full table scan and how to give it a query matchig to
> data and indexes.
>
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
> Is it readable in explain plan? (the most right bottom- node is the
> driving table or anything like that?)
>
| Id | Operation | Name |Rows | Bytes | Cost |
| 0 | SELECT STATEMENT |
| | | |
| 1 | TABLE ACCESS BY INDEX ROWID| STATS$DATABASE_INSTANCE
| | | |
| 2 | NESTED LOOPS |
| | | |
| 3 | TABLE ACCESS FULL | STATS$SNAPSHOT
| | | |
|* 4 | INDEX RANGE SCAN | STATS$DATABASE_INSTANCE_PK
| | | |
-------------------------------------------------------------------------------------------
This should be read as
2
3 4
Step 3 is driving.
> If I understand correct so a lookup table that is small enough to keep
> in buffer completely will cause the whole "main table" as a return?
Sort of.
What usually happens is you have a foreign key on your main table to
the lookup table.
This foreign key however is not used in any fashion in limiting the
result set.
Consequently Oracle fetches way too much in buffer cache, which has to
be filtered out in the last step.
Hth
-- Sybrand Bakker Senior Oracle DBAReceived on Mon Apr 02 2007 - 09:23:47 CDT
 |
 |