Re: To mess up with data dict or not

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 1 Oct 2010 08:49:37 -0700 (PDT)
Message-ID: <47eb0454-a116-43a0-b36d-d31b279c842d_at_i5g2000yqe.googlegroups.com>

On Sep 30, 11:45 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> I am creating a text index in a 10.2.0.4 RAC database, on small, 600GB
> table. The index creation is running in parallel, 16 processes and here
> is the problem:
>   1  select * from (
>   2  select event,time_waited
>   3  from v$session_event
>   4  where sid=73
>   5  order by time_waited desc)
>   6* where rownum<=10
> SQL> /
>
> EVENT                                                            
> TIME_WAITED
> ----------------------------------------------------------------
> -----------
> db file scattered read                                              
> 1274172
> enq: SQ - contention                                                  
> 120067
> direct path read temp                                                  
> 84161
> db file sequential read                                                
> 75501
> log file switch (archiving needed)                                    
> 72990
> gc cr multi block request                                              
> 40222
> direct path read                                                      
> 32759
> control file sequential read                                          
> 22733
> gc cr grant 2-way                                                      
> 10548
> gc current grant 2-way                                                  
> 9672
>
> The 2nd most time intensive event is waiting on the sequence. Remember,
> the statement running is parallel "create index indextype is
> CTXSYS.CONTEXT" statement so the what is the sequence? The answer is
> simple:
> SQL> select object,owner
>   2  from v$access
>   3  where type='SEQUENCE' and sid=73;
>
> OBJECT
> ---------------------------------------------------------------------------­-----
> OWNER
> ----------------------------------------------------------------
> DBMS_LOCK_ID
> SYS
>
> So, the problematic sequence is SYS.DBMS_LOCK_ID. As it turns out, the
> sequence is created with the cache size of 20 numbers, which is woefully
> inadequate, having in mind the size of the task. What would be the
> downside of changing the cache to something decent, like 8192 numbers,
> besides losing support, of course?
>
> --http://mgogala.byethost5.com

I do not understand why building a context index would need to use the dbms_lock_id sequence to begin with but changing the cache size should not harm anything. Oracle support has had us change the sequence behind v$session.audsid since it was causing hangs in a RAC environment so this type of action has definitely been done before. I do think I would start with a smaller cache size though and work my way up incrementally if necessary.

HTH -- Mark D Powell -- Received on Fri Oct 01 2010 - 10:49:37 CDT