Re: CREATE INDEX: PARALLEL flag influences index usage

From: Robert Klemme <bob.news_at_gmx.net>
Date: Mon, 14 Mar 2005 13:58:11 +0100
Message-ID: <39lg35F62ei25U1@individual.net>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:dbp3319bd6pjq4l4u3khbpp5o9skkn2mr6_at_4ax.com...
> On Fri, 11 Mar 2005 16:17:41 +0100, "Robert Klemme" <bob.news_at_gmx.net>
> wrote:
>
> >Now my question is, what's happening here? How does the option
influence
> >the execution plan and why does the optimizer choose the slow variant
if
> >PARALLEL is switched off? Thanks a lot for any hints!
>
> You need to do some analysis, and you are the only one who can do it,
> as this group is not clairvoyant.

Well, yes. I just didn't want to start with a long posting loaded of lots of details that might obscure the view.

> We need you to run (and post) the EXPLAIN PLAN for both statements,
> alternatively you can set event 10053 level 1 in your session
> (alter session set event = "10053 trace name context forever, level
> 1") which will force the optimizer to generate a trace file with a
> break down of it's decisions.

Ok, here are the plans:

OPERATION OPTIONS OBJECT_NAME COST CARDINALITY BYTES OPTIMIZER

------------------- ----------------- ------------ ----- ----------- -----
 ----------
SELECT STATEMENT                                      17           2    52
CHOOSE
 TABLE ACCESS       FULL              AG_USER         17           2    52

ANALYZED OPERATION OPTIONS OBJECT_NAME COST CARDINALITY BYTES OPTIMIZER

------------------- ----------------- ------------ ----- ----------- -----
 ----------
SELECT STATEMENT                                       4           2    52
CHOOSE
 TABLE ACCESS       BY INDEX ROWID    AG_USER          4           2    52
ANALYZED
  INDEX             RANGE SCAN        IDX_USER         2           2

ANALYZED
> Then having a copy of the parallel settings would also be handy.

Those settings were standard AFAIK - unfortunately I don't have access to the db at the moment. I'll try to dig them up and provide them. From memory, parallel_automatic_tuning was FALSE. All tables and indexes had default setting, i.e., no "parallel" clause during creation, and the session was default, too. Also, statistics were up to date.

> Apart from that, the fact you seem to state you need to parallellize
> lookups on an index with only 17000 measly rows seems to indicate
> there is something seriously wrong at your site, and you may be curing
> symptoms only right now.

Likely - but at the moment the symptoms are all I have. I'm trying to find out what's behind this and that's the very reason I started this thread. :-)

I tried with another Oracle instance (8.1.7) and that showed different behavior, i.e., the index was used regardless of parallel settings of the table and index. This is what I would have expected at the other instance (9.x) also.

I think you're right that something must be wrong there. It's only that I have no clue at the moment *what* it could be.

Thanks for listening!

Kind regards

    robert Received on Mon Mar 14 2005 - 06:58:11 CST