RE: New to RAC and need some ideas/suggestions

The 'gc' waits are an indicator of traffic going across the interconnect. This is generally to be avoided, as much as possible.

If you have other sessions inserting into the same table or set of tables, from a different node, you're likely to see this, and see performance suffer. Try to isolate the workload of this type to a particular node.

Also, consider hash partitioning heavily used indexes (number of partitions equal to smallest power of 2 greater than or equal to the number of nodes in the cluster). Remember, you can hash partition indexes, even for columns of tables that are not partitioned at all.

Welcome to the wonderful world of RAC.

Hope that helps,

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor Sent: Tuesday, August 21, 2012 4:52 PM
To: oracle-l_at_freelists.org
Subject: New to RAC and need some ideas/suggestions

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

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Aug 21 2012 - 16:07:51 CDT