OTN Discussion Forums : help reading an explain plan ...

Guess2

Posts: 329
Registered: 08/05/00

help reading an explain plan
Posted: Jul 1, 2009 7:10 AM

Here is an except from an explain plan.

what does index$_join_002 mean?

| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    74 |   133K  (1)| 00:31:04 |
|*  1 |  FILTER                           |                             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI            |                             | 26855 |  1940K| 10561   (1)| 00:02:28 |
|*  3 |    VIEW                           | index$_join$_002            | 66364 |   907K|   851   (2)| 00:00:12

super_sid

Posts: 97
Registered: 06/13/09

Re: help reading an explain plan
Posted: Jul 1, 2009 7:13 AM

in response to: Guess2

Reply

Hi,

It is a system generated view. It could result from the main query having a sub-query.

saravanan

Posts: 82
Registered: 08/14/05

Re: help reading an explain plan
Posted: Jul 1, 2009 7:14 AM

in response to: Guess2

Reply

In this case index$_join$_002 is the view created by oracle to plan the execution and you won't be able see those view in the database.

It is used only for that execution.

-Saravanan

Guess2

Posts: 329
Registered: 08/05/00

Re: help reading an explain plan
Posted: Jul 1, 2009 9:06 AM

in response to: saravanan

Reply

I am having trouble forcing a good plan. I have 2 databases with comparable data sets and the indexes are the same. One has a good plan and one a bad plan. When I analyze the tables in the database with the good plan, it changes to the bad plan.

Definiton of good plan: returns in 3 seconds
definition of bad plan: returns in 6 minutes.

Note that as soon as i analyzed the tables, I started getting the bad plan. I am at a loss on how to force the plan to change.

BAD PLAN

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    70 | 62008   (1)| 00:14:29 |
|*  1 |  FILTER                           |                             |       |       |            |          |
|*  2 |   HASH JOIN SEMI                  |                             | 29528 |  2018K|  1501   (1)| 00:00:22 |
|*  3 |    TABLE ACCESS BY INDEX ROWID    | EXT_T                       | 29528 |  1614K|  1344   (0)| 00:00:19 |
|   4 |     DOMAIN INDEX                  | EXT_TEXT_IND                      |       |       | 13441   (0)| 00:03:09 |
|   5 |    MAT_VIEW ACCESS BY INDEX ROWID | KM_MV                    |   107K|  1464K|   155   (0)| 00:00:03 |
|*  6 |     INDEX RANGE SCAN              | KAP_IND                            |   107K|         |   136   (1)| 00:00:02 |

good plan. I dont get this when I analyze my tables.


| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    74 |   133K  (1)| 00:31:04 |
|*  1 |  FILTER                           |                             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI            |                             | 26855 |  1940K| 10561   (1)| 00:02:28 |
|*  3 |    VIEW                           | index$_join$_002            | 66364 |   907K|   851   (2)| 00:00:12 |
|*  4 |     HASH JOIN                     |                             |       |       |            |          |
|*  5 |      INDEX RANGE SCAN             | KAP_IND | 66364 |   907K|   105   (1)| 00:00:02 |
|   6 |      INDEX FAST FULL SCAN         | KAP_PK                      | 66364 |   907K|   742   (1)| 00:00:11 |
|*  7 |    TABLE ACCESS BY INDEX ROWID    | EXT_T      | 26855 |  1573K|  9709   (0)| 00:02:16 |
|   8 |     DOMAIN INDEX                  | EXT_TEXT_IND     |       |       |  9709   (0)| 00:02:16 |

Edited by: Guess2 on Jul 1, 2009 9:06 AM

Edited by: Guess2 on Jul 1, 2009 9:07 AM

Edited by: Guess2 on Jul 1, 2009 9:07 AM

Edited by: Guess2 on Jul 1, 2009 9:08 AM

saravanan

Posts: 82
Registered: 08/14/05

Re: help reading an explain plan
Posted: Jul 1, 2009 9:20 AM

in response to: Guess2

Reply

In 9i you can use stored outline and in oracle 10g you can use SQL Profile to store the plan and use it.

-Saravanan

Guess2

Posts: 329
Registered: 08/05/00

Re: help reading an explain plan
Posted: Jul 1, 2009 9:27 AM

in response to: saravanan

Reply

the problem is that i have analyzed the table and now the good plan is gone. so there is nothing ic an use to sql profile.

I am in 10.1

If you look at the plan. The good plan is doing a hash join semi right and using 2 indexes

the bad plan is doing a hash join semi, with 1 index and a different join order.

GregX

Posts: 256
Registered: 11/28/07

Re: help reading an explain plan
Posted: Jul 1, 2009 11:34 AM

in response to: Guess2

Reply

There is a reference to a materialized view in one of your plans, but not in the other. Are you sure it's about the same sql? Do you use Grid Control which can give you some tuning advises?

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: help reading an explain plan
Posted: Jul 1, 2009 1:20 PM

in response to: Guess2

Reply

Guess2 wrote:

what does index$_join_002 mean?

| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|*  3 |    VIEW                           | index$_join$_002            | 66364 |   907K|   851   (2)| 00:00:12

The VIEW operator means Oracle has optimised a separate query block, possibly generating an intermediate result set. (See http://jonathanlewis.wordpress.com/2009/06/25/explain-view/ ) In this case it has constructed the resultset you need from the KM_MV materialized view table by extracting data from the two indexes KAP_IND and KAP_PK and doing a hash join between them. The name index$_join$_NNN happens to be the name that the optimizer uses internally when constructing a result set using this method.

You later post shows an oddity:

|*  3 |    TABLE ACCESS BY INDEX ROWID    | EXT_T                       | 29528 |  1614K|  1344   (0)| 00:00:19 |
|   4 |     DOMAIN INDEX                  | EXT_TEXT_IND                |       |       | 13441   (0)| 00:03:09 |

Was there really a cost of 13441 for the index but only 1344 for the table, or was this a copying error?
It's quite hard to give you good advice for dealing with this example since it's mixing domain indexes and query rewrite and that's a rare option. One (short-term) option would be to try disable index joins - either with an alter session command, or with the hint opt_param('_index_join_enabled','false')

[Edit: Just realised that opt_param appeared in 10.2.0.1 - and you're running 10.1 so it won't apply; you'll have to try using the alter session or alter system command].

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

Edited by: Jonathan Lewis on Jul 1, 2009 9:24 PM
Added note about version

Randolf Geist

Posts: 1,669
Registered: 07/03/08

Re: help reading an explain plan
Posted: Jul 1, 2009 1:30 PM

in response to: Jonathan Lewis

Reply

Jonathan Lewis wrote:
You later post shows an oddity:

|*  3 |    TABLE ACCESS BY INDEX ROWID    | EXT_T                       | 29528 |  1614K|  1344   (0)| 00:00:19 |
|   4 |     DOMAIN INDEX                  | EXT_TEXT_IND                |       |       | 13441   (0)| 00:03:09 |

Was there really a cost of 13441 for the index but only 1344 for the table, or was this a copying error?

Jonathan,

the TIME column suggests that it is coincidence but the real cost (19 secs. to 189 secs., 1344 to 13441 single block reads as cost).

It's quite hard to give you good advice for dealing with this example since it's mixing domain indexes and query rewrite and that's a rare option. One (short-term) option would be to try disable index joins - either with an alter session command, or with the hint opt_param('_index_join_enabled','false')

Is this really query rewrite? I thought that query rewrite shows up as "MAT_VIEW REWRITE ACCESS" operation in the plan, but I'm not entirely sure if this is correct in all cases.

Why disable the index join option when this seems to be the "good" plan?

To the OP:

- How do you analyze the tables and what do you mean by "not having statistics"?

- Can you show us the exact command used to analyze and the command to get rid of the statistics?

- Does the execution "without" statistics show the usage of dynamic sampling in the "Notes" section?

- Can you trace the SQL execution or use DBMS_XPLAN.DISPLAY_CURSOR with STATISTICS_LEVEL = ALL or the GATHER_PLAN_STATISTICS hint to get a clearer understanding where estimate for the cheaper plans goes wrong, i.e. where the majority of the 6 minutes is spent?

- You might want to run Alberto Dell'Era's [XPLAN|http://www.adellera.it/scripts_etcetera/xplan/index.html] tool on your statement which offers some comprehensive output regarding the most important information used by the optimizer, although I'm not sure how it is going to deal with domain indexes.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Jul 1, 2009 10:34 PM

Some suggestions added

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: help reading an explain plan
Posted: Jul 1, 2009 1:48 PM

in response to: Randolf Geist

Reply

Randolf Geist wrote:

the TIME column suggests that it is coincidence but the real cost (19 secs. to 189 secs., 1344 to 13441 single block reads as cost).

Good argument - but still odd that the parent should have a lower cost (and time) than its child.

It's quite hard to give you good advice for dealing with this example since it's mixing domain indexes and query rewrite and that's a rare option. One (short-term) option would be to try disable index joins - either with an alter session command, or with the hint opt_param('_index_join_enabled','false')

Is this really query rewrite? I thought that query rewrite shows up as "MAT_VIEW REWRITE ACCESS" operation in the plan, but I'm not entirely sure if this is correct in all cases.

I think the operation changed a couple of times across versions - the fact that it says MAT_VIEW at all is (for 10.1) a good indicator that rewrite took place. I think you're right for 10.2, and I think (but I'd have to check) that 9i said "TABLE ACCESS" without even mentioning that the table was a materialized view.
[Edit: Just checked the though listing of operators that Julian Dyke used to maintain - you're right again, I'm wrong. even in 10.1 the absence of the "REWRITE" shows that the code was making a direct reference to the materialized view]

Why disable the index join option when this seems to be the "good" plan?

Easy - with apologies to the OP - I copied the two plans into a text file re-align text and make them readable, and labelled them the wrong way round when I copied them. It was a mistake.

Plan B is to use dbms_xplan with the 'outline' option (see http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/ ) to find out how to reference the materialized view then insert an index_join() hint and work from there..

Ideally it would be nicer to know why the costs and cardinality have changed so much and whether various statistics could be adjusted to help the optimizer (looking at the ODCI calls and user-defined stats for the domain index).

It's also worth asking how much more of the plan there is, of course, because the filter may be indicating a subquery that has been chopped off the end that could possibly be pushed to good advantage.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

Edited by: Jonathan Lewis on Jul 2, 2009 8:32 AM
Highilghted error in comment about MAT_VIEW ACCESS.