Home » RDBMS Server » Performance Tuning » Alter Table parallel complexity
Alter Table parallel complexity [message #207497] Tue, 05 December 2006 16:01 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
Dear All,

Here is my problem: we have a complex query which access around 4 tables for the reporting. The query performance is extremely slow. However if we alter the table to parallel mode, the query goes through a full tablescan of a large table and spit the results out in less than 15 minutes. However, it is not that simple: we have go like

alter table x101 parallel (degree 8 instances 2);

Remember, we are not running the RAC as we just have one instance. with instances 2, seems like Oracle does a much better job. I was told by Oracle Guru that, this may corrupt the data dictionary since we are feeding wrong info to oracle. Do you guys agree with that. Does instances 2 increases the degree of parallelization implicitly. Or how it could affect the other queries hitting this particular table.

Any help, advice, suggestions will be appreciated and remembered for a long time with love.

thanks.
Re: Alter Table parallel complexity [message #207580 is a reply to message #207497] Wed, 06 December 2006 02:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why don't you deal with the original problem - the query itself?
Re: Alter Table parallel complexity [message #207664 is a reply to message #207580] Wed, 06 December 2006 09:04 Go to previous message
rkl1
Messages: 97
Registered: June 2005
Member
The query is generated by OLAP tools by the analyst so we do have a very little influence on the query structure. However with out the parallel mode (instances 2), Oracle always picks some indexes which are rather detrimental to the query performance. Once the table altered to parallel mode, a full scan is engaged and we could get back the results in minutes and therefore it is a such a tempting thing to do. We can't drop the obnoxious indexes since they are helpful to run the other queries.

The big dilema is since we are running under the default single instance mode, is it ok to tell oracle that this particular table is under the multiple instance mode (which is not, since our configuration is NoT RAC). The fear is whether it could corrupt the data dictionary leading to the corruption of database.

Thanks.
Previous Topic: more precise timing with TKPROF
Next Topic: Doubt on Oracle Post-Wait Extension in Semaphore allocation
Goto Forum:
  


Current Time: Tue Dec 06 08:20:48 CST 2016

Total time taken to generate the page: 0.08801 seconds