index created in serial instead of parallel

[Sumit Singh]

HI,

 

In one of our batch programs, we use dynamic DDL to initially drop and at the end of it recreate indexes.

We use Parallel processing to speed up the process.

The index DDL looks like this.

CREATE INDEX <ind_name> ON table_name (column1,column2) tablespace INDX_TS nologging parallel (degree 5);

Some of the indexes are using the requested parallel degree, others are always getting created in serial.

This is confirmed from the trace files.

 

Also, trace files do not tell the reason for not choosing parallel plan. Tried _px_trace, however, it does not capture the DDL which are created in serial.

 

These are the parallel parameters:

fast_start_parallel_rollback            string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning             boolean     FALSE
parallel_execution_message_size integer     4296
parallel_instance_group                string
parallel_io_cap_enabled                boolean     FALSE
parallel_max_servers                    integer     200
parallel_min_percent                     integer     25
parallel_min_servers                 integer     8
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

 

Let me know what else I will be looking to find out the reason for CBO choosing the serial plan instead of parallel.

 

Thanks,

 

Sumit

[ATM Consultants]

a. Looking at which stats, you came to conclusion that they are
created serially?

b. Is there any pattern that you could identify for indexes that are
created serially?

c. Oracle, OS versions (complete)?

d. Did you try the following to see whether parallelism is picked for
indexes that are created serially.
alter session enable parallel ddl;
alter session force parallel ddl;


Let us know.

[Sumit Singh]

a. This is confirmed from trace files, I have generated by modifying the plsql proc. (added alter session set sql_trace=true just before the DDL's)

 

b. yeah, if I use Execute Immediate to create the indexes (in the same proc), its created in serial, if I call an autonomous proc to create the indexes, it is created in parallel.

I identified the above behaviour today only, the "create indexes" are part of a large procedure, initialy they are dropped, and at the end, they are recreated.

If they are created using Execute Immediate, they are not taking the desired parallelism, however if moved to an autonomous proc, they do take the desired parallel degree.

 

I suspect, there is some limit/restriction on number of parallel DDL/DML performed by a single transaction and my proc is hitting that (as there are a lot of other parallel DML/DDL happening), let me know if anybody know about this.

 

c. Oracle Version - 11.1.0.6.0

 

d. alter session enable parallel ddl; - this is used, but not making any difference

   alter session force parallel ddl; - will try this.

 

Thanks,

[ddf]

> > > Sumit- Hide quoted text -
>
> - Show quoted text -

It may be that your parallel_min_percent setting is creating this
problem as you may not have 50 parallel servers available when some of
these index creations are executed.


David Fitzjarrell

[Sumit Singh]

I have tried with parallel_min_percent 0, still got the same behaviour. Also, I am specifying parallel degree 5, and used a batch program to query PX_PROCESS every 30 seconds to see whether I get all the parallel servers used.

 

Actually I modified the parallel_min_servers to 100 and run the pocedure, and there was always some parallel server available.

[ddf]

> > David Fitzjarrell- Hide quoted text -

>
> - Show quoted text -

You are apparently experiencing Bug 6274465 and should patch your
installation to 11.1.0.7.0, where this bug is fixed.


David Fitzjarrell

[ATM Consultants]

Bug: 6274465 says
If during the execution of a query on a table(s), DDL statements are run on the
same table(s) ifrom another session, the first query may run in serial.

WORKAROUND:

avoid running DDL's on tables, when there are session querying the same tables.


It doesnt mention about DDL using serial.
Also, are you running DDLs while selects are running or this is an
idle/test system?

I doubt you are hitting 6274465.

[ddf]

On Jun 18, 10:23 am, ATM Consultants <atm.consultants....@gmail.com>
wrote:

Of course until the OP decides to provide much more information all
responses are suspect.


David Fitzjarrell

[Sumit Singh]

I think I am not hitting this bug. Following is the exact steps I am doing. Also, this is happening at one of our clients production environment, which is on 10.2.0.4, while I am testing this in my local test environment which is 11.1.0.6.0.

 

Following are the steps I am doing in a big plsql package (almost 10000 lines of code):

 

step 1: drop the indexes, constraints

step 2: create a lot of tables in parallel

step 3: DML/MERGE on a lot of tables in parallel

step 4: recreate the indexes in parallel (some of these are on the tables in step3)

step 5: recreate/enable constrants.

step 6: END.

 

Now, when I am doing the step 4 by calling another procedure (say proc A) which is recreating indexes using execute immediate, all indexes are created in parallel. However, If I call another procedure proc B, which is autonomous, from proc A (by replacing execute immediate), the indexes are created in parallel.

 

There is a restriction for PDML that if a I have done DML in parallel on a table, no other query/DML is possible in parallel again in the same transaction. However, in my case, I am trying to do DDL, and the moment I say create index, there is an implicit commit.

 

So, technicalli autonomous transaction and execute immediate is same in a way(atleast in my case), still they are behaving  differentlly.

 

thanks.

[ATM Consultants]

Can you try with following and let us know the results:

After PDML, put explicit "commit" and not rely on implicit commit and
try the same.

It seems to be a session state/context that is probably not allowing
you to do PDDL and commiting explicitly could probably allow you to do
so.