in Oracle

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]

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.

.
.
.