 | |
To mess up with data dict or not
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
To mess up with data dict or not -
09-30-2010
, 10:45 PM
#2
| |||
| |||
|
|
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 |
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 --
#3
| |||
| |||
|
|
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? |
8467336, which must get at least a nomination for an award for least
descriptive visible bug ever.
Searching on SQ contention comes up with a few platform dependent RAC
mutex type problems, I wonder if you are close to one of those - if
so, might be interesting if you speed things up with a bigger cache.
Or not. Try it and see.
I guess you have to make things fail to get an obscure
misconfiguration fixed. Is changing the cache on a dictionary
sequence really a support nullifier?
jg
--
@home.com is bogus.
Darn net neutrality. http://skunkpost.com/news.sp?newsId=3291
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.