Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Database Performance Tuning
How To Double, Triple Or Even Quadruple Your Oracle Database Performance
If the performance of your Oracle database is letting you down, we can give you all the help you need to improve it. A few simple steps and perseverance are all it takes to transform your database from a slow coach to an express train.What's the one thing that would produce the biggest improvement in
performance of your Oracle database?
The biggest gain in performance for the least amount of cost is always going to be achieved by tuning your SQL, no matter how big or small or sophisticated or simple your system is. Poorly tuned queries will perform vast amounts of i/o as they read data from the disk and write to it during sort operations. As disk i/o is one of the slowest activities and one of the most processor intensive activities, if you reduce this the processor will spend less time just juggling resources, your applications will spend less time waiting for resources and everything will run much faster.Here are a few tips on tuning your SQL:
1. De-normalise frequently joined tables. Normalisation is an analysis technique, not a design technique. 2. Make sure indexes are being used where appropriate. Functions on an indexed column (except for Oracle8i and above function-based indexes) will prevent the use of an index. eg. select <my_cols> from <my_table> where max(indexed_col)=<some_val> will require a full table scan of the table being queried. 3. If you have an OLTP system, beware of having too many indexes. Whenever records are inserted or deleted the corresponding index entries have to be created or removed, generating twice as much work for the database. 4. Make sure you’re using the right type of indexes. Bitmap indexes work well in datawarehouses but not in OLTP systems whereas B-tree indexes are the opposite - they work well in OLTP systems but not in datawarehouses We hope you found these tips useful, but if you lack either the time or the expertise to tune your databases why not let us do it for you? See our guaranteed Oracle performance tuning service. We also have a whole series of articles on Oracle performance tuning . For other help with Oracle databases, see our Oracle tips and tricks section. --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky- rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy. View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
For Better,
Faster, Smarter
Oracle Consulting
and Training
Oracle Database Performance Tuning
How To Double, Triple Or Even Quadruple Your Oracle
Database Performance
If the performance of your Oracle database is letting you down, we can give you all the help you need to improve it. A few simple steps and perseverance are all it takes to transform your database from a slow coach to an express train.What's the one thing that would produce the biggest
improvement in
performance of
your Oracle
database?
The biggest gain in performance for the least amount of cost is always going to be achieved by tuning your SQL, no matter how big or small or sophisticated or simple your system is. Poorly tuned queries will perform vast amounts of i/o as they read data from the disk and write to it during sort operations. As disk i/o is one of the slowest activities and one of the most processor intensive activities, if you reduce this the processor will spend less time just juggling resources, your applications will spend less time waiting for resources and everything will run much faster.Here are a few tips on tuning your SQL:
1. De-normalise frequently joined tables. Normalisation is an analysis technique, not a design technique. 2. Make sure indexes are being used where appropriate. Functions on an indexed column (except for Oracle8i and above function-based indexes) will prevent the use of an index. eg. select <my_cols> from <my_table> where max(indexed_col)=<some_val> will require a full table scan of the table being queried. 3. If you have an OLTP system, beware of having too many indexes. Whenever records are inserted or deleted the corresponding index entries have to be created or removed, generating twice as much work for the database. 4. Make sure you’re using the right type of indexes. Bitmap indexes work well in datawarehouses but not in OLTP systems whereas B- tree indexes are the opposite - they work well in OLTP systems but not in datawarehouses We hope you found these tips useful, but if you lack either the time or the expertise to tune your databases why not let us do it for you? See our guaranteed Oracle performance tuning service. We also have a whole series of articles on Oracle performance tuning . For other help with Oracle databases, see our Oracle tips and tricks section. --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy. View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
