Search My Oracle Blog

Custom Search

24 April, 2015

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.

.
.
.


20 April, 2015

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.

.
.
.


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