Where did the downgrades come from?

In a data warehousewe we faced a increasing number of downgrades of parallelism. It seemed to be getting worse by the day. Indeed, it turned out that the number of free available parallel processes showed a constant decrease. A closer examination of PX used showed that many had Toad as source.
How was it possible that the number of parallel sessions that originated from Toad grew constantly?
On the other hand, there was still a comfortable number of PX is available. How was it possible that we were still seeing downgrades? What parameters should we check out?

Advertisements

4 comments

  1. Hi Lothar,
    i try to find your hidden easter eggs :-))

    > How was it possible that the number of parallel sessions that originated from Toad grew constantly?

    I haver never used TOAD, but i can think of several reasons like:
    – More (implicit) queries on global views in RAC environments by performance tools in TOAD
    – Auto DOP for developer queries on increasing (size) database objects

    > How was it possible that we were still seeing downgrades? What parameters should we check out?

    I gonna omit the common suspects like parameter parallel_adaptive_multi_user or parallel_degree_policy and try to think around the corner as this an easter egg hunt ;-))

    – AUTO DOP (PARALLEL_MIN_TIME_THRESHOLD) is enabled and the new cost is time model was accidentally enabled (http://tinyurl.com/nxdksp9)
    – Database resource manager was accidentally introduced or adjusted for a specific group of people (http://tinyurl.com/oxcmyp2)
    – User profiles were accidentally adjusted (sessions_per_user)

    Best Regards
    Stefan

    P.S.: Happy Easter!

    Like

    1. Ah sorry, some typo.

      The sentence “AUTO DOP (PARALLEL_MIN_TIME_THRESHOLD) is enabled and the new cost is time model was accidentally enabled” should be “AUTO DOP (PARALLEL_MIN_TIME_THRESHOLD) is enabled and the new cost is time model was accidentally adjusted (e.g. wrong values)”.

      Liked by 1 person

      1. Hi Lothar,
        ah ok, thanks for the hint. I guess i have found the easter egg ;-))

        > How was it possible that the number of parallel sessions that originated from Toad grew constantly?

        It is caused by specific cursor handling (and PX) of TOAD and fetching data in grid (or schema browser). The PX servers are not released until all data is fetched (array size) or the cursor is closed. However the data is only fetched partially (array by array) by scrolling in grid view.

        Regards
        Stefan

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s