This discussion is archived
12 Replies Latest reply: Oct 22, 2013 9:39 PM by BillyVerreynne RSS

slow performance when SQL queries run in paralleism

887829 Newbie
Currently Being Moderated


Hi,

 

Version-11gR1 , datawarehouse

os linux

When queries are executed in parallel the query is performing very slow.as many such parallel queries run at a time. Previously it was not the case , but as the data is grown now it's taking time.

Please let me know if any parameter to tune to improve parallelism.

Queries are run on big tables ,around 40GB size of tables ..Pls. Advice

CPU utilization is just 20% and 10G memory also is available , only 10% of swapping memory in use.

 

Regards,

  • 1. Re: slow performance when SQL queries run in paralleism
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi,

     

    What is the value: parallel_degeree_policy?

    If value is MANUAL, please change to AUTO with alter system set parallel_degree_policy=AUTO; and try excute script again.

     

    Regards

    Mahir M. Quluzade

     

  • 2. Re: slow performance when SQL queries run in paralleism
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Prove it is slow. Show us the technical details.

     

    We cannot comment on unsubstantiated claims that parallel query is now suddenly (without any reason) "slow".

  • 3. Re: slow performance when SQL queries run in paralleism
    887829 Newbie
    Currently Being Moderated

    Thanks Mahir,

    Yes, it's set to manual, we 'll try putting it in auto and check. IF any other recommendation ?


    SQL> show parameter parallel

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    fast_start_parallel_rollback         string      LOW
    parallel_adaptive_multi_user         boolean     TRUE
    parallel_automatic_tuning            boolean     FALSE
    parallel_degree_limit                string      CPU
    parallel_degree_policy               string      MANUAL
    parallel_execution_message_size      integer     16384
    parallel_force_local                 boolean     FALSE
    parallel_instance_group              string
    parallel_io_cap_enabled              boolean     FALSE
    parallel_max_servers                 integer     485
    parallel_min_percent                 integer     0

    parallel_min_servers                 integer     0
    parallel_min_time_threshold          string      AUTO
    parallel_server                      boolean     FALSE
    parallel_server_instances            integer     1
    parallel_servers_target              integer     1024
    parallel_threads_per_cpu             integer     2
    recovery_parallelism                 integer     0
    SQL>

     

    When the same parallel query run alone the query executes faster in 10 min. but when ran in parallel with other queries during peak hours it takes 30 minutes.

  • 4. Re: slow performance when SQL queries run in paralleism
    Karthick_Arp Guru
    Currently Being Moderated

    Whenever there is a performance issue, directly jumping into changing few DB level parameters to get it fixed is a bad idea. And does not work most of the time. Every performance problem need to be addressed independently. You need to identify the underlying bottleneck. Without that trying to find a solution is just like shooting in the dark. There are various oracle supplied tools that can be used to identify performance bottleneck.

     

    I would like to point you the following thread When your query takes too long ... This one by RobvanWijk explains some of the tools that can be used to identify performance related issues.

     

    Once you are done with that and still have issues I would suggest you this thread HOW TO: Post a SQL statement tuning request - template posting by RandolfGeist. This lets you know what are the details you need to provide in a public forum when asking for a performance tuning request.

  • 5. Re: slow performance when SQL queries run in paralleism
    MahirM.Quluzade Guru
    Currently Being Moderated

    I think, you must analysis ADDM report, AWR report and EXECUTION PLAN of this Query statement.

    If you saw problem in this reports, then please paste here.

     

     

  • 6. Re: slow performance when SQL queries run in paralleism
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    you probably forgot to set PARALLEL_FAST = true in init.ora.

     

    Best regards,

      Nikolay

  • 7. Re: slow performance when SQL queries run in paralleism
    887829 Newbie
    Currently Being Moderated

    Hi,

     

    No parameter found named PARALLEL_FAST .

  • 8. Re: slow performance when SQL queries run in paralleism
    Paul Horth Expert
    Currently Being Moderated

    I think he meant go_faster=really_fast.

  • 9. Re: slow performance when SQL queries run in paralleism
    Vite DBA Pro
    Currently Being Moderated

    Have you analysed the queries to see what they are waiting on.

     

    I'll bet its IO.

     

    Regards

    Andre

  • 10. Re: slow performance when SQL queries run in paralleism
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Perhaps not? Could well be that the PQ pool is exhausted and the query is run serially. Or maybe there is a global cache issue that causes serialisation.

     

    One needs to first determine WHAT is happening is the point I raised above. Claiming that parallel query is no longer working correctly, based on mere observation of elapsed run-time, is a very dangerous and fundamentally incorrect claim - as it lacks any evidence to substantiate that claim.

     

    Heck it could be that a fibre channel port failed, reducing I/O bandwidth with 50%. Or that 2 of the 4 cluster nodes are down, reducing CPU and I/O capacity with 50%. It could be due to a rebalance of the ASM diskgroup(s) being done by the DBA, at power 11, seriously degrading I/O performance. Etc.

     

    Claims like the one made is just plain silly - it MUST be backed up with HARD technical evidence. Else is not worth the bits used to write it in..

  • 11. Re: slow performance when SQL queries run in paralleism
    887829 Newbie
    Currently Being Moderated

    Hi,

     

    Ya.. It's IO issue.Higher waits observed in User IO, and few queries found which requires tuning which includes these parallel queries and

    for few queries no recommendastion available when run tuning advisor but Full table scan was observed for explain plan.

     

    For SQL tuning we are working with developers, just wanted to make sure whether anything can be done to improve the parallelism.

     

     


  • 12. Re: slow performance when SQL queries run in paralleism
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    We do not know what analysis you did. We cannot see the results. So you need to tell us the exact details.

     

    Higher I/O waits mean nothing without context. Higher than what? What is the baseline? How was the I/O measured? For which processes?

     

    I/O waits are a fact of live when dealing with spinning rust.

Legend

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