Dec/090
Oracle Performance Tuning Tactics Part 2: Using Indexes
To recap, the “weapons” available in your “arsenal” for performance tuning are as follows:- Full table scans ; Indexes; Joins; Views; De-normalization; Oracle Stored Procedures; Sub-queries.
2. Indexes
There are several types of indexes available with Oracle databases:
* normal/B-Tree (balanced/ binary tree) indexes;
* bitmap indexes (which literally use a map of bits to represent the whole index and are
* function-based indexes (which are based on the value of an expression instead of the value of a column);
* partitioned indexes (tables and indexes may be partitioned separately)
* domain indexes (application-specific indexes created on complex data types such as spatial data).
This article only discusses bitmap and normal (b-tree) indexes.
2.1 B-Tree Indexes
B-Tree Indexes are most appropriate when retrieving a small amount of data from a very large table. The bigger the table and the lower the number of rows that you want to retrieve then the more effective the index.
If you think of the printed material in the real world, this makes perfect sense, you wouldn’t bother to create an index for a short article, but you would certainly want one for a lengthy reference book. The reasons are analogous too. When reading a short article it would take longer to find the areas of interest using the index then it would to read the whole article. It’s the same when you’re querying the database – for a small table with Oracle’s multi-block read feature of Oracle, the whole table would be read in one go, so by going to the index first, the database would have to read more data overall.
Indexes may improve read access to data and update operations (including insert and delete) using sub-queries. However update performance will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly, thereby increasing the amount of i/o that has to performed.
Therefore having many indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that adding more indexes would degrade rather than improve performance. You also need to ensure that the column(s) you are proposing to index is/are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.
Another factor to consider with b-tree indexes in Oracle databases, is how specific they are – their cardinality. The ideal b-tree index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the data, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.
Dec/090
Oracle Performance Tuning Part 1: Using Full Table Scans
It is a common misconception that all SQL queries on all tables in Oracle databases should be index driven. In fact, using full-table scans can improve performance in two scenarios: when querying very small tables and when querying very large tables.
The Effect of Full Table Scans When Querying Small Tables
Let’s suppose your using your Oracle database to run in-house designed and built HR application. Consider a reference table such as a list of department ids and the associated department names. Even a large company is likely to have only a few departments – HR, Sales, Marketing, Finance, IT, so the table is going to be quite small.
Now let’s suppose the table has just 2 columns – department id and department name – with an index on the department id. To find the department name for a given id, we would have to read the index and then read the table, but because the table is so small and because Oracle reads multiple database blocks in one read operation the whole table is scanned in just one read, so however efficient the index, by using it we will be performing unnecessary i/o.
In this case, therefore, a full table scan is faster than an index scan and table lookup. The exception to this of course is when the table has been created as an index-only table (available since Oracle 
which means that the whole table is stored in a B-tree structure (although you may have pointers to overflow areas).
The Effect of Full Table Scans When Querying Very Large Tables
Let’s look at using this technique for querying very large tables in your Oracle database. Surely they should use an index? Otherwise you might have to read thousands of blocks. It is correct to say that a full table scan of a very large table could read many thousands of data blocks, but as we shall see it may be better to do this than to perform an index scan and table lookup.
The situation when the a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data in the table and it may perform better even when you are retrieving as little as 1% of the table data. Of course if you only want to retrieve one row in the table, then you would want to use an index.
Index Scan And Table Lookup Vs. Full Table Scan For Very Large Tables in Oracle Databases
Let’s look at the 2 scenarios then – retrieving 10% of the table data by index scan and table lookup vs. full table scan.
To make the maths easy, assume our table has 10,000,000 rows with 10 data rows per block and 100 index entries per block. Therefore to read 10% of the table via an index scan and table lookup, we would have to read 10,000 (1,000,000/100) index blocks plus 100,000 (1,000,000/10) data blocks. That’s 110,000 blocks in total.
However this assumes that the data is stored in order which means that we only retrieve the blocks of data that we want. If the data is not sorted then the worst case is that we would have to read 1 block for each row of data i.e. 1,000,000 blocks, which would would give us a worst case total of 1,010,000 blocks.
For a full table scan the maths is easy: (10,000,000 rows)/(10 rows per block) = 1,000,000 blocks. This is less than the worst case scenario for an indexed read, but more than the best case scenario for an indexed read. This would seem to suggest that if you sort your data before loading, an indexed read would be faster than a full-table scan.
Whilst it is true that pre-sorting the data of very large table will improve performance , it is not necessarily correct that the read via the index will be better overall. We also need to take into account what happens to the blocks stored in the buffer cache of the Oracle SGA and the impact this will have on other users of the database.
Let’s look at the effect on the buffer cache of reading many data blocks via an index. As we know, data and index blocks are stored in the buffer cache by Oracle for reuse by other queries by being marked as least recently used when we do an indexed read. However, those data blocks read by a full table scan are quickly aged out of the buffer cache, because they are not marked as least recently used.
What this means is that the large number of blocks (1,010,000) of index and table data read via the indexed read of our table will be saved in the SGA flushing practically all other data from it – which will obviously have an effect on other users.
Conversely, when a full table scan is performed only the last blocks read are held in the SGA (the actual number is determined by the multi-block read count) so the impact on other users of the database would be minimal.
Summary
To decide whether or not a full-table scan would be better than an indexed read, for a large table you need to consider what proportion of the table the query will retrieve from your Oracle database and consider the likely effect of that on other users. The denser the data, the more efficient a full table scan is for very large tables, but generally if you’re reading more than 1-10% of a very large table, a full table scan would be more efficient than an index scan and table lookup.
For small tables you will get much better performance from your Oracle database by caching the table in its entirety (so that it is always in memory), or by using an index-organised table then you will by relying on indexes. Having said that, every table should have a primary key index to guarantee uniqueness, but you don’t have to use it.
Dec/090
Oracle Performance Tuning Overview
There isn’t the time or space to provide an exhaustive study – whole books have been written about Oracle performance tuning. Instead we’ll just consider a few of the most important tactics and there’ll be suggestions for further reading, so you can expand your knowledge of this important subject.
One point that needs to be made clear is that performance tuning is not an exact science – you can’t always predict that a certain tactic or technique will improve performance. Every change has to be tested, because whilst it may improve performance in one area, it may also degrade performance in another area. Adding an index is a classic example of this – it may improve query performance but the performance of inserts and deletes become slower because the index entries also have to be updated.
The other thing to remember about performance tuning is that you need to decide on your goals before you start – do you need a 10% decrease in response time for queries ? Is adding new records or updating existing records 20% slower than targets ?
You also need to be aware that improving response time is not the only aim that you might have – you might want to reduce memory usage instead – which might mean having to redesign or rewrite some stored procedures. All these issues need to be considered before you start looking at performance.
Let’s start with a list of the topics that we’ll cover:
Use of full-table scans
How and when to use indexes.
How you can optimise joins.
How to use views to get a high performance database.
Why your database should NOT be normalised.
How to use stored procedures to sky-rocket Oracle performance.
How to use sub queries to boost Oracle performance
Jun/090
Oracle Performance Tuning Tactics Part 1
Use Full Table Scans To Boost The Performance Of Your Oracle Database
Before we start, let me make it clear that performance tuning is not always an exact science – by which I mean that you can’t always predict that a certain tactic or technique will improve performance. Every change has to be tested, because whilst it may improve performance in one area, it may also degrade performance in another area. Adding an index is a classic example of this – it may improve query performance but the performance of inserts and deletes become slower because the index entries also have to be updated.
The other thing to remember about performance tuning is that you need to decide on your goals before you start – do you need a 10% decrease in response time for queries ? Is adding new records or updating existing records 20% slower than targets ?
You also need to be aware that improving response time is not the only aim that you might have – you might want to reduce memory usage instead – which might mean having to redesign or rewrite some stored procedures. All these issues need to be considered when you you start looking at performance.
I’ll start by listing the “weapons” you have available in your “arsenal” which are as follows:
1. Full table scans
2. Indexes
3. Joins
4. Views
5. De-normalization
6. Stored Procedures
7. Sub-queries
1. Full table scans
This might seem counter-intuitive, surely all queries on a table should be index driven ? Actually, no, they shouldn’t. If the table is very large or very small using an index may increase the amount of i/o that is required to access the data and as i/o is the slowest operation, this will cause a degradation in performance.
1.1 The Effect On Small Tables
Consider a very small (reference) table such as a list of department ids and the associated department names in a HR application. Even a large company is likely to have only a few departments – hr, sales, marketing, finance, IT, so the table is going to be quite small. Now let’s suppose the table has just 2 columns – department id and department name – with an index on the department id. To find the department name for a given id, we would have to read the index and then read the table, but because the table is so small and because Oracle reads multiple database blocks in one read operation the whole table is scanned in just one read, so however efficient the index, by using it we will be performing unnecessary i/o.
In this case, therefore, a full table scan is faster than an index scan and table lookup. The exception to this of course is when the table has been created as an index-only table (available since Oracle Cool which means that the whole table is stored in a B-tree structure (although you may have pointers to overflow areas).
1.2 The Effect On Very Large Tables
Let’s look at using this technique for very large tables now. Surely they should use an index ? Otherwise you might have to read thousands and thousands of blocks. It is correct to say that a full table scan of a very large table could read many thousands of data blocks, but as we shall see it may be better to do this than to perform an index scan and table lookup.
The situation when the a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data in the table and it may perform better even when you are retrieving as little as 1% of the table data. Of course if you only want to retrieve one row in the table, then you would want to use an index.
1.2.1 Index Scan And Table Lookup Vs. Full Table Scan For Very Large Tables
Let’s look at the 2 scenarios then – retrieving 10% of the table data by index scan and table lookup vs. full table scan.
To make the maths easy, assume our table has 10,000,000 rows with 10 data rows per block and 100 index entries per block. Therefore to read 10% of the table via an index scan and table lookup, we would have to read 10,000 (1,000,000/100) index blocks plus 100,000 (1,000,000/10) data blocks. That’s 110,000 blocks in total.
However this assumes that the data is stored in order which means that we only retrieve the blocks of data that we want. If the data is not sorted in the table then the worst case is that we would have to read 1 block for each row of data i.e. 1,000,000 blocks, which would would give us a worst case total of 1,010,000 blocks.
For a full table scan the maths is easy: (10,000,000 rows)/(10 rows per block) = 1,000,000 blocks. This is less than the worst case scenario for an indexed read, but more than the best case scenario for an indexed read. This would seem to suggest that if you sort your data before loading, an indexed read would be faster than a full-table scan.
Whilst it is true that pre-sorting the data of very large table will improve performance , it is not necessarily correct that the read via the index will be better overall. We also need to take into account what happens to the blocks stored in the buffer cache of the Oracle SGA and the impact this will have on other users of the database.
Let’s look at the effect on the buffer cache of reading many data blocks via an index. As we know, data and index blocks are stored in the buffer cache for reuse by other queries by being marked as least recently used when we do an indexed read. However, those data blocks read by a full table scan are quickly aged out of the buffer cache, because they are not marked as least recently used.
What this means is that the large number of blocks (1,010,000) of index and table data read via the indexed read of our table will be saved in the SGA flushing practically all other data from it – which will obviously have an effect on other users.
Conversely, when a full table scan is performed only the last blocks read are held in the SGA (the actual number is determined by the multi-block read count) so the impact on other users of the database would be minimal.
1.3 Summary
In conclusion, therefore, to decide whether or not a full-table scan would be better than an indexed read, for a large table you need to consider what proportion of the table the query will retrieve from your Oracle database and consider the likely effect of that on other users. The denser the data, the more efficient a full table scan is for very large tables, but generally if you’re reading more than 1-10% of a very large table, a full table scan would be more efficient than an index scan and table lookup.
For very small tables you will get much better performance from your Oracle database by caching the table in its entirety (so that it is always in memory), or by using an index-organised table then you will by relying on indexes. Having said that, every table should have a primary key index to guarantee uniqueness, but you don’t have to use it.