13 Replies Latest reply: May 15, 2008 2:24 PM by Jonathan Lewis RSS

    PX Deq Credit: send blkd At AWR "Top 5 Timed Events"

    549855
      PX Deq Credit: send blkd At Top 5 Timed Events

      Hi ,
      Below are examples of "Top 5 Timed Events" in my Staging data warehouse database.
      ALWAYS , at the most Top 5 Timed Events is the event : PX Deq Credit: send blkd.
      Oracle saids that its an idel event, but since it always at the the top of my AWR reports
      and all the others events are far behind it , i have a feeling that it may indicate of
      a problem.

      Top 5 Timed Events Avg %Total
      ~~~~~~~~~~~~~~~~~~ wait Call
      Event Waits Time (s) (ms) Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      PX Deq Credit: send blkd 3,152,038 255,152 81 95.6 Other
      direct path read 224,839 4,046 18 1.5 User I/O
      CPU time 3,217 1.2
      direct path read temp 109,209 2,407 22 0.9 User I/O
      db file scattered read 31,110 1,436 46 0.5 User I/O
      -------------------------------------------------------------

      Top 5 Timed Events Avg %Total
      ~~~~~~~~~~~~~~~~~~ wait Call
      Event Waits Time (s) (ms) Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      PX Deq Credit: send blkd 6,846,579 16,359 2 50.4 Other
      direct path read 101,363 5,348 53 16.5 User I/O
      db file scattered read 105,377 4,991 47 15.4 User I/O
      CPU time 3,795 11.7
      direct path read temp 70,208 940 13 2.9 User I/O
      -------------------------------------------------------------

      Hir some more information:
      Its a 500GB database on linux Red hat 4 with 8 CPUs and 16GB memory.
      Its based on an ASM file system.

      From the spfile:
      SQL> show parameter parallel

      NAME_COL_PLUS_SHOW_PARAM VALUE_COL_PLUS_SHOW_PARAM
      ------------------------------ ------------------------------
      parallel_adaptive_multi_user TRUE
      parallel_automatic_tuning FALSE
      parallel_execution_message_size 4096
      parallel_instance_group
      parallel_max_servers 240
      parallel_min_percent 0
      parallel_min_servers 0
      parallel_server FALSE
      parallel_server_instances 1
      parallel_threads_per_cpu 2
      recovery_parallelism 0

      Thanks.
        • 1. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
          108476
          Hi,

          Having PX Deq Credit: send blkd as your top user wait event is normal. My guess would be that you have maxed the CPU's with the parallel processes.

          I have some notes here:

          http://dba-oracle.com/bk_sql_wait_event_px_deq_credit_send_blkd_tips.htm

          --------------------

          Hope this helps. . .

          Donald K. Burleson
          Oracle Press author
          Author of "Oracle Tuning: The Definitive Reference":
          http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
          • 2. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
            549855
            Hi Mr.Burleson ,
            Thank you for your answer.

            "Having PX Deq Credit: send blkd as your top user wait event is normal. " -->

            Also having sqlnet message from client at the top could be "normal" , but
            i already read HotSos paper that shows that its not always the case.
            So i wonder if this my case too.
            1. What checks can i do in order be sure that in my case having CONSTANTLY
            "PX Deq Credit: send blkd" at the top of my AWR reports is not a problem.
            (p.s : its a 1 HR AWR report)
            2. What can be done in order to reduce its values ?
            Does increasing the value of init.ora parameter parallel_execution_message_size
            can help ? (i already changed it from 2K to 4K).

            "My guess would be that you have maxed the CPU's with the parallel processes." -->
            Mybe. But in this case i would expect to see it at the most top of the Top 5 Timed Events
            (Note: i already read Mr.Lewis paper about CPU in statspack report , and know that CPU TIME its not realy a wait event).

            Thanks Again
            • 3. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
              Jonathan Lewis
              user546852

              Although Don Burleson and I may have different ideas of exactly what we mean by "normal", I think we agree in this case that your problem is that your system is running parallel query too aggressively.

              The manuals, and related documents, are wrong about "PX Deq Credit: Send Blked" - there are two different scenarios where it can appear - one as an "idle" event and one as a performance threat.

              When PX slaves feed a query co-ordinator (QC), only one can supply data at a time and the others go into the "PX Deq Credit: Send blkd" with a timeout of 2 seconds. The end user doesn't see the result set appearing any more slowly because of this.

              When one layer of PX slaves is passing data up the tree to the next layer, then there is competition for the PX table queues (virtual tables) with PX slaves blocked and unable to write into the virtual tables. Waits at this point are time-wasting events.

              So you can get a little hint on whether your waits are "idle" or not is by seeing how close the average wait time is to 2 seconds - you are averaging 81 m/s, which suggests you are seeing a lot of interference between slaves.

              Note - your average I/O times are also slow, which fits in with PX slaves overloading the discs, and you are doing quite a lot of direct path reads which are typically PX scans, and direct path reads which are likely to be sorts or hash joins spilling to disk.

              It's a little surprising that your hourly snapshot shows only 3,217 seconds of CPU, when you have 8 CPUs available. One of the common side effects of excessive parallelism is excessive CPU usage. I suspect that if you look at the OS stats report from AWR you will see quite a lot of SYS CPU being used (or possibly CPU is disappearing to other tasks on the machine) - and that the total CPU usage of the system is significantly higher than the CPU usage reported in the Top 5 section.

              Looking at the parameter listings, you have max_parallel_servers = 240. I doubt if you've got enough discs to support that many slaves being active at once, and you haven't got enough CPUs to support that many slaves if they get a load of data into memory. I thought the default value for this parameter in 10g was 10 x CPUs - so has someone set it to a high value manually in an attempt to get better performance ?

              Bottom line - you're probably getting too aggressive on parallel query. Look at cutting parallel_max_servers. Look at how you've defined parallelism on tables and with hints to see where you can reduce the degree of parallelism.

              Suggested reading for further ideas: Doug Burn's papers on parallel execution - which can be found under http://oracledoug.com/papers.html

              Regards
              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk
              • 4. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                588955
                These are ideal wait events. Obviously you are using parallel sessions/queries that's way these are on top.
                • 5. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                  20394
                  Jonathan,

                  According to Bug 5898416, parallel_max_servers is calculated based on some unpublished formula. I often see it at 20 times cpu_count but it varies.

                  Yong Huang
                  • 6. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                    26741
                    parallel_max_servers is calculated based on some unpublished formula. I often see it at 20 times cpu_count but it varies.
                    Yes, I have a 10.2.0.2 database where PARALLEL_MAX_SERVERS was
                    automatically 80. (note : CPU_COUNT is correctly 4.)
                    I had to forcibly set PARALLEL_MAX_SERVERS down.
                    PROCESSES is 1500 so that could be influencing PARALLEL_MAX_SERVERS.

                    (I hadn't known about the
                    x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5) 
                    multipliers mentioned in the Bug and, now that I see it, in the Data Warehousing
                    Guide, Chapter 25)
                    • 7. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                      Jonathan Lewis
                      Yong Huang,

                      Thanks for the correction - you're right, it's much commoner to see 20 * CPUs on 10g. The value of 10 * CPUs is the common baseline for 9i (when parallel_automatic_tuning is set to true).

                      Personally, I think a value that's closer to 4 * CPUs might be a better default for most systems as this would help to avoid CPU starvation when runaway queries hit the system.


                      I'd say that that bug note is a good example of how Matalink notes shouldn't be written. Providing a list of sample settings with a following comment that "it changes according to processes" is just enough information to cloud the issue, without adding real value.

                      The note should have said something like: "If the calculated value for parallel_max_servers is too large compared to the processes setting, then a limiting factor is also applied. The obvious extreme where this applies is when parallel_max_servers is larger than processes, but the limit is actually a little below processes as Oracle has to acquire some processes to start the instance. Here are a couple of examples to demonstrate the point."


                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk
                      • 8. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                        26741
                        20 * CPUs is very high.

                        I thought that my 9i environments used to default to 5 even on 4 CPUs.

                        In any case, that is a lesson learned. Just in the past two weeks I've had
                        to scale down parallel query on two databases where developers were
                        enthusiastically adding DEGREE on certain tables.
                        • 9. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                          Jonathan Lewis
                          20 * CPUs is very high.
                          I agree, but that's the basic 10g model.
                          I thought that my 9i environments used to default to 5 even on 4 CPUs.
                          That's the default in 9i if you don't enable parallel_automatic_tuning. (Or parallel_adaptive_multi_user).
                          In any case, that is a lesson learned. Just in the past two weeks I've had
                          to scale down parallel query on two databases where developers were
                          enthusiastically adding DEGREE on certain tables.
                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          http://www.jlcomp.demon.co.uk
                          • 10. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                            26741
                            Where did I read recently that using PARALLEL Hints explicitly in SQLs
                            is preferred over setting DEGREE on tables .............?

                            The PARALLEL Hint provides us better control over how many parallel query
                            slaves that query will use.
                            • 11. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                              Jonathan Lewis
                              Possibly on a Burleson website, or repeated by Don Burleson on this forum.

                              It's a suggestion that I think is appropriate for a fairly large number of sites - many people simply throw parallel execution into the mix without considering the global impact when all they want is a few special cases of hammering the system briefly to get an important job done quickly. Hinting, when you can control the code, is likely to result in far fewer randomly occurring accidents.

                              Regards
                              Jonathan Lewis
                              http://jonathanlewis.wordpress.com
                              http://www.jlcomp.demon.co.uk
                              • 12. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                                549855
                                Hi Mr.Lewis
                                Thank you for your very detailed answer.
                                I reduce the value of parallel_max_server from 240 to 160 , but "PX Deq Credit: Send Blked"
                                still shows more or less the same values.
                                I checked again the OS stats report from AWR and sow quite a lot of SYS CPU , as you
                                suspect - But the machine is far away from being in CPU starvation.
                                I am planning to reduce the DOP as you suggested .

                                Metalink Note:280939.1 said:
                                "Consider the use of different number for the DOP on your tables.
                                On large tables and their indexes use high degree like #CPU.
                                For smaller tables use DOP (#CPU)/2 as start value. "
                                Question 1:
                                "On large tables"--> Does Metalink mean to a large table by its size (GB)
                                or by number of rows ?

                                Question 2:
                                I checked how many parallel operations had been downgraded and found that
                                less than 4% had been downgraded. Do you think that i still have to consider
                                reducing the DOP ?

                                Thanks Again.

                                SQL> select name , value
                                from v$sysstat
                                where name like '%arallel%';

                                NAME VALUE
                                ------------------------------------------------- ----------
                                DBWR parallel query checkpoint buffers written 1356270
                                queries parallelized 3743
                                DML statements parallelized 0
                                DDL statements parallelized 6
                                DFO trees parallelized 3754
                                Parallel operations not downgraded 3654
                                Parallel operations downgraded to serial 22
                                Parallel operations downgraded 75 to 99 pct 16
                                Parallel operations downgraded 50 to 75 pct 37
                                Parallel operations downgraded 25 to 50 pct 35
                                Parallel operations downgraded 1 to 25 pct 12
                                • 13. Re: PX Deq Credit: send blkd At AWR "Top 5 Timed Events"
                                  Jonathan Lewis
                                  >
                                  Metalink Note:280939.1 said:
                                  "Consider the use of different number for the DOP on your tables.
                                  On large tables and their indexes use high degree like #CPU.
                                  For smaller tables use DOP (#CPU)/2 as start value.

                                  Question 1:
                                  "On large tables"--> Does Metalink mean to a large
                                  table by its size (GB) or by number of rows ?
                                  That's one of those vague things that people say without thinking that it
                                  could have different meanings. Most people assume that a table that is
                                  large in Gb is also large in number of rows.

                                  As far as PQ is concerned I think that large numbers of rows may be more significant than large size, because (a) in multi-layer queries you pass rows around and (b) although the initial rows may be big you might not need all the columns to run the query, so Gb become less relevant once the data scan is complete

                                  As a strategy for keeping DOP on the tables, by the way, it sounds quite
                                  good. The difficulty is in the fine-tuning.

                                  Question 2:
                                  I checked how many parallel operations had been
                                  downgraded and found that less than 4% had been
                                  downgraded. Do you think that i still have to consider
                                  reducing the DOP ?
                                  Having lots of slaves means you are less likely to get downgrades. But it's the number of slaves active for a single query that introduce the dequeue waits - so yes, I think you do need to worry about the DOP. (Counter-intuitively, the few downgraded queries may have been performing better than the ones running at full DOP).


                                  The difficulty is this - do you need to choose a strategy, or do you just need to fix a couple of queries.

                                  Strategy 1: set DOP to 1 on all tables and indexes, then hint all queries that you think need to run parallel, possibly identifying a few tables and indexes that could benefit from an explicit setting for DOP.

                                  Strategy 2: set DOP to #CPUs on all very large tables and their indexes and #CPUs/2 on the less large tables and their indexes. Check for any queries that perform very badly and either hint different degrees, or fine-tune the degree on a few tables.

                                  Strategy 3: leave parallelism at default, identify particularly badly performing queries and either put in hints for DOP, or use them to identify any tables that need specific settings for DOP.


                                  Starting from scratch, I would want to adopt strategy 1.

                                  Starting from where you are at present, I would spend a little time checking to see if I could get some clues from any extreme queries - i.e. following strategy 3; but if under a lot of time pressure and saw no improvement I would switch to strategy 2.


                                  Regards
                                  Jonathan Lewis
                                  http://jonathanlewis.wordpress.com
                                  http://www.jlcomp.demon.co.uk