Home » RDBMS Server » Performance Tuning » PARALLEL when I don't want it
PARALLEL when I don't want it [message #578602] Fri, 01 March 2013 23:38 Go to next message
scottwmackey
Messages: 505
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)
 
Re: PARALLEL when I don't want it [message #578606 is a reply to message #578602] Sat, 02 March 2013 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: PARALLEL when I don't want it [message #578636 is a reply to message #578606] Sat, 02 March 2013 11:39 Go to previous messageGo to next message
scottwmackey
Messages: 505
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
Re: PARALLEL when I don't want it [message #578638 is a reply to message #578636] Sat, 02 March 2013 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
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 Go to previous messageGo to next message
xtender
Messages: 33
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
Re: PARALLEL when I don't want it [message #578656 is a reply to message #578636] Sun, 03 March 2013 02:43 Go to previous messageGo to next message
John Watson
Messages: 4693
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.
Re: PARALLEL when I don't want it [message #578664 is a reply to message #578656] Sun, 03 March 2013 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: PARALLEL when I don't want it [message #578686 is a reply to message #578656] Mon, 04 March 2013 02:31 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
We have a winner. Thanks John. Removing the table reference in the hint did it.
Re: PARALLEL when I don't want it [message #578688 is a reply to message #578686] Mon, 04 March 2013 02:51 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So my understanding of the documentation was wrong.
Thanks for this topic, I've learned something new today.

Regards
Michel
Previous Topic: problem with statspack
Next Topic: Hybrid Columnar Compression
Goto Forum:
  


Current Time: Sat Oct 25 05:13:47 CDT 2014

Total time taken to generate the page: 0.09234 seconds