Tuning the database usually has very little to no impact on performance.
The impact of choosing a RAID-5 configuration on performance is much bigger than anything which can be tuned in Oracle.
Also a wild guess is your configuration has only 1 disk, so it will be a bottleneck anyway.
Then again apparently you didn't tune your statement, and also my experience is you should avoid joining more than 10 tables.
Joining 30 tables is sheer madness.
Senior Oracle DBA
You're joining 30 LARGE tables on a 9i on top of a RIAD 5 and you think there is something wrong with oracle when it does lots of I/O for your query? Why ?
Why are you joining 30 tables? You don't need to normalize every single thing you know.
It will be a nightmare figuring out optimized join methods and order with 30 tables.
Joining that many tables is not exceptional. I'm working on the EBS view po_negotiated_sources_v right now, which joins about 20 tables. There are many views with more.
Generally speaking, I view excessive disc I/O not as a problem, but as a symptom of a problem. The solution is not to tune the I/O, but to remove the need for I/O. So you need to follow the usual SQL tuning cycle, beginning with gathering information about the execution plan, the tables, the indexes, and the object and system statistics.
Oracle Certified Master DBA
Thanks for the feedback all much appreciated.
I will investigate tuning the sql query and let you know.
If joining 30 tables is madness how else would i query 30 tables (just looking for pointers)? At the end of the day the data is spread across 30 identical tables and i need to be able to query it on demand.
For example before the union i was actually looping through an array of tables running the same query and appending the data. This was slower than the union.
I had already gone through the indexes and set them up, this definitely helped.
>At the end of the day the data is spread across 30 identical tables
It appears this application was designed by an amateur who did not normalize data to Third Normal Form.
Do NOT blame the poor performance on Oracle when the root cause is flawed and fatal design.
Most likely there should only be a single table with one additional column to differentiate the 30 different types of classes or records.
This is the equivalent to tying you left wrist to your right ankle & then complaining that you can not run very fast.
>I'm running an Oracle 9i Standard Database on Windows Server 2003.
Why are you willing to upgrade the OS & hardware & totally unwilling to upgrade the Oracle DB?
This is NOT a supported combination of Oracle version & OS!
Sorry for the super late reply
I've given Alvaro the correct answer.
It was indeed the correct path to take, i spent a while going of the explain plans and optimising the queries and setting up indexing. This made a HUGE difference.
One query went from 6 minutes to < 1 minute. So i can't complain with the results.
Thanks for the help.