Sunday, March 20, 2011

PX COORDINATOR FORCED SERIAL operation

This is just a short heads-up for those that come across an execution plan showing the PX COORDINATOR FORCED SERIAL operation. I don't have official confirmation but according to my tests a plan with such an operation effectively means: Cost for parallel execution but execute serially (You might remember that I've recently mentioned in another post that there is the possibility to have a plan executed in parallel but costed serially, weird isn't it). Why such an operation exists is not clear to me - obviously it would make much more sense to cost straight away for serial execution in such a case. Probably there is a good reason, otherwise such an operation didn't exist but I think at least the costing is questionable in current versions.

So the message effectively is this: Review the execution plans in such cases - due to the cost reduction introduced by parallel execution it might be favoured by the optimizer over more efficient (serial) execution plans that would be chosen if the costing was based on serial execution.

So far I've only be able to get this operation in conjunction with user-defined functions that are not enabled for parallel execution, therefore parallel execution with such functions is questionable anyway from a performance perspective, however I don't know if there are other scenarios where this operation gets used. Furthermore it is not entirely clear to me under which circumstances the optimizer resorts to that operation - slight modifications to the SQL sometimes showed that the operation was replaced by the normal PX COORDINATOR operation without making entirely obvious why it was possible to use normal parallel execution in those cases.

Here is a simple test case that shows that due to the cost reduction of the (alleged) parallel execution the optimizer favours a full table scan although the serial cost of the same plan is higher than an index-based plan. Since the actual execution is serial it should have been using the index-based plan.


set doc off
doc
--------------------------------------------------------------------------------
--
-- File name: px_parallel_force_serial_testcase.sql
--
-- Purpose: Simple testcase for a "PX COORDINATOR FORCED SERIAL" operation
-- It looks like in the versions mentioned below
-- this operation means "cost for parallel" but "execute serial"
-- The test case shows that this effectively allows the optimizer
-- to reject plans that would be chosen if the plan was costed
-- serially. Here the plan based on INDEX RANGE SCAN costs more
-- than a degree 4 parallel full table scan but costs less than
-- a serial full table scan.
--
-- Author: Randolf Geist http://oracle-randolf.blogspot.com
--
-- Last tested: March 2011
--
-- Versions: 10.2.0.4
-- 10.2.0.5
-- 11.1.0.7
-- 11.2.0.1
-- 11.2.0.2
--------------------------------------------------------------------------------
#

set echo on timing on linesize 200 pagesize 999 long 1000000 feedback 1 tab off autotrace off trimspool on

drop table t_temp;

purge table t_temp;

drop table t;

purge table t;

drop package pk_par_test;

exec dbms_random.seed(0)

create table t
as
select
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 100000
order by
dbms_random.value;

alter table t parallel 4;

create package pk_par_test authid current_user
as
-- Dummy function, not enabled for parallel execution
function f_par_test_serial(
in_varchar in varchar2 default null
, in_number in number default null
, in_date in date default null
, in_wait in number default null
, in_append in number default null
) return varchar2;

-- Dummy function, enabled for parallel execution
function f_par_test_parallel(
in_varchar in varchar2 default null
, in_number in number default null
, in_date in date default null
, in_wait in number default null
, in_append in number default null
) return varchar2 parallel_enable;
end pk_par_test;
/

create package body pk_par_test
as
-- Dummy function, not enabled for parallel execution
function f_par_test_serial(
in_varchar in varchar2 default null
, in_number in number default null
, in_date in date default null
, in_wait in number default null
, in_append in number default null
) return varchar2
as
begin
if in_wait is not null then
execute immediate 'begin dbms_lock.sleep(:in_wait); end;' using in_wait;
end if;

return 'Instance: ' || sys_context('userenv', 'instance') || ' / SID: ' || sys_context('userenv', 'sid') ||
case when in_append is not null then ': ' || coalesce(in_varchar, to_char(in_number), to_char(in_date)) end;
end;

-- Dummy function, enabled for parallel execution
function f_par_test_parallel(
in_varchar in varchar2 default null
, in_number in number default null
, in_date in date default null
, in_wait in number default null
, in_append in number default null
) return varchar2 parallel_enable
as
begin
if in_wait is not null then
execute immediate 'begin dbms_lock.sleep(:in_wait); end;' using in_wait;
end if;

return 'Instance: ' || sys_context('userenv', 'instance') || ' / SID: ' || sys_context('userenv', 'sid') ||
case when in_append is not null then ': ' || coalesce(in_varchar, to_char(in_number), to_char(in_date)) end;
end;
end pk_par_test;
/

create index t_idx on t (id);

exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1')

-- alter session set statistics_level = all;

-- alter session set tracefile_identifier = 'forced_serial';

-- alter session set events '10053 trace name context forever, level 1';

column name format a40

select
name
, value
from
v$mystat v
, v$statname s
where
s.name like '%parallelized'
and s.statistic# = v.statistic#;

-- This query will be executed serially, but obviously costed like being executed in parallel
-- Why is it not costed serially if the optimizer knows that it is going to be executed serially?
--explain plan for
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
from t
where id between 1 and 200;

select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED'));

column name format a40

select
name
, value
from
v$mystat v
, v$statname s
where
s.name like '%parallelized'
and s.statistic# = v.statistic#;

column monitor_output format a200

select dbms_sqltune.report_sql_monitor(session_id=>USERENV('SID')) as monitor_output from dual;

-- Disabling parallel query
-- Now only serial costs will be evaluated
alter session disable parallel query;

-- Uses the index, because the indexed plan is cheaper than a serial full table scan
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
from t
where id between 1 and 200;

select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED'));

column name format a40

select
name
, value
from
v$mystat v
, v$statname s
where
s.name like '%parallelized'
and s.statistic# = v.statistic#;

column monitor_output format a200

select dbms_sqltune.report_sql_monitor(session_id=>USERENV('SID')) as monitor_output from dual;

-- alter session set events '10053 trace name context off';

-- The indexed plan is cheaper than a serial full table scan
explain plan for
select /*+ full(t) */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
from t
where id between 1 and 200;

select * from table(sys.dbms_xplan.display(null, null, 'ADVANCED'));


And this is the output I got from 11.2.0.1, but the same applies to all versions mentioned in the script:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t_temp;
drop table t_temp
*
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.01
SQL>
SQL> purge table t_temp;
purge table t_temp
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN


Elapsed: 00:00:00.01
SQL>
SQL> drop table t;

Table dropped.

Elapsed: 00:00:00.17
SQL>
SQL> purge table t;

Table purged.

Elapsed: 00:00:00.04
SQL>
SQL> drop package pk_par_test;

Package dropped.

Elapsed: 00:00:00.14
SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> create table t
2 as
3 select
4 rownum as id
5 , rpad('x', 100) as filler
6 from
7 dual
8 connect by
9 level <= 100000
10 order by
11 dbms_random.value;

Table created.

Elapsed: 00:00:01.29
SQL>
SQL> alter table t parallel 4;

Table altered.

Elapsed: 00:00:00.21
SQL>
SQL> create package pk_par_test authid current_user
2 as
3 -- Dummy function, not enabled for parallel execution
4 function f_par_test_serial(
5 in_varchar in varchar2 default null
6 , in_number in number default null
7 , in_date in date default null
8 , in_wait in number default null
9 , in_append in number default null
10 ) return varchar2;
11
12 -- Dummy function, enabled for parallel execution
13 function f_par_test_parallel(
14 in_varchar in varchar2 default null
15 , in_number in number default null
16 , in_date in date default null
17 , in_wait in number default null
18 , in_append in number default null
19 ) return varchar2 parallel_enable;
20 end pk_par_test;
21 /

Package created.

Elapsed: 00:00:00.07
SQL>
SQL> create package body pk_par_test
2 as
3 -- Dummy function, not enabled for parallel execution
4 function f_par_test_serial(
5 in_varchar in varchar2 default null
6 , in_number in number default null
7 , in_date in date default null
8 , in_wait in number default null
9 , in_append in number default null
10 ) return varchar2
11 as
12 begin
13 if in_wait is not null then
14 execute immediate 'begin dbms_lock.sleep(:in_wait); end;' using in_wait;
15 end if;
16
17 return 'Instance: ' || sys_context('userenv', 'instance') || ' / SID: ' || sys_context('userenv', 'sid') ||
18 case when in_append is not null then ': ' || coalesce(in_varchar, to_char(in_number), to_char(in_date)) end;
19 end;
20
21 -- Dummy function, enabled for parallel execution
22 function f_par_test_parallel(
23 in_varchar in varchar2 default null
24 , in_number in number default null
25 , in_date in date default null
26 , in_wait in number default null
27 , in_append in number default null
28 ) return varchar2 parallel_enable
29 as
30 begin
31 if in_wait is not null then
32 execute immediate 'begin dbms_lock.sleep(:in_wait); end;' using in_wait;
33 end if;
34
35 return 'Instance: ' || sys_context('userenv', 'instance') || ' / SID: ' || sys_context('userenv', 'sid') ||
36 case when in_append is not null then ': ' || coalesce(in_varchar, to_char(in_number), to_char(in_date)) end;
37 end;
38 end pk_par_test;
39 /

Package body created.

Elapsed: 00:00:00.04
SQL>
SQL> create index t_idx on t (id);

Index created.

Elapsed: 00:00:00.25
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.63
SQL>
SQL> -- alter session set statistics_level = all;
SQL>
SQL> -- alter session set tracefile_identifier = 'forced_serial';
SQL>
SQL> -- alter session set events '10053 trace name context forever, level 1';
SQL>
SQL> column name format a40
SQL>
SQL> select
2 name
3 , value
4 from
5 v$mystat v
6 , v$statname s
7 where
8 s.name like '%parallelized'
9 and s.statistic# = v.statistic#;

NAME VALUE
---------------------------------------- ----------
queries parallelized 0
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 0

4 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> -- This query will be executed serially, but obviously costed like being executed in parallel
SQL> -- Why is it not costed serially if the optimizer knows that it is going to be executed serially?
SQL> --explain plan for
SQL> select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
2 from t
3 where id between 1 and 200;

INST_SID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Instance: 1 / SID: 136

1 row selected.

Elapsed: 00:00:00.03
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0mmr8fw2hpqyt, child number 0
-------------------------------------
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as
inst_sid from t where id between 1 and 200

Plan hash value: 1199313732

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 118 (100)| | | | |
| 1 | PX COORDINATOR FORCED SERIAL| | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 106 | 118 (2)| 00:00:02 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 1 | 106 | 118 (2)| 00:00:02 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 200 | 21200 | 117 (1)| 00:00:02 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 200 | 21200 | 117 (1)| 00:00:02 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 200 | 21200 | 117 (1)| 00:00:02 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | T | 200 | 21200 | 117 (1)| 00:00:02 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
7 - SEL$1 / T@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access(:Z>=:Z AND :Z<=:Z)
filter(("ID"<=200 AND "ID">=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
2 - (#keys=0) "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
3 - "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
4 - "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
5 - (#keys=1) "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
6 - "FILLER"[VARCHAR2,100]
7 - "FILLER"[VARCHAR2,100]


58 rows selected.

Elapsed: 00:00:00.48
SQL>
SQL> column name format a40
SQL>
SQL> select
2 name
3 , value
4 from
5 v$mystat v
6 , v$statname s
7 where
8 s.name like '%parallelized'
9 and s.statistic# = v.statistic#;

NAME VALUE
---------------------------------------- ----------
queries parallelized 0
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 0

4 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> column monitor_output format a200
SQL>
SQL> select dbms_sqltune.report_sql_monitor(session_id=>USERENV('SID')) as monitor_output from dual;

MONITOR_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid from t where id between 1 and 200

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : CBO_TEST (136:99)
SQL ID : 0mmr8fw2hpqyt
SQL Execution ID : 16777218
Execution Started : 03/12/2011 19:56:55
First Refresh Time : 03/12/2011 19:56:55
Last Refresh Time : 03/12/2011 19:56:55
Duration : .032408s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Fetch Calls : 2

Global Stats
==========================================================================
| Elapsed | Cpu | IO | PL/SQL | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Time(s) | Calls | Gets | Reqs | Bytes |
==========================================================================
| 0.03 | 0.03 | 0.00 | 0.00 | 2 | 1600 | 2 | 16384 |
==========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1199313732)
================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | 1 | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 118 | 1 | +0 | 1 | 0 | | | |
| 3 | HASH UNIQUE | | 1 | 118 | 1 | +0 | 1 | 1 | 464K | | |
| 4 | PX RECEIVE | | 200 | 117 | | | 1 | | | | |
| 5 | PX SEND HASH | :TQ10000 | 200 | 117 | 1 | +0 | 1 | 0 | | | |
| 6 | PX BLOCK ITERATOR | | 200 | 117 | 1 | +0 | 1 | 200 | | | |
| 7 | TABLE ACCESS FULL | T | 200 | 117 | 1 | +0 | 1 | 200 | | | |
================================================================================================================================================


1 row selected.

Elapsed: 00:00:00.68
SQL>
SQL> -- Disabling parallel query
SQL> -- Now only serial costs will be evaluated
SQL> alter session disable parallel query;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> -- Uses the index, because the indexed plan is cheaper than a serial full table scan
SQL> select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
2 from t
3 where id between 1 and 200;

INST_SID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Instance: 1 / SID: 136

1 row selected.

Elapsed: 00:00:00.03
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 0mmr8fw2hpqyt, child number 1
-------------------------------------
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as
inst_sid from t where id between 1 and 200

Plan hash value: 771813181

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | HASH UNIQUE | | 1 | 106 | 203 (1)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 200 | 21200 | 202 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | T_IDX | 200 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID">=1 AND "ID"<=200)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
2 - "FILLER"[VARCHAR2,100]
3 - "T".ROWID[ROWID,10]


50 rows selected.

Elapsed: 00:00:00.25
SQL>
SQL> column name format a40
SQL>
SQL> select
2 name
3 , value
4 from
5 v$mystat v
6 , v$statname s
7 where
8 s.name like '%parallelized'
9 and s.statistic# = v.statistic#;

NAME VALUE
---------------------------------------- ----------
queries parallelized 0
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 0

4 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> column monitor_output format a200
SQL>
SQL> select dbms_sqltune.report_sql_monitor(session_id=>USERENV('SID')) as monitor_output from dual;

MONITOR_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid from t where id between 1 and 200

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : CBO_TEST (136:99)
SQL ID : 0mmr8fw2hpqyt
SQL Execution ID : 16777219
Execution Started : 03/12/2011 19:56:56
First Refresh Time : 03/12/2011 19:56:56
Last Refresh Time : 03/12/2011 19:56:56
Duration : .0156s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Fetch Calls : 2

Global Stats
================================================
| Elapsed | Cpu | PL/SQL | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Calls | Gets |
================================================
| 0.02 | 0.02 | 0.00 | 2 | 202 |
================================================

SQL Plan Monitoring Details (Plan Hash Value=771813181)
=============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | |
| 1 | HASH UNIQUE | | 1 | 203 | 1 | +0 | 1 | 1 | 468K | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 200 | 202 | 1 | +0 | 1 | 200 | | | |
| 3 | INDEX RANGE SCAN | T_IDX | 200 | 2 | 1 | +0 | 1 | 200 | | | |
=============================================================================================================================================


1 row selected.

Elapsed: 00:00:00.20
SQL>
SQL> -- alter session set events '10053 trace name context off';
SQL>
SQL> -- The indexed plan is cheaper than a serial full table scan
SQL> explain plan for
2 select /*+ full(t) */ distinct pk_par_test.f_par_test_serial(filler) as inst_sid
3 from t
4 where id between 1 and 200;

Explained.

Elapsed: 00:00:00.03
SQL>
SQL> select * from table(sys.dbms_xplan.display(null, null, 'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1793979440

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 424 (2)| 00:00:06 |
| 1 | HASH UNIQUE | | 1 | 106 | 424 (2)| 00:00:06 |
|* 2 | TABLE ACCESS FULL| T | 200 | 21200 | 423 (1)| 00:00:06 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"<=200 AND "ID">=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "PK_PAR_TEST"."F_PAR_TEST_SERIAL"("FILLER")[4000]
2 - "FILLER"[VARCHAR2,100]

41 rows selected.

Elapsed: 00:00:00.21
SQL>

3 comments:

Joel Garry said...

None of this is clear to me, but I note a description of "purity testing" in an older bug 4133884. I'm postulating when we see things that seem to make no sense we are actually seeing workarounds due to different requirements at different layers of execution. In other words, there is not only parallelization and serialization in queries, but in multiuser execution. There are limits in how much something can know about what will happen to its results in the future. In the subsequent operations, tests must be made to decide if what the optimizer has decided to do is "pure." Perhaps there are some cases where it was decided to do things backwards earlier in the stream so the right thing will happen later most of the time.

word:giletio

Greg Rahn said...

The reason this happens is that the query plan tree is built from the bottom up. The non-parallel function is then hit but the plan has already been partially built. As a result, the plan tree is completed, but the parallel built plan is executed with a single process (this is the forced serial part). Hope that helps.

Randolf said...

Hi Greg,

thanks a lot for the details and explanation!

Much appreciated.

Randolf