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.

.
.
.


4 comments:

Yasin Baskan said...

Hi Hemant,

Parallel queries are not hard parsed each time. The number of parses you see comes from the query coordinator and the PX servers. Since you are using a DOP of 4 you see 5 parses each time you run the statement, one query coordinator, four PX servers. With a DOP of 2 you would see 3 parses for example. Please see the following example which shows the second execution of a query with DOP=2. There are 6 parses because of 2 executions but no hard parses.


SQL> select n.name,s.VALUE from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like 'parse count%' ;

NAME VALUE
------------------------------ -----------
parse count (total) 259
parse count (hard) 102
parse count (failures) 0
parse count (describe) 0

Elapsed: 00:00:00.01
SQL> select /*+ parallel(2) */ count(*) from sales;

COUNT(*)
----------
960

Elapsed: 00:00:00.02
SQL> select n.name,s.VALUE from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like 'parse count%' ;

NAME VALUE
------------------------------ -----------
parse count (total) 263
parse count (hard) 102
parse count (failures) 0
parse count (describe) 0

Elapsed: 00:00:00.00
SQL> select parse_Calls,sql_text from v$sql where lower(sql_text) like '%sales%';

PARSE_CALLS SQL_TEXT
----------- --------------------------------------------------------------------------------
6 select /*+ parallel(2) */ count(*) from sales
2 select parse_Calls,sql_text from v$sql where lower(sql_text) like '%sales%'
3 select parse_Calls,sql_text from v$sql where lower(sql_text) like '%sales%'

Yasin Baskan
Product Manager for Parallel Execution

Hemant K Chitale said...

Yasin,
Thank you for the correction.

Rakesh Reddy said...

Thanks Hemant for posts on parallel series, In my case I could only see 5 executions during first run, all subsequent executions have only 4 parse calls per execution. I am using parallel(4) hint instead of modifying object degree.

SQL> select executions, parse_calls, invalidations, sql_fulltext from v$sqlstats where sql_id = 'fg52yv7v85zjy';

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


SQL> select /*+ PARALLEL(4) */ count(*) from rakesh_objects;

COUNT(*)
----------
18010

SQL> select executions, parse_calls, invalidations, sql_fulltext from v$sqlstats where sql_id = 'fg52yv7v85zjy';

EXECUTIONS PARSE_CALLS INVALIDATIONS
---------- ----------- -------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
2 9 0
select /*+ PARALLEL(4) */ count(*) from rakesh_objects


SQL> select /*+ PARALLEL(4) */ count(*) from rakesh_objects;

COUNT(*)
----------
18010

SQL> select executions, parse_calls, invalidations, sql_fulltext from v$sqlstats where sql_id = 'fg52yv7v85zjy';

EXECUTIONS PARSE_CALLS INVALIDATIONS
---------- ----------- -------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
3 13 0
select /*+ PARALLEL(4) */ count(*) from rakesh_objects


SQL> select /*+ PARALLEL(4) */ count(*) from rakesh_objects;

COUNT(*)
----------
18010

SQL> select executions, parse_calls, invalidations, sql_fulltext from v$sqlstats where sql_id = 'fg52yv7v85zjy';

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




Thanks
Rakesh

Hemant K Chitale said...

Rakesh,
Query V$SQL to see if you had 2 child cursors. V$SQLSTATS aggregates across the child cursors.

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