I ask you if this is appropriate, instead offering up using whatever tool accessing the data to provide the PARALLEL hint.
The DOP is set pretty low, given our current system.
But it's still set and can't be easily turned off.
I'm all for PARALLEL, but it's been beaten into my head that it should be used, specifically for batch operations. In other words, transforming or loading data.
With the DOP set at the table or index level, it is not (necessarily, see resource limits below) controllable. If you have 1 or 2 users issuing SELECT statements against the table, it's not a big deal. Let's say the DOP is set to 8. 8 processes are spun off for each user. That's 16 processes now running that SELECT statement. Now let's say you have 1000 users. You probably won't make it to 8000 processes...your machine will probably keel over and die...or worse, just sit there forever.
But we have to set DOP at the table/index level...our users don't know how to write SQL.
Fair enough...teach them how to write it.
That takes too much time.
How do you ever expect them to learn? It might be a good short term solution, but is it really a good long term solution? Teaching your users how to write better SQL would be in everyone's best interest.
- Initially, you'll be swamped with "How do I?" type questions.
- Then the questions will only trickle in.
- You'll have much more savy business users who can now probably articulate their needs much better which will lead to
- Better design documents
- Better requirements
- You can finally begin to push off more of this "reporting" type functionality out to the business (where it should be in my opinion).
OK, that might be a bit of a fantasy.
What about setting up resource profiles for the users?
I've never used them, but I was reading up on them tonight for this post.
What can you control with this feature?
- Limiting the User Session Level
- Limiting Database Call Levels
- Limiting CPU Time
- Limiting Logical Reads
- Limiting Other Resources
- You can limit the number of concurrent sessions for each user
- You can limit the idle time for a session.
- You can limit the elapsed connect time for each session.
- You can limit the amount of private System Global Area (SGA) space (used for private SQL areas) for a session