hi Friends,
I see this wait event latch: parallel query alloc buffer, when a job meant for doing some cleanup ran this query.
Why does this wait event come happen? , i searched google,MOS no exact hit for explanation of the exact same event.
Looking at query does it happen because of the incorrect use of parallel hint i.e. no object mentioned and also it is not specified like parallel(5) .
It is not causing any big issues, just want to know for my understanding sake.
SQL> select event,count(9) from dba_hist_active_sess_history where sql_id='&a' and instance_number=1 group by event order by 2 desc;
EVENT | COUNT(9)
----------------------------------------------------------------|----------
latch: parallel query alloc buffer | 102
latch: shared pool | 10
| 5
os thread startup | 2
insert into TABLE_SOURCE_2_BAD(RID,DELC_I,BADROW_SL_SM
,LAST_UPDATED_DT,LAST_UPDATED_BY) select /*+PARALLEL 5*/
rid,124,'N',SYSDATE,'EPA_JOB' from (select /*+PARALLEL 5*/ c.rid FROM
TABLE_SOURCE c WHERE c.DELC_I = 124 minus select
/*+PARALLEL 5*/ b.rowid from TABLE_SOURCE_2 b where exists (
select a.rid from TABLE_SOURCE a where a.rid=b.rowid ))
Execution plan:
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 7 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 26602 | 311K| 7 (43)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 4 | VIEW | | 26602 | 311K| 7 (43)| 00:00:01 | Q1,03 | PCWP | |
| 5 | MINUS | | | | | | Q1,03 | PCWP | |
| 6 | SORT UNIQUE | | 26602 | 649K| 3 (34)| 00:00:01 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 26602 | 649K| 2 (0)| 00:00:01 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 26602 | 649K| 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 26602 | 649K| 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | TABLE_SOURCE | 26602 | 649K| 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 11 | SORT UNIQUE | | 3 | 72 | 4 (50)| 00:00:01 | Q1,03 | PCWP | |
| 12 | PX RECEIVE | | 3 | 72 | 3 (34)| 00:00:01 | Q1,03 | PCWP | |
| 13 | PX SEND HASH | :TQ10002 | 3 | 72 | 3 (34)| 00:00:01 | Q1,02 | P->P | HASH |
| 14 | NESTED LOOPS | | 3 | 72 | 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 15 | SORT UNIQUE | | 26602 | 311K| 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 16 | PX RECEIVE | | 26602 | 311K| 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 17 | PX SEND HASH | :TQ10000 | 26602 | 311K| 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 18 | PX BLOCK ITERATOR | | 26602 | 311K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 19 | TABLE ACCESS FULL | TABLE_SOURCE | 26602 | 311K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | TABLE ACCESS BY USER ROWID| TABLE_SOURCE_2 | 1 | 12 | 1 (0)| 00:00:01 | Q1,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------
10 - access(:Z>=:Z AND :Z<=:Z)
filter("C"."DELC_I"=124)
19 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=7)
- automatic DOP: Computed Degree of Parallelism is 128 because of degree limit <<is this 128 got something to do with the resource manager, we have it configured.
Below are the %parallel% parameters in the DB
| |Is |Sess |Sys
NAME |VALUE |Default |Modif |Modif
--------------------------------------------------|--------------------|---------|------|----------
_parallel_syspls_obey_force |TRUE |TRUE |TRUE |TRUE
fast_start_parallel_rollback |FALSE |FALSE |FALSE |TRUE
parallel_adaptive_multi_user |TRUE |TRUE |FALSE |TRUE
parallel_automatic_tuning |FALSE |TRUE |FALSE |FALSE
parallel_degree_limit |CPU |TRUE |TRUE |TRUE
parallel_degree_policy |MANUAL |TRUE |TRUE |TRUE
parallel_execution_message_size |16384 |TRUE |FALSE |FALSE
parallel_force_local |TRUE |FALSE |TRUE |TRUE
parallel_instance_group | |TRUE |TRUE |TRUE
parallel_io_cap_enabled |FALSE |TRUE |TRUE |TRUE
parallel_max_servers |256 |FALSE |FALSE |TRUE
parallel_min_percent |0 |TRUE |TRUE |FALSE
parallel_min_servers |0 |TRUE |FALSE |TRUE
parallel_min_time_threshold |AUTO |TRUE |TRUE |TRUE
parallel_server |TRUE |TRUE |FALSE |FALSE
parallel_server_instances |2 |TRUE |FALSE |FALSE
parallel_servers_target |72 |FALSE |FALSE |TRUE
parallel_threads_per_cpu |2 |TRUE |FALSE |TRUE
recovery_parallelism |0 |TRUE |FALSE |FALSE
DB version:11.2.0.4.0
OS :Linux x86 64-bit
Regd,
Sachin