OraFAQ Forum: Performance Tuning » PARALLEL when I don't want it

Home » RDBMS Server » Performance Tuning » PARALLEL when I don't want it

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PARALLEL when I don't want it [message #578602]

Fri, 01 March 2013 23:38

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

I have a query that is building a reporting table. As you can see from all the PX..., it is running in parallel. I don't, however, want this. I need it to play nice with other queries that are running concurrently. So I need it not to run in parallel. I have tried the NOPARALLEL hint, but it doesn't seem to work. Anybody have any suggestions?

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ NOPARALLEL(seg)*/
  3         DISTINCT seg.unique_hits
  4    FROM seg
  5   WHERE log_id       > TO_CHAR(TRUNC(SYSDATE)-62, 'yyyymmddhh24mi')
  6     AND user_id = 3219;
 
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3723213523
--------------------------------------------------------------------------------
| Id  | Operation                                | Name                | Rows  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                     | 14809 |
|   1 |  PX COORDINATOR                          |                     |       |
|   2 |   PX SEND QC (RANDOM)                    | :TQ10001            | 14809 |
|   3 |    HASH UNIQUE                           |                     | 14809 |
|   4 |     PX RECEIVE                           |                     | 14809 |
|   5 |      PX SEND HASH                        | :TQ10000            | 14809 |
|   6 |       HASH UNIQUE                        |                     | 14809 |
|   7 |        PX PARTITION RANGE ITERATOR       |                     | 14809 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| SEG                 | 14809 |
|*  9 |          INDEX RANGE SCAN                | DSA1_CDATE_IDX      |   118K|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   8 - filter("SEG"."LOG_ID">TO_NUMBER(TO_CHAR(TRUNC(SYSDATE@!)-62,'yyyymmddhh24
   9 - access("SEG"."USER_ID"=3219)

Report message to a moderator

Re: PARALLEL when I don't want it [message #578606 is a reply to message #578602]

Sat, 02 March 2013 01:07

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is your Oracle version (4 decimals).
Note that NOPARALLEL is depreciated since 10g, you have to use NO_PARALLEL.

Regards
Michel

Report message to a moderator

Re: PARALLEL when I don't want it [message #578636 is a reply to message #578606]

Sat, 02 March 2013 11:39

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Thanks for the reply. I should have checked that, but tried them both. Neither one worked.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as ...
 
SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ NO_PARALLEL(s)*/
  3         s.unique_visitors
  4    FROM seg s
  5   WHERE s.log_id       > TO_CHAR(TRUNC(SYSDATE)-62, 'yyyymmddhh24mi')
  6     AND s.user_id = 3219;
 
Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2133139703
--------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Byt
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     | 14809 |   5
|   1 |  PX COORDINATOR                      |                     |       |
|   2 |   PX SEND QC (RANDOM)                | :TQ10000            | 14809 |   5
|   3 |    PX PARTITION RANGE ITERATOR       |                     | 14809 |   5
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| SEG                 | 14809 |   5
|*  5 |      INDEX RANGE SCAN                | DSA1_CDATE_IDX      |   118K|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("SEG"."LOG_ID">TO_NUMBER(TO_CHAR(TRUNC(SYSDATE@!)-62,'yyyymmddhh24
   5 - access("SEG"."USER_ID"=3219)
 
18 rows selected

Report message to a moderator

Re: PARALLEL when I don't want it [message #578638 is a reply to message #578636]

Sat, 02 March 2013 12:09

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Can you post the IN-OUT and Pstart/Pstop columns.
What is the value of DEGREE for the table in dba_tables?
What is the value of PQ_STATUS for your session in v$session?

Regards
Michel

[Updated on: Sat, 02 March 2013 12:09]

Report message to a moderator

Re: PARALLEL when I don't want it [message #578647 is a reply to message #578638]

Sat, 02 March 2013 20:30

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

alter session set "_optimizer_ignore_hints" = TRUE ;

maybe someone has turned hints off on your system. Check this parameter.

I am just guessing though.

[Updated on: Sat, 02 March 2013 20:31]

Report message to a moderator

Re: PARALLEL when I don't want it [message #578654 is a reply to message #578636]

Sun, 03 March 2013 02:18

xtender
Messages: 34
Registered: January 2011
Location: Russia

Member

Could you show your parameters for parallelizing:

show parameter parallel

and as Kevin already wrote - "_optimizer_ignore_hints":

select
   a.ksppinm name
   ,b.ksppstvl value
   ,b.ksppstdf deflt
from
   sys.x$ksppi a
   ,sys.x$ksppcv b
where
   a.indx = b.indx
and a.ksppinm = '_optimizer_ignore_hints'
order by name

Report message to a moderator

Re: PARALLEL when I don't want it [message #578656 is a reply to message #578636]

Sun, 03 March 2013 02:43

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Rather than
/*+ NO_PARALLEL(s)*/
use
/*+ NO_PARALLEL */
I think you are getting parallelism because you have decorated the index, not the table. A simple no_parallel hint should stop parallelism against anything.

Report message to a moderator

Re: PARALLEL when I don't want it [message #578664 is a reply to message #578656]

Sun, 03 March 2013 03:32

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But documentation states:

Quote:

Beginning with Oracle Database 11g Release 2 (11.2.0.1), the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints.

I also read it as NO_PARALLEL implies NO_PARALLEL_INDEX for all indexes of the table but I may be wrong.
So the question is also: what the value of DEGREE for the index in dba_indexes?

Regards
Michel

[Updated on: Sun, 03 March 2013 03:34]

Report message to a moderator