I have a job running with the error ORA-12842: Cursor Invalidated During Parallel Execution,and I re-run and it's complete.
I check the MOS NOTE:1322894.1 to look up the cause,I see that it give the cause is :
ORA-12842 can be reported to a user session when executing a parallel query.
This is normal and usually can be ignored but may cause problems for some applications.
ORA-12842: Cursor invalidated during parallel execution -- the object changed
So I go to check the alert.log if there're any DDL operation relate to the job's objects, I look up the job's code,but I found nothing about PARALLEL QUERY SQL in the script except the SQL: alter session force parallel DML parallel ..; AND alter session force parallel QUERY parallel ..;
So I was wonder where are the PARALLEL QUERY?
Or the statement 'alter session force parallel QUERY parallel ..;' is the same mean to ALL THE QUERY ARE PARALLEL QUERY?
Please help!
Thanks!
Yes, if you run:
alter session force parallel QUERY parallel ..;
Oracle will parallelize all queries it's able to in the session.
http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc.htm#ADMIN11188 Note it doesn't guarantee parallel execution of queries. It could still go with serial.
You can stop this by using the no_parallel hint:
set serveroutput off
create table t as
select rownum x from dual connect by level <= 10000;
exec dbms_stats.gather_table_stats(user, 't');
select /*+ gather_plan_statistics */count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */count(*) from t
Plan hash value: 1071362934
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
alter session force parallel QUERY parallel 2;
select /*+ gather_plan_statistics */count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */count(*) from t
Plan hash value: 1826541471
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 |
| 4 | SORT AGGREGATE | |
| 5 | PX BLOCK ITERATOR | |
| 6 | TABLE ACCESS FULL| T |
-------------------------------------------
select /*+ gather_plan_statistics no_parallel */count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics no_parallel */count(*) from t
Plan hash value: 1071362934
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
You can read more about this at:
http://www.adellera.it/blog/2013/05/17/alter-session-force-parallel-query-and-indexes/ You can also get parallel execution because someone set this property on the table:
alter session enable parallel query;
select /*+ gather_plan_statistics */count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */count(*) from t
Plan hash value: 1071362934
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
alter table t parallel 2;
select /*+ gather_plan_statistics */count(*) from t;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */count(*) from t
Plan hash value: 1826541471
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 |
| 4 | SORT AGGREGATE | |
| 5 | PX BLOCK ITERATOR | |
| 6 | TABLE ACCESS FULL| T |
-------------------------------------------
So if you want to stop parallel execution, either:
- Remove the "alter session force ..."
- Add the no_parallel hint to queries you want to run serially
Also check user_tables.degree to see if someone has enabled parallel that way.