| Alter Table parallel complexity [message #207497] |
Tue, 05 December 2006 16:01  |
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 #207664 is a reply to message #207580] |
Wed, 06 December 2006 09:04  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
