RAC newbie plus PARTITIONING newbie - looking for suggestions on the attached SQL

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 22 Aug 2012 08:35:50 -0500
Message-ID: <CAP79kiRgGfu+k_E=q2GxCbvK_fRnUX6qL9p7Skj7YG37LR4d2A_at_mail.gmail.com>

(If the formatting gets screwed up, I apologize as I'm attempting to use Gmail which is being hard to get along with) The SELECT Portion of this statement returns all rows in 2 minutes.

However, the total INSERT takes well over 30 minutes and has the following plan.

(This is a 3rd party application but we have some control over the indexes and types of indexes being used)

(Waits for the session are listed below – though Grid was showing ENQ: FB events that Toad didn't show when I grabbed them)

(Again, I'm looking for any ideas to get me started as I'm out of my expertise area right now with RAC and PARTITIONING involved)

SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *

2 FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3285883390

| 0 | INSERT STATEMENT | | 33776 |

9466K| 12667   (2)| 00:02:33 |       |       |
|*  1 |  HASH JOIN                           |                   | 33776 |
9466K| 12667   (2)| 00:02:33 |       |       |
|*  2 |   HASH JOIN RIGHT SEMI               |                   | 11480


|   313K|   112   (1)| 00:00:02 |       |       |

|   3 |    PARTITION LIST SINGLE             |                   | 11577

|   158K|    15   (0)| 00:00:01 |   KEY |   KEY |

|*  4 |     TABLE ACCESS FULL                | WQ_ORG_ACCOUNT    | 11577


|   158K|    15   (0)| 00:00:01 |   KEY |   KEY |

|   5 |    PARTITION LIST SINGLE             |                   | 15609

|   213K|    96   (0)| 00:00:02 |   KEY |   KEY |

|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID| MON_ACCOUNT       | 15609

|   213K|    96   (0)| 00:00:02 |   KEY |   KEY |

|   7 |      BITMAP CONVERSION TO ROWIDS     |                   |

|       |            |       |  |       |

|*  8 |       BITMAP INDEX SINGLE VALUE      | MON_ACCT_FK_ORG   |


|       |            |       |   KEY |   KEY |

|   9 |   TABLE ACCESS FULL                  | MON_ACCOUNT_PAYER |

1021K|   252M| 12551   (2)| 00:02:31 |       |       |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("MA"."ID"="MAPY"."MON_ACCOUNT_ID")
   2 - access("WQOA"."MON_ACCOUNT_ID"="MA"."ID")
   4 - filter("WQOA"."ORG_ID"=TO_NUMBER(:P_ORG_ID))
   8 - access("MA"."ORG_ID_PROVIDER"=TO_NUMBER(:P_ORG_ID))

       *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

Received on Wed Aug 22 2012 - 08:35:50 CDT

This message: [ Message body ]
Next message: Rich Jesse: "EM12c reporting two sessions with same SID"
Previous message: Storey, Robert (DCSO): "RE: Tnsnames mystery"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message