Hi We are using version 12.1.0.2 of oracle. We have one query which was running since long back with a profile which was forcing it to go for PARALLEL execution. Nobody had noticed this one, till 7th september when it ran too long(~5hr) causing other jobs to lag, and i saw by just removing the profile the query has finished in faster time. We dropped the profile and then the query ran okay. I was trying to understand why with the same plan(hash value-2571792927) the query ran longer that day? Data volume was closely same and the database was also not having any other resource crunch.I am not able to get any cause for same.
.
When i go through the plan i see the hints associated with the profile was actually as below and the plan it was using was varying a lot in past, but with the same exact plan(hash value-2571792927) the query has finished successfully in past many times in ~2.5hrs. And i see the note- section of each plan showing different DOP(Degree of parallelism). I had not having much stats to compare to understand the cause of deviation of number of parallel slaves with same plan, so then i went through the few historical execution in OEM, i see the query with same plan was using ~46sessions and ~80 sessions somedays in past, but on 7th septmber (when it ran very long), i see the number of sessions showing as ~93 in OEM for this query, so it means its was using ~93 parallel slaves to process the query. So wanted to understand if this automatic increase in number of parallel slaves can cause this drastic slowness? and why and how oracle is changing the number of parallel slaves for the same query with same plan? Even with difference in stats should not it go for a different plan altogether? Is there any dba_hist* view from which i can get the exact number of parallel threads used for each execution of this query in past rather seeing it from OEM?
/*+PARALLEL all_rows OPT_ESTIMATE(@"SEL$1", JOIN, ("tab2"@"SEL$1", "tab1"@"SEL$1", "tab3"@"SEL$1"), SCALE_ROWS=0.08932968219) OPTIMIZER_FEATURES_ENABLE(default)*/
Below is the Note section's of few of the plans, I have not mentioned the plan to keep it look simple:-
- automatic DOP: Computed Degree of Parallelism is 1 because of IO calibrate statistics are missing
below is the query and its sql monitor which shows the query was running longer on 7th sep. Due to parallel execution the db time shows too large. And the duration is just showing the time till which one of the idle parallel slave got killed by itself , so its kind of sql monitor bug i believe. ACtually this query was running beyond ~5hrs+ and as i can see from the "time active(S)" column of the sql-monitor, it does show the queryhas ran ~11715seconds i.e ~3.2hrs+ when this sql monitor has been captured. Again i am not sure why this sql monitor shows the parallel slaves as ~46, on oem i can see the number of sessions were ~93 for that sql during that time and this sql executes once at time.
below is the query:-
SELECT tab2.c1 AS c1,
tab2.c2 AS c2,
tab2.tdid AS tdid,
tab2.sdt AS sdt
FROM tab3,
tab1,
tab2
WHERE tab1.sid = tab3.scd
AND tab2.tdid = tab1.tdid
AND tab2.sdt = tab1.sdt
ORDER BY tdid, sdt
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
SQL Execution ID : 16777219
Execution Started : 09/07/2018 01:22:23
First Refresh Time : 09/07/2018 01:23:40
Last Refresh Time : 09/07/2018 02:54:14
Duration : 5511s
Service : SYS$USERS
Global Stats
===============================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===============================================================================================
| 137513 | 20330 | 117170 | 0.17 | 0.23 | 13 | 911M | 157M | 1TB |
===============================================================================================
Parallel Execution Details (DOP=23 , Servers Allocated=46)
==================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
==================================================================================================================================================================
| PX Coordinator | QC | | 0.78 | 0.41 | 0.19 | 0.17 | | | 180 | 44 | 352KB | |
| p000 | Set 1 | 1 | | | | | | | | | . | cursor: pin S wait on X (75) |
| p001 | Set 1 | 2 | | | | | | | | | . | library cache lock (75) |
| p002 | Set 1 | 3 | | | | | | | | | . | cursor: pin S wait on X (75) |
| p003 | Set 1 | 4 | | | | | | | | | . | library cache lock (75) |
| p004 | Set 1 | 5 | | | | | | | | | . | library cache lock (75) |
| p005 | Set 1 | 6 | | | | | | | | | . | library cache lock (75) |
| p006 | Set 1 | 7 | | | | | | | | | . | library cache lock (75) |
| p007 | Set 1 | 8 | | | | | | | | | . | db file sequential read (52) |
| p008 | Set 1 | 9 | | | | | | | | | . | library cache lock (75) |
| p009 | Set 1 | 10 | | | | | | | | | . | library cache lock (75) |
| p00a | Set 1 | 11 | | | | | | | | | . | library cache lock (75) |
| p00b | Set 1 | 12 | | | | | | | | | . | library cache lock (75) |
| p00c | Set 1 | 13 | 0.00 | | | | | 0.00 | | | . | library cache lock (75) |
| p00d | Set 1 | 14 | 0.00 | | | | | 0.00 | | | . | library cache lock (75) |
| p00e | Set 1 | 15 | | | | | | | | | . | library cache lock (75) |
| p00f | Set 1 | 16 | | | | | | | | | . | library cache lock (75) |
| p00g | Set 1 | 17 | | | | | | | | | . | library cache lock (75) |
| p00h | Set 1 | 18 | 0.00 | | | | | 0.00 | | | . | library cache lock (75) |
| p00i | Set 1 | 19 | | | | | | | | | . | library cache lock (75) |
| p00j | Set 1 | 20 | 0.00 | | | | | 0.00 | | | . | library cache lock (75) |
| p00k | Set 1 | 21 | | | | | | | | | . | cursor: pin S wait on X (75) |
| p00l | Set 1 | 22 | | | | | | | | | . | cursor: pin S wait on X (75) |
| p00m | Set 1 | 23 | | | | | | | | | . | cursor: pin S wait on X (75) |
| p00n | Set 2 | 1 | 2375 | 236 | 2139 | | 0.01 | | 9M | 2M | 14GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (32) |
| | | | | | | | | | | | | db file parallel read (2030) |
| | | | | | | | | | | | | db file sequential read (44) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (1) |
| p00o | Set 2 | 2 | 5607 | 636 | 4971 | | 0.00 | | 28M | 5M | 39GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (22) |
| | | | | | | | | | | | | db file parallel read (4746) |
| | | | | | | | | | | | | db file sequential read (111) |
| | | | | | | | | | | | | direct path read (1) |
| | | | | | | | | | | | | read by other session (2) |
| p00p | Set 2 | 3 | 8216 | 1140 | 7076 | | 0.00 | | 54M | 9M | 71GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (23) |
| | | | | | | | | | | | | db file parallel read (4735) |
| | | | | | | | | | | | | db file sequential read (112) |
| | | | | | | | | | | | | direct path read (1) |
| | | | | | | | | | | | | read by other session (3) |
| p00q | Set 2 | 4 | 2401 | 237 | 2164 | | 0.00 | | 9M | 2M | 14GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (23) |
| | | | | | | | | | | | | db file parallel read (2055) |
| | | | | | | | | | | | | db file sequential read (54) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (2) |
| p00r | Set 2 | 5 | 2383 | 230 | 2153 | | 0.00 | | 9M | 2M | 13GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (23) |
| | | | | | | | | | | | | db file parallel read (2054) |
| | | | | | | | | | | | | db file sequential read (45) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (4) |
| p00s | Set 2 | 6 | 11745 | 2051 | 9694 | | 0.05 | | 89M | 15M | 114GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (24) |
| | | | | | | | | | | | | db file parallel read (4690) |
| | | | | | | | | | | | | db file sequential read (155) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (1) |
| p00t | Set 2 | 7 | 2501 | 238 | 2263 | | 0.00 | | 10M | 2M | 14GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (23) |
| | | | | | | | | | | | | db file parallel read (2135) |
| | | | | | | | | | | | | db file scattered read (2) |
| | | | | | | | | | | | | db file sequential read (60) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (1) |
| p00u | Set 2 | 8 | 8341 | 1257 | 7083 | | 0.03 | | 55M | 9M | 70GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (31) |
| | | | | | | | | | | | | db file parallel read (4730) |
| | | | | | | | | | | | | db file sequential read (128) |
| p00v | Set 2 | 9 | 12095 | 2078 | 10016 | | 0.00 | | 92M | 16M | 121GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (18) |
| | | | | | | | | | | | | db file parallel read (4731) |
| | | | | | | | | | | | | db file sequential read (116) |
| | | | | | | | | | | | | direct path read (1) |
| p00w | Set 2 | 10 | 2435 | 231 | 2204 | | 0.00 | | 9M | 2M | 14GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (18) |
| | | | | | | | | | | | | db file parallel read (2107) |
| | | | | | | | | | | | | db file sequential read (68) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (3) |
| p00x | Set 2 | 11 | 11805 | 1903 | 9901 | | 0.00 | | 87M | 16M | 119GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (17) |
| | | | | | | | | | | | | db file parallel read (4705) |
| | | | | | | | | | | | | db file sequential read (124) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (4) |
| p00y | Set 2 | 12 | 2565 | 252 | 2314 | | 0.00 | | 10M | 2M | 15GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (23) |
| | | | | | | | | | | | | db file parallel read (2195) |
| | | | | | | | | | | | | db file scattered read (1) |
| | | | | | | | | | | | | db file sequential read (50) |
| | | | | | | | | | | | | direct path read (1) |
| p00z | Set 2 | 13 | 2378 | 234 | 2144 | | 0.00 | | 9M | 2M | 13GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (30) |
| | | | | | | | | | | | | db file parallel read (2012) |
| | | | | | | | | | | | | db file sequential read (54) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (2) |
| p010 | Set 2 | 14 | 552 | 34 | 505 | | 0.00 | 13 | 1M | 223K | 2GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (26) |
| | | | | | | | | | | | | db file parallel read (463) |
| | | | | | | | | | | | | db file sequential read (26) |
| | | | | | | | | | | | | direct path read (1) |
| | | | | | | | | | | | | read by other session (1) |
| p011 | Set 2 | 15 | 2509 | 248 | 2261 | | 0.00 | | 10M | 2M | 14GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (27) |
| | | | | | | | | | | | | db file parallel read (2146) |
| | | | | | | | | | | | | db file sequential read (50) |
| | | | | | | | | | | | | direct path read (1) |
| | | | | | | | | | | | | read by other session (2) |
| p012 | Set 2 | 16 | 11968 | 1950 | 10018 | | 0.01 | | 93M | 16M | 121GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (21) |
| | | | | | | | | | | | | db file parallel read (4660) |
| | | | | | | | | | | | | db file sequential read (138) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (3) |
| p013 | Set 2 | 17 | 2492 | 249 | 2243 | | 0.00 | | 9M | 2M | 14GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (27) |
| | | | | | | | | | | | | db file parallel read (2114) |
| | | | | | | | | | | | | db file sequential read (73) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (6) |
| p014 | Set 2 | 18 | 2454 | 244 | 2210 | | 0.00 | | 10M | 2M | 14GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (25) |
| | | | | | | | | | | | | db file parallel read (2110) |
| | | | | | | | | | | | | db file sequential read (42) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (3) |
| p015 | Set 2 | 19 | 4390 | 427 | 3963 | | 0.00 | | 18M | 3M | 26GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (28) |
| | | | | | | | | | | | | db file parallel read (3771) |
| | | | | | | | | | | | | db file sequential read (89) |
| | | | | | | | | | | | | direct path read (2) |
| p016 | Set 2 | 20 | 12374 | 2211 | 10163 | | 0.04 | | 102M | 17M | 126GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (22) |
| | | | | | | | | | | | | db file parallel read (4704) |
| | | | | | | | | | | | | db file scattered read (1) |
| | | | | | | | | | | | | db file sequential read (138) |
| | | | | | | | | | | | | direct path read (1) |
| | | | | | | | | | | | | read by other session (2) |
| p017 | Set 2 | 21 | 2353 | 238 | 2115 | | 0.00 | | 9M | 2M | 13GB | library cache lock (75) |
| | | | | | | | | | | | | free buffer waits (28) |
| | | | | | | | | | | | | db file parallel read (2004) |
| | | | | | | | | | | | | db file sequential read (59) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (2) |
| p018 | Set 2 | 22 | 11505 | 1851 | 9654 | | 0.03 | | 91M | 15M | 115GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (19) |
| | | | | | | | | | | | | db file parallel read (4729) |
| | | | | | | | | | | | | db file sequential read (106) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (3) |
| p019 | Set 2 | 23 | 12068 | 2152 | 9916 | | 0.02 | | 97M | 16M | 122GB | cursor: pin S wait on X (75) |
| | | | | | | | | | | | | free buffer waits (17) |
| | | | | | | | | | | | | db file parallel read (4660) |
| | | | | | | | | | | | | db file sequential read (161) |
| | | | | | | | | | | | | direct path read (2) |
| | | | | | | | | | | | | read by other session (3) |
==================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2571792927)
===============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 28 | | | | | | |
| 1 | PX COORDINATOR | | | | | | 28 | | 44 | 352KB | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 1 | 2520 | | | | | | | | | |
| 3 | SORT ORDER BY | | 1 | 2520 | | | | | | | | | |
| 4 | PX RECEIVE | | 1 | 2519 | | | | | | | | | |
| 5 | PX SEND RANGE | :TQ10001 | 1 | 2519 | | | 23 | | | | | | |
| 6 | NESTED LOOPS | | 1 | 2519 | 11715 | +81 | 23 | 0 | | | | 0.12 | Cpu (108) |
| 7 | NESTED LOOPS | | 1 | 2519 | 11715 | +81 | 23 | 409M | | | | 0.11 | Cpu (97) |
| 8 | MERGE JOIN CARTESIAN | | 1 | 2519 | 11717 | +79 | 23 | 409M | | | | | |
| 9 | BUFFER SORT | | | | 11713 | +79 | 23 | 765 | | | 28672 | | |
| 10 | PX RECEIVE | | 1 | | 1 | +79 | 23 | 1794 | | | | | |
| 11 | PX SEND BROADCAST | :TQ10000 | 1 | | | | | | | | | | |
| 12 | PX SELECTOR | | | | | | | | | | | | |
| 13 | INDEX FULL SCAN | tab3_pk | 1 | | | | | | | | | | |
| 14 | BUFFER SORT | | 12M | 2519 | 11718 | +78 | 765 | 409M | | | 797M | 0.10 | Cpu (85) |
| 15 | PX BLOCK ITERATOR | | 12M | 1672 | 3 | +79 | 23 | 12M | | | | | |
| 16 | TABLE ACCESS FULL | tab2 | 12M | 1672 | 4 | +78 | 432 | 12M | 1275 | 1GB | | 0.05 | Cpu (3) |
| | | | | | | | | | | | | | direct path read (37) |
| 17 | PARTITION RANGE ITERATOR | | 1 | | 11715 | +81 | 457M | 409M | | | | 0.49 | Cpu (428) |
| 18 | INDEX RANGE SCAN | tab1_ix2 | 1 | | 11716 | +80 | 457M | 409M | 32M | 242GB | | 27.28 | free buffer waits (273) |
| | | | | | | | | | | | | | Cpu (2624) |
| | | | | | | | | | | | | | db file parallel read (20640) |
| | | | | | | | | | | | | | db file sequential read (240) |
| | | | | | | | | | | | | | read by other session (45) |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID | tab1 | 65162 | | 11683 | +80 | 696M | 0 | 125M | 1TB | | 67.90 | free buffer waits (274) |
| | | | | | | | | | | | | | Cpu (3601) |
| | | | | | | | | | | | | | db file parallel read (53646) |
| | | | | | | | | | | | | | db file scattered read (4) |
| | | | | | | | | | | | | | db file sequential read (1763) |
| | | | | | | | | | | | | | read by other session (3) |
===============================================================================================================================================================================================================