Limiting number of parallel query slaves

[Horace]

Hi Guys,

Is it possible to limit the amount of parallel query slaves spawned per user?

I have parallel_max_servers set to 64, but the first user can sometimes spawn all 64!! which means that everyone else runs in serial mode.

I've got memory usage pinned by pga_aggregate_target on a windows box, so I don't run into 'out of memory process' issues.

Thanks.

[jmodic]

I don't think you can limit this per-session.

[JChiappa]

afaik no, you can´t set a limit for each user, what tou can do is :

- set a (small) degree for each table involved

or

- use PARALLEL_ADAPTIVE_MULTI_USER, with this the first user will NOT be able to use all the slaves, some will be reserved, and in the future, with new users trying to do parallel things, more slaves will be "removed" from the first user. But this algorithm is CLOSED, you can´t change it, you can´t specify what portion os slaves to each user...

Regards,

Chiappa

[Bore]

You can use Resource manager to limit the number of px slaves per consumer group.
You can modify that to a case where the consumer group has one user only and hence limit px slaves per user

Regards
Boris

[tamilselvan]

This link may help you:
http://download-west.oracle.com/docs...2/d_resmgr.htm

Tamil

[Horace]

Thanks Guys,

Gonna look at PARALLEL_ADAPTIVE_MULTI_USER