RAC newbie plus PARTITIONING newbie - looking for suggestions on the attached SQL
- From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 22 Aug 2012 08:35:50 -0500
(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
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RAC newbie plus PARTITIONING newbie - looking for suggestions on the attached SQL - Chris Taylor
