Continuing the previous thread, having restarted the database again, with the same CPU_COUNT and missing I/O Calibration statistics ....
The question this time is : What if the table level DoP is specifically 1 ?
Aaha ! Again ! The same SQL statement, the same SQL_ID, the same Execution Plan (Plan Hash Value) and Oracle chose to use 8 PX Servers for the query ! Again, ignoring the table level DoP (of 1)
So, once again, we see that Oracle actually computes a DoP that looks like it is CPU_COUNT x PARALLEL_THREADS_PER_CPU. Let's verify this.
YES SIR ! Oracle chose to use 16 PX Servers this time. So that does look like CPU_COUNT x PARALLEL_THREADS_PER_CPU. Have you also noticed the COST ? The COST has also dropped to half. So, the COST is also computed based on the number of PX Servers that it expects to be able to grab and use.
.
.
.
The question this time is : What if the table level DoP is specifically 1 ?
[oracle@localhost ~]$ sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 2 23:22:28 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS>show parameter cpu NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cpu_count integer 4 parallel_threads_per_cpu integer 2 resource_manager_cpu_allocation integer 4 SYS>show parameter parallel NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW parallel_adaptive_multi_user boolean FALSE parallel_automatic_tuning boolean FALSE parallel_degree_limit string CPU parallel_degree_policy string MANUAL parallel_execution_message_size integer 16384 parallel_force_local boolean FALSE parallel_instance_group string parallel_io_cap_enabled boolean FALSE parallel_max_servers integer 135 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_min_time_threshold string AUTO parallel_server boolean FALSE parallel_server_instances integer 1 parallel_servers_target integer 64 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 SYS>
SYS>select * from dba_rsrc_io_calibrate;
no rows selected
SYS>
SYS>connect hemant/hemant
Connected.
HEMANT>set serveroutput off
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';
DEGREE
----------------------------------------
4
HEMANT>alter table large_table parallel 1;
Table altered.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';
DEGREE
----------------------------------------
1
HEMANT>select /*+ PARALLEL */ count(*) from LARGE_TABLE;
COUNT(*)
----------
4802944
HEMANT>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 47m7qyrj6uzqn, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from LARGE_TABLE
Plan hash value: 2085386270
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2622 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
27 rows selected.
HEMANT>select px_servers_executions from v$sqlstats where sql_id='47m7qyrj6uzqn';
PX_SERVERS_EXECUTIONS
---------------------
8
HEMANT>
Aaha ! Again ! The same SQL statement, the same SQL_ID, the same Execution Plan (Plan Hash Value) and Oracle chose to use 8 PX Servers for the query ! Again, ignoring the table level DoP (of 1)
So, once again, we see that Oracle actually computes a DoP that looks like it is CPU_COUNT x PARALLEL_THREADS_PER_CPU. Let's verify this.
HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_threads_per_cpu=4;
System altered.
SYS>alter system flush shared_pool;
System altered.
SYS>connect hemant/hemant
Connected.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';
DEGREE
----------------------------------------
1
HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;
COUNT(*)
----------
4802944
HEMANT>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8b0ybuspqu0mm, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from Large_Table
Plan hash value: 2085386270
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1311 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
27 rows selected.
HEMANT>select px_servers_executions from v$sqlstats where sql_id='8b0ybuspqu0mm';
PX_SERVERS_EXECUTIONS
---------------------
16
HEMANT>
YES SIR ! Oracle chose to use 16 PX Servers this time. So that does look like CPU_COUNT x PARALLEL_THREADS_PER_CPU. Have you also noticed the COST ? The COST has also dropped to half. So, the COST is also computed based on the number of PX Servers that it expects to be able to grab and use.
.
.
.
No comments:
Post a Comment