Oracle10g database/table Tuning

Question

I am using Oracle Datbase 10g Standard Edition for my ASP.NET website. There i have one table which is going to populate with data on daily basis (around 10000 rows daily). The growth rate of data in that table is 100% i.e. every year the number of rows will increase previous plus 10000 rows.

This is main table for reports like graphical and crystal.

I know when the data increases the select query performance degrade. Here i would like your help to tune my Oracle database/ table so that the performance of select query always remain good?

I am not very good in oracle. Please help me..............

Answer 1

Simple really, make sure your indexes match up with your query. Check your explain plans, usual oracle stuff - Read around on the subject and you'll find lots of info!

It does depend on how your queries run. You could benefit hugely by partitioning by month if your queries only pull back a months worth of data. Then you'll only hit a 1/12th of the data you would have done otherwise. But if the queries are not constrained in that way then this may not help.

Finally make sure you're not reading all the data just to summarise it in the report. So if you are doing this consider creating an overnight job to create an aggregate table, and query that instead.

Oh; Also if insert speed is an issue you may want to keep number of indexes down - if that is the case you may need to have a separate reporting table to the one where you insert the live data, and then sync the data across nightly or something.

Answer 2

Actually there is no reason why the performance should degrade as the table increases. If your query uses indexes, to retrieve individual rows or focused sets (i.e. INDEX RANGE SCAN) then the performance should remain stable as the table grows. This partly depends on the profile of your data. Indexes with a low clustering factor will continue to perform but indexes with high clustering factor will degrade. Find out more.

Where you almost certainly will have a problem is running queries which don't use indexes or which use INDEX FAST FULL SCAN. Such queries obviously will degrade over time as the table grows. To deal with that scenario you can build materialized views which pre-aggregate the data.

The best general advice is

1. Read the Oracle online documentation. It is pretty comprehensive.
2. Start benchmarking your queries' performance now (using Statspack). That way you will be able to do trend analysis and spot when things start to go awry.

Answer 3

Generate extra test data and test if the performance indeed degrades. Maybe you worry for nothing:)