Parallel Execution -- 4 Parsing PX Queries

Unlike "regular" Serial Execution queries that undergo only 1 hard parse and multiple soft parses on repeated execution, Parallel Execution queries actually are hard parsed by each PX Server plus the co-ordinator at each execution.  [Correction, as noted by Yasin in his comment : Not hard parsed, but separately parsed by each PX Server]

UPDATE 26-Apr-15:  See Oracle Support  Note 751588.1 and Bug 6274465  to understand how the PX Servers (PQ Slaves) also parse the SQL statement.


Here's a quick demo.

First, I start with a Serial Execution query.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 24 22:53:55 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

HEMANT>set serveroutput off
HEMANT>alter table large_table noparallel;

Table altered.

HEMANT>select count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5ys3vrapmbx6w, child number 0
-------------------------------------
select count(*) from large_table

Plan hash value: 3874713751

--------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       | 18894 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |            |          |
|   2 |   TABLE ACCESS FULL| LARGE_TABLE |  4802K| 18894   (1)| 00:03:47 |
--------------------------------------------------------------------------


14 rows selected.

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '5ys3vrapmbx6w';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         1           1             0 select count(*) from large_table

HEMANT>
HEMANT>select count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '5ys3vrapmbx6w';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         2           2             0 select count(*) from large_table

HEMANT>
HEMANT>select count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '5ys3vrapmbx6w';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         3           3             0 select count(*) from large_table

HEMANT>
HEMANT>select count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '5ys3vrapmbx6w';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         4           4             0 select count(*) from large_table

HEMANT>
HEMANT>select count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '5ys3vrapmbx6w';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         5           5             0 select count(*) from large_table

HEMANT>

5 executions with no additional parse overheads.

Next, I run Parallel Execution.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 24 23:04:45 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

HEMANT>set serveroutput off
HEMANT>alter table large_table parallel 4;

Table altered.

HEMANT>select /*+ PARALLEL */ count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4wd97vn0ytfmc, 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
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '4wd97vn0ytfmc';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         1           5             0 select /*+ PARALLEL */ count(*) from large_table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '4wd97vn0ytfmc';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         2          10             0 select /*+ PARALLEL */ count(*) from large_table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '4wd97vn0ytfmc';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         3          15             0 select /*+ PARALLEL */ count(*) from large_table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '4wd97vn0ytfmc';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         4          20             0 select /*+ PARALLEL */ count(*) from large_table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from large_table;

  COUNT(*)
----------
   4802944

HEMANT>select
  2  executions, parse_calls, invalidations, sql_fulltext
  3  from v$sqlstats
  4  where sql_id = '4wd97vn0ytfmc';

EXECUTIONS PARSE_CALLS INVALIDATIONS SQL_FULLTEXT
---------- ----------- ------------- --------------------------------------------------------------------------------
         5          25             0 select /*+ PARALLEL */ count(*) from large_table

HEMANT>

Each of the 5 executions had parse overheads for each PX server.
Note : The 5 "PARSE_CALLS" per execution is a result of 4 PX servers.  You might see a different number in your tests.

.
.
.

Parallel Execution -- 3b Limiting PX Servers with Resource Manager

As demonstrated earlier, in the absence of CALIBRATE_IO, the "automatic" degree computed by a PARALLEL Hint is CPU_COUNT x PARALLEL_THREADS_PER_CPU

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
         4

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         1                    16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Now, I shall explore using the Resource Manager to place a limit.

HEMANT>connect system/oracle
Connected.
SYSTEM>BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;  2    3  
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
  3  CONSUMER_GROUP=>'PX_QUERY_USER', 
  4  COMMENT=>'New Group for PX');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
  3  PLAN=>'LIMIT_PX_TO_4',
  4  COMMENT=>'Limit PQ/PX to 4 Server Processes');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( 
  3  PLAN=>'LIMIT_PX_TO_4',
  4  GROUP_OR_SUBPLAN=>'PX_QUERY_USER',
  5  PARALLEL_DEGREE_LIMIT_P1=>4,
  6  COMMENT=>'Directive to limit PQ/PX to 4 Server Processes');
  7  END;
  8  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan LIMIT_PX_TO_4
ORA-06512: at "SYS.DBMS_RMIN", line 444
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 809
ORA-06512: at line 2


SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
  3  PLAN=>'LIMIT_PX_TO_4',
  4  GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
  5  MGMT_P1=>10,
  6  COMMENT=>'Directive for OTHER_GROUPS (mandatory)');
  7  END;
  8  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>alter system set resource_manager_plan='LIMIT_PX_TO_4';

System altered.

SYSTEM>alter system flush shared_pool;

System altered.

SYSTEM>   

I have now created 1 single consumer group and a plan.
Note 1 :  The COMMENT is mandatory for the Consumer Group, the Plan and the Directives.
Note 2 : It is mandatory to specify Directives for OTHER_GROUPS in the Plan (even if I don't explicitly define any other groups).
For the Group 'PX_QUERY_USER', I've set a PX Limit of 4 (and no CPU limit).  For the 'OTHER_GROUPS', I've set a CPU Limit of 10%
I must now associate the Consumer Group with the User.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
  3  ATTRIBUTE=>DBMS_RESOURCE_MANAGER.ORACLE_USER,
  4  VALUE=>'HEMANT',
  5  CONSUMER_GROUP=>'PX_QUERY_USER');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>alter system flush shared_pool;

System altered.

SYSTEM>
SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP( 
  3  GRANTEE_NAME=>'HEMANT',
  4  CONSUMER_GROUP=>'PX_QUERY_USER',
  5  GRANT_OPTION=>FALSE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SYSTEM>
SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
  2  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
  3  END;
  4  /

PL/SQL procedure successfully completed.

SYSTEM> 

Let me test the configuration now.

SYSTEM>connect hemant/hemant
Connected.
HEMANT>select username, resource_consumer_group
  2  from v$session
  3  where username='HEMANT'
  4  /

USERNAME                       RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
HEMANT                         PX_QUERY_USER

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         1                     4 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Now, the same query executed with only 4 PX servers.

.
.
.