Home » SQL & PL/SQL » SQL & PL/SQL » Performance ramifications Parallel hint.(merged 2) 10g
icon5.gif  Performance ramifications Parallel hint.(merged 2) 10g [message #412884] Mon, 13 July 2009 04:15 Go to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
Hello all,

I'm hoping someone can help me out here. What are the performance ramifications of using the parallel hint with 'default' with regards to database performance in general? Will any other users automatically be hindered because my hint causes the DB to reserve max. CPU time for me?

Assuming I'm processing 10mio rows, would it be wiser/fairer to split my processing into x statements each processing y amount of rows?

My DBA could give me no relevant answer and his simple 'don't use the hint' was an easy and unprofessional way out.

Any information would be greatly appreciated

M.
Re: Performance ramifications Parallel hint.(merged 2) 10g [message #412891 is a reply to message #412884] Mon, 13 July 2009 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Performance ramifications Parallel hint.(merged 2) 10g [message #412966 is a reply to message #412884] Mon, 13 July 2009 12:25 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
DEFAULT option will give you extremely unreliable execution times, as Oracle will take into account the current Load.

Specifying a fixed number of slaves is usually preferable as you can manage the work better.

If you DBA does his job right, and specifies the max parallel slaves correctly, then you should be ok using it.

If he is that fussy about it, ask him to create a "parallel execution" profile, and user to go with it, so he can manage the resource usage.

Re: Performance ramifications Parallel hint.(merged 2) 10g [message #413026 is a reply to message #412966] Tue, 14 July 2009 01:23 Go to previous messageGo to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
Hello and thank you to those who answered.

@coleing: This is exactly what I wanted to hear. Smile Everything I read on the subject tells me that although it is possible to bring a server to a stand-still using OPQ, there are many ways in which the DBA can prevent this from happening and still allow the use of such technology.

@Michel: Thanks for the tip Smile Guess I've got a bit of reading to do!
Re: Performance ramifications Parallel hint.(merged 2) 10g [message #413033 is a reply to message #413026] Tue, 14 July 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parallelism is not a matter of the developer but of the DBA.
Only DBA knows what is the hardware and OS and if they can support parallelism.
As a developer, NEVER put parallelism hint in your queries or applications unless the DBA are OK to do it. You have to discuss with them.

Regards
Michel
Re: Performance ramifications Parallel hint.(merged 2) 10g [message #413038 is a reply to message #413033] Tue, 14 July 2009 01:38 Go to previous messageGo to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
That is of course true! And I would never take it upon myself to ignore the warning. Nevertheless it is also the job of a DBA to specify the reason why. As I said in my first post 'don't use it!' is not a reason. So, as my DBA is unwilling to explain, I have to get the explanation from somewhere else.

Once more: It's not that I desperately want to use the hint, it's that I want to know why not Smile
Re: Performance ramifications Parallel hint.(merged 2) 10g [message #413047 is a reply to message #413038] Tue, 14 July 2009 02:45 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is also the job of a DBA to specify the reason why. As I said in my first post 'don't use it!' is not a reason.

And you're right.

Quote:
So, as my DBA is unwilling to explain,

Maybe he is not skilled enough to know what parallelism implies and so use the safe way for him: nothing should change, nothing I don't know should be used.

Maybe you can point him to the book I posted.

Regards
Michel
Previous Topic: Changing the value via procedure
Next Topic: how to update a column from table which it was took from input procedure
Goto Forum:
  


Current Time: Sun Dec 11 00:22:18 CST 2016

Total time taken to generate the page: 0.11654 seconds