New to RAC and need some ideas/suggestions
- From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Aug 2012 15:51:46 -0500
Guys/Gals,
(3 Node 10.2.0.4 RAC on RH 5.4 Linux)
I'm new to RAC and we have an INSERT statement with a subselect - The
INSERT uses an /*+ APPEND */ hint and is taking ABNORMAL amount of time to
insert.
I pulled the SELECT Portion out, and it returns the full result set in 2
minutes. The query in question is experiencing the following wait events
and being new to RAC I'm a little bit out of my area of expertise right now
so I'm hoping some of you can get me starte on the right path.
Anything in the below waits jump out at you? Anything of these events
directly tied to any of the other events? (ie. the GC waits are a result
of the sequential file reads etc etc?) I'm looking for any pointers at
this point as I'm profess to being a newbie on this one ;)
I'm going to be searching Google and Metalink but wanted to get this out
there while I searched.
Regards!
Chris Taylor
*INST_ID* *SID* *EVENT* *TOTAL_WAITS* *TOTAL_TIMEOUTS* *TIME_WAITED*
*TIME_WAITED (Secs)* *AVERAGE_WAIT* *MAX_WAIT* *TIME_WAITED_MICRO* 1 5361 db
file sequential read 121669 0 9342 93.42 0.08 7 93415894 1 5361 gc cr disk
read 79122 0 7703 77.03 0.1 122 77025252 1 5361 events in waitclass Other
5917 110 5495 54.95 0.93 98 54948864 1 5361 gc buffer busy 68472 7 4734
47.34 0.07 98 47342911 1 5361 gc cr block 2-way 41812 0 1848 18.48 0.04 2
18480795 1 5361 library cache lock 1198 12 1230 12.3 1.03 49 12301880 1 5361 gc
cr block 3-way 19029 0 1124 11.24 0.06 2 11237781 1 5361 local write wait
810 0 325 3.25 0.4 3 3251102 1 5361 gc current block busy 114 0 300 3 2.63
15 2995528 1 5361 gc current multi block request 5837 0 168 1.68 0.03 1
1683256 1 5361 gc cr multi block request 9247 0 161 1.61 0.02 2 1609422 1
5361 enq: RO - fast object reuse 74 1 110 1.1 1.49 49 1099982 1 5361 log
file switch completion 50 0 89 0.89 1.78 5 888949 1 5361 db file scattered
read 280 0 85 0.85 0.3 2 853838 1 5361 enq: HW - contention 3031 0 80 0.8
0.03 2 798684 1 5361 row cache lock 1946 0 69 0.69 0.04 3 685297 1 5361 read
by other session 306 0 61 0.61 0.2 4 610072 1 5361 library cache load lock
109 0 58 0.58 0.53 3 579767 1 5361 enq: TX - index contention 20 0 56 0.56
2.81 13 562932 1 5361 gc current block 2-way 356 0 41 0.41 0.12 6 410860 1
5361 gc current grant busy 306 0 38 0.38 0.13 4 383419 1 5361 gc cr block
busy 68 0 31 0.31 0.46 3 309735 1 5361 latch: row cache objects 249 0 27
0.27 0.11 6 266857 1 5361 gc current grant 2-way 896 0 26 0.26 0.03 1 258090
1 5361 gc current block 3-way 194 0 23 0.23 0.12 3 228328 1 5361 enq: SQ -
contention 20 0 17 0.17 0.85 2 170972 1 5361 latch: cache buffers chains
1502 0 16 0.16 0.01 1 155447 1 5361 buffer busy waits 32 0 12 0.12 0.38 6
122178 1 5361 library cache pin 198 0 7 0.07 0.04 1 71726 1 5361 latch:
library cache 53 0 7 0.07 0.14 2 74282 1 5361 latch: shared pool 12 0 6 0.06
0.53 5 63493 1 5361 gc cr failure 62 0 2 0.02 0.03 0 19042 1 5361 SQL*Net
message from client 14 0 2 0.02 0.14 1 19661 1 5361 gc current retry 7 0 1
0.01 0.19 1 13382 1 5361 log file sync 1 0 1 0.01 0.74 1 7360 1 5361 enq:
TM - contention 10 0 1 0.01 0.12 1 11656 1 5361 SQL*Net message to client 14
0 0 0 0 0 22 1 5361 gc cr grant 2-way 11 0 0 0 0.03 0 2869 1 5361 gc cr
block congested 1 0 0 0 0.18 0 1757 1 5361 latch: library cache pin 1 0 0 0
0.01 0 66 1 5361 gc current split 2 0 0 0 0.06 0 1246
--
//www.freelists.org/webpage/oracle-l
Other related posts:
