TIME -------------------- FEB-01-2008 16:15:12 1 row selected. INST_ID INSTANCE_NAME HOST_NAME VERSION STATUS STARTUP_TIME ------- ---------------- -------------------- ----------------- ------------ -------------------- 1 rman1 rac1.colestock.test 10.2.0.3.0 OPEN FEB-01-2008 15:09:46 2 rman2 rac2.colestock.test 10.2.0.3.0 OPEN FEB-01-2008 14:52:06 2 rows selected. SQL> SQL> -- Taking Hang Analyze dumps SQL> -- This may take a little while... SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug -g all hanganalyze 3 Hang Analysis in /u02/app/oradata/rman/admin/bdump/rman2_diag_9622.trc SQL> -- This part may take the longest, you can monitor bdump or udump to see if the SQL> -- file is being generated. SQL> oradebug -g all dump systemstate 267 Statement processed. SQL> SQL> -- WAITING SESSIONS: SQL> -- The entries that are shown at the top are the sessions that have SQL> -- waited the longest amount of time that are waiting for non-idle wait SQL> -- events (event column). You can research and find out what the wait SQL> -- event indicates (along with its parameters) by checking the Oracle SQL> -- Server Reference Manual or look for any known issues or documentation SQL> -- by searching Metalink for the event name in the search bar. Example SQL> -- (include single quotes): [ 'buffer busy due to global cache' ]. SQL> -- Metalink and/or the Server Reference Manual should return some useful SQL> -- information on each type of wait event. The inst_id column shows the SQL> -- instance where the session resides and the SID is the unique identifier SQL> -- for the session (gv$session). The p1, p2, and p3 columns will show SQL> -- event specific information that may be important to debug the problem. SQL> -- To find out what the p1, p2, and p3 indicates see the next section. SQL> -- Items with wait_time of anything other than 0 indicate we do not know SQL> -- how long these sessions have been waiting. SQL> -- SQL> set numwidth 10 SQL> column state format a7 tru SQL> column event format a25 tru SQL> column last_sql format a40 tru SQL> select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds, 2 sw.p1, sw.p2, sw.p3, sa.sql_text last_sql 3 from gv$session_wait sw, gv$session s, gv$sqlarea sa 4 where sw.event not in 5 ('rdbms ipc message','smon timer','pmon timer', 6 'SQL*Net message from client','lock manager wait for remote message', 7 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 8 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 9 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 10 'listen endpoint status','slave wait','wakeup time manager') 11 and sw.seconds_in_wait > 0 12 and (sw.inst_id = s.inst_id and sw.sid = s.sid) 13 and (s.inst_id = sa.inst_id and s.sql_address = sa.address) 14 order by seconds desc; INST_ID SID STATE EVENT SECONDS P1 P2 P3 LAST_SQL ---------- ---------- ------- ------------------------- ---------- ---------- ---------- ---------- ---------------------------------------- 1 121 WAITING enq: TX - row lock conten 3230 1415053318 262172 272 update sh.sales set quantity_sold=100 1 row selected. SQL> SQL> -- EVENT PARAMETER LOOKUP: SQL> -- This section will give a description of the parameter names of the SQL> -- events seen in the last section. p1test is the parameter value for SQL> -- p1 in the WAITING SESSIONS section while p2text is the parameter SQL> -- value for p3 and p3 text is the parameter value for p3. The SQL> -- parameter values in the first section can be helpful for debugging SQL> -- the wait event. SQL> -- SQL> column event format a30 tru SQL> column p1text format a25 tru SQL> column p2text format a25 tru SQL> column p3text format a25 tru SQL> select distinct event, p1text, p2text, p3text 2 from gv$session_wait sw 3 where sw.event not in ('rdbms ipc message','smon timer','pmon timer', 4 'SQL*Net message from client','lock manager wait for remote message', 5 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 6 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 7 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 8 'listen endpoint status','slave wait','wakeup time manager') 9 and sw.seconds_in_wait > 0 10 order by event; EVENT P1TEXT P2TEXT P3TEXT ------------------------------ ------------------------- ------------------------- ------------------------- Streams AQ: qmn coordinator id Streams AQ: qmn slave idle wai Streams AQ: waiting for messag queue id process# wait time Streams AQ: waiting for time m enq: TX - row lock contention name|mode usn<<16 | slot sequence 5 rows selected. SQL> SQL> -- GES LOCK BLOCKERS: SQL> -- This section will show us any sessions that are holding locks that SQL> -- are blocking other users. The inst_id will show us the instance that SQL> -- the session resides on while the sid will be a unique identifier for SQL> -- the session. The grant_level will show us how the GES lock is granted to SQL> -- the user. The request_level will show us what status we are trying to obtain. SQL> -- The lockstate column will show us what status the lock is in. The last column SQL> -- shows how long this session has been waiting. SQL> -- SQL> set numwidth 5 SQL> column state format a16 tru; SQL> column event format a30 tru; SQL> select dl.inst_id, s.sid, p.spid, dl.resource_name1, 2 decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 3 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 4 'KJUSEREX','Exclusive',request_level) as grant_level, 5 decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 6 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 7 'KJUSEREX','Exclusive',request_level) as request_level, 8 decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening', 9 'KJUSERCA','Canceling','KJUSERCV','Converting') as state, 10 s.sid, sw.event, sw.seconds_in_wait sec 11 from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw 12 where blocker = 1 13 and (dl.inst_id = p.inst_id and dl.pid = p.spid) 14 and (p.inst_id = s.inst_id and p.addr = s.paddr) 15 and (s.inst_id = sw.inst_id and s.sid = sw.sid) 16 order by sw.seconds_in_wait desc; INST_ID SID SPID RESOURCE_NAME1 GRANT_LEVEL REQUEST_LEVEL STATE SID EVENT SEC ------- ----- ------------ ------------------------------ ------------- ------------- ---------------- ----- ------------------------------ ----- 1 121 23997 [0x4001c][0x110],[TX] Exclusive Exclusive 121 enq: TX - row lock contention 3233 2 158 9644 [0x19][0x2],[RS] Exclusive Exclusive 158 rdbms ipc message 0 2 rows selected. SQL> SQL> -- GES LOCK WAITERS: SQL> -- This section will show us any sessions that are waiting for locks that SQL> -- are blocked by other users. The inst_id will show us the instance that SQL> -- the session resides on while the sid will be a unique identifier for SQL> -- the session. The grant_level will show us how the GES lock is granted to SQL> -- the user. The request_level will show us what status we are trying to obtain. SQL> -- The lockstate column will show us what status the lock is in. The last column SQL> -- shows how long this session has been waiting. SQL> -- SQL> set numwidth 5 SQL> column state format a16 tru; SQL> column event format a30 tru; SQL> select dl.inst_id, s.sid, p.spid, dl.resource_name1, 2 decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 3 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 4 'KJUSEREX','Exclusive',request_level) as grant_level, 5 decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)', 6 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)', 7 'KJUSEREX','Exclusive',request_level) as request_level, 8 decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening', 9 'KJUSERCA','Cancelling','KJUSERCV','Converting') as state, 10 s.sid, sw.event, sw.seconds_in_wait sec 11 from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw 12 where blocked = 1 13 and (dl.inst_id = p.inst_id and dl.pid = p.spid) 14 and (p.inst_id = s.inst_id and p.addr = s.paddr) 15 and (s.inst_id = sw.inst_id and s.sid = sw.sid) 16 order by sw.seconds_in_wait desc; INST_ID SID SPID RESOURCE_NAME1 GRANT_LEVEL REQUEST_LEVEL STATE SID EVENT SEC ------- ----- ------------ ------------------------------ ------------- ------------- ---------------- ----- ------------------------------ ----- 1 121 23997 [0x4001c][0x110],[TX] Null Exclusive 121 enq: TX - row lock contention 3233 1 158 19193 [0x19][0x2],[RS] Null Exclusive 158 rdbms ipc message 3 2 rows selected. SQL> SQL> -- LOCAL ENQUEUES: SQL> -- This section will show us if there are any local enqueues. The inst_id will SQL> -- show us the instance that the session resides on while the sid will be a SQL> -- unique identifier for. The addr column will show the lock address. The type SQL> -- will show the lock type. The id1 and id2 columns will show specific parameters SQL> -- for the lock type. SQL> -- SQL> set numwidth 12 SQL> column event format a12 tru SQL> select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2, 2 decode(l.block,0,'blocked',1,'blocking',2,'global') block, 3 sw.event, sw.seconds_in_wait sec 4 from gv$lock l, gv$session_wait sw 5 where (l.sid = sw.sid and l.inst_id = sw.inst_id) 6 and l.block in (0,1) 7 order by l.type, l.inst_id, l.sid; INST_ID SID ADDR TY ID1 ID2 BLOCK EVENT SEC ------------ ------------ -------- -- ------------ ------------ -------- ------------ ------------ 1 120 2E315334 TX 262172 272 blocking SQL*Net mess 3117 1 121 2FFC2E58 TX 262172 272 blocked enq: TX - ro 3233 2 rows selected. SQL> SQL> -- LATCH HOLDERS: SQL> -- If there is latch contention or 'latch free' wait events in the WAITING SQL> -- SESSIONS section we will need to find out which proceseses are holding SQL> -- latches. The inst_id will show us the instance that the session resides SQL> -- on while the sid will be a unique identifier for. The username column SQL> -- will show the session's username. The os_user column will show the os SQL> -- user that the user logged in as. The name column will show us the type SQL> -- of latch being waited on. You can search Metalink for the latch name in SQL> -- the search bar. Example (include single quotes): SQL> -- [ 'library cache' latch ]. Metalink should return some useful information SQL> -- on the type of latch. SQL> -- SQL> set numwidth 5 SQL> select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name 2 from gv$latchholder lh, gv$session s, gv$process p 3 where (lh.sid = s.sid and lh.inst_id = s.inst_id) 4 and (s.inst_id = p.inst_id and s.paddr = p.addr) 5 order by lh.inst_id, s.sid; no rows selected SQL> SQL> -- LATCH STATS: SQL> -- This view will show us latches with less than optimal hit ratios SQL> -- The inst_id will show us the instance for the particular latch. The SQL> -- latch_name column will show us the type of latch. You can search Metalink SQL> -- for the latch name in the search bar. Example (include single quotes): SQL> -- [ 'library cache' latch ]. Metalink should return some useful information SQL> -- on the type of latch. The hit_ratio shows the percentage of time we SQL> -- successfully acquired the latch. SQL> -- SQL> column latch_name format a30 tru SQL> select inst_id, name latch_name, 2 round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio, 3 round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS" 4 from gv$latch 5 where round((gets-misses)/decode(gets,0,1,gets),3) < .99 6 and gets != 0 7 order by round((gets-misses)/decode(gets,0,1,gets),3); INST_ID LATCH_NAME HIT_RATIO SLEEPS/MISS ------- ------------------------------ --------- ----------- 1 slave class create .947 1 2 slave class create .961 1 2 user lock .971 0 3 rows selected. SQL> SQL> -- No Wait Latches: SQL> -- SQL> select inst_id, name latch_name, 2 round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio, 3 round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS" 4 from gv$latch 5 where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99 6 and immediate_gets + immediate_misses > 0 7 order by round((immediate_gets/(immediate_gets+immediate_misses)), 3); select inst_id, name latch_name, * ERROR at line 1: ORA-12801: error signaled in parallel query server PZ99, instance rac1.colestock.test:rman1 (1) ORA-01476: divisor is equal to zero SQL> SQL> -- GLOBAL CACHE CR PERFORMANCE SQL> -- This shows the average latency of a consistent block request. SQL> -- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds depending SQL> -- on your system configuration and volume, is the average latency of a SQL> -- consistent-read request round-trip from the requesting instance to the holding SQL> -- instance and back to the requesting instance. If your CPU has limited idle time SQL> -- and your system typically processes long-running queries, then the latency may SQL> -- be higher. However, it is possible to have an average latency of less than one SQL> -- millisecond with User-mode IPC. Latency can be influenced by a high value for SQL> -- the DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process SQL> -- can issue more than one request for a block depending on the setting of this SQL> -- parameter. Correspondingly, the requesting process may wait longer. Also check SQL> -- interconnect badwidth, OS tcp settings, and OS udp settings if SQL> -- AVG CR BLOCK RECEIVE TIME is high. SQL> -- SQL> set numwidth 20 SQL> column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9 SQL> select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED", 2 b1.value "GCS CR BLOCK RECEIVE TIME", 3 ((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)" 4 from gv$sysstat b1, gv$sysstat b2 5 where b1.name = 'global cache cr block receive time' and 6 b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id 7 or b1.name = 'gc cr block receive time' and 8 b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ; INST_ID GCS CR BLOCKS RECEIVED GCS CR BLOCK RECEIVE TIME AVG CR BLOCK RECEIVE TIME (ms) -------------------- ---------------------- ------------------------- ------------------------------ 2 97 16 1.6 1 384 33 .9 2 rows selected. SQL> SQL> -- GLOBAL CACHE LOCK PERFORMANCE SQL> -- This shows the average global enqueue get time. SQL> -- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the elapsed SQL> -- time for a get includes the allocation and initialization of a new global SQL> -- enqueue. If the average global enqueue get (global cache get time) or average SQL> -- global enqueue conversion times are excessive, then your system may be SQL> -- experiencing timeouts. See the 'WAITING SESSIONS', 'GES LOCK BLOCKERS', SQL> -- 'GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' sections if the SQL> -- AVG GLOBAL LOCK GET TIME is high. SQL> -- SQL> set numwidth 20 SQL> column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9 SQL> select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS", 2 b3.value "GLOBAL LOCK GET TIME", 3 (b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)" 4 from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3 5 where b1.name = 'global lock sync gets' and 6 b2.name = 'global lock async gets' and b3.name = 'global lock get time' 7 and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id 8 or b1.name = 'global enqueue gets sync' and 9 b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time' 10 and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id; INST_ID GLOBAL LOCK GETS GLOBAL LOCK GET TIME AVG GLOBAL LOCK GET TIME (ms) -------------------- -------------------- -------------------- ----------------------------- 2 42536 2533 .6 1 35690 2300 .6 2 rows selected. SQL> SQL> -- RESOURCE USAGE SQL> -- This section will show how much of our resources we have used. SQL> -- SQL> set numwidth 8 SQL> select inst_id, resource_name, current_utilization, max_utilization, 2 initial_allocation 3 from gv$resource_limit 4 where max_utilization > 0 5 order by inst_id, resource_name; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL -------- ------------------------------ ------------------- --------------- ---------- 1 cmtcallbk 0 1 187 1 dml_locks 19 70 748 1 enqueue_locks 18 40 2302 1 enqueue_resources 35 57 968 1 gcs_resources 4784 5016 9379 1 gcs_shadows 7533 7587 9379 1 ges_big_msgs 19 34 964 1 ges_cache_ress 356 576 0 1 ges_procs 48 49 320 1 ges_reg_msgs 90 118 1050 1 max_rollback_segments 11 11 187 1 max_shared_servers 1 1 UNLIMITED 1 parallel_max_servers 4 4 40 1 processes 49 51 150 1 sessions 63 67 170 1 sort_segment_locks 0 1 UNLIMITED 1 transactions 3 6 187 2 cmtcallbk 0 2 187 2 dml_locks 0 56 748 2 enqueue_locks 19 44 2302 2 enqueue_resources 18 39 968 2 gcs_resources 1562 1868 2758 2 gcs_shadows 1730 2718 2758 2 ges_big_msgs 30 64 964 2 ges_cache_ress 348 554 0 2 ges_procs 47 50 320 2 ges_reg_msgs 83 214 1050 2 max_rollback_segments 11 11 187 2 max_shared_servers 1 1 UNLIMITED 2 parallel_max_servers 4 4 40 2 processes 48 52 150 2 sessions 60 66 170 INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL -------- ------------------------------ ------------------- --------------- ---------- 2 sort_segment_locks 0 1 UNLIMITED 2 transactions 0 9 187 34 rows selected. SQL> SQL> -- DLM TRAFFIC INFORMATION SQL> -- This section shows how many tickets are available in the DLM. If the SQL> -- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which could SQL> -- cause a DLM hang. Make sure that you also have enough TCKT_AVAIL. SQL> -- SQL> set numwidth 5 SQL> select * from gv$dlm_traffic_controller 2 order by TCKT_AVAIL; INST_ID LOCAL_NID REMOTE_NID REMOTE_RID REMOTE_INC TCKT_AVAIL TCKT_LIMIT TCKT_RCVD TCKT_WAIT SND_SEQ_NO RCV_SEQ_NO SND_Q_LEN SND_Q_MAX SND_Q_TOT SND_Q_TM_BASE SND_Q_TM_WRAP STATUS SND_PROXY ------- --------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- --------- --------- --------- ------------- ------------- ------ --------- 2 1 0 1 4 749 1000 0 NO 9546 0 0 84 6106 46 0 ##### 7 1 0 1 2 4 750 1000 3 NO 5413 0 0 26 2789 0 0 ##### 8 1 0 1 0 4 750 1000 3 NO 17025 0 0 28 12513 227 0 65601 6 1 0 1 1 4 750 1000 3 NO 7826 0 0 18 3994 0 0 ##### 7 2 1 0 0 4 750 1000 0 NO 17808 0 0 31 13515 289 0 65601 6 2 1 0 2 4 750 1000 0 NO 7022 0 0 101 4840 44 0 ##### 8 6 rows selected. SQL> SQL> -- DLM MISC SQL> -- SQL> set numwidth 10 SQL> select * from gv$dlm_misc; INST_ID STATISTIC# NAME VALUE ---------- ---------- -------------------------------------- ---------- 2 0 messages sent directly 6664 2 1 messages flow controlled 5373 2 2 messages sent indirectly 12973 2 3 messages received logical 36968 2 4 flow control messages sent 0 2 5 flow control messages received 0 2 6 gcs msgs received 18811 2 7 gcs msgs process time(ms) 580 2 8 ges msgs received 18157 2 9 ges msgs process time(ms) 725 2 10 msgs causing lmd to send msgs 8626 2 11 lmd msg send time(ms) 0 2 12 gcs side channel msgs actual 126 2 13 gcs side channel msgs logical 9172 2 14 gcs pings refused 0 2 15 gcs writes refused 1 2 16 gcs error msgs 0 2 17 gcs out-of-order msgs 0 2 18 gcs immediate (null) converts 650 2 19 gcs immediate cr (null) converts 14441 2 20 gcs immediate (compatible) converts 269 2 21 gcs immediate cr (compatible) converts 0 2 22 gcs blocked converts 54 2 23 gcs queued converts 0 2 24 gcs blocked cr converts 95 2 25 gcs compatible basts 4 2 26 gcs compatible cr basts (local) 2506 2 27 gcs cr basts to PIs 0 2 28 dynamically allocated gcs resources 0 2 29 dynamically allocated gcs shadows 0 2 30 gcs recovery claim msgs 0 2 31 gcs indirect ast 1769 INST_ID STATISTIC# NAME VALUE ---------- ---------- -------------------------------------- ---------- 2 32 gcs dbwr write request msgs 75 2 33 gcs dbwr flush pi msgs 55 2 34 gcs lms write request msgs 9 2 35 gcs lms flush pi msgs 0 2 36 gcs write notification msgs 0 2 37 gcs retry convert request 21 2 38 gcs regular cr 0 2 39 gcs undo cr 0 2 40 gcs stale cr 101 2 41 gcs assume no cvt 464 2 42 gcs assume cvt 0 2 43 broadcast msgs on commit(actual) 1161 2 44 broadcast msgs on commit(logical) 1384 2 45 broadcast msgs on commit(wasted) 156 2 46 acks for commit broadcast(actual) 1152 2 47 acks for commit broadcast(logical) 1208 2 48 false posts waiting for scn acks 0 1 0 messages sent directly 8685 1 1 messages flow controlled 6358 1 2 messages sent indirectly 7061 1 3 messages received logical 48621 1 4 flow control messages sent 0 1 5 flow control messages received 0 1 6 gcs msgs received 29842 1 7 gcs msgs process time(ms) 1129 1 8 ges msgs received 18779 1 9 ges msgs process time(ms) 921 1 10 msgs causing lmd to send msgs 6723 1 11 lmd msg send time(ms) 0 1 12 gcs side channel msgs actual 85 1 13 gcs side channel msgs logical 4178 1 14 gcs pings refused 4 INST_ID STATISTIC# NAME VALUE ---------- ---------- -------------------------------------- ---------- 1 15 gcs writes refused 0 1 16 gcs error msgs 0 1 17 gcs out-of-order msgs 0 1 18 gcs immediate (null) converts 544 1 19 gcs immediate cr (null) converts 12966 1 20 gcs immediate (compatible) converts 295 1 21 gcs immediate cr (compatible) converts 4 1 22 gcs blocked converts 395 1 23 gcs queued converts 0 1 24 gcs blocked cr converts 382 1 25 gcs compatible basts 79 1 26 gcs compatible cr basts (local) 6605 1 27 gcs cr basts to PIs 0 1 28 dynamically allocated gcs resources 0 1 29 dynamically allocated gcs shadows 0 1 30 gcs recovery claim msgs 0 1 31 gcs indirect ast 1771 1 32 gcs dbwr write request msgs 18 1 33 gcs dbwr flush pi msgs 95 1 34 gcs lms write request msgs 29 1 35 gcs lms flush pi msgs 0 1 36 gcs write notification msgs 4 1 37 gcs retry convert request 0 1 38 gcs regular cr 0 1 39 gcs undo cr 0 1 40 gcs stale cr 21 1 41 gcs assume no cvt 5318 1 42 gcs assume cvt 0 1 43 broadcast msgs on commit(actual) 1208 1 44 broadcast msgs on commit(logical) 1369 1 45 broadcast msgs on commit(wasted) 118 1 46 acks for commit broadcast(actual) 1066 INST_ID STATISTIC# NAME VALUE ---------- ---------- -------------------------------------- ---------- 1 47 acks for commit broadcast(logical) 1161 1 48 false posts waiting for scn acks 0 2 49 gcs forward cr to pinged instance 0 2 50 gcs cr serve without current lock 0 2 51 msgs sent queued 24463 2 52 msgs sent queue time (ms) 3159 2 53 msgs sent queued on ksxp 25942 2 54 msgs sent queue time on ksxp (ms) 11495 2 55 msgs received queue time (ms) 4401 2 56 msgs received queued 36968 2 57 implicit batch messages sent 1332 2 58 implicit batch messages received 1226 2 59 gcs refuse xid 0 2 60 gcs ast xid 0 2 61 gcs compatible cr basts (global) 0 2 62 gcs pkey conflicts retry 0 2 63 messages received actual 26082 2 64 process batch messages sent 12311 2 65 process batch messages received 9234 2 66 messages sent pbatched 26449 2 67 msgs causing lms(s) to send msgs 2035 2 68 lms(s) msg send time(ms) 0 2 69 global posts requested 10 2 70 global posts dropped 0 2 71 global posts queued 10 2 72 global posts sent 10 2 73 global posts queue time 76 2 74 messages sent not implicit batched 14693 2 75 messages queue sent actual 16025 2 76 messages queue sent logical 24500 1 49 gcs forward cr to pinged instance 0 1 50 gcs cr serve without current lock 0 INST_ID STATISTIC# NAME VALUE ---------- ---------- -------------------------------------- ---------- 1 51 msgs sent queued 19298 1 52 msgs sent queue time (ms) 1723 1 53 msgs sent queued on ksxp 26082 1 54 msgs sent queue time on ksxp (ms) 11350 1 55 msgs received queue time (ms) 5026 1 56 msgs received queued 48622 1 57 implicit batch messages sent 1226 1 58 implicit batch messages received 1332 1 59 gcs refuse xid 0 1 60 gcs ast xid 0 1 61 gcs compatible cr basts (global) 13 1 62 gcs pkey conflicts retry 0 1 63 messages received actual 25942 1 64 process batch messages sent 9299 1 65 process batch messages received 12229 1 66 messages sent pbatched 15897 1 67 msgs causing lms(s) to send msgs 1602 1 68 lms(s) msg send time(ms) 0 1 69 global posts requested 5 1 70 global posts dropped 0 1 71 global posts queued 5 1 72 global posts sent 5 1 73 global posts queue time 12 1 74 messages sent not implicit batched 13886 1 75 messages queue sent actual 15113 1 76 messages queue sent logical 19390 154 rows selected. SQL> SQL> -- LOCK CONVERSION DETAIL: SQL> -- This view shows the types of lock conversion being done on each instance. SQL> -- SQL> select * from gv$lock_activity; INST_ID FROM T ACTION_VAL COUNTER ---------- ---- - --------------------- ---------- 0 NULL S Lock buffers for read 0 0 NULL S Lock buffers for read 0 2 rows selected. SQL> SQL> -- TOP 10 WRITE PINGING/FUSION OBJECTS SQL> -- This view shows the top 10 objects for write pings accross instances. SQL> -- The inst_id column shows the node that the block was pinged on. The name SQL> -- column shows the object name of the offending object. The file# shows the SQL> -- offending file number (gc_files_to_locks). The STATUS column will show the SQL> -- current status of the pinged block. The READ_PINGS will show us read converts SQL> -- and the WRITE_PINGS will show us objects with write converts. Any rows that SQL> -- show up are objects that are concurrently accessed across more than 1 instance. SQL> -- SQL> set numwidth 8 SQL> column name format a20 tru SQL> column kind format a10 tru SQL> select inst_id, name, kind, file#, status, BLOCKS, 2 READ_PINGS, WRITE_PINGS 3 from (select p.inst_id, p.name, p.kind, p.file#, p.status, 4 count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, 5 sum(p.forced_writes) WRITE_PINGS 6 from gv$ping p, gv$datafile df 7 where p.file# = df.file# (+) 8 group by p.inst_id, p.name, p.kind, p.file#, p.status 9 order by sum(p.forced_writes) desc) 10 where rownum < 11 11 order by WRITE_PINGS desc; no rows selected SQL> SQL> -- TOP 10 READ PINGING/FUSION OBJECTS SQL> -- This view shows the top 10 objects for read pings. The inst_id column shows SQL> -- the node that the block was pinged on. The name column shows the object name SQL> -- of the offending object. The file# shows the offending file number SQL> -- (gc_files_to_locks). The STATUS column will show the current status of the SQL> -- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS SQL> -- will show us objects with write converts. Any rows that show up are objects SQL> -- that are concurrently accessed across more than 1 instance. SQL> -- SQL> set numwidth 8 SQL> column name format a20 tru SQL> column kind format a10 tru SQL> select inst_id, name, kind, file#, status, BLOCKS, 2 READ_PINGS, WRITE_PINGS 3 from (select p.inst_id, p.name, p.kind, p.file#, p.status, 4 count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, 5 sum(p.forced_writes) WRITE_PINGS 6 from gv$ping p, gv$datafile df 7 where p.file# = df.file# (+) 8 group by p.inst_id, p.name, p.kind, p.file#, p.status 9 order by sum(p.forced_reads) desc) 10 where rownum < 11 11 order by READ_PINGS desc; no rows selected SQL> SQL> -- TOP 10 FALSE PINGING OBJECTS SQL> -- This view shows the top 10 objects for false pings. This can be avoided by SQL> -- better gc_files_to_locks configuration. The inst_id column shows the node SQL> -- that the block was pinged on. The name column shows the object name of the SQL> -- offending object. The file# shows the offending file number SQL> -- (gc_files_to_locks). The STATUS column will show the current status of the SQL> -- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS SQL> -- will show us objects with write converts. Any rows that show up are objects SQL> -- that are concurrently accessed across more than 1 instance. SQL> -- SQL> set numwidth 8 SQL> column name format a20 tru SQL> column kind format a10 tru SQL> select inst_id, name, kind, file#, status, BLOCKS, 2 READ_PINGS, WRITE_PINGS 3 from (select p.inst_id, p.name, p.kind, p.file#, p.status, 4 count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, 5 sum(p.forced_writes) WRITE_PINGS 6 from gv$false_ping p, gv$datafile df 7 where p.file# = df.file# (+) 8 group by p.inst_id, p.name, p.kind, p.file#, p.status 9 order by sum(p.forced_writes) desc) 10 where rownum < 11 11 order by WRITE_PINGS desc; no rows selected SQL> SQL> -- INITIALIZATION PARAMETERS: SQL> -- Non-default init parameters for each node. SQL> -- SQL> set numwidth 5 SQL> column name format a30 tru SQL> column value format a50 wra SQL> column description format a60 tru SQL> select inst_id, name, value, description 2 from gv$parameter 3 where isdefault = 'FALSE' 4 order by inst_id, name; INST_ID NAME VALUE DESCRIPTION ------- ------------------------------ -------------------------------------------------- ------------------------------------------------------------ 1 _no_recovery_through_resetlogs TRUE no recovery through this resetlogs operation 1 aq_tm_processes 2 number of AQ Time Managers to start 1 audit_file_dest /u02/app/oradata/rman/admin/adump Directory in which auditing files are to reside 1 background_dump_dest /u02/app/oradata/rman/admin/bdump Detached process dump directory 1 cluster_database TRUE if TRUE startup in cluster database mode 1 cluster_database_instances 2 number of instances to use for sizing cluster db SGA structu 1 compatible 10.2.0.3.0 Database will be completely compatible with this software ve 1 control_files /u02/app/oradata/rman/control01.ctl, /u02/app/orad control file names list ata2/rman/control02.ctl 1 core_dump_dest /u02/app/oradata/rman/admin/cdump Core dump directory 1 db_block_size 8192 Size of database block in bytes 1 db_domain colestock.test directory part of global database name stored with CREATE DA 1 db_file_multiblock_read_count 16 db block to be read each IO 1 db_name rman database name specified in CREATE DATABASE 1 dispatchers (PROTOCOL=TCP) (SERVICE=rmanXDB) specifications of dispatchers 1 global_names TRUE enforce that database links have same name as remote databas 1 instance_number 1 instance number 1 job_queue_processes 10 number of job queue slave processes 1 local_listener listener_rman1 local listener 1 log_archive_dest_1 LOCATION=/u02/app/oradata/rman/arch archival destination #1 text string 1 log_archive_dest_2 SERVICE=stage.colestock.test arch optional noregis archival destination #2 text string ter reopen=60 template=/tmp/from_the_source_%s_%t_ %r.arc 1 log_archive_dest_state_2 ENABLE archival destination #2 state text string 1 log_archive_format rman_%t_%s_%r.arc archival destination format 1 log_archive_max_processes 10 maximum number of active ARCH processes 1 open_cursors 300 max # cursors per session 1 pga_aggregate_target 121634816 Target size for the aggregate PGA memory consumed by the ins 1 processes 150 user processes 1 remote_listener listeners_rman remote listener INST_ID NAME VALUE DESCRIPTION ------- ------------------------------ -------------------------------------------------- ------------------------------------------------------------ 1 remote_login_passwordfile SHARED password file usage parameter 1 sga_target 268435456 Target size of SGA 1 statistics_level TYPICAL statistics level 1 thread 2 Redo thread to mount 1 undo_management AUTO instance runs in SMU mode if TRUE, else in RBU mode 1 undo_tablespace UNDOTBS1 use/switch undo tablespace 1 user_dump_dest /u02/app/oradata/rman/admin/udump User process dump directory 2 _no_recovery_through_resetlogs TRUE no recovery through this resetlogs operation 2 aq_tm_processes 2 number of AQ Time Managers to start 2 audit_file_dest /u02/app/oradata/rman/admin/adump Directory in which auditing files are to reside 2 background_dump_dest /u02/app/oradata/rman/admin/bdump Detached process dump directory 2 cluster_database TRUE if TRUE startup in cluster database mode 2 cluster_database_instances 2 number of instances to use for sizing cluster db SGA structu 2 compatible 10.2.0.3.0 Database will be completely compatible with this software ve 2 control_files /u02/app/oradata/rman/control01.ctl, /u02/app/orad control file names list ata2/rman/control02.ctl 2 core_dump_dest /u02/app/oradata/rman/admin/cdump Core dump directory 2 db_block_size 8192 Size of database block in bytes 2 db_domain colestock.test directory part of global database name stored with CREATE DA 2 db_file_multiblock_read_count 16 db block to be read each IO 2 db_name rman database name specified in CREATE DATABASE 2 dispatchers (PROTOCOL=TCP) (SERVICE=rmanXDB) specifications of dispatchers 2 global_names TRUE enforce that database links have same name as remote databas 2 instance_number 2 instance number 2 job_queue_processes 10 number of job queue slave processes 2 local_listener listener_rman2 local listener 2 log_archive_dest_1 LOCATION=/u02/app/oradata/rman/arch archival destination #1 text string 2 log_archive_dest_2 SERVICE=stage.colestock.test arch optional noregis archival destination #2 text string ter reopen=60 template=/tmp/from_the_source_%s_%t_ %r.arc INST_ID NAME VALUE DESCRIPTION ------- ------------------------------ -------------------------------------------------- ------------------------------------------------------------ 2 log_archive_dest_state_2 ENABLE archival destination #2 state text string 2 log_archive_format rman_%t_%s_%r.arc archival destination format 2 log_archive_max_processes 10 maximum number of active ARCH processes 2 open_cursors 300 max # cursors per session 2 pga_aggregate_target 121634816 Target size for the aggregate PGA memory consumed by the ins 2 processes 150 user processes 2 remote_listener listeners_rman remote listener 2 remote_login_passwordfile SHARED password file usage parameter 2 sga_target 268435456 Target size of SGA 2 statistics_level TYPICAL statistics level 2 thread 1 Redo thread to mount 2 undo_management AUTO instance runs in SMU mode if TRUE, else in RBU mode 2 undo_tablespace UNDOTBS2 use/switch undo tablespace 2 user_dump_dest /u02/app/oradata/rman/admin/udump User process dump directory 68 rows selected. SQL> SQL> -- TOP 10 WAIT EVENTS ON SYSTEM SQL> -- This view will provide a summary of the top wait events in the db. SQL> -- SQL> set numwidth 10 SQL> column event format a25 tru SQL> select inst_id, event, time_waited, total_waits, total_timeouts 2 from (select inst_id, event, time_waited, total_waits, total_timeouts 3 from gv$system_event where event not in ('rdbms ipc message','smon timer', 4 'pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 5 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 6 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 7 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 8 'listen endpoint status','slave wait','wakeup time manager') 9 order by time_waited desc) 10 where rownum < 11 11 order by time_waited desc; INST_ID EVENT TIME_WAITED TOTAL_WAITS TOTAL_TIMEOUTS ---------- ------------------------- ----------- ----------- -------------- 2 Streams AQ: waiting for t 968482 66 7 2 Streams AQ: qmn slave idl 504498 207 4 2 virtual circuit status 485029 159 158 2 Streams AQ: qmn coordinat 483070 370 177 2 dispatcher timer 482101 78 78 2 DIAG idle wait 480610 24768 0 1 Streams AQ: waiting for t 472340 51 0 1 Streams AQ: qmn slave idl 399579 160 0 1 virtual circuit status 399043 129 128 1 Streams AQ: qmn coordinat 397898 314 139 10 rows selected. SQL> SQL> -- SESSION/PROCESS REFERENCE: SQL> -- This section is very important for most of the above sections to find out SQL> -- which user/os_user/process is identified to which session/process. SQL> -- SQL> set numwidth 7 SQL> column event format a30 tru SQL> column program format a25 tru SQL> column username format a15 tru SQL> select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username, 2 p.username os_user, sw.event, sw.seconds_in_wait sec 3 from gv$process p, gv$session s, gv$session_wait sw 4 where (p.inst_id = s.inst_id and p.addr = s.paddr) 5 and (s.inst_id = sw.inst_id and s.sid = sw.sid) 6 order by p.inst_id, s.sid; INST_ID SID SERIAL# PID SPID PROGRAM USERNAME OS_USER EVENT SEC ------- ------- ------- ------- ------------ ------------------------- --------------- --------------- ------------------------------ ------- 1 105 751 22 19224 oracle@rac1.colestock.tes SYS oracle PX Deq: reap credit 0 1 108 307 49 2422 oraclerman1@rac1.colestoc SYS oracle SQL*Net message from client 726 1 115 17 45 24536 oraclerman1@rac1.colestoc SH oracle SQL*Net message from client 1398 1 119 2039 48 22033 oraclerman1@rac1.colestoc SYS oracle SQL*Net message from client 825 1 120 88 44 24129 oraclerman1@rac1.colestoc SH oracle SQL*Net message from client 3117 1 121 631 43 23997 oraclerman1@rac1.colestoc SH oracle enq: TX - row lock contention 3233 1 122 48 42 21142 oraclerman1@rac1.colestoc SCOTT oracle SQL*Net message from client 3780 1 123 282 41 20976 oraclerman1@rac1.colestoc SCOTT oracle SQL*Net message from client 3808 1 126 1 40 19588 oracle@rac1.colestock.tes oracle Streams AQ: qmn slave idle wai 4053 1 128 1 38 19449 oracle@rac1.colestock.tes oracle Streams AQ: waiting for time m 4053 1 130 4 37 19447 oracle@rac1.colestock.tes oracle Streams AQ: waiting for time m 4053 1 132 1 35 19421 oracle@rac1.colestock.tes SYS oracle Streams AQ: waiting for messag 0 1 133 2 36 19433 oracle@rac1.colestock.tes SYS oracle SQL*Net message from client 0 1 134 21 34 19426 oracle@rac1.colestock.tes SYS oracle SQL*Net message from client 0 1 137 11 33 19334 oracle@rac1.colestock.tes oracle Streams AQ: qmn coordinator id 12 1 139 1 32 19317 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 140 1 31 19315 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 141 1 30 19313 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 142 1 29 19311 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 143 1 28 19309 oracle@rac1.colestock.tes oracle rdbms ipc message 9 1 144 1 27 19307 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 145 1 26 19305 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 146 1 25 19303 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 147 1 24 19301 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 148 14 23 19299 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 150 12 21 19386 oracle@rac1.colestock.tes SYS oracle SQL*Net message from client 486 1 152 1 20 19214 oracle@rac1.colestock.tes oracle rdbms ipc message 0 1 153 1 17 19203 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 154 1 16 19201 oracle@rac1.colestock.tes oracle rdbms ipc message 0 1 155 1 15 19199 oracle@rac1.colestock.tes oracle rdbms ipc message 9 1 156 1 14 19197 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 157 1 13 19195 oracle@rac1.colestock.tes oracle smon timer 12 INST_ID SID SERIAL# PID SPID PROGRAM USERNAME OS_USER EVENT SEC ------- ------- ------- ------- ------------ ------------------------- --------------- --------------- ------------------------------ ------- 1 158 1 12 19193 oracle@rac1.colestock.tes oracle rdbms ipc message 3 1 159 1 11 19191 oracle@rac1.colestock.tes oracle rdbms ipc message 0 1 160 1 10 19189 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 161 1 9 19187 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 163 1 8 19183 oracle@rac1.colestock.tes oracle gcs remote message 827 1 165 1 7 19179 oracle@rac1.colestock.tes oracle gcs remote message 3187 1 166 1 6 19177 oracle@rac1.colestock.tes oracle ges remote message 294 1 167 1 5 19175 oracle@rac1.colestock.tes oracle rdbms ipc message 0 1 168 1 4 19173 oracle@rac1.colestock.tes oracle rdbms ipc message 12 1 169 1 3 19171 oracle@rac1.colestock.tes oracle DIAG idle wait 0 1 170 1 2 19169 oracle@rac1.colestock.tes oracle pmon timer 3135 2 107 236 35 5351 oraclerman2@rac2.colestoc SYS oracle SQL*Net message to client 0 2 110 224 21 22243 oracle@rac2.colestock.tes oracle Streams AQ: qmn slave idle wai 871 2 112 217 23 17556 oraclerman2@rac2.colestoc SCOTT oracle SQL*Net message from client 3814 2 113 2 45 9946 oracle@rac2.colestock.tes SYS oracle SQL*Net message from client 60 2 114 2 44 9939 oracle@rac2.colestock.tes SYS oracle SQL*Net message from client 3 2 116 3 42 9904 oracle@rac2.colestock.tes oracle Streams AQ: waiting for time m 3032 2 117 6 43 9934 oracle@rac2.colestock.tes SYS oracle SQL*Net message from client 120 2 118 517 36 20758 oraclerman2@rac2.colestoc SH oracle SQL*Net message from client 3304 2 119 100 38 17946 oraclerman2@rac2.colestoc SCOTT oracle SQL*Net message from client 3721 2 120 177 46 21282 oraclerman2@rac2.colestoc SH oracle SQL*Net message from client 1570 2 123 2 41 9902 oracle@rac2.colestock.tes oracle Streams AQ: waiting for time m 3799 2 124 2536 22 15972 oracle@rac2.colestock.tes SYS oracle PX Deq: Execution Msg 0 2 126 1 34 9755 oracle@rac2.colestock.tes oracle Streams AQ: qmn coordinator id 12 2 139 1 33 9728 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 140 1 32 9726 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 141 1 31 9724 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 142 1 30 9722 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 143 1 29 9720 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 144 1 28 9718 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 145 1 27 9716 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 146 1 26 9714 oracle@rac2.colestock.tes oracle rdbms ipc message 12 INST_ID SID SERIAL# PID SPID PROGRAM USERNAME OS_USER EVENT SEC ------- ------- ------- ------- ------------ ------------------------- --------------- --------------- ------------------------------ ------- 2 147 3 24 9710 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 148 3 25 9712 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 150 12 40 9878 oracle@rac2.colestock.tes SYS oracle SQL*Net message from client 183 2 151 536 39 20923 oraclerman2@rac2.colestoc SH oracle SQL*Net message from client 3274 2 152 1 20 9665 oracle@rac2.colestock.tes oracle rdbms ipc message 0 2 153 1 17 9654 oracle@rac2.colestock.tes oracle rdbms ipc message 15 2 154 1 16 9652 oracle@rac2.colestock.tes oracle rdbms ipc message 3 2 155 1 15 9650 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 156 1 14 9648 oracle@rac2.colestock.tes oracle rdbms ipc message 15 2 157 1 13 9646 oracle@rac2.colestock.tes oracle smon timer 15 2 158 1 12 9644 oracle@rac2.colestock.tes oracle rdbms ipc message 3 2 159 1 11 9642 oracle@rac2.colestock.tes oracle rdbms ipc message 3 2 160 1 10 9640 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 161 1 9 9638 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 163 1 8 9634 oracle@rac2.colestock.tes oracle gcs remote message 1045 2 165 1 7 9630 oracle@rac2.colestock.tes oracle gcs remote message 1393 2 166 1 6 9628 oracle@rac2.colestock.tes oracle ges remote message 3 2 167 1 5 9626 oracle@rac2.colestock.tes oracle rdbms ipc message 0 2 168 1 4 9624 oracle@rac2.colestock.tes oracle rdbms ipc message 12 2 169 1 3 9622 oracle@rac2.colestock.tes oracle DIAG idle wait 3 2 170 1 2 9620 oracle@rac2.colestock.tes oracle pmon timer 3147 85 rows selected. SQL> SQL> -- SYSTEM STATISTICS: SQL> -- All System Stats with values of > 0. These can be referenced in the SQL> -- Server Reference Manual SQL> -- SQL> set numwidth 5 SQL> column name format a60 tru SQL> column value format 9999999999999999999999999 SQL> select inst_id, name, value 2 from gv$sysstat 3 where value > 0 4 order by inst_id, name; INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 CPU used by this session 6098 1 CPU used when call started 1658 1 CR blocks created 222 1 DB time 440601 1 DBWR checkpoint buffers written 27041 1 DBWR checkpoints 49 1 DBWR fusion writes 101 1 DBWR thread checkpoint buffers written 23343 1 DBWR transaction table writes 89 1 DBWR undo block writes 31457 1 DFO trees parallelized 83 1 PX local messages recv'd 632 1 PX local messages sent 635 1 PX remote messages recv'd 2334 1 PX remote messages sent 2445 1 Parallel operations not downgraded 83 1 SQL*Net roundtrips to/from client 5493 1 active txn count during cleanout 256 1 application wait time 316002 1 auto extends on undo tablespace 27 1 background checkpoints completed 10 1 background checkpoints started 10 1 background timeouts 15704 1 buffer is not pinned count 68978 1 buffer is pinned count 1080998 1 bytes received via SQL*Net from client 2872037 1 bytes sent via SQL*Net to client 724932 1 calls to get snapshot scn: kcmgss 34801 1 calls to kcmgas 41187 1 calls to kcmgcs 253 1 change write time 2484 1 cleanout - number of ktugct calls 265 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 cleanouts and rollbacks - consistent read gets 195 1 cleanouts only - consistent read gets 3 1 cluster key scan block gets 17603 1 cluster key scans 11138 1 cluster wait time 339 1 commit batch/immediate performed 3 1 commit batch/immediate requested 3 1 commit cleanout failures: callback failure 4 1 commit cleanouts 848 1 commit cleanouts successfully completed 844 1 commit immediate performed 3 1 commit immediate requested 3 1 commit txn count during cleanout 20 1 concurrency wait time 747 1 consistent changes 83074 1 consistent gets 234998 1 consistent gets - examination 163201 1 consistent gets from cache 235166 1 cursor authentications 274 1 data blocks consistent reads - undo records applied 83073 1 db block changes 3564236 1 db block gets 3523933 1 db block gets direct 7 1 db block gets from cache 3523942 1 deferred (CURRENT) block cleanout applications 514 1 dirty buffers inspected 14455 1 enqueue conversions 4134 1 enqueue releases 99373 1 enqueue requests 100479 1 enqueue timeouts 1069 1 enqueue waits 2683 1 execute count 20792 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 free buffer inspected 57571 1 free buffer requested 58167 1 gc CPU used by this session 527 1 gc cr block flush time 10 1 gc cr block receive time 33 1 gc cr blocks received 384 1 gc cr blocks served 97 1 gc current block flush time 128 1 gc current block pin time 6 1 gc current block receive time 195 1 gc current block send time 41 1 gc current blocks received 2106 1 gc current blocks served 7397 1 gc local grants 41132 1 gc remote grants 5810 1 gcs messages sent 18811 1 ges messages sent 18091 1 global enqueue get time 2302 1 global enqueue gets async 9408 1 global enqueue gets sync 26354 1 global enqueue releases 20996 1 heap block compress 160 1 hot buffers moved to head of LRU 2753 1 immediate (CR) block cleanout applications 198 1 immediate (CURRENT) block cleanout applications 73 1 index crx upgrade (positioned) 4060 1 index fetch by key 48458 1 index scans kdiixs1 12163 1 leaf node splits 16 1 lob reads 746 1 lob writes 8 1 lob writes unaligned 8 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 logons cumulative 683 1 logons current 43 1 messages received 14082 1 messages sent 13960 1 no buffer to keep pinned count 1 1 no work - consistent read gets 63051 1 opened cursors cumulative 17014 1 opened cursors current 90 1 parse count (failures) 9 1 parse count (hard) 1039 1 parse count (total) 9778 1 parse time cpu 421 1 parse time elapsed 867 1 physical read IO requests 5924 1 physical read bytes 126222336 1 physical read total IO requests 20462 1 physical read total bytes 846671360 1 physical read total multi block requests 1354 1 physical reads 15404 1 physical reads cache 15402 1 physical reads cache prefetch 9616 1 physical reads direct 6 1 physical reads direct temporary tablespace 1 1 physical reads prefetch warmup 104 1 physical write IO requests 15358 1 physical write bytes 331489280 1 physical write total IO requests 10612 1 physical write total bytes 2192269312 1 physical write total multi block requests 4505 1 physical writes 40465 1 physical writes direct 7 1 physical writes from cache 40458 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 physical writes non checkpoint 40291 1 pinned buffers inspected 508 1 prefetch warmup blocks aged out before use 77 1 prefetched blocks aged out before use 4 1 process last non-idle time 1201907888 1 queries parallelized 52 1 recursive calls 252581 1 recursive cpu usage 1391 1 redo blocks written 1043614 1 redo buffer allocation retries 120 1 redo entries 1769178 1 redo log space requests 58 1 redo log space wait time 1014 1 redo ordering marks 31377 1 redo size 516453420 1 redo subscn max counts 60 1 redo synch time 6 1 redo synch writes 168 1 redo wastage 463256 1 redo write time 8223 1 redo writes 1369 1 rollback changes - undo records applied 6 1 rollbacks only - consistent read gets 27 1 rows fetched via callback 13940 1 session connect time 1201904080 1 session cursor cache count 843 1 session cursor cache hits 12940 1 session logical reads 3758931 1 session pga memory 624822144 1 session pga memory max 1218464260 1 session uga memory 8615400344 1 session uga memory max 187620704 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 1 shared hash latch upgrades - no wait 5169 1 sorts (memory) 5148 1 sorts (rows) 155837 1 sql area evicted 84 1 sql area purged 33 1 summed dirty queue length 22835 1 switch current to new buffer 8323 1 table fetch by rowid 77913 1 table fetch continued row 187 1 table scan blocks gotten 22753 1 table scan rows gotten 2833605 1 table scans (long tables) 23 1 table scans (short tables) 1511 1 total number of times SMON posted 71 1 transaction lock background gets 21 1 transaction lock foreground requests 21 1 transaction lock foreground wait time 4 1 transaction rollbacks 3 1 undo change vector size 249421564 1 user I/O wait time 5742 1 user calls 8854 1 user commits 10 1 user rollbacks 4148 1 workarea executions - optimal 2687 1 write clones created in background 1 1 write clones created in foreground 24 2 CPU used by this session 2749 2 CPU used when call started 2831 2 CR blocks created 159 2 DB time 172151 2 DBWR checkpoint buffers written 966 2 DBWR checkpoints 44 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 DBWR fusion writes 57 2 DBWR thread checkpoint buffers written 114 2 DBWR transaction table writes 83 2 DBWR undo block writes 347 2 DFO trees parallelized 551 2 PX local messages recv'd 4196 2 PX local messages sent 4197 2 PX remote messages recv'd 2444 2 PX remote messages sent 2336 2 Parallel operations downgraded to serial 25 2 Parallel operations not downgraded 550 2 SQL*Net roundtrips to/from client 6913 2 active txn count during cleanout 65 2 application wait time 155 2 background checkpoints completed 3 2 background checkpoints started 3 2 background timeouts 23104 2 buffer is not pinned count 74964 2 buffer is pinned count 127988 2 bytes received via SQL*Net from client 1076424 2 bytes sent via SQL*Net to client 736198 2 calls to get snapshot scn: kcmgss 37936 2 calls to kcmgas 2735 2 calls to kcmgcs 270 2 change write time 52 2 cleanout - number of ktugct calls 91 2 cleanouts only - consistent read gets 14 2 cluster key scan block gets 15240 2 cluster key scans 7692 2 cluster wait time 400 2 commit cleanout failures: callback failure 11 2 commit cleanouts 2410 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 commit cleanouts successfully completed 2399 2 commit txn count during cleanout 49 2 concurrency wait time 770 2 consistent changes 159 2 consistent gets 154044 2 consistent gets - examination 66834 2 consistent gets direct 5 2 consistent gets from cache 153871 2 cursor authentications 475 2 data blocks consistent reads - undo records applied 158 2 db block changes 23125 2 db block gets 18831 2 db block gets direct 5 2 db block gets from cache 18810 2 deferred (CURRENT) block cleanout applications 1258 2 dirty buffers inspected 392 2 enqueue conversions 10430 2 enqueue releases 258099 2 enqueue requests 259093 2 enqueue timeouts 975 2 enqueue waits 3617 2 execute count 25708 2 free buffer inspected 19149 2 free buffer requested 22231 2 gc CPU used by this session 468 2 gc cr block flush time 11 2 gc cr block receive time 16 2 gc cr block send time 1 2 gc cr blocks received 97 2 gc cr blocks served 384 2 gc current block flush time 44 2 gc current block pin time 2 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 gc current block receive time 1165 2 gc current block send time 10 2 gc current blocks received 7397 2 gc current blocks served 2106 2 gc local grants 9611 2 gc remote grants 4361 2 gcs messages sent 29842 2 ges messages sent 18727 2 global enqueue get time 2536 2 global enqueue gets async 10863 2 global enqueue gets sync 31829 2 global enqueue releases 24888 2 heap block compress 36 2 hot buffers moved to head of LRU 1834 2 immediate (CR) block cleanout applications 14 2 immediate (CURRENT) block cleanout applications 414 2 index crx upgrade (positioned) 3973 2 index fetch by key 29940 2 index scans kdiixs1 14416 2 leaf node 90-10 splits 9 2 leaf node splits 19 2 lob reads 792 2 lob writes 52 2 lob writes unaligned 52 2 logons cumulative 723 2 logons current 42 2 messages received 15564 2 messages sent 15442 2 no buffer to keep pinned count 1 2 no work - consistent read gets 78698 2 opened cursors cumulative 20869 2 opened cursors current 95 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 parse count (failures) 17 2 parse count (hard) 1338 2 parse count (total) 12247 2 parse time cpu 393 2 parse time elapsed 1069 2 physical read IO requests 8436 2 physical read bytes 138919936 2 physical read total IO requests 25005 2 physical read total bytes 427869184 2 physical read total multi block requests 930 2 physical reads 16962 2 physical reads cache 13661 2 physical reads cache prefetch 5564 2 physical reads direct 3296 2 physical reads direct temporary tablespace 3246 2 physical reads prefetch warmup 64 2 physical write IO requests 1377 2 physical write bytes 38641664 2 physical write total IO requests 8943 2 physical write total bytes 124827136 2 physical write total multi block requests 1898 2 physical writes 4717 2 physical writes direct 3270 2 physical writes direct (lob) 5 2 physical writes direct temporary tablespace 3240 2 physical writes from cache 1447 2 physical writes non checkpoint 4479 2 pinned buffers inspected 3 2 prefetch warmup blocks aged out before use 19 2 prefetched blocks aged out before use 4 2 process last non-idle time 1201907734 2 queries parallelized 310 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 recovery blocks read 27 2 recursive calls 305540 2 recursive cpu usage 1543 2 redo blocks read for recovery 138 2 redo blocks written 11142 2 redo buffer allocation retries 17 2 redo entries 14467 2 redo log space requests 17 2 redo ordering marks 263 2 redo size 5021908 2 redo subscn max counts 78 2 redo synch time 164 2 redo synch writes 6543 2 redo wastage 498232 2 redo write time 1347 2 redo writer latching time 2 2 redo writes 1617 2 rollbacks only - consistent read gets 158 2 rows fetched via callback 15303 2 session connect time 1201903900 2 session cursor cache count 432 2 session cursor cache hits 15599 2 session logical reads 172875 2 session pga memory 630156108 2 session pga memory max 1253561608 2 session uga memory 17206444112 2 session uga memory max 254227760 2 shared hash latch upgrades - no wait 5154 2 sorts (memory) 7263 2 sorts (rows) 809215 2 sql area evicted 214 2 sql area purged 77 INST_ID NAME VALUE ------- ------------------------------------------------------------ -------------------------- 2 summed dirty queue length 554 2 switch current to new buffer 158 2 table fetch by rowid 82080 2 table fetch continued row 197 2 table scan blocks gotten 33893 2 table scan rows gotten 2482837 2 table scans (long tables) 71 2 table scans (short tables) 1227 2 total number of times SMON posted 73 2 transaction lock background gets 21 2 transaction lock foreground requests 21 2 transaction lock foreground wait time 4 2 undo change vector size 1843676 2 user I/O wait time 4305 2 user calls 10712 2 user commits 24 2 user rollbacks 5064 2 workarea executions - optimal 3937 2 write clones created in foreground 1 371 rows selected. SQL> SQL> -- CURRENT SQL FOR WAITING SESSIONS: SQL> -- Current SQL for any session in the WAITING SESSIONS list SQL> -- SQL> set numwidth 5 SQL> column sql format a80 wra SQL> select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql 2 from gv$session_wait sw, gv$session s, gv$sqlarea sa 3 where sw.sid = s.sid (+) 4 and sw.inst_id = s.inst_id (+) 5 and s.sql_address = sa.address 6 and sw.event not in ('rdbms ipc message','smon timer','pmon timer', 7 'SQL*Net message from client','lock manager wait for remote message', 8 'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 9 'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 10 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 11 'listen endpoint status','slave wait','wakeup time manager') 12 and sw.seconds_in_wait > 0 13 order by sw.seconds_in_wait desc; INST_ID SID SEC SQL ------- ----- ----- -------------------------------------------------------------------------------- 1 121 3233 update sh.sales set quantity_sold=100 1 row selected. SQL> SQL> -- Taking Hang Analyze dumps SQL> -- This may take a little while... SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug -g all hanganalyze 3 Hang Analysis in /u02/app/oradata/rman/admin/bdump/rman2_diag_9622.trc SQL> -- This part may take the longest, you can monitor bdump or udump to see if the SQL> -- file is being generated. SQL> oradebug -g all dump systemstate 267 Statement processed. SQL> SQL> set echo off TIME -------------------- FEB-01-2008 16:15:56 1 row selected.