Search My Oracle Blog

Custom Search

02 March, 2015

Parallel Execution -- 1b The PARALLEL Hint and AutoDoP (contd)

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 ?

[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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016