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.
5 executions with no additional parse overheads.
Next, I run Parallel Execution.
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.
.
.
.
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:
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
Yasin,
Thank you for the correction.
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
Rakesh,
Query V$SQL to see if you had 2 child cursors. V$SQLSTATS aggregates across the child cursors.
Post a Comment