Boost You Career With Our Online Oracle Training

Introduction

This is a special online Oracle training course dedicated to showing you how to boost the performance of your Oracle databases and thereby give your career a boost.

Performance tuning is most often a tactical process - ie. looking at a specific poorly performing module or query and improving that module or query in isolation.

This is fine as far as it goes, but a lot of the time systems would benefit from a strategic review and optimisation and this online Oracle training course is going to show you how to do this.

Strategic Performance Review

Let's answer the 2nd question first.

A strategic review should be undertaken when there are system wide performance issues affecting many modules/sub systems.

For example a data warehouse system taking several hours to produce management reports or an oltp system taking several minutes to respond. In cases like these, it is obvious that there is something not quite right with the system as a whole.

Having answered the when, we can devote the rest of this online Oracle training course to answering the what.

In a sense a strategic performance review is a return to the system design stage - you are looking at the system from a high level - so you need to consider system wide issues such as

• tablespace layout;
• use of stored procedures;
• use of views;
• use of database triggers;
• i/o load balancing;
• use of indexes;
• archiving;
• and even database structure - normalising or denormalising tables.

For a datawarehouse or decision support system you may need to build more aggregate tables (or take advantage of materialised views which were introduced in Oracle8i), to enable the high level information to be accessed quickly. If carried out correctly, these changes can have a significant effect system wide.

Those are a few general issues you can consider, but you can also go deep into every module and consider specific issues with every query in each module, such as:

• unplanned full-table scans;
• use of the wrong index;
• not using an index when there is an appropriate index;
• use of an index when it is not appropriate;
• incomplete join conditions;
• not using a view when an appropriate one exists;
• using views based on the join of other complex views;
• use of inefficient stored procedures;
• not using stored procedures at all;
• use of a stored procedure when the operation could be performed much faster in sql
• and so on

We're not going to go into the details of these issues or what to do about them in this short online Oracle training course, the idea here is just to look at the "big picture".

High-Level Issues

As I mentioned earlier, a system needs an overhaul when it is not performing adequately as a whole. This essential information will either be painfully obvious, or it will be imparted to you by the users who are working with much larger volumes of data than exist on the development database.

When this happens, you then need to collect system wide information about the database - the more information the better.

You can write your own queries to gather statistics from the database, but generally your time would be better spent on analysing and resolving the problems rather than writing queries to extract statistics from the database when there are many tools available such as TOAD or Oracle Enterprise Manager that will do the job for you at the press of a button.

This information would include statistics such as the parse/execute ratio, hit ratio, database space usage, most expensive queries (in terms of i/o and cpu) data file to tablespace mapping, object to tablespace mapping and so on.

All of this can be used to identify where the problems are and what to do about them.

For example a high parse/execute ratio would indicate that cursors are not being used efficiently or that some queries should be using a view or a stored procedure.

Looking at the most expensive queries enables you to home in on those and tune them or replace them with a query on cached, aggregated or denormalised data.

Looking at the i/o statistics can tell you if you need to spread the load across several disks or if that's not an option, that you need to consider ways of reducing i/o by using stored procedures to encapsulate application logic in the database, or by caching data in the front end.

Each of these factors needs to be considered seperatley and in conjunction with all the others, but by optimising just one or two of these areas you can improve performance disportionately, leaving time to concentrate on the design of the next system or enhancements to the existing one. In fact i/o is the most expensive operation - it costs both elapsed time and cpu time, so if that can be reduced you get a double benefit.

There's not enough space in this online Oracle training course to go into more details on these factors but if performance is a serious issue invest in a copy of Oracle Performance Tuning by Mark Gurry & Peter Corrigan

Strategies For Individual Modules

As this is part of a strategic review you won't want to spend the time and effort to optimise every single query in every single module. Instead you'll want to use your technical knowledge about the system to ensure that in technical terms the modules are using the right strategy.

To do this you'll want to start with the worst performing modules, or, if you don't know them, just pick a few at random to a do a quality audit on them and check the "big picture" ie. that the modules are using views/stored procedures where appropriate, using the right indexes etc.

If the quality audit of the front-end and the strategic review don't provide the performance improvements needed then you will have to adopt a tactical approach and look at individual queries in detail in each module, but that's outside the scope of this online Oracle training course.

Looking For More High Quality Online Oracle Training ?

• SQL performance tuning tips
• Beginner's Sql Tutorial