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
I know it's not the real focus of your article but you've always got parallel_adaptive_multi_user for an element of "control".
ReplyDeleteResource Manager can control the degree of parallel for groups of users - see http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_dw_best_practies_11g11_2008_09.pdf
ReplyDeleteI hate parallel_adaptive_multi_user for user queries - users like to expect that a certain query takes x seconds (whatever the x is!) when it might vary between 0.01*x and 1000X depending on who gets the resource first then that is the recipe for broken reporting SLAs and angry users
Parallel does not scale.
ReplyDeleteWha?
I heard Tom Kyte describe this and once I listened, it all made sense. You pretty much described it.
If you are "controlled" and you want to make better use of your resources, then in batch situations it makes much more sense than in end user uncontrolled area where people expect their reports to run the same whether they are the only ones running reports or 100 users are running reports.
Also to take advantage of resource controls, you need to differentiate between those classes of users so if you use a specific user(s) account for the connections then no problem.
HINT, in OBIEE for the Physical table, there is a hint field. You can put parallel hints in there, but I believe you will need to reference the table name completed or use a variable to plug the alias OBIEE gives it automatically, which I am not sure how to do just yet. Would need to dig into the docs/metalink/blogosphere to find out.
Also, with OBIEE, the end users don't write the queries, BI does.
ReplyDeleteYou may need to tell BI in the Logical Layer the layout, what the facts are, dimension tables etc, so that it writes better physical SQL. That can be difficult sometimes with these automated tools, but perhaps having users join in the correct manner and then understanding Physical DB concepts like constraints and indexing (even different kinds of indexing) and the pros and cons with each approach would make them somewhat better IMO.
This comment has been removed by the author.
ReplyDeleteParallel processing is probably one of the most mis-understood & abused features of Oracle.
ReplyDeleteI'm pretty sure the great one (a.k.a. tommy boy) said:
Parallel != fast
Oracle is an incredible system for multiuser (thread/process) processing. It does an incredible job of managing this. Parallel processing, left in the hands of the naive, says give me this huge multicore system and make it my PC for the next few moments. Abused, this can crush the system.
Suggest the following:
a. do whatever you can to not allow users to run parallel queries. They can wait for their one little thread to complete.
b. try using materialized views and query re-write to provide the reporting performance your users desire.
c. Question your use of partitioning. Strongly.