Oracle Optimizer Plan Costing - Full Table Scans

Hopefully this is the start of a series on how the Oracle Cost Based Optimizer determines the cost of a particular execution plan for a SQL statement. Knowing how it arrives at the cost of each possible access path to a data set, we can better understand why a particular access path was chosen, and why an alternative access path had a higher cost. This information was kick started by A Look Under The Hood Of CBO: The 10053 Event by Wolfgang Breitling, and also information in Troubleshooting Oracle Performance by Christian Antognini, and refined by various tests I did.The Under the Hood paper is very good, and has a good description of how the Optimizer goes about costing different access paths to data sets. Unfortunately it seems to be based on Oracle 9i, and things have changed slightly in Oracle 10g. The principles are the same, but the trace information output by the optimizer has changed slightly.

For this post I will focus on how the optimizer works generally, and specifically how a Full Table Scan is costed. These will not be full, exhaustive explanations of the inner workings of the Oracle Cost Based Optimiser, but short and to the point descriptions of the key costings.

Plan Costing

The Optimizer itself costs all execution plans in terms of the number of disk I/Os involved, because these are generally the largest component of the execution plan cost. Other costs such as CPU costs are also included in the total plan cost, after being converted to an I/O count via a conversion value. When the lowest cost plan has been produced, this can be easily converted to an elapsed time value by simply multiplying the cost by an average I/O time value.

When producing an execution plan for a SQL query (queries are the most common and also the most complicated things that the Optimizer has to deal with), the Optimizer simply tries to produce all possible execution plans and cost each of them. From these it chooses the plan with the lowest cost. Clearly this could be quite an exhaustive way of doing things. So what the Optimizer actually does is to fully cost one execution plan, and then only partially cost the subsequent execution plans.

Each subsequent plan is costed as each step of the plan is determined by the Optimizer. If the partial cost of such a plan so far is greater than the current best plan, then it is abandoned. This early pruning avoids the optimizer wasting too much time on plans that are too costly. Likewise if a new plan is produced with a total cost less than the current best plan, then the new plan becomes the current best plan.

After considering all possible execution plans the optimizer will have the lowest cost execution plan for the SQL query.

The execution plans themselves are formed in an iterative fashion. First, for each table referenced in the SQL query the optimizer costs each potential access path to the data in that table. Indexes are considered if they can be used directly to implement a predicate - a filter condition within the SQL query restricting the rows returned. Then the optimizer considers each possible join combination between the tables in the SQL query, using each possible join method - Nested Loop, Sort Merge or Hash Join. These joins use some of the costs for accessing each of the tables determined eariler, combined according to how that join method works. Thus each possible join path (A to B to C, A to C to B, B to A to C, etc.) is costed in turn using each of the 3 possible join methods, to arrive at the lowest cost execution plan for the SQL query.

Full Table Scan

This is one access method to data in a table that is always available to the optimizer. Logically the cost is the number of disk I/Os required to read every data block in the table. The number of data blocks in a table is obtained from the BLOCKS column in USER_TABLES. It is as accurate as the last time you gathered statistics on that table. Oracle 10g has an automatic job that runs every night by default to update the statistics on tables that have changed significantly, to minimise the drift between actual table sizes and the statistics stored about them in the database.

An enhancement Oracle has is the ability to issue multi-block reads that read more than one block from disk in a single read request. This reduces the total number of disk I/Os needed. Thus the number of disk I/Os needed is reduced by the multi-block read count (MBRC), according to the formula:

• Blocks / MBRC

The Optimizer wants all costs to be in units of equal disk I/Os i.e. in units of single block reads. However, the time for a multi-block read is likely to be different – generally slightly longer given that more data is transferred. To convert a multi-block read cost into units of a single block read, the multi-block read cost is divided by the time for a multi-block read and multiplied by the time for a single block read. This adjustment means that the “cost” is now in units of cost of single block reads, as used by other access paths such as indexes.

Therefore the cost of a Full Table Scan is actually:

• (Blocks * MREADTIM) / (MBRC * SREADTIM)

MREADTIM and SREADTIM, as well as MBRC, are part of a set of statistics Oracle has about the relative performance of the computer system it is running on. These are termed the System Statistics, and are visible in sys.aux_stats$ or via the dbms_stats.get_system_stats procedure. Out of the box following installation, Oracle only has a minimal set of default values for some of these statistics - IOSEEKTIM, IOTFRSPEED and CPUSPEEDNW. All the other system statistics have no value. They can be set in a number of ways - the intended way being to get Oracle to measure these values from the system itself while it is executing some representative workload (these are termed Workload Statistics) - but they can be set manually if desired, or left unset.

Oracle uses those System Statistics that it has values for. For those with no value Oracle uses various substitution formulae to calculate values to use instead.

If there is a value for MBRC in the System Statistics then the Optimizer uses that value. Otherwise, it uses the value of the db_file_multiblock_read_count (DBFMBRC) initialization parameter.

If MREADTIM and SREADTIM do not have any values in the System Statistics, then the following substitutions are made:

• SREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE / IOTFRSPEED)
• MREADTIM = IOSEEKTIM + (MBRC * DB_BLOCK_SIZE / IOTFRSPEED)

This is probably the general case on most systems - no Workload System Statistics are present (MBRC, SREADTIM, MREADTIM and others) - and only default values for IOSEEKTIM (10 milliseconds) and IOTFRSPEED (4 KB / millisecond). For an 8 KB block size and DBFMBRC of 8 this gives values for SREADTIM of 12 ms and for MREADTIM of 26 ms, and the ratio of MREADTIM to SREADTIM of 2.167.

A simple test shows this to be the case - a SELECT on a table with no indexes. The table I used had 1252 blocks of 8 KB each, and DBFMBRC was set to 8. No System Statistics had been collected, so only the defaults exist for IOSEEKTIM and IOTFRSPEED. Using the substituted formulae for MREADTIM and SREADTIM the calculated cost should be:

• (1252 * 26) / (8 * 12) = 32552 / 96 = 339.08

The reported cost by Oracle was 341, which is very close. The difference is due to various factors, such as rounding within the calculation, the optimizer probably costing an extra read for the table header (+1), and the optimizer adding in a CPU cost for processing the data.

Using these formulae and the values of the System Statistics, we can calculate for ourselves the expected cost of a Full Table Scan of any table.

System Activity Data - What is the System up to?

I'm always amazed at how many people do not know about the SAR utility on UNIX and Linux systems. It is a great little utility for collecting a lot of data about what is going on on a system. No, it is not the best possible tool for all such information, but it comes as standard on most UNIX systems and will give you most of what you want for very little effort on your part.

The problem we often deal with is that when running an application and taking application level measurements we are not sure what is happening lower down on the system, under the covers as it were. When the application performance is slow it would be nice to know if the computer system itself was very heavily loaded too, or whether the cause of the slowness lies somewhere else. Such relatively simple data about activity and resource usage on the system can help us establish what class of problem we have - a saturated resource in the system itself, or a badly behaving application.

To know what the computer system is up to we need to know about all of the resources in it - CPU, Memory, and Disks. And we need several different measurements on each resource - number of requests, elapsed times, and queue lengths. We also need an accurate timestamp with each of these measurements, to know when it occurred, and the period of time it covered.

All of this can be relatively easily achieved with SAR - the System Activity Reporter. It will collect and record a wide range of measurements on system resources, and the time when they occurred.

Behind the scenes SAR uses SADC - the System Activity Data Collector. And in fact it is SADC we are more concerned with than SAR. SAR simply extracts and formats the data you want from an SADC data set. We can run SADC to collect all the data it can about the system, at the frequency we want it collected (measurement period) and for the duration we want (how long until). SADC has a very simple set of options to it:

• /usr/lib/sa/sadc t n file

where

• t is the time duration between measurements i.e. the frequency
• n is the number of measurements to make in total
• file is the name of a file to write these measurements to

To not have SADC tie up a terminal window, it is often run in the background with the '&' specifier at the end of the command line - control then returns to the terminal window. This can also be used in command files and scripts.

The file produced by SACD contains binary data, not text data, and needs SAR to read it and format it as readable text.

• sar [-data_option] -f file

If no data option is specified it defaults to CPU (-u). The common data options are:

• -u CPU
• -d Disk I/O
• -r Physical Memory
• -p Virtual Memory Paging

Remember that the SADC file contains all the data collected, so you can analyse any sub-set of this data any time you want to, at any later point in time. That is the benefit of collecting all of the possible measurements together at the same time and then saving them to a file.

You can obviously use SADC to collect data whenever you have performance problems. But it is far better to run SADC all the time, so that you already have the data to analyse after someone reports a problem. One way to do this is to put an entry into a "crontab" file for use by the "cron" utility, which runs jobs at regular intervals. Putting an entry similar to the following into "crontab" for the system administrator for midnight would run SADC for 24 hours collecting measurements every minute. If this is too frequent just modify the time and number values by the same ratio, so that the two multiplied together give 86400 seconds (24 hours).

• /usr/lib/sa/sadc 60 1440 /usr/adm/sa/`uname -n`_`date +%y%m%d`.sad &

This will create a file named after the system and the current date in a central directory. Make sure there is enough space in that location, or change it to somewhere else. You will need to do housekeeping once in a while to purge and delete old data sets. This could also be done via another "cron" to delete files older than one or two months.

I have found SADC and SAR invaluable in a number of circumstances where I have been able to show that it was the underlying hardware that had performance issues, and not the application itself. And I have used it in the opposite manner too, to show that a poorly performing application was not suffering from running out of system resources or their slow performance. On most UNIX systems the impact of running SADC is minimal, as the UNIX kernel is already collecting all of these measurements anyway. The overhead is simply of SADC reading them from memory and writing them to disk at the frequency requested. And generally this is a very low and negligible overhead, compared to that of the application software itself.

Java Data Types and Oracle (2)

As I said before, when using a Java application against an Oracle database, the java.util.Date data type gets mapped to a java.sql.Timestamp within the layers above JDBC (this application uses an abstraction layer on top of JDBC), and in turn this comes through to Oracle as being of type TIMESTAMP when used against a bind variable in a SQL statement. When used within a WHERE clause, rather than truncate the TIMESTAMP to a DATE by losing the fractions of a second part, Oracle does the opposite and converts each DATE value read from the database to a TIMESTAMP value by adding on a zero sub-second part. The result of this is that any index on such a DATE column is not used by the optimizer in the execution plan for the query, because of the data type conversion.

Oracle themselves acknowledge that this is an issue in their JDBC FAQ and state that in the Oracle 11g JDBC drivers the mapping has reverted back to java.sql.Timestamp and Oracle's DATE being equivalent rather than different. In the meantime, those of us in the real world have to deal with real applications using the Oracle 10g drivers which map java.sql.Timestamp to Oracle's TIMESTAMP data type instead.

As I described before, we came up with a short term fix of creating a different index on the table being queried, with the DATE column as the last column in the index. This did have a major performance benefit (15 hours of the last run down to 2.5 hours with the new index), but was still not the optimal solution. Oracle was still doing an INDEX SKIP SCAN rather than an INDEX UNIQUE SCAN, and so was reading many more index entries than it needed to i.e. more Buffer Gets than it needed to.

I put together a Java program using JDBC to test the effects of using different Java data types (Classes really) and different JDBC settings. The simplest fix is to use the "V8Compatible" property as mentioned in the Oracle FAQ linked to earlier. You create the Property, set the "V8Compatible" value to "true" and then make your normal JDBC connection to Oracle (I've removed all error handling code in the following):

// Create and set V8Compatible property
Properties oraProperties = new Properties () ; // NEW
oraProperties.setProperty("oracle.jdbc.V8Compatible", "true") ; // NEW

// Standard JDBC connection code
OracleDataSource ods = new OracleDataSource();
ods.setURL(connect_string);
ods.setUser(username);
ods.setPassword(password);
// Set properties of the connection
ods.setConnectionProperties (connectionProperties) ; // NEW

Connection dbcon = ods.getConnection () ;

With no other changes than the 3 NEW lines indicated, the application's java.util.Date value now came through as an Oracle DATE after going through the intermediate mapping layers, and the original index was now correctly used in a UNIQUE SCAN.

This is clearly the easiest and most direct solution, and does what we want - all Java Date / Time values now come through mapped as Oracle DATE values, rather than TIMESTAMP values. Although the "V8Compatible" property is deprecated in Oracle 11g, this will have no effect as the behaviour of the Oracle 11g JDBC drivers has changed to what we want, and it can ignore the unneeded property setting.

The other possible solution, which is being investigated by those who know more about the Java abstraction layer API being used by the application than I do, is to get this abstraction layer to wrap all java.util.Date objects within oracle.sql.DATE objects. Thus the JDBC connection is only ever given oracle.sql.DATE values, which are direct equivalents of DATE values in the database. So again, any index would be used directly, and no data type conversions would take place within the Oracle database server.

However, the details of this look like they would still involve mapping a java.util.Date object to a java.sql.Timestamp first in the abstraction layer, and then wrapping this within an oracle.sql.DATE object. This is because oracle.sql.DATE has a constructor that takes a java.sql.Timestamp argument, allowing easy conversion. But it does not have a constructor that takes a java.util.Date argument, or any superclass of it. java.sql.Timestamp is a subclass of java.util.Date.

So for now we have one guaranteed long term fix with the "V8Compatible" property, and another possible fix by changing the mapping within the abstraction layer. So now it is up to the people that own the Java application code to deliver their preferred change, and then we can remove the extra index that we created as a short term fix.

Java Data Types and Oracle Index Usage

I'm not a great fan of Java. I'm not saying I hate it or anything, just that I've never exactly fallen in love with it. While it offers some potential advantages as an Object Oriented Language over others, it also suffers from a lot of complexity in trying to get it to do anything useful that you want it to do. One way or another I keep running into issues with Java, of one form or another.

I've been working with a client who has a Java based application, which through various layers of Classes and Objects ends up issuing SQL statements against Oracle 10g through JDBC. Up to now the client has been happy with the application, in spite of some of the hoops they have had to jump through to get it to work. Finding data abstraction layers that work together and can be used in production systems has been an issue for them - Open Source legal liabilities etc. But one way or another they have managed to assemble a set of Java based components that they have integrated together into their application.

They have been live with a new module for 3 months now, and reported that elapsed times on the module were gradually getting longer and longer. It is a batch job that takes transactions posted into the system today, and updates various counts and totals in other data sets. Elapsed times had gone from an initial 2 hours to 10 hours only 2 months later, which was far too long.

One of the first weird things was that the client could not believe that their application ran so slow, because they had done lots of testing before going live with it. And their testing had used similar data volumes to their production environment. More on that later.

Looking at some AWR reports I was quickly able to establish that the elapsed time was down to one UPDATE statement. The client knew this too, and could not tell why the UPDATE was so slow. It was using bind variables throughout, with a very simple WHERE clause. There were no sub-queries or joins, it was using the main primary index on the table, and was not doing any disk I/O (physical reads).

When they next ran that module of the application, I monitored V$SQL until I saw that UPDATE, and then did a DBMS_XPLAN.DISPLAY_CURSOR on it using its SQL_ID and CHILD_NUMBER. The execution plan I got did indeed saying it was using an index, but a SKIP SCAN on it. This means that the index is being used inefficiently, and not as efficiently as it could be. Something was stopping Oracle using all of the index - it has 9 columns in it.

Further examination showed that the WHERE clause in the UPDATE did specify all columns in the unique index it was using. So it should have been doing a UNIQUE SCAN. Checking the execution plan again, I spotted that there was a reference to one column within a call to an INTERNAL_FUNCTION. This got me thinking. The column in question was a DATE column, and was the second column in the 9 column unique index.

Ah-ha. Something is wrong with the DATE value provided by the application at run time, and Oracle cannot use it directly to compare to the DATE values in the database. Instead Oracle is converting the value in each record in the database to something else, and then comparing it to the value in the query. With the net effect that it is not using all of the index. Thus, instead of doing a UNIQUE SCAN to find one single record, it was instead finding all records with the same value for the first column, and then comparing each of these to the converted DATE value. And in some cases one value of the first column could have many hundreds or thousands of matching data rows.

Why was the query gradually getting slower each day and week? Because the DATE column referred to the date of the business transaction itself, and sometimes they had to process late transactions from several days ago - hence the UPDATE of an existing record rather than an INSERT. And over time, more and more records were being added for different DATE values. So the UPDATEs were having to retrieve and check more records each week that went by.

What about the testing done prior to production? They had assumed that there would be very few late, back dated transactions, and had only tested new transactions for the past day. Thus all the processing involved INSERTs only into the table. Any late transactions in the test data were negligible in volume. However, in production up to 50% of the transactions were late. And the UPDATE was taking 100 times longer to execute than the INSERT.

What caused this? Digging further I established that Oracle saw the value it was getting was of type TIMESTAMP, which has sub-second values in it. And rather than lose precision by truncating its value, it was instead converting the DATE value in each data row to a TIMESTAMP value before comparing.

And some further digging shows that this is a known issue with Oracle and Java applications. See their own FAQ at:
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01

Most of the Java Date types (Timestamp or Util.Date) are considered by Oracle to be Timestamps, and the JDBC layer passes such values through as Timestamp. Hence the conversion by Oracle, and the lack of use of the index. What is clear from reading the documentation is that Oracle got this mapping wrong. Instead of mapping the Oracle DATE to only the Java sql.Date type, they should also have allowed a mapping from Java Timestamp to Oracle DATE. Which is why they have changed the behaviour in the Oracle 11.1 JDBC driver. With this correct mapping, no conversion is done, the Java supplied query value is used directly, and the index is used properly for a UNIQUE SCAN.

A solution to this is tricky, as the customer is on Oracle 10g, and not planning on moving to 11g at the moment. Why? Well if it was simple there would already be a solution published by Oracle and people would be using it. And there isn't - see the FAQ referenced before. The database is not new, and the Java module is something new added on to an existing application suite. So switching all columns in the database from DATE to TIMESTAMP is not an option. Using sql.Date in the Java application is not an option, because it has a zero value for its time part i.e. no time part. So something else needs to be done for the proper solution.

One option is to change the SQL being used in the UPDATE and in the WHERE clause instead of:

• date_column = :bind_variable

do

• date_column = cast (:bind_variable as date)

This way the value will always be a date, and the UPDATE will use the full index. However, the customer is worried about other such queries that may be lurking within their application code. They want a generic solution that just works for all DATE columns. Which means a change to the Java data type mapping somewhere, somehow.

While the customer works out how to implement their own change to their Java code (at which abstraction layer of all of the layers involved in mapping an application Object down to something that calls JDBC do they make a change?) they needed a quick fix to reduce the elapsed time of this job. The solution? Create an index with the same columns, but with the DATE column last in it. Yes, it takes time to create the index, and some disk space is used up. But the UPDATE now runs a lot faster, because it uses more of the index structure with the other 8 columns in it, and ends up with far fewer data rows to check against the DATE value.

The net result? The 10 hour job has come down to 2.5 hours, putting the customer back inside their 8 hour window. And it will probably remain at similar levels for at least the next month or so. Giving the customer time to work out how to fix their Java data type mappings.

As I said at the beginning, using Java often can cause all kinds of other problems elsewhere. Even when you are using it as the Java documentation tells you to. And isn't it amazing how the best tests in the world always seem to miss something that turns out to be more important and relevant than the other tests they concentrated on? It seems that the Testing Team never actually took the time and effort to establish exactly what mix of business transactions would be processed by the application. They just made some assumptions about what it would be.

Queuing Theory & Resource Utilisation

While Queuing Theory can be quite academic and mathematical at times, it does include a number of core rules or laws about how systems and their components behave under higher levels of utilization and the effect on the length of queues of pending requests.

One of the conclusions from this is that you cannot have high utilization of any resource without some level of queuing occurring. It may not seem obvious, but it is true. Essentially the queue of pending requests or jobs is needed to keep the resource busy most of the time. If the queue was ever empty then the utilization of the resource would be very low. High utilization is only achieved by having a queue of waiting requests, so that there is always a next thing to do when the current thing completes.

You could theoretically also achieve 100% CPU utilization by only having a few very active processes and no queues. So on a 4 CPU system you could achieve 100% CPU utilization with only 4 processes, each always busy and executing instructions. In this scenario there is no queuing for the CPUs, as there are only 4 processes, and the efficiency of the system is very good.

However, such a scenario is very, very unlikely. The processes could never block for anything and would always be executing CPU instructions. Which means that they would not be doing any disk I/Os, or communicating with each other by some kind of messages, or using locks or latches on shared structures. Which is the complete opposite of the Oracle Database Architecture.

An Oracle database server will have many processes running on it - one shadow server process per connected session (unless you are using the Shared Server configuration) - and they will use shared memory, and locks and latches to control data consistency, and perform lots of disk I/Os.

So an Oracle database server does conform to the general model in Queuing Theory of having lots of separate clients (the shadow servers) making requests on the resources in the system. And as a result, it does conform to the golden rule of high resource utilisation equals queues of pending requests.

As a result, 100% CPU utilization is very bad, and is symptomatic of very large queues of waiting processes. Queuing Theory also shows that above 50% utilization of a resource, there is always a request in the queue more often than not. Note that this is ‘on average’ - sometimes the queue can be empty and sometimes it can have several requests in it - but on average the number of waiting requests will be more than zero.

A general rule of thumb is to get worried at 80% utilization, as the number of concurrent requests will average something around four, and rises exponentially above this. An explanation of some of this and a nice graph of queue length versus utilization is available in this Microsoft article on Modeling Principles for Sizing. I know it is a Microsoft article, but it does summarize the effects of queuing well, and has the nice graph in it.

These queues can apply to all aspects of a computer system - CPU, Disk, Network and Memory. To drive any one of these at 80% utilisation or above means that you have queues of pending requests, which are needed to keep the utilisation that high.

The net effect of such high utilisation is an increase in response time. The total elapsed time for a given request is now the wait time in the queue plus the service time of the request itself. When the queue is 4 long on average, then the response time is actually 5 times the service time e.g. you might spend 40 ms waiting to perform a disk I/O of 10 ms itself. So high utilisation and high queues has a direct effect on the elapsed time of individual transactions.

The formula for the Total Number of jobs in the system (N) in proportion to Utilisation (U) is:

• N = U / (1 - U)

Note that N is not the same as Q - the number of requests waiting in the queue. N is both the number in the Queue plus any requests currently being serviced - the total within the system.

And the formula for Response Time is:

• R = Service Time / (1 - U).

So higher utilisation directly leads to longer queues, and larger response times.

Whether this is acceptable to your situation depends whether your goal is total throughput irrespective of transaction response time, or your goal is individual transaction response time. In other words, is it an online system, or more of a batch system processing relatively large units of work.

While Queuing Theory can seem theoretical at times, to me it reinforces the message that when you scale up the load on any system there will always be a bottleneck. And that bottleneck will reach high utilisation as it nears its capacity, and large queues will form in front of it. Identifying where the queues are in a system, what the bottleneck is, and what can be done to fix it - reduce service time or increase capacity - are key to performance tuning. And an appreciation of Queuing Theory has helped me get a deeper understanding of this area.

Staging Tables and Statistics in Oracle

As we should all know, Oracle 10g only has a cost based optimiser for executing SQL statements, and all of its decisions are based on the statistics it has about the tables in the database. Generally this works well, and there is also a default job that runs each day to keep these statistics up to date. Where there can be a problem though is with tables that are used to stage intermediate results during a series of processing steps.

Such tables often start off empty, are loaded with data produced by the first steps in processing, and then read and processed by the subsequent steps. Eventually the data will be deleted from the staging table as its processing is finished. Because the statistics on these tables indicate that they are empty or near empty, Oracle will simply scan them rather than use any index. When the data set being processed is small, this mistake can go unnoticed, with little effect on the overall performance and elapsed time. However, as the data set being processed gets larger and larger so the effects of the wrong execution plan become larger too, and the elapsed time grows and grows.

The solution of course is to update the statistics on such a staging table after the data has been loaded into it, so that Oracle can make the right decisions. There are a number of ways to do this, which I'll get onto, and you can also find out elsewhere on the web easily enough.

The other thing to be aware of in this situation is temporary tables - global temporary tables as Oracle calls them - where the data in the temporary table is session private and is deleted when the session disconnects. These temporary tables are also often used to stage data that is being processing in a series of steps, and have certain potential advantages - data is private to each session, and the data is automatically deleted when the session disconnects.

However, there are issues with collecting statistics on such temporary tables, which you must be aware of. The net result is that it is basically impossible to collect statistics on such temporary tables. You have to use another mechanism to set the statistics on a temporary table.

For normal data tables you can use the GATHER_TABLE_STATS procedure in the DBMS_STATS package to gather up to date statistics on the table after the data has been loaded into it. By leaving other options to their default values, Oracle should also update the statistics on the indexes too.

If you want to avoid the elapsed time associated with the full GATHER_TABLE_STATS you can instead run DELETE_TABLE_STATS. In such situtations when the Oracle optimiser has no statistics on a table it samples the table itself to make some estimates for those statistics. This is termed dynamic sampling. In principle this ensures that the optimiser has some statistics on the table that reflect its size and data distribution. Again this will not work as intended for a temporary table.

And finally you can use SET_TABLE_STATS to explicitly set statistics such as the row count of the table. This avoids the gathering of statistics associated with either full statistics or dynamic sampling, and works for temporary tables too.

Although this may not seem earth shattering, having correct and valid statistics is critical to the way Oracle 10g works. And poor performance and wrong behaviour from Oracle when executing queries can often be due to poor or incorrect statistics. Often developers can forget about this, assuming that the overnight job will keep the statistics up to date - a database administrator's responsibility. But these temporary staging tables are something that change so frequently that they can never have up to date or correct statistics on them, no matter what the database administrators do. Which in turn means that they must be dealt with explicitly within the application code, to somehow set the statistics to suitable values using one of the mechanisms just described. Only then will Oracle access the data in these tables in an optimal way.

Contention and a bottleneck - or not?

I recently had the following situation: during a heavy data load by multiple, concurrent jobs the customer said that they saw contention, and provided me with a STATSPACK report from this period. Initially I was confused:

• On the one hand, the top wait event was a very high value for a latch wait on the cache buffers chain.
• On the other hand, the CPU utilisation during this whole period was only around 10%.

Normally I would say that this system had capacity to spare - 90% CPU unused - but the wait figures indicated that something was wrong somewhere. Digging further into STATSPACK I verified that the top SQL statement was the INSERT into the one table, and that the number of waits correlated with the number of sleeps on the cache buffer chain latch.

Now this told me that these were actual sleeps, where the session failed to get the latch when it tries in a busy spin loop. And these sleeps are relatively expensive and time consuming. Hence the high value for the wait time experienced.

Digging further I saw that there were buffer busy waits too, and managed to establish that these were all on the same index on the table. Now this table has six indexes - i1 to i6 - and the buffer busy waits were all on index i6. And this is on a column called last_update_time. So clearly this was a sequential, monotonically increasing series of values, and would all end up in the final leaf block of the index. So we had a classic case of records with the same value, or very close values, being inserted, and causing contention on the index block where those same values would be stored. And the lock on the index block during the block split and contents copying, would be the cause of the sleeps on the cache buffer chain latch for that block.

I also did some checking on the index structure itself, by analysing the index and then looking at the statistics. This showed that the percentage of space actually used in each block averaged just over 50%. So Oracle was actually doing 50:50 index block splits when the last index leaf block became full, rather than 90:10. This is probably due to the update times not being perfectly in sequence from the different data loading processes. But this also meant that this new block was already 50% full and so would be ready to split even sooner than if it were only 10% full.

The solution? Well, there were a couple of possibilities.

One was to use a REVERSE key index. However, these are only useful for equality tests and not ranges. And date columns tend to be used for range queries (from and to).

Another option would be to partition the index by another column, which would have different values in each INSERT from each of the loading processes. This basically creates a set of separate smaller indexes, and so increases the number of last leaf blocks across these sub-indexes, and so reduces contention. However, partitioning is an extra cost option, and needs extra maintenance over the number of partitions and their configuration.

The recommended option is to modify the index and add the extra column after the last_update_time, mentioned just now in partitioning. This would mean the index was still useful for equality and range conditions, and would spread out the same last_update_time value between multiple index leaf blocks having different values for the second column - in this case essentially an account identifier. Now with this extra layer of data values in the index it will distribute the new values over more index leaf blocks, and so reduce contention and improve throughput times.