Re: interpreting statspack reports ( PX Deq )

I don't think we have any difference of opinion about how to deal with the PX wait events - however I think your apparent definition of "idle event" is potentially misleading, and I don't think the implication of classing the "PX Send Blkd" as an idle event is safe.

Consider your point:

    >the PQ events can indeed be regarded as idle events. That is, tuning the

    >instance will do nothing for PQ performance. And yes, it may be ...

Exactly the same point could be made about "db file scattered read" - 'Tuning the instance' will, in general, do nothing for tablescan performance,
but that doesn't mean that "db file scattered read" is an idle event.

(I'm always a little doubtful about the phrase 'tuning the instance' - if you increase the sort_area_size, for example, is this tuning the instance. Or should it be called "tuning the SQL" in the cases where 90% of the problems happen to have disappeared because SQL paths have changed in response to the changes in available resources ?).

I think the direction of the original posting was related to Statspack and PX events appearing in the "top 5" report. As with all system-level tools I am not a great enthusiast for Statspack; but in the context of the question, if you are going to use the report as a quick indicator then I would say you could call most of the PX events idle events (and insert them into the stats$idle (?) table), but the "PX Deq Send Blkd" event MAY be giving you a global warning about your use of Parallel Query and hinting at the existence of some relatively easy ways (which does not mean tweaking parameters) to make some bits of your system go faster .

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Ricky Sanchez wrote in message <3D0D39B2.41BB0F2_at_more.net>...


>Jonathan, et al-

>

>Just to clarify and reiterate my comments: with respect to the server

>the PQ events can indeed be regarded as idle events. That is, tuning the

>instance will do nothing for PQ performance. And yes, it may be

>symptomatic of a bad pq plan, but must be dealt with as a sql tuning

>exercise.

>

>Most often the root problem is imbalance in partitioning the query among

>the slaves. Better table stats and use of histograms would be the first

>thing I would check.

>

>- ricky

>

>

>Jonathan Lewis wrote:

>>

>> The "PX Deq Credit: send blkd" is not necessarily

>> an idle event, and can be indicative of a sub-optimal

>> parallel execution plan.

>>

>> The significance of "Send Blkd" is that the process

>> has some information to forward and it is being

>> blocked.  If this occurs in the "parallel to serial"

>> stage of a "QC (range)" transfer, then it is an

>> idle event, and the processes that are waiting

>> their turn will be ticking over on this event every

>> 2 seconds.  If it is in a "parallel to parallel" phase,

>> then each block is a time-wasting block, will

>> affect total throughput time, and probably incurs

>> the overhead of an o/s task switch.

>>

>>

>> Ricky Sanchez wrote in message <3D09555E.2303AF3C_at_more.net>...

>> >Indeed these are parallel query events. With respect to the database

>> >they can be ignored as "idle events". If parallel query is a performance

>> >issue for you, approach it as a sql tuning exercise instead of a

>> >database tuning issue.

>> >