Oracle Database Discussions

Hi Gurus.

I need you help to find out the wait events for a particular table and index when batch export job running from the application. I have done some work around to to find out, but i am not able to figure out the issue or what are the wait events. i am working on linux 4node rac 11gR2.

Below are the steps i have done.

1. find object_id for a table finding the wait events to that object_id.
SYS@ccmintpt1 SQL>select data_object_id from dba_objects where object_name='ACCOUNT_DETAIL_DENORM';

DATA_OBJECT_ID
--------------

82646
88518
84184



SYS@ccmintpt1 SQL>select w.sid,w.event,w.wait_class,w.wait_class# from gv$session s,gv$session_wait w where s.row_wait_obj#=82646;

no rows selected

SYS@ccmintpt1 SQL>select w.sid,w.event,w.wait_class,w.wait_class# from gv$session s,gv$session_wait w where s.row_wait_obj#=88518;

no rows selected

SYS@ccmintpt1 SQL>select w.sid,w.event,w.wait_class,w.wait_class# from gv$session s,gv$session_wait w where s.row_wait_obj#=84184;

no rows selected


2. find the object id for the index of that table and finding the wait events to that object_id.

SYS@ccmintpt1 SQL>select data_object_id from dba_objects where object_name='XAK2ACCOUNT_DETAIL_DENORM';

DATA_OBJECT_ID
--------------
88655

SID EVENT WAIT_CLASS WAIT_CLASS#
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------
1 rdbms ipc message Idle 6
2 heartbeat monitor sleep Idle 6
3 rdbms ipc message Idle 6
4 wait for unread message on broadcast channel Idle 6
6 SQL*Net message from client Idle 6
7 SQL*Net message from client Idle 6
8 SQL*Net message from client Idle 6
9 SQL*Net message from client Idle 6
10 SQL*Net message from client Idle 6
11 SQL*Net message from client Idle 6
12 SQL*Net message from client Idle 6
13 SQL*Net message from client Idle 6
1876 SQL*Net message from client Idle 6
1877 VKTM Logical Idle Wait Idle 6
1878 rdbms ipc message Idle 6
1879 rdbms ipc message Idle 6
1880 rdbms ipc message Idle 6
1881 SQL*Net message from client Idle 6
3200 SQL*Net message from client Idle 6
3210 SQL*Net message from client Idle 6
3212 db file sequential read User I/O 8
3213 SQL*Net message from client Idle 6
3214 SQL*Net message from client Idle 6
3216 SQL*Net message from client Idle 6
3751 rdbms ipc message Idle 6
3752 gcs remote message Idle 6
3754 rdbms ipc message Idle 6
3756 SQL*Net message from client Idle 6
3757 SQL*Net message from client Idle 6
1326 SQL*Net message from client Idle 6
1876 Streams AQ: waiting for time management or cleanup tasks Idle 6
1877 VKTM Logical Idle Wait Idle 6
1878 rdbms ipc message Idle 6
1879 rdbms ipc message Idle 6
1880 rdbms ipc message Idle 6
1882 SQL*Net message from client Idle 6
3751 rdbms ipc message Idle 6
3752 gcs remote message Idle 6
3754 rdbms ipc message Idle 6
3756 rdbms ipc message Idle 6
3758 SQL*Net message from client Idle 6
3759 PX Deq: Execution Msg Idle 6
3200 SQL*Net message from client Idle 6
3210 SQL*Net message from client Idle 6
3212 db file sequential read User I/O 8
3213 SQL*Net message from client Idle 6
3214 SQL*Net message from client Idle 6
3216 SQL*Net message from client Idle 6
3751 rdbms ipc message Idle 6
3752 gcs remote message Idle 6
3754 rdbms ipc message Idle 6
3756 SQL*Net message from client Idle 6
3757 SQL*Net message from client Idle 6
3786 SQL*Net message from client Idle 6
3787 SQL*Net message from client Idle 6
1 heartbeat monitor sleep Idle 6
2 wait for unread message on broadcast channel Idle 6
314 rdbms ipc message Idle 6
315 Streams AQ: qmn coordinator idle wait Idle 6
627 pmon timer Idle 6
628 rdbms ipc message Idle 6
629 Streams AQ: waiting for messages in the queue Idle 6
940 Streams AQ: waiting for time management or cleanup tasks Idle 6
941 VKTM Logical Idle Wait Idle 6
942 rdbms ipc message Idle 6
1253 rdbms ipc message Idle 6
1254 rdbms ipc message Idle 6
1256 Space Manager: slave idle wait Idle 6
1566 DIAG idle wait Idle 6
1567 rdbms ipc message Idle 6
1568 Streams AQ: qmn slave idle wait Idle 6
1879 rdbms ipc message Idle 6
1880 smon timer Idle 6
1881 rdbms ipc message Idle 6
2192 PING Idle 6
2193 rdbms ipc message Idle 6
2194 rdbms ipc message Idle 6
2505 rdbms ipc message Idle 6
2506 rdbms ipc message Idle 6
2818 rdbms ipc message Idle 6
2819 rdbms ipc message Idle 6
2821 PX Deq: reap credit Other 0
3131 DIAG idle wait Idle 6
3132 jobq slave wait Idle 6
3444 rdbms ipc message Idle 6
3445 jobq slave wait Idle 6
3757 ges remote message Idle 6
3758 rdbms ipc message Idle 6
3759 VKRM Idle Idle 6
4070 gcs remote message Idle 6
4072 rdbms ipc message Idle 6
4383 gcs remote message Idle 6
4385 PX Deq: Execution Msg Idle 6
4696 rdbms ipc message Idle 6
4697 rdbms ipc message Idle 6
1 rdbms ipc message Idle 6
2 heartbeat monitor sleep Idle 6
3 rdbms ipc message Idle 6
4 wait for unread message on broadcast channel Idle 6
6 SQL*Net message from client Idle 6


From this output, what are the waits events i need to take into consideration.
Is this the right approach to follow?

Please let me know.
Thanks in advance.