To mess up with data dict or not

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 1 Oct 2010 03:45:29 +0000 (UTC)
Message-ID: <i83lgp$g2d$1_at_solani.org>

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

Received on Thu Sep 30 2010 - 22:45:29 CDT