Wednesday, April 24, 2013

Incorrect Parallel degree calculated when Auto DOP used in 11.2.0.3 Exadata.


This may be bug in 11.2.0.3 on exadata, AUTO DOP calculate Incorrect Parallel degree, when parallel hints are used with Auto DOP.



SQL> explain plan for
SELECT /*+PARALLEL(E)*/ SITE_ID, ITEM_NUMBER, SERIAL_NUMBER, PORT_NUMBER, EQUIPMENT_ADDRESSABLE, TRIM(STATUS_DATE) 
STATUS_DATE, QUALITY_ASSURANCE_CODE, TRIM(QUALITY_ASSURANCE_DATE) QUALITY_ASSURANCE_DATE, EQUIPMENT_ADDRESS,
EQUIPMENT_OVERRIDE_ACTIVE, INITIALIZE_REQUIRED, PARENTAL_CODE, TEMP_ENABLED, TRIM(TRANSMISSION_DATE) TRANSMISSION_DATE, 
DNS_NAME, IP_ADDRESS, FQDN, LOCAL_STATUS, TRIM( LOCAL_STATUS_DATE) LOCAL_STATUS_DATE, SERVER_ID, SERVER_STATUS, 
TRIM(SERVER_STATUS_DATE) SERVER_STATUS_DATE, EQUIP_DTL_STATUS, PORT_CATEGORY_CODE, HEADEND, ACCOUNT_NUMBER, 
SUB_ACCOUNT_ID, VIDEO_RATING_CODE, PORT_TYPE, SERVICE_CATEGORY_CODE, SERVICE_OCCURRENCE, CREATED_USER_ID, TRIM(DATE_CREATED)
DATE_CREATED, LAST_CHANGE_USER_ID, TRIM(LAST_CHANGE_DATE) LAST_CHANGE_DATE, CABLE_CARD_ID, JOURNAL_DATE 
FROM CLE_DUMMY_DETAIL E 
WHERE JOURNAL_DATE >= (SELECT LAST_RUN_DATE FROM ETL_SITE_RUN_DATE@ODS.WORLD 
WHERE TABLE_NAME = 'xyz' AND SITE_NAME = 'CLE' ) 
AND JOURNAL_DATE <= (SELECT HEART_BEAT_DATE FROM ETL_SITE_RUN_DATE@ODS.WORLD 
WHERE TABLE_NAME = 'xyz' AND SITE_NAME = 'CLE' ) 
;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 350686579

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1158 | 203K| 18 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR |  | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| CLE_CDO1CPP | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | PCWP | |
| 5 | REMOTE | ETL_SITE_RUN_DATE | 1 | 47 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | REMOTE | ETL_SITE_RUN_DATE | 1 | 47 | 1 (0)| 00:00:01 | PODS_~ | R->S | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("JOURNAL_DATE">= (SELECT "LAST_RUN_DATE" FROM "ETL_SITE_RUN_DATE" WHERE "SITE_NAME"='CLE' AND
"TABLE_NAME"='xyz') AND "JOURNAL_DATE"<= (SELECT "HEART_BEAT_DATE" FROM "ETL_SITE_RUN_DATE"
WHERE "SITE_NAME"='CLE' AND "TABLE_NAME"='xyz'))

Remote SQL Information (identified by operation id):
----------------------------------------------------

6 - SELECT "SITE_NAME","TABLE_NAME","HEART_BEAT_DATE" FROM "ETL_SITE_RUN_DATE" "ETL_SITE_RUN_DATE" WHERE
"SITE_NAME"='CLE' AND "TABLE_NAME"='xyz' (accessing 'PODS_ETL_CONTROL.WORLD' )


Note
-----
- Degree of Parallelism is 32767 because of hint

31 rows selected.

SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- 
parallel_adaptive_multi_user    FALSE
parallel_automatic_tuning      FALSE
parallel_degree_limit       8
parallel_degree_policy      AUTO
parallel_execution_message_size  16384
parallel_force_local       FALSE
parallel_io_cap_enabled      FALSE
parallel_max_servers     196
parallel_min_percent     0
parallel_min_servers     2
parallel_min_time_threshold     60
parallel_server           TRUE
parallel_server_instances    5
parallel_servers_target    90
parallel_threads_per_cpu    2
recovery_parallelism     0

FileName
----------------


Tested with without DBLINK.

-- Testing this on 11.2.0.3

SQL> select /*+ Parallel(E) */ * from foo e;

Execution Plan
----------------------------------------------------------
Plan hash value: 3135133324

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18M| 1708M| 9922 (1)| 00:00:01| | | |
| 1 | PX COORDINATOR | | | | || | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| FOO | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 32767 because of hint

SQL> connect / as sysdba
Connected.
SQL> select count(*) from foo;

COUNT(*)
----------
18472960

parallel_adaptive_multi_user     TRUE
parallel_automatic_tuning      FALSE
parallel_degree_limit       2
parallel_degree_policy      AUTO
parallel_execution_message_size    16384
parallel_force_local       FALSE
parallel_instance_group 
parallel_io_cap_enabled      FALSE
parallel_max_servers       135
parallel_min_percent       0
parallel_min_servers       0
parallel_min_time_threshold     1
parallel_server        FALSE
parallel_server_instances      1
parallel_servers_target      64
parallel_threads_per_cpu      2
recovery_parallelism       0



comparison on 11.2.0.2 and 11.2.0.3

----test case 
drop table foo;
alter session set parallel_degree_policy = auto;
alter session set parallel_degree_limit = 2;
alter session set parallel_min_time_threshold = 1;
create table foo as select * from all_objects where rownum < 16;
alter table foo parallel 6;
set autotrace traceonly explain
select /*+ Parallel(e) */ * from foo e;

--------
FOO has a default DOP of 6

11.2.0.3 -- select /*+ Parallel(a) */ * from foo a; --- Degree of Parallelism is 32767 because of hint
11.2.0.2 -- select /*+ Parallel(a) */ * from foo a; -- automatic DOP: Computed Degree of Parallelism is 1
11.2.0.3 select /*+ Parallel */ * from foo; -- - automatic DOP: Computed Degree of Parallelism is 2
11.2.0.2 select /*+ Parallel */ * from foo; -- - automatic DOP: Computed Degree of Parallelism is 2
11.2.0.3 select /*+ Parallel(20) */ * from foo; -- - Degree of Parallelism is 20 because of hint
11.2.0.2 select /*+ Parallel(20) */ * from foo; -- - Degree of Parallelism is 20 because of hint


1 comment:

  1. This is very helpful and I just have one question, what is this xplan you used? I had a xplan and very old. I think yours is very good.

    Thank you in advance. If you need I can give you my email address.

    ReplyDelete