ORACLENERD: PARALLEL Rant?

PARALLEL Rant?

Sunday, February 14, 2010

Let's say you have DEGREE set at the table and index level.

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
1. Better design documents
2. 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

What's the point of all this? I'm not too sure. It's a rant I guess. I just got done reading Dom Brooks recent post and reminded me of this conversation...which I have all too often.

Labels: design, development, oradb, parallel, random

¶ 2/14/2010 10:12:00 PM

Comments:

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".

# posted by

DomBrooks : February 15, 2010 at 4:44 AM

Resource 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

I 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

# posted by

Pete Scott : February 15, 2010 at 6:18 AM

Parallel does not scale.

Wha?

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.

# posted by

Tom : February 15, 2010 at 1:08 PM

Also, with OBIEE, the end users don't write the queries, BI does.

You 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.

# posted by

Tom : February 15, 2010 at 1:11 PM

This comment has been removed by the author.

# posted by

John T : February 15, 2010 at 4:06 PM

Parallel processing is probably one of the most mis-understood & abused features of Oracle.

I'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.

# posted by

John T : February 15, 2010 at 4:15 PM