Asked by a old DBA friend about waits caused by wait event "OS THREAD STARTUP" found in his 10g RAC(4 nodes on solaris with ASM)
SELECT DECODE (session_state, 'WAITING', event, NULL) event,
session_state, COUNT(*), SUM (time_waited) time_waited
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state order by time_waited desc;
Node 1
enq: TX - row lock contention
WAITING
3517
1734721441
os thread startup
WAITING
743
681425655
log file sync
WAITING
3557
530889167
DFS lock handle
WAITING
1131
528346071
db file sequential read
WAITING
34162
502368757
PX Deq: Signal ACK
WAITING
3917
406436176
reliable message
WAITING
283
251896304
gc buffer busy
WAITING
580
175092687
db file parallel read
WAITING
2606
169615544
Streams AQ: qmn coordinator waiting for slave to start
WAITING
123
121679315
I'm concluding on base of my experiecne of parallel queries(on test
machine with single CPU!!!! so no benefit in my case no matter I had RAC)
I see there are PX events indicating your RAC database
instances has parallel query/dml executions but I
guess the os thread startup wait event has nothing to
do with rac environment. This event indicates the
waitupon starting os process(thread) to start query
slave process for execution of parallel query. if u
set init parameter parallel_min_server sufficently
high,you may slightly cut this overhead but its not
recomendded. But instead of following rules of thumb
you check trade-off if you have significant waits.
Posts
