Home » RDBMS Server » Performance Tuning » Parallelism disabled in data warehouse (Oracle 9.2 on unix)
icon7.gif  Parallelism disabled in data warehouse [message #417342] Fri, 07 August 2009 07:32 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
Occasionally I would like to know what is normal.
I don't generally talk to other human beings about work stuff so all I have is this.

I work in a data warehouse, with plenty of operational processing as well.

Parallel queries are disabled at the database level. If parallel queries were enabled the system would come down due to a few processes taking all the processing power. There are reports and other processes that take 20 hours and let us say things are not well organized. Queries regularly fail due to lack of rollback space or temp space.

Do most data warehouses have parallelism? Is it normal or possible for only certain users to have parallel query permission?

thanks.
Re: Parallelism disabled in data warehouse [message #417344 is a reply to message #417342] Fri, 07 August 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Do most data warehouses have parallelism?

Yes.

Quote:
regularly fail due to lack of rollback space

This should not happen in DWH as you don't make updates (but out of the line bulk operations) in this kind of database.

Regards
Michel

[Updated on: Fri, 07 August 2009 07:42]

Report message to a moderator

Re: Parallelism disabled in data warehouse [message #417374 is a reply to message #417342] Fri, 07 August 2009 12:57 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
" Is it normal or possible for only certain users to have parallel query permission"


You can enable parallel query at table level, but if you do that, anyone can add it to their select query as hints and use it also.

However, there is something called parallel adaptive multi-user which basically means that the server wont get overloaded, the queries will just slow down the bigger the load.

if you request 10 parallel servers, and oracle wants to downgrade your request, it will, or even serialise it.
Re: Parallelism disabled in data warehouse [message #417447 is a reply to message #417374] Sat, 08 August 2009 21:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It isn't normal to have operational queries/transactions running on your data warehouse.

There are very good reasons why physically separating your OLTP system from your DW is a best practice.

Once you have separated them, parallelism is "normal", depending on the type of warehouse.

Ross Leishman
Previous Topic: Hard parse in RAC
Next Topic: Performance issue in Plsql
Goto Forum:
  


Current Time: Fri Dec 09 23:27:17 CST 2016

Total time taken to generate the page: 0.11258 seconds