This discussion is archived
6 Replies Latest reply: Jan 29, 2013 8:21 AM by 977635 RSS

why is OEM SQL Monitoring showing parallel on almost every statement

977635 Newbie
Currently Being Moderated
I'm confused here.

I'm running Oracle EE 11.2.0.2 and when I look in OEM SQL Monitoring, it shows nearly every sql statement running with a degree of parallelism of *2*.

I've checked dba_tables and the 'degree' for all tables is only 1.
I look at the actual sql statement, and there are no hints to tell it to use parallelism.
So why and how is the database using parallelism?

I do see that parallel_threads_per_cpu is set to 2, but this is default for our Solaris 10 operating system.

REF: (for 11.2)
===========
PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.

The default is platform-dependent and is adequate in most cases. You should decrease the value of this parameter if the machine appears to be overloaded when a representative parallel query is executed. You should increase the value if the system is I/O bound.

I guess the next question here is how to tell if my database is actually IO bound or not?
  • 1. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    JohnWatson Guru
    Currently Being Moderated
    What is your setting for parallel_degee_policy? If it is auto, then you can get parallel execution without ny hints or table decoration.
  • 2. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    977635 Newbie
    Currently Being Moderated
    Hi John. Thanks for your reply.
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ------------------------------
    parallel_degree_policy               string                           MANUAL
    And so, the more I read about PARALLEL_THREADS_PER_CPU, the more I wonder if I should increase this value.
    But first, I want to understand why I'm seeing parallelism in OEM set to 2 for almost everything that runs in the database, but note, NOT ALL.
    Some queries, especially those running from Crystal Reports, are not using parallelism at all.
    Is it possible to set a parameter at the session level that runs parallelism, and perhaps this is being done by the application?

    I'm going to try increasing my PARALLEL_THREADS_PER_CPU to 4 and see if this changes the parallelism in OEM, (but I doubt it).

    I should note that my most recent AWR report shows my db_file_sequential_read in the top 5 wait events.
    This would imply my index reads and table reads by ROWID are waiting for disk - possibly I/O bound.

    Edited by: 974632 on Jan 28, 2013 10:25 AM
  • 3. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    ji li Pro
    Currently Being Moderated
    When you find out, I would be curious to know as well.
  • 4. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    974632 wrote:
    I'm confused here.

    I'm running Oracle EE 11.2.0.2 and when I look in OEM SQL Monitoring, it shows nearly every sql statement running with a degree of parallelism of *2*.

    I've checked dba_tables and the 'degree' for all tables is only 1.
    I look at the actual sql statement, and there are no hints to tell it to use parallelism.
    So why and how is the database using parallelism?

    I do see that parallel_threads_per_cpu is set to 2, but this is default for our Solaris 10 operating system.
    Any statement that is supposed to run parallel is reported in the SQL Monitoring screen - this may be why you find that nearly every statement you see there is a parallel statement - they're self-selecting.

    Don't mess with parallel_threads_per_cpu.

    Have you checked to see if you have any indexes set to a degree other than 1 ?
    Can you see any common patterns in the queries that run parallel ?


    Regards
    Jonathan Lewis
  • 5. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    977635 Newbie
    Currently Being Moderated
    Thank you for your reply (and advice regarding parallel_threads_per_cpu).
  • 6. Re: why is OEM SQL Monitoring showing parallel on almost every statement
    977635 Newbie
    Currently Being Moderated
    Hi Jonathan.

    I checked back and I did find that we do have many tables set with DEGREE=2.
    I'm not sure how I missed it before. Perhaps I only checked a few tables in question, but it appears many tables are actually set to a degree of parallelism is set to 2.

    I do have a related question regarding parallel processing: what is your opinion to set parallel_automatic_tuning=TRUE?
    It seems this would be set to TRUE by default, but apparently it is not.
    Anyway, I see the warnings from Oracle that if we do set this to TRUE, then we must specify the parallel clause for the target tables in the system (which it seems we have already done).
    So, what would be your view on this?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points