Solution: Where did the downgrades come from ?

As Stefan already correctly ponted out, the downgrades did origin from parallel sessions that Toad issued. Under special circumstance, the invoking client process has long ceased to exist, but the database sessions stays. As a result, we came under increasing pressure as the number of these sessions grew slowly. There were fewer and fewer new px be allocated because the pool is not infinite.
As a measure, however, we have a profile is activated, that kills inactive session after a certain time.
The second reason was that the parameter parallel_adaptive_multi_user was set to true. This ensured that about a half of the parallel session pools could be used for parallel processing. The parameter was obviously absurd in a data warehouse. However, the local DBA’s insisted not to change the default. For the first time I was faced with the absurd idea that a default is something of a taboo. Instead,they set the parameter was parallel_threads_per_cpu triumphantly to the default of 2. However, the OS had reported all threads as CPUs, so now twice as many CPUs were reported as really existed, as twice as many threads.
As for the downgrades three meaningless values ​​in the parameters have happy neutralized each other. Why, however, the Resource Manager was still running, I honestly do not get. 😉

Advertisements

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