OraFAQ Forum: Performance Tuning » Two similar queries, two different plans

Home » RDBMS Server » Performance Tuning » Two similar queries, two different plans (Oracle 8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wed, 02 April 2008 16:30

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I know, not the most helpful of titles.

I've got two queries, both of the same form. Their only difference is the from and where clause used within the inline view "a".

select
    same exact column list
from
    (
        same select; single numeric column with alias of "id1"
        different from
        different where
    ) a,
    (
        union all query that is identical in both queries
        which includes a field called "id2"
    ) b,
    regular_table,
    regular_table,
    etc table list, same in both queries
where
    same conditions in both queries, including:
    a.id1 = b.id2
    which is the critical driving condition for performance
;

So I'm basically using two different queries inside inline view "a" in order to get an id column, and then hitting inline view "b" with that id column, which gives "b" enough to go on to drive a good index.

Both inline view "a" queries are trivial:
select id from table where some_field = some_value

However, only one of the queries seems to be pushing the a.id1 = b.id2 condition inside to inline view "b". The one that does performs a wonderful index range scan of an index. The one that doesn't performs an ugly index full scan of the same index.

Below are the inner most portions of the explain plans:

              HASH JOIN (Cost=26 Card=1 Bytes=176)
                INDEX (RANGE SCAN) OF 'XXX' (UNIQUE) (Cost=2 Card=2 Bytes=18)
                VIEW (Cost=23 Card=2 Bytes=334)
                  UNION-ALL
                    FILTER
                      TABLE ACCESS (BY INDEX ROWID) OF 'AAAA' (Cost=19 Card=1 Bytes=111)
                        INDEX (RANGE SCAN) OF 'AAAA_IX' (NON-UNIQUE) (Cost=3 Card=1)
                    FILTER
                      TABLE ACCESS (BY INDEX ROWID) OF 'BBBB' (Cost=4 Card=1 Bytes=112)
                        INDEX (RANGE SCAN) OF 'BBBB_IX' (NON-UNIQUE) (Cost=3 Card=1)

              HASH JOIN (Cost=805441 Card=2 Bytes=354)
                INDEX (RANGE SCAN) OF 'YYY' (NON-UNIQUE) (Cost=5 Card=139 Bytes=1390)
                VIEW (Cost=805435 Card=14957 Bytes=2497819)
                  UNION-ALL
                    FILTER
                      TABLE ACCESS (BY INDEX ROWID) OF 'AAAA' (Cost=773127 Card=2122 Bytes=235542)
                        INDEX (FULL SCAN) OF 'AAAA_IX' (NON-UNIQUE) (Cost=3510 Card=2122)
                    FILTER
                      TABLE ACCESS (BY INDEX ROWID) OF 'BBBB' (Cost=32308 Card=12835 Bytes=1437520)
                        INDEX (FULL SCAN) OF 'BBBB_IX' (NON-UNIQUE) (Cost=25024 Card=12835)

The plan reported cardinalities in this system are horrible, in part because of 8i, in part because of statistics, and in part other factors; none of which I can do anything about.

But Oracle does have the general idea: the "view" line of the first plan reports a very small cardinality in comparison with the view from the second plan, and I think this is becuase in the first case the condition was pushed, and in the second it wasn't.

I can't figure out why one would push and the other wouldn't. Any suggestions on what to look for? Options on how to correct?

Report message to a moderator

Wed, 02 April 2008 21:18

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I suspect you are mis-reading the plan. Do you imagine that the first inline view is returning a nice small result set that is used to probe the UNION ALL inline view? That's not what's happening. If it was, you would see a NESTED LOOPS join.

Since we see a HASH join, we can determine that each inline view is being run independently. In order for the first one to be using an index, it must be using some CONSTANT PREDICATE (not a JOIN PREDICATE).

smartin wrote on Thu, 03 April 2008 07:30

Both inline view "a" queries are trivial:
select id from table where some_field = some_value

In the first (good) query, my bet is that some_field is the same column as ID1. Am I right?

If so, then transitivity is sending some_value down to the UNION ALL inline view, which is then executed independently.

In the second (bad) query, some_field and ID1 are different columns, so some_value cannot be transitively pushed down to the UNION ALL inline view.

If you do want to try a NESTED LOOPS join, try an /*+ OREDERED USE_NL(b)*/ hint.

Ross Leishman

Report message to a moderator

Thu, 03 April 2008 08:27

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Thanks Ross for the quick response. I could indeed be mis-reading the plan, and I'll double check, and play more with my existing use of leading and replacing that with ordered.

And with the nested loops - when I first saw the good case being done, and also saw that it was a hash, I was surprised.

But, the some_field and some_value in the trivial "a" inline view have nothing in common with the id field used in the join. The join id comes from the select portion of that view.

I'll see if I can't post more of the real query.

Oh, and as FYI, I just tried accessing your old tuning site, and it gives the forbidden error. The one at http://people.aapt.net.au (followed by a little more which I'll let you give out if you so desire).

Report message to a moderator

Thu, 03 April 2008 08:47

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Oh, and I found your tuning guide, which seems to now be hosted here on orafaq:

http://www.orafaq.com/tuningguide/

And gave a quick scan through...it is possible I'm running into what you cautioned about with older oracle versions (I'm on 8i) involving hints, using both ordered and use_nl together to avoid older oracle issues, for example, instead of in isolation.

And...well, I may have just found something, thanks to your advice...you may have been more right about that constant than I originally thought. The columns involved in the join are clearly different, which threw me off. But, the "a" view does have a constant in the select list...

Report message to a moderator

Thu, 03 April 2008 09:38

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Well...it looks like the constant in the select list was what was triggering the good behavior all along.

Essentially, one of the versions of "a" had:

select constant id1, col id2

And the other had:

select col id1, constant id2

With both columns id1 and id2 involved in the join, and using an index involved with id1. So in the first case, the join index was able to use the constant, and in the second case it wasn't.

But, I still don't understand why, even with nested loops as below, why the join can't use the index on id1 when id1 is not a constant. It is just a number, which is aleady known because it is the top half of the NL. Why doesn't the bottom see it?

NESTED LOOPS (Cost=200553148 Card=3 Bytes=540)
  VIEW (Cost=15 Card=249 Bytes=3237)
    SORT (UNIQUE) (Cost=15 Card=249 Bytes=2490)
      INDEX (RANGE SCAN) OF 'XXXX_1' (NON-UNIQUE) (Cost=5 Card=249 Bytes=2490)
  VIEW
    UNION-ALL
      FILTER
        TABLE ACCESS (BY INDEX ROWID) OF 'YYYY' (Cost=773127 Card=2122 Bytes=235542)
          INDEX (FULL SCAN) OF 'YYYY_1' (NON-UNIQUE) (Cost=3510 Card=2122)
      FILTER
        TABLE ACCESS (BY INDEX ROWID) OF 'ZZZZ' (Cost=32308 Card=12835 Bytes=1437520)
          INDEX (FULL SCAN) OF 'ZZZZ_1' (NON-UNIQUE) (Cost=25024 Card=12835)

Report message to a moderator

Thu, 03 April 2008 22:20

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Right. Glad you got the main issue sorted.

Now you are talking about View Merging and its second-best-friend Predicate Pushing.

The 10g documentation actually REMOVED useful documentation on these two. So the following links are to 9i (I know you are on 8i, but this is also for the benefit of people living in this century Wink

). The reason these are important is because Oracle must TRANSFORM the query to get the join-predicate INSIDE the UNION ALL rather than OUTSIDE where it currently is.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005536
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005864

Looking at View Merging, we see the restriction:

9i Tuning Guide

The optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain any of the following:

Set operators (UNION, UNION ALL, INTERSECT, MINUS)
A CONNECT BY clause
A ROWNUM pseudocolumn
Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list

So View Mergeing is OUT. What about Predicate Pushing?

Unfortunately, not even the 9i guide includes a list of conditions when predicate pushing is available. All it says is that to push a predicate into an outer join view, you will need the /*+PUSH_PRED*/ hint.

Even if you don't have an outer join, that might be worth a try.

Are you certain you don't have any other fanciness inside the UNION ALL?
- outer joins
- DISTINCT, GROUP BY
- aggregation / analytics
- CONNECT BY
- more views
- Expressions in the SELECT clause for join columns
- DB links

Predicate Pushing is non-magical. It does not do anything you cannot do yourself with SQL. As a test, are you able to rewrite the query in such a way that the join predicate is in each of the UNION ALL subqueries? If so, does it use the range-scan plan.

If it won't use the range-scan even when you rewrite it manually, you can stop worrying about Predicate Pushing.

Ross Leishman

Report message to a moderator

Fri, 04 April 2008 08:01

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Thanks Ross for your comments in this thread. Hopefully it will help others as well. And don't get me started about working on a database from this century, I've used that exact same line to folks here Smile

I did end up re-writing the query myself, essentially doing a "manual predicate push", by having the driving inline view "a" repeated twice, once for each half of the union all. And it did indeed work, and range scan the index as desired.

I tried the push_pred hint and it wouldn't do it. I could have been using the hint wrong I suppose. But it is 8i you know, and the CBO has gotten better since then. Perhaps the restrictions on pushing were similar to merging back then, especially regarding the union and other set operators.

You know I should check the Lewis CBO book...

But anyway, the current solution does work, even if it is not ideal. The negative to it is that the driving inline view is repeated twice within the query. And since there is no "with" clause in 8i, it has to run the driver view itself twice (although the second time will be quite cached).

[Updated on: Fri, 04 April 2008 08:08]

Report message to a moderator

Fri, 04 April 2008 21:35

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Your reluctance to post the code makes it difficult to diagnose any further. I guess your employer/client has got IP issues with posting code on the internet, or the code is big and ugly - which of course brings other factors into play with tuning.

If you are interested in taking it further, set up a simple equivalent example using EMP/DEPT and see if it pushes the predicate.

Just noticed something interesting. It's using a FULL SCAN on the index, not a FAST FULL SCAN. FFS was available in 8i. It should only be performing a full scan to avoid a sort.

Either 8i was a bit buggy on this, or maybe it's using the RBO. Seems unlikely to use the RBO since the plan is showing statistics. Are you sure you don't have an OPTIMIZER_GOAL set to RULE or a RULE hint?

Ross Leishman

Report message to a moderator

Fri, 04 April 2008 21:39

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

8i Tuning Guide

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

You probably don't have FFS enabled - that would explain it.

Ross Leishman

Report message to a moderator

Wed, 09 April 2008 08:50

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Sorry on the not posting the code thing...I tried to do my best to reproduce accurately what was going on. You did a fine job of spotting the root of the problem, which was me mis-reading the plan and thinking it was doing a predicate push when it was actually doing a constant predicate.

Reading the plan would have been helped by the extra information shown by the 10g explains (showing the details of the filter predicates and the transformations applied to each step of the plan). But alas, another drawback of 8i.

As for Index FFS, yes it is there in 8i, and I have successfully used it in this current environment with other queries. We are using CBO and FFS is enabled, at least in some situations (I am a peon here and have no rights to see things such as init params).

However, it seems tricky sometimes (perhaps not as robust as in 10g). There are times when I would think a FFS would be useable and Oracle simply won't do it, even with the index_ffs hint. I have to be very careful about the columns I select out of a table, at a given inline view level, when wanting to see a index ffs.

In this particular case, a full scan of the index in any way would not be adequate performance wise, even if it was a FFS instead of a FULL, although of course FFS would be faster.

This was a tuning project I'm working on to help with some reports that were sometimes taking 10 minutes and sometimes taking so long (hours) that they would never finish. I'm getting the average cases down to under a minute, and the long ones in a couple minutes.

Report message to a moderator

Previous Topic:	Reg Hash Join in Performance tunning
Next Topic:	Index Creation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:35:48 CST 2024