Jump to content
ayush

Index rebuild from parallel session and library cache lock

Recommended Posts

Hi Donald,

i am trying to build multiple indexes in parallel session
i have table t1 which has partition p1 and sub partitions p1s1,p1s2,p1s3,p1s4......p1s500
from 15 parallel session i am picking 15 indexes and am trying to rebuild it.

so my 15 session will be like:

sess1: alter index i1 rebuild subpartition i1_p1s1 rebuild 
sess2: alter index i1 rebuild subpartition i1_p1s2 rebuild 
sess3: alter index i1 rebuild subpartition i1_p1s3 rebuild 
sess4: alter index i1 rebuild subpartition i1_p1s4 rebuild 
sess5: alter index i1 rebuild subpartition i1_p1s5 rebuild 
.
.
.
.
sess15: alter index i1 rebuild subpartition i1_p1s15 rebuild 


Now the problem i face is, these 14 sessions are waiting with library cache lock till session 1 completes it library cache load
is it a normal behavior?
I was under impression that subpartitions are different objects so one subpartition should not block the others  work.

machine details:
num of instances: 4
num cpu : 80
num cpu core : 80

Oracle version: 11Gr2(11.2.0.4.0) 4 node RACS

SGA: 300GB

Shared pool: 30GB

 

Share this post


Link to post
Share on other sites

Hi Ayush, and welcome to the forum!

First, consider using nologging:

http://www.dba-oracle.com/t_fastest_way_to_rebuild_oracle_index.htm

Library cache locks happen when you rebuild an index, but only when you have lots of non-reentrant SQL in the library cache:

http://www.dba-oracle.com/m_library_cache_load_lock.htm

You should also check to see if setting cursor_sharing=force might clear out non-reentrant SQL:

http://www.dba-oracle.com/t_cursor_sharing.htm

also see if you are using adaptive_cursor_sharing properly:

http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm

And don’t use extended_cursor_sharing.

I have some great notes here, and also look-up the MOSC notes for full details:

http://www.dba-oracle.com/m_library_cache_load_lock.htm

Good luck!

 

Share this post


Link to post
Share on other sites

Thanks Donald for a quick reply.

" The loads of SQL statements are serialized and locked in exclusive mode, such that only one process can load an object or a piece of an object at a time. "

I just want to understand the meaning of this line. When you say an object, does it refer to the table or the subpartition of the table?

As per my understanding, a subpartition of a table is a different object altogether, so 2 subpartition should not be blocking each other, if i have sufficient shared pool available.

Please let me know if my understanding is correct or not.

If my understanding is wrong then i need to re-look into the code design else I need to look into re-sizing the shared pool.

Share this post


Link to post
Share on other sites

Hi Ayush,

I would be remiss if I did not ask why you detected a need to rebuild this monster index?

Please read:

http://www.dba-oracle.com/art_index1.htm

Obviously, rebuilding with NOLOGGING on an idle database may relieve any library cache locking issues, but the bigger question is why you are undertaking this task!

Are you rebuilding this index for performance reasons?  Do you see any benefit afterwards?

Please advise.  Thanks!

Share this post


Link to post
Share on other sites
3 hours ago, burleson said:

Hi,

I think that within this context, “object” refers to a library cache object. (i. e. a SQL statement).

Thanks Donald, it means i need to work with Shared Pool.

We need to rebuild index because they become unusable.

We have our fact tables partitioned on a daily basis.

On weekends we compress the data for last week.

Basically we change the partition property to compress and do an alter table move subpartition.

So all the local indexes go into unusable state.

I cannot use NOLOGGING because at tablespace level we have set parameter as force logging, for Data Guard.

 

Share this post


Link to post
Share on other sites

Hi,

Sorry, how do you mean that the index becomes unusable?

You never do DDL or mark it as unusable?

If true, that the index just stops being used for no reason, then you may have a serious bug.

Please clarify . . .

Share this post


Link to post
Share on other sites

Hey Donald,

It is not a bug. We compress the previous week's data.

Our table is partitioned on daily basic and sub partitioned on an integer column. For each week we have 5 partition and each partition has around 500 sub partition.
Every week we run a task which will compress the data of last week.
Basically, on a weekend we run the below command for 5 partitions:

1) alter table t1 modify partition p1 compress(forms outer loop for 5 partitions.)
2) alter table t1 MOVE SUBPARTITION p1_s1 (this will be in inner loop for all the 550 sub partitions)
 

This step 2 is making the index unusable.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×