An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the Upgrade

by Roger Schrag
Database Specialists, Inc.

About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.

Introduction

This paper is intended to share our company’s experience upgrading from Oracle 8i to Oracle 10g. On one hand, many of our DBA colleagues would like to upgrade their databases to the latest Oracle release so that they can put the newest features to work. But on the other hand, most of those same conservative DBAs would hesitate to take a system that is running perfectly well and put it at risk by moving to the bleeding edge of Oracle’s latest offering. For several years, we chose to stand pat with Oracle 8i—that release met all of our needs, and we found it to be smooth and stable. In late 2004 desupport loomed over us, and we finally decided it was time to upgrade. Hence our upgrade to Oracle 10g which we will discuss in this paper. If our experience can help you plan for your own upgrade in any way, then this paper will have achieved its goal.

In the next section of this paper we will discuss some general observations that emerged from our Oracle 10g upgrade experience. We will talk in this section at a very high level about our environment, the upgrade strategy we chose, and how the upgrade went for us overall. We will not focus on the details of how to perform the upgrade itself, because you can find that information in the Oracle documentation set. Instead we will focus on our experiences.

In the third section of this paper we will choose specific topics, such as SGA size and query optimization, and discuss in depth how Oracle 10g worked for us. Again, we will focus on our experience and not what the documentation says. We’ll provide data from TKPROF, v$ views, and such in order to help you understand how we reached our conclusions. If you find the length of this paper intimidating, please take heart in the fact that a lot of it is taken up with TKPROF report excerpts and other supporting information that you can delve into or skip as you prefer.

Please remember that each Oracle system is unique and will have its own challenges. Never take somebody else’s word on anything when it comes to Oracle technology. In this paper we are only relaying our experiences—we do not intend to make any broad judgments or general claims about Oracle 10g. The only way for you to know how your specific system will fare on Oracle 10g is to try it—in a test environment—and see.

Our Upgrade to Oracle 10g – The View from 30,000 Feet

In this section we will first give a quick overview of our Oracle environment and upgrade strategy so that you have a background for everything else you will read in this paper. Then we will share our overall impressions of the upgrade process, compatibility, and Oracle 10g itself. In this section we will keep things at a high level—later on we will drill down into greater detail.

Our Oracle Environment

At Database Specialists, one of our service offerings is remote administration and production support for Oracle databases. We have a custom monitoring tool that we have developed and evolved over many years to help us do this efficiently. We install an agent on each customer’s database server, and this agent sends information in the form of agent files to a server in our data center at regular intervals.

Daemons running on our server decrypt these agent files, parse them, load the information into an Oracle database, analyze the data for problems and trends, and send out email notifications as appropriate. A web-based user interface allows us and our customers to view reports on demand and adjust preferences and configuration settings as desired.

We were using Oracle 8i version 8.1.7 for our production database. We were running the 32 bit version of Oracle Standard Edition on a Sun server running the 64 bit version of Solaris 8. About 99% of the application code—both the backend daemons and the web-based user interface—is written in PL/SQL.

Our application was designed and developed from the beginning by a small group of experienced Oracle DBAs and developers. We believe the application architecture, database schemas, and PL/SQL code are designed well for efficiency and scalability. We leverage various features which seem mundane today but were new in Oracle 8i—such as global temporary tables, bulk binding, and using the table() function to query PL/SQL collections as if they were database tables.

Over the years whenever we experienced a response time issue or detected a threat to scalability of the system, we applied standard performance management techniques just like we would for any of our customers. Sometimes this resulted in hints being added to queries, and today there are about 50 SQL statements throughout our application that contain optimizer hints. There are probably still some aspects of our application that are theoretically suboptimal, but we do not plan to address them until they cause somebody to experience a response time problem. We are busy people and we don’t have time for what Gaja Vaidyanatha calls “compulsive tuning disorder.”

Our Oracle 8i production environment was extremely stable. We long ago learned how to work around the few Oracle bugs that manifested themselves in our system, and we typically bounced the Oracle instance only a few times each year. We are constantly adding new features to the application, but we do our work in a development environment and deploy changes into production only after thorough testing. In short, Oracle 8i was meeting our needs.

Our Upgrade Strategy

We developed and tested our upgrade strategy by restoring a hot backup of the production database onto a test server similar to the production server and upgrading this database to Oracle 10g. Early on we decided to use the export/import method to perform the upgrade. This allowed us to switch all tablespaces to locally managed tablespaces, freshly install Oracle components, and compact all of our table and index segments. We might have been able to achieve most of these objectives with the Database Upgrade Assistant, but the prospect of starting with a “fresh” data dictionary appealed to us.

After we choreographed the upgrade procedure and ran through it successfully on the test server, the result was two test databases on the test server—an Oracle 8i database that was a carbon copy of production and an Oracle 10g database that contained the same data as production. This allowed us to run all sorts of tests to compare how the two versions of Oracle handled identical workloads and challenges. (The fact that tables and indexes in the Oracle 10g database were compacted into fewer data blocks during the import introduced an imperfection in the tests, unfortunately.)

Our production database was not very big (about 15 Gb) and we were able to work out a cutover strategy where the Oracle 8i production database would remain open while we were importing the data into the new Oracle 10g database and then reload the missing agent files into the Oracle 10g database later. This allowed us to use the export/import upgrade method with less than one hour of down time.

Our Overall Impressions of the Upgrade and Compatibility

We found that Oracle 10g version 10.1.0.2 and the 10.1.0.3 patch set both installed surprisingly smoothly on our Sun Solaris 8 servers. Several operating system patches were required before we could run the Oracle Universal Installer, but this was all documented clearly. The fact that the OUI double-checked our operating system patches was a nice touch.

We encountered two compatibility issues during the first upgrade attempt, but they both had very simple resolutions and were not unreasonable. First, we needed to reconfigure our EXTPROC settings and relink our external libraries that we call from PL/SQL. The change in settings was required because Oracle made the EXTPROC infrastructure more secure in Oracle 9i version 9.2.0. The relink was necessary because our upgrade included a change from a 32 bit version of Oracle to a 64 bit version.

The second issue we encountered was that a few of our stored procedures caused PL/SQL internal errors when we tried to compile them in the Oracle 10g test database. We figured out pretty quickly that all of the problem procedures had been wrapped with the Oracle 8i PL/SQL wrapper utility. We rewrapped them with the Oracle 10g PL/SQL wrapper, and then they compiled without errors in the Oracle 10g test database.

Aside from these two issues, everything else worked very smoothly. Having worked with Oracle since version 5.1 and having been involved in many upgrades over the years, we were pretty impressed.

The web-based user interface for our application uses Oracle’s modplsql Apache add-on to invoke PL/SQL stored procedures from HTTP requests. We planned to keep the web server and modplsql component running from an Oracle 8i home initially so that we would not need to upgrade this area of the system at the same time that we upgraded the database. We found that our Oracle 10g database interoperated with the Oracle 8i modplsql client just fine.

Our Overall Impressions of Oracle 10g

Out of the box, the Oracle 10g database, Enterprise Manage Database Control, and iSQLPlus all worked just fine. (The latter two were terribly slow, but they did work.) Oracle 10g’s import utility read our Oracle 8i export file without incident, and our system was upgraded to Oracle 10g in no time with surprisingly little pain or frustration. Oracle 10g even tidies up a few things and makes them less complicated than previous releases, such as consolidating several tablespaces into one SYSAUX tablespace.

Our Oracle 10g database appears to be just as stable as our Oracle 8i database was. We have not encountered any ORA-600 errors or any “odd” behavior. Of course, we are running our Oracle software on Solaris, which for many years has seemed to us to be the most stable platform for Oracle. And, probably more significantly, our application at this point still does not use any cutting edge new Oracle features. As far as we know, we are not yet using any features added in Oracle 10g. And we have even been conservative in our use of Oracle 9i features, sticking so far to basic enhancements like automatic undo segment management, a locally managed SYSTEM tablespace, and a server parameter file instead of an init.ora file.

In general we found Oracle 10g to be (as you would expect) bigger and hungrier for resources than ever. The Oracle 10g executable is bigger, and we had to make our SGA bigger as well Things like hard parses and optimizer statistics collection consume more resources than in the past. Oracle’s background daemon processes use more resources and the SYS schema contains more objects than ever. We could go on, but the truth is that we found Oracle 10g’s increased overhead to be manageable.

Overall our application performs about as well on Oracle 10g as it did on Oracle 8i. A few queries run faster and a few run slower, but the pluses and minuses tend to cancel each other out. We found just a few queries in our application that ran fine on Oracle 8i but caused performance issues on Oracle 10g. For most of the queries that we had previously added hints to, we found that Oracle 10g did better without the hints than Oracle 8i did without the hints—but Oracle 8i with the hints did significantly better than Oracle 10g without the hints. So would we have avoided the need for hints in some of our queries if Oracle 10g had been around all along? We don’t think so.

We were let down by Oracle 10g’s SQL Tuning Advisor. The examples in the documentation are trivial and, indeed, we got the advisor to give meaningful recommendations when we made up our own simple test cases. However, when we gave the advisor the problem queries in our application (the queries for which we had developed hints in the past), the advisor consistently reported, “There are no recommendations to improve the statement.” One of the problem queries that the advisor could not help was a simple join between two tables. We still don’t understand why the advisor couldn’t find the better execution plan for such a simple query, and we would like to think that the SQL Tuning Advisor will do better in other environments than ours.

Overall, the upgrade to Oracle 10g went much more smoothly for us than we expected. We were able to live with the increased resource requirements, and there were very few situations in which our application actually performed worse on Oracle 10g. Once we start leveraging newer Oracle features, we expect to be able to get more out of our Oracle system than ever before, and the increased resource requirements should be well worth it for us.

Our Upgrade to Oracle 10g – The Details

In this section we will discuss in greater detail several of our observations about Oracle 10g and how our system fared before and after the upgrade. In particular, we will explore many of the topics we touched on in the previous section.

Sizing the Shared Pool and SGA

Our Oracle 8i production database ran smoothly for several years with the shared_pool_size parameter set to 40 Mb and a total SGA size of roughly 84 Mb. When it comes to sizing the SGA, we have always followed a methodology of making things only as large as they need to be. Although our application has over 50,000 lines of PL/SQL code, we don’t use dynamic SQL with embedded literal values. Thus while the database averaged 15 to 20 statement executions per second, there were fewer than 660 hard parses per day on average. Meanwhile our buffer cache hit ratio averaged over 97% and our library cache hit ratio averaged a hair under 100%. For these reasons we were comfortable with an 84 Mb SGA on Oracle 8i.

We knew that our SGA was on the small side, and that we would have to bump it up during the upgrade to Oracle 10g. For one thing, Metalink document 263809.1 says the minimum shared_pool_size setting for Oracle 10g on 64 bit platforms is 144 Mb. Given that our database server has 2 Gb of physical memory, this was not a problem.

Oracle 10g provides an Upgrade Information Tool to help plan an Oracle 10g database upgrade. The tool is basically a SQL*Plus script that you run against your existing database, and it will give you advice to make the upgrade go more smoothly. This script told us to change the shared_pool_size setting to 150944944, or about 144 Mb.

We tried a few shared_pool_size settings out for fun (or, shall we say, “educational purposes”). Our Oracle 10g instance would not start will shared_pool_size set to 48 Mb. Upping it to 64 Mb allowed us to open the database, but ORA-4031 memory allocation errors were frequent. The system appeared to function properly with a 96 Mb shared pool, but ultimately we followed Oracle’s recommendation and went with 144 Mb.

We found three reasons why Oracle 10g needs the shared_pool_size parameter to be set to a larger value than previous releases of Oracle. It would be unreasonable to say that since we had to increase the parameter by 260% you will have to do the same. In the following sections we will look at each of these three shared pool sizing factors.

Allocation for Overhead

A portion of the shared pool is devoted to holding internal memory structures that represent overhead. In Oracle 8i and 9i, Oracle would make the shared pool larger than the size specified by the shared_pool_size parameter in order to allow space for these overhead structures. Thus in Oracle 8i and 9i the shared_pool_size parameter specifies the amount of usable space desired in the shared pool.

In Oracle 10g, the shared_pool_size parameter specifies the actual size of the shared pool. Some of this space will be devoted to holding the overhead structures, so in Oracle 10g the actual usable space in the shared pool will be less than what was specified by the shared_pool_size parameter. This change in behavior is explained in Metalink document 270935.1 and does not in and of itself necessarily lead to a larger SGA.

On our Oracle 8i production database we could see that the shared pool was about 3 Mb larger (roughly 8%) than the shared_pool_size parameter specified:

SQL> SELECT SUM (bytes) / 1024 / 1024 actual_pool_size
  2  FROM   v$sgastat
  3  WHERE  pool = 'shared pool';

ACTUAL_POOL_SIZE
----------------
      43.1291847

SQL> SHOW PARAMETER shared_pool_size

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
shared_pool_size                     string  41943040

SQL>

We’ve seen situations where the disparity can be more significant. One of our customers using Oracle 9i, for example, has shared_pool_size set to 416 Mb, but the actual size of their shared pool is 528 Mb—a difference of 112 Mb or roughly 27%.

Shared SQL Area Memory Usage

In addition to the shared pool having less usable space in Oracle 10g for the same shared_pool_size setting, it also appears that individual SQL statements occupy more space in Oracle 10g’s shared SQL area than Oracle 8i’s—in our case almost twice as much. We discovered this by querying the sharable_mem, runtime_mem, and persistent_mem columns in v$sql. (Remember that our Oracle 8i software was 32 bit and our Oracle 10g software was 64 bit, so this will account for some of the increase.)

We engaged the backend daemons of our application to perform a typical workload in our Oracle 8i and 10g test databases while the databases were otherwise idle. After the workload was complete, we queried v$sql. The backend daemons of our application execute PL/SQL packages in the DBRX_OWNER schema.

Looking at memory usage for statements parsed in the DBRX_OWNER schema on our Oracle 8i test database we saw:

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username = 'DBRX_OWNER'
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBRX_OWNER          362    6,275,020        256,176   1,996,324   8,527,520

SQL>

Looking at our Oracle 10g test database we saw:

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username = 'DBRX_OWNER'
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBRX_OWNER          360   12,941,006        487,048   3,361,160  16,789,214

SQL>

The two databases had roughly the same number of application statements in the shared SQL area, but these statements took up nearly twice as much memory on Oracle 10g.

This might suggest that the size of the shared pool needs to be doubled when upgrading to Oracle 10g, but we don’t want to jump to any broad conclusions. In our case we had to more than triple the shared_pool_size setting in order to meet the minimum shared pool requirement for Oracle 10g, so the extra memory used for each SQL statement was not an issue for us.

We did not find any discussion of increased shared SQL area memory usage on Metalink. For all we know, some of the increase might have happened between Oracle 8i and 9i. We also do not know how much of the increase can be attributed to the 32 bit to 64 bit software change.

SQL Statements Generated by Oracle

In the shared SQL area of any Oracle instance you will find statements that were issued by user applications, and you will also find statements that were issued by Oracle itself. Some people refer to the latter as “recursive SQL” or “SYS SQL.” Whatever you call it, Oracle 10g seems to take this category of statements to a whole new level.

After running the application workload in the previous section, we queried v$sql to see what non-application statements were in the shared SQL area. Here is what we saw on the Oracle 8i test database:

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
SYS                 192    2,331,619        125,356     569,688   3,026,663
SYSTEM               30      810,325         19,644     163,480     993,449
                        ------------ -------------- ----------- -----------
sum                        3,141,944        145,000     733,168   4,020,112

SQL>

Here is what we saw on the Oracle 10g test database:

SQL> SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
  2           SUM (B.persistent_mem) persistent_mem,
  3           SUM (B.runtime_mem) runtime_mem,
  4           SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
  5  FROM     dba_users A, v$sql B
  6  WHERE    A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
  7  AND      B.parsing_user_id = A.user_id
  8  GROUP BY A.username;

USERNAME       COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM   TOTAL_MEM
------------ ---------- ------------ -------------- ----------- -----------
DBSNMP               99    4,161,758        137,504   1,701,032   6,000,294
SYS                 695   24,402,627      1,024,744   8,103,496  33,530,867
SYSMAN              670   16,644,400        806,904   4,403,720  21,855,024
SYSTEM               14      533,442         18,152     290,280     841,874
                        ------------ -------------- ----------- -----------
sum                       45,742,227      1,987,304  14,498,528  62,228,059

SQL>

This is one of the places where you can see the toll that the automation features—Automatic Workload Repository, Automatic Database Diagnostic Manager, Enterprise Manager, and so on—put on the database. SQL generated by Oracle took up an order of magnitude more space in the shared SQL area of our Oracle 10g test database than it did in our Oracle 8i test database. In our Oracle 10g test database, SQL generated by Oracle took up more space than all of our application code—and our application contains more than a trivial amount of SQL.

To be fair, we should point out that our Oracle 8i database environment was Standard Edition, with a minimum of Oracle options installed and no Enterprise Manager. Meanwhile, our Oracle 10g database environment is Enterprise Edition, with Enterprise Manager Database Control and the “default” set of database options installed. Thus this is not exactly an apples-to-apples comparison.

Optimizer Statistics Collection and Accuracy

On our Oracle 8i production database we had a job scheduled with dbms_job that ran every Sunday night to update optimizer statistics for all of our application tables and indexes. Statistics were collected with an ANALYZE statement like:

ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;

This scheme worked well for us for several years, so we had never gotten around to switching to dbms_stats.

Oracle 10g has a built-in database job called GATHER_STATS_JOB to collect statistics for the entire database. This job uses dbms_stats and only gathers statistics for objects where existing statistics are missing or believed to be stale. We could not find documentation as to exactly how the sample size is chosen nor which table columns are chosen to get histograms.

As a test, we deleted optimizer statistics on all objects in the DBRX_OWNER schema of our Oracle 8i and 10g test databases. Then we traced an Oracle job queue process as it gathered the statistics again. We found that on our system optimizer statistics collection in Oracle 10g is a much more resource-intensive operation than it was in Oracle 8i. Of course, Oracle 10g only collects statistics during a time window that you specify (the default is 10 pm to 6 am) and only for objects where the existing statistics are missing or believed to be stale. A summary of information from the trace files we collected is as follows:

 Resources Used to Collect 
Optimizer Statistics
Oracle 8i
 (ANALYZE) 
Oracle 10g
 (automatic) 
CPU seconds 1,101 2,595
Elapsed seconds 2,044 5,244
Logical reads 597,717 73,082,675
Physical reads 545,844 2,926,625

One reason Oracle 10g consumed more resources to collect statistics had to do with histograms. While our ANALYZE statements in Oracle 8i did not collect any histograms, Oracle 10g chose to collect histograms on over 20% of the columns in our DBRX_OWNER schema:

SQL> SELECT   histogram, COUNT(*)
  2  FROM     user_tab_columns
  3  GROUP BY histogram;

HISTOGRAM         COUNT(*)
--------------- ----------
FREQUENCY              267
HEIGHT BALANCED         74
NONE                  1202
                ----------
sum                   1543

SQL>

It appeared to us as if Oracle 10g chose to create a FREQUENCY histogram for columns that had low cardinality in their values, and a HEIGHT BALANCED histogram for columns with skew or gaps in the data distribution. Whether or not a column was indexed appeared to have no bearing on histogram creation. Oracle maintains a table in the SYS schema called col_usage$ where it tracks which table columns appear in WHERE clauses and how they are used. Oracle will not create a histogram on a column if it has never been used in a WHERE clause.

Many of the histograms Oracle chose to create in our environment do not appear to be useful because in some cases the columns almost never appear in a WHERE clause. In other cases, the columns that got histograms are not indexed and only appear in WHERE clauses where other selective indexed columns from the same table appear. So on our system we believe Oracle went to a lot of effort to create unnecessary histograms. To be fair, however, it would be extremely difficult for Oracle to truly know which columns would or would not benefit from a histogram.

Another possible cause for the greater resource consumption by Oracle 10g during statistics collection might have to do with sample size. The ANALYZE statements we used in Oracle 8i specified a sample size of 5 percent. Looking at the sample_size column in the user_tables view on our Oracle 8i test database, we could see that indeed the sample size for tables over 1 Mb in size was almost always between 4.5% and 5.4%. For tables under 1 Mb in size, Oracle sampled all rows.

As we mentioned earlier, we could not find any documentation for how Oracle 10g chooses sample sizes when gathering optimizer statistics—except that it is automatic. What we found actually happening on our Oracle 10g test database was all over the map:

Here is how we collected some of the above information from our Oracle 10g test database:

SQL> SELECT   A.table_name, A.num_rows, B.bytes / 1024 / 1024 mb,
  2           100 * (A.sample_size / A.num_rows) sample_pct
  3  FROM     user_tables A, user_segments B
  4  WHERE    A.table_name IN
  5          ('SAMPLE_DATA_FILES', 'SAMPLE_JOBS',
  6           'COMMON_SQL_PLAN_PARTS', 'SAMPLE_SQL_TEXTS',
  7           'SAMPLE_LIBRARY_CACHE_STATS')
  8  AND      B.segment_type = 'TABLE'
  9  AND      B.segment_name = A.table_name
 10  ORDER BY sample_pct;

TABLE_NAME                    NUM_ROWS         MB SAMPLE_PCT
-------------------------- ----------- ---------- ----------
SAMPLE_DATA_FILES           14,938,632   1,088.00        0.4
SAMPLE_JOBS                  1,360,429      54.00        4.1
COMMON_SQL_PLAN_PARTS          174,851       9.00        6.9
SAMPLE_LIBRARY_CACHE_STATS   1,414,830      80.00      100.0
SAMPLE_SQL_TEXTS             6,346,638     760.00      100.0

SQL>

It appears as if Oracle starts out with a very small sample from each medium or large table, gathering successively larger samples for those columns where skew, gaps in data distribution, or low cardinality are apparent. For example, from the trace file we can see the trials and tribulations that Oracle went through to collect statistics on the 760 Mb table where ultimately Oracle sampled every row:

In many cases Oracle 10g did a poor job of estimating the number of distinct values in a column. In most cases the ANALYZE statement on the Oracle 8i test database had come up with a more accurate estimate. Some of the poor estimates might have stemmed from sample sizes that were too small, but this is just a guess.

Bear in mind that optimizer statistics do not need to be exactly correct in order to be effective. This is why we estimate them instead of computing them in the first place. However, the question arises, “How accurate do the statistics need to be?” If Oracle chooses an acceptable execution plan for every query, then the statistics are accurate enough. But if some queries get unacceptably poor execution plans, it is hard to say whether or not inaccurate statistics are the cause. We found it interesting that Oracle 10g’s choice of sample size in our environment was so erratic. Whether this truly caused a problem or not, we cannot tell. (In the next section we will see that Oracle 10g chose poor execution plans for a few of our queries, but we simply cannot determine if inaccurate optimizer statistics are to blame.)

Query Optimization

Some pretty complex SQL statements lurk within the web-based reports and backend daemon logic of our application. However, the workload on our database follows an OLTP model with all SQL statements in the system executing very quickly. In our production Oracle 8i database, rarely did any SQL statement run for longer than a fraction of a second, with the exception of the quarterly data purge process.

We believe that we have done a reasonable job of writing practical, logical SQL statements throughout the application. In most cases Oracle 8i had no problem finding efficient execution plans for our SQL. In those few places where Oracle 8i chose an inefficient execution plan that led to a bona fide response time problem, we added hints to yield a better plan. Over the years we added hints to approximately 50 SQL statements throughout the application.

Query optimization represented a major concern of ours going into the Oracle 10g upgrade. The application has a lot of SQL in it, and a really bad choice of execution plan for almost any query could significantly degrade the system throughput and response time. It would be great if Oracle 10g could make our queries run faster, but what we really worried about was some queries running slower. If an application module consists of 100 SQL statements and just one of those statements heads south in a big way, it could present a perceptible problem.

We found in our test environment that most SQL in our application consumed roughly the same CPU time and number of logical reads on Oracle 10g as on Oracle 8i. Some statements ran a little faster, and a few ran a little slower. Only a very few SQL statements were slow enough on Oracle 10g to cause concern. Overall, most workload operations yielded similar response times in both versions of Oracle. Because our application consists primarily of many quick-running SQL statements, we did not expect Oracle 10g enhancements to bring us much performance improvement out of the box. It didn’t.

Once we ascertained that only a few queries posed problems for us on Oracle 10g, we wondered whether Oracle 10g really needed the hints that we had added to our trickiest queries over the years. We wondered if Oracle 10g would be smart enough to find an efficient execution plan where Oracle 8i could not. We found that in some cases our Oracle 10g test database ran the problem queries with hints removed much faster than our Oracle 8i test database could (also with hints removed), but that either test database could run the problem queries with hints in place even faster. Put another way, we found that in some cases Oracle 10g did a better job with our problem queries than Oracle 8i, but the hints we had developed still gave much better results than Oracle 10g could come up with on its own.

In the following sections we’ll look more closely into some of these query optimization issues and how we reached the conclusions that we did.

SQL That Ran Similarly on Oracle 8i and 10g

Our application’s Loader Daemon parses, validates, and loads files from our monitoring agents into the database for analysis and reporting. The Loader Daemon is a PL/SQL package roughly 7,800 lines long. Seven SQL statements in the package have hints. Starting out with the same data in the Oracle 8i and 10g test databases, we traced the Loader Daemon on each database while loading the same agent file into each. Some statements ran 0.01 CPU seconds faster on one database than the other, while some statements performed slightly fewer logical reads on one database than the other. However, the difference appeared almost to be random noise that cancelled itself out. Here are the summary statistics of what we observed:

 Resources Used by Loader Daemon 
to Load One Agent File
 Oracle 8i   Oracle 10g 
User SQL statements traced 110 127
Internal SQL statements traced 9 9
Unique SQL statements traced 109 110
Total OCI calls 1,800 1,792
CPU seconds 3.13 3.12
Logical reads 13,767 12,920
Physical reads 6 13

The number of user SQL statements traced and the number of logical reads differ enough between Oracle 8i and Oracle 10g to warrant discussion. The difference in logical reads is easy to explain: As we’ve mentioned before, the tables and indexes in our Oracle 10g test database were more compact. This saved Oracle some logical reads because data that needed to be read occupied fewer blocks.

The difference in SQL statements traced has a slightly more complicated explanation. Oracle’s PL/SQL engine maintains a cursor cache. If a stored procedure closes a cursor, the PL/SQL engine secretly keeps it open so that an additional parse call can be avoided should the same statement get executed again. In Oracle 8i the open_cursors parameter governs the size of PL/SQL’s cursor cache, but in Oracle 10g (and Oracle 9i version 9.2.0.5 and later) the session_cached_cursors parameter governs the size of this cache instead. This behavior change is explained in Metalink document 274496.1.

In Oracle 10g the session_cached_cursors parameter defaults to 0, and in this case the PL/SQL engine caches 50 cursors. Our Oracle 8i test database, meanwhile, had the open_cursors parameter set to 300 and v$open_cursor showed that 117 cursors were being held open for our loader daemon session. Since the PL/SQL cursor cache on our Oracle 10g test database was smaller, cursors were aging out of the cache. This caused more parse calls than necessary, and caused TKPROF to report a larger number of user SQL statements in the Oracle 10g trace file. (Note that these extra parses were soft parses and therefore were not very expensive.)

For those who might be interested, the summary section at the bottom of the TKPROF report from the Oracle 8i test database is as follows:

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PL/SQL lock timer                               5        5.13         20.46


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      119      0.09       0.08          0          0          0           0
Execute   1079      2.83       3.30          6       1936       9315        2115
Fetch      602      0.21       0.12          0       1892        624         586
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1800      3.13       3.50          6       3828       9939        2701

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  file open                                       3        0.00          0.00
  db file sequential read                         6        0.02          0.07

  110  user  SQL statements in session.
    9  internal SQL statements in session.
  119  SQL statements in session.
********************************************************************************
Trace file: 8i_load.trc
Trace file compatibility: 8.00.04
Sort options: execpu
       1  session in tracefile.
     110  user  SQL statements in trace file.
       9  internal SQL statements in trace file.
     119  SQL statements in trace file.
     109  unique SQL statements in trace file.
   46637  lines in trace file.
      22  elapsed seconds in trace file.

The summary section at the bottom of the TKPROF report from the Oracle 10g test database is as follows:

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PL/SQL lock timer                               6        5.00         19.73
  HS message to agent                            11        0.06          0.13


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      136      0.05       0.04          0          0          0           0
Execute   1082      2.95       4.20         13       1954       9205        2115
Fetch      574      0.12       0.27          0       1761          0         572
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1792      3.12       4.51         13       3715       9205        2687

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                          2        0.02          0.03
  latch: library cache                            1        0.01          0.01
  latch: shared pool                              1        0.00          0.00

  127  user  SQL statements in session.
    9  internal SQL statements in session.
  136  SQL statements in session.
********************************************************************************
Trace file: 10g_load.trc
Trace file compatibility: 10.01.00
Sort options: execpu
       1  session in tracefile.
     127  user  SQL statements in trace file.
       9  internal SQL statements in trace file.
     136  SQL statements in trace file.
     110  unique SQL statements in trace file.
   36351  lines in trace file.
      21  elapsed seconds in trace file.

The Performance Summary report is a report in the web-based user interface that provides a summary of many performance statistics for one monitored Oracle database over a specified period of time, along the lines of a Statspack report. The report is implemented as a PL/SQL package approximately 3,200 lines long that takes parameter inputs, fetches the desired information from the database, and stuffs the results into a global temporary table for retrieval and presentation by another application module.

We coded a simple stub to call the Performance Summary report package with a fixed set of parameters. Starting out with the same data in the Oracle 8i and 10g test databases, we traced a call to the stub on each database. Again, some statements ran slightly faster on one database than the other, while some statements performed slightly fewer logical reads on one database than the other. Here are the summary statistics of what we observed:

 Resources Used by Performance
Summary Report 
 Oracle 8i   Oracle 10g 
User SQL statements traced 98 98
Internal SQL statements traced 10 10
Unique SQL statements traced 98 97
Total OCI calls 654 531
CPU seconds 0.89 0.88
Logical reads 4,641 3,661
Physical reads 1 0

Again, the difference in logical reads can be attributed to the fact that the Oracle 10g tables and indexes were more tightly compacted. This time the number of user SQL statements is the same in both databases, because this report does not run SQL statements multiple times in a loop the way the Loader Daemon does.

The difference in OCI calls is also noticeable. Both database trace files had the same number of parse and execute calls, while Oracle 8i had twice as many fetch calls at Oracle 10g (246 versus 123). From this it might appear that Oracle 8i’s PL/SQL engine does an extra fetch call to determine all rows have been fetched while perhaps Oracle 10g asks for more rows on the first fetch call to avoid an additional call.

For those who might be interested, the summary section at the bottom of the TKPROF report from the Oracle 8i test database is as follows:

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.11       0.10          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.11       0.10          0          0          0           1

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      105      0.05       0.10          0          0          0           0
Execute    298      0.39       0.42          1       1631        657         204
Fetch      246      0.34       0.46          0       2263         90         236
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      649      0.78       0.98          1       3894        747         440

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  file open                                       1        0.00          0.00
  db file sequential read                         1        0.00          0.00

   98  user  SQL statements in session.
   10  internal SQL statements in session.
  108  SQL statements in session.
********************************************************************************
Trace file: rpt1-8ib.trc
Trace file compatibility: 8.00.04
Sort options: fchqry
       1  session in tracefile.
      98  user  SQL statements in trace file.
      10  internal SQL statements in trace file.
     108  SQL statements in trace file.
      98  unique SQL statements in trace file.
    5424  lines in trace file.
       1  elapsed seconds in trace file.

The summary section at the bottom of the TKPROF report from the Oracle 10g test database is as follows:

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.21       0.17          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.21       0.18          0          0          0           1

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      105      0.04       0.02          0          0          0           0
Execute    298      0.39       0.43          0       1134        650         205
Fetch      123      0.24       0.25          0       1877          0         334
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      526      0.67       0.71          0       3011        650         539

Misses in library cache during parse: 0

   98  user  SQL statements in session.
   10  internal SQL statements in session.
  108  SQL statements in session.
********************************************************************************
Trace file: rpt1-10gb.trc
Trace file compatibility: 10.01.00
Sort options: fchqry
       1  session in tracefile.
      98  user  SQL statements in trace file.
      10  internal SQL statements in trace file.
     108  SQL statements in trace file.
      97  unique SQL statements in trace file.
    4504  lines in trace file.
       0  elapsed seconds in trace file.

SQL That Ran Faster in Oracle 10g

Since we had long ago added hints to any queries that were perceptibly slow, all functions of the application already ran smoothly and “fast enough” in Oracle 8i. For this reason, we did not expect anything to run noticeably faster in Oracle 10g. Indeed, we could not detect any faster response time or greater throughput for our system after upgrading to Oracle 10g.

When we removed hints from the statements in our application that we had optimized manually in Oracle 8i, we found that Oracle 10g was usually able to find a better execution plan than Oracle 8i. However, we found that in some cases the execution plan chosen by Oracle 10g was still far inferior to the execution plan generated by the hints we had added. We will now look at two examples.

One query that pops up in several reports retrieves a list of recent event notifications for all databases to which the specified user has access. The query joins seven tables. This is far from the most complex query ever written, but it is not trivial, either. To get the query to run efficiently in Oracle 8i, we had added a hint to tell Oracle to access the leading table by index, the order in which to join to the other tables, and which join algorithm (hash or nested loops) to use for each join. The query is as follows:

SELECT   /*+ ORDERED INDEX (privs) USE_NL (i s ar acr) USE_HASH (t l) */
         t.test_severity_id severity, i.instance_id,
         NVL (privs.instance_nickname, i.current_instance_name) inst_name,
         ar.first_detected, t.short_description brief_description,
         l.report_section_id
FROM     customer_user_instance_privs privs, customer_instances i,
         samples s, analysis_results ar, analysis_common_results acr,
         analysis_tests t, lookup_report_40000_formats l
WHERE    privs.user_id = :cp_user_id
AND      privs.current_cust_user_priv_level IN ('admin', 'read only')
AND      i.instance_id = privs.instance_id
AND      privs.user_wishes_to_see = 'y'
AND      s.instance_id = i.instance_id
AND      s.sample_type IN ('ping', 'full_stat')
AND      s.sample_date_db_local_time >
         (
         SELECT s2.sample_date_db_local_time -
                  (i.display_events_for_so_many_hrs / 24)
         FROM   samples s2
         WHERE  s2.sample_id =
           rpt_util.most_recent_analyzed_sample (i.instance_id)
         )
AND      ar.sample_id = s.sample_id
AND      acr.analysis_common_result_id = ar.analysis_common_result_id
AND      t.test_id = acr.test_id
AND      t.alert_type = 'event'
AND      l.test_id = t.test_id
ORDER BY severity, first_detected DESC, inst_name;

The data in the tables referenced by this query is not skewed in any way that we could detect. We ran the query on our Oracle 8i and 10g test databases with a typical value for the bind variable and here is a summary of what we saw:

 Resources Used by Recent Event 
Notifications Query
Query With Hint Query Without Hint
 Oracle 8i   Oracle 10g   Oracle 8i   Oracle 10g 
CPU seconds 0.10 0.09 51.84 2.91
Logical reads 2,208 1,451 1,678,011 4,111
Physical reads 7 0 27,551 0

You can see that without the hint, Oracle 10g did a much better job with this query than Oracle 8i. Unfortunately, Oracle 10g’s choice of execution plan still was far from the best. Adding the hint reduced the CPU time by 97% and logical reads by 65%.

For those who might be interested, Oracle 10g was able to run the version of the query with the hint faster than Oracle 8i. This is because Oracle 10g performed the subquery earlier in the plan and thus filtered out more rows sooner. Without the hint, Oracle 8i pushed the subquery to later in the plan when in fact the subquery was efficient and reduced the candidate result set from nearly a million rows to a few dozen. Oracle 10g without the hint wisely performed the subquery as early as possible, but unfortunately chose to hash join to a table with over 800,000 rows when the candidate result set had only 300 rows, and a nested loops join could have been done with a unique index lookup.

For those who might be interested, the execution plan from the Oracle 8i test database when no hint was used is as follows:

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
      0   SORT (ORDER BY)
      0    FILTER
   7093     HASH JOIN
     71      TABLE ACCESS   MODE: ANALYZED (FULL) OF
                 'LOOKUP_REPORT_40000_FORMATS'
   7092      HASH JOIN
      4       TABLE ACCESS   MODE: ANALYZED (FULL) OF
                  'ANALYSIS_TESTS'
 512382       HASH JOIN
 512382        NESTED LOOPS
 832470         HASH JOIN
 465504          HASH JOIN
     41           TABLE ACCESS   MODE: ANALYZED (FULL) OF
                      'CUSTOMER_INSTANCES'
 465504           TABLE ACCESS   MODE: ANALYZED (FULL) OF
                      'SAMPLES'
 832469          INDEX   MODE: ANALYZED (FAST FULL SCAN) OF
                     'ANALYSIS_RESULTS_PK' (UNIQUE)
 512382         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                    'CUSTOMER_USER_INST_PRIVS_PK' (UNIQUE)
 126110        INDEX   MODE: ANALYZED (FAST FULL SCAN) OF
                   'ANALYSIS_COMMON_RESULTS_N1' (NON-UNIQUE)
     42     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'SAMPLES'
     42      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'SAMPLES_PK'
                 (UNIQUE)

The execution plan from the Oracle 10g test database when no hint was used is as follows. (Note that the embedded PL/SQL function rpt_util.most_recent_analyzed_sample() performed 101 logical reads. Hence the execution plan below shows a total of 4,212 logical reads instead of the 4,111 mentioned above.)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=4212 pr=0 pw=0 time=3573213 us)
      0   HASH JOIN  (cr=4212 pr=0 pw=0 time=3573077 us)
     71    TABLE ACCESS FULL LOOKUP_REPORT_40000_FORMATS (cr=3 pr=0 pw=0 time=489 us)
      0    HASH JOIN  (cr=4209 pr=0 pw=0 time=3562005 us)
      4     TABLE ACCESS FULL ANALYSIS_TESTS (cr=18 pr=0 pw=0 time=853 us)
    243     HASH JOIN  (cr=4191 pr=0 pw=0 time=3554047 us)
 126110      INDEX FAST FULL SCAN ANALYSIS_COMMON_RESULTS_N1 (cr=341 pr=0 pw=0 time=126363 us)(object id 49302)
    243      HASH JOIN  (cr=3850 pr=0 pw=0 time=2830427 us)
    343       TABLE ACCESS BY INDEX ROWID SAMPLES (cr=391 pr=0 pw=0 time=19666 us)
    359        NESTED LOOPS  (cr=292 pr=0 pw=0 time=578919 us)
     15         NESTED LOOPS  (cr=58 pr=0 pw=0 time=1791 us)
     41          TABLE ACCESS FULL CUSTOMER_INSTANCES (cr=15 pr=0 pw=0 time=759 us)
     15          INDEX UNIQUE SCAN CUSTOMER_USER_INST_PRIVS_PK (cr=43 pr=0 pw=0 time=1588 us)(object id 49663)
    343         INLIST ITERATOR  (cr=234 pr=0 pw=0 time=40802 us)
    343          INDEX RANGE SCAN SAMPLES_UK2 (cr=234 pr=0 pw=0 time=40979 us)(object id 49504)
     14           TABLE ACCESS BY INDEX ROWID SAMPLES (cr=147 pr=0 pw=0 time=33644 us)
     14            INDEX UNIQUE SCAN SAMPLES_PK (cr=133 pr=0 pw=0 time=33165 us)(object id 49501)
 832469       INDEX FAST FULL SCAN ANALYSIS_RESULTS_PK (cr=3459 pr=0 pw=0 time=1665167 us)(object id 49571)

Another query that appears in multiple reports retrieves the number of transactions that occurred on a specified database over a specified time period. The query joins six tables. To get the query to run efficiently in Oracle 8i, we had added a hint to tell Oracle the order in which to join the tables and which join algorithm (always nested loops) to use for each join. The query looks like this:

SELECT   /*+ ORDERED USE_NL (D A C E F) */
         A.sample_id, A.sample_date_db_local_time,
         C.value + E.value transaction_count,
         F.startup_time_db_local_time
FROM     (
         SELECT common_stat_name_id
         FROM   common_stat_names
         WHERE  name = 'user rollbacks'
         ) B,
         (
         SELECT common_stat_name_id
         FROM   common_stat_names
         WHERE  name = 'user commits'
         ) D,
         samples A, sample_sysstats C,
         sample_sysstats E, sample_instance_info F
WHERE    A.instance_id = :cp_instance_id
AND      A.sample_sequence BETWEEN :cp_sequence_start AND :cp_sequence_end
AND      A.sample_type = 'full_stat'
AND      C.sample_id = A.sample_id
AND      C.common_stat_name_id = B.common_stat_name_id
AND      E.sample_id = A.sample_id
AND      E.common_stat_name_id = D.common_stat_name_id
AND      F.sample_id (+) = A.sample_id
ORDER BY A.sample_sequence;

Again, the data in the tables referenced by this query is not particularly skewed in any way that we could detect. We ran the query on the two test databases with a typical set of bind variable inputs. Here is a summary of what we saw:

 Resources Used by Transaction 
Count Query
Query With Hint Query Without Hint
 Oracle 8i   Oracle 10g   Oracle 8i   Oracle 10g 
CPU seconds 0.01 0.01 0.71 0.40
Logical reads 290 226 20,514 8,256
Physical reads 0 0 0 0

Once again the Oracle 10g test database did a better job than Oracle 8i when no hints were used. Unfortunately, Oracle 10g’s choice of execution plan again was not the best. Adding the hint to the query reduced the CPU time and logical reads by 97%.

As mentioned before, the tables and indexes on the Oracle 10g test database were more compacted. This is why Oracle 10g was able to run the hinted version of the query with fewer logical reads than Oracle 8i. The execution plan for this version of the query was the same on both test databases.

For those who might be interested, both versions of Oracle pushed the joins to the common_stat_names table too late when no hint was used. Although there are hundreds of different statistic names, this query is only interested in two of them. But without the hint, Oracle was retrieving all of the statistics for each sample and discarding the unwanted rows later. Oracle 10g managed to do this faster than Oracle 8i because it used a slightly better join order and appropriately opted for a hash join in one case instead of nested loops.

For those who might be interested, the execution plan on the Oracle 8i test database when no hint was used is as follows:

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     34   NESTED LOOPS
   7957    NESTED LOOPS
     35     NESTED LOOPS
   7957      NESTED LOOPS
     35       NESTED LOOPS (OUTER)
     35        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'SAMPLES'
     35         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SAMPLES_UK1'
                    (UNIQUE)
     34        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                   'SAMPLE_INSTANCE_INFO_PK' (UNIQUE)
   7990       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'SAMPLE_SYSSTATS_PK' (UNIQUE)
   7990      INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                 'COMMON_STAT_NAMES_UK1' (UNIQUE)
   7990     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SAMPLE_SYSSTATS_PK'
                (UNIQUE)
     34    INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_UK1'
               (UNIQUE)

The execution plan on the Oracle 10g test database when no hint was used is as follows:

Rows     Row Source Operation
-------  ---------------------------------------------------
     34  SORT ORDER BY (cr=8256 pr=0 pw=0 time=399893 us)
     34   FILTER  (cr=8256 pr=0 pw=0 time=374969 us)
     34    NESTED LOOPS OUTER (cr=8256 pr=0 pw=0 time=374828 us)
     34     NESTED LOOPS  (cr=8186 pr=0 pw=0 time=372718 us)
   7956      NESTED LOOPS  (cr=194 pr=0 pw=0 time=55490 us)
     34       HASH JOIN  (cr=104 pr=0 pw=0 time=38388 us)
      1        INDEX RANGE SCAN COMMON_STAT_NAMES_PK (cr=2 pr=0 pw=0 time=120 us
)(object id 49622)
   7956        NESTED LOOPS  (cr=102 pr=0 pw=0 time=24098 us)
     34         TABLE ACCESS BY INDEX ROWID SAMPLES (cr=12 pr=0 pw=0 time=1475 u
s)
     34          INDEX RANGE SCAN SAMPLES_UK1 (cr=3 pr=0 pw=0 time=77 us)(object
 id 49503)
   7956         INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=90 pr=0 pw=0 time=25624
us)(object id 49777)
   7956       INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=90 pr=0 pw=0 time=33206 us
)(object id 49777)
     34      INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=7992 pr=0 pw=0 time=3015
71 us)(object id 49623)
     34     INDEX UNIQUE SCAN SAMPLE_INSTANCE_INFO_PK (cr=70 pr=0 pw=0 time=1279
 us)(object id 49746)

SQL That Ran Slower on Oracle 10g

Thankfully, we found only a few queries in our application that ran noticeably slower on Oracle 10g. In one case there was a report, consisting of several dozen queries, that took more than twice as much CPU time and an order of magnitude more logical reads to run on the Oracle 10g test database as compared to the Oracle 8i test database. The problem was entirely attributable to one very innocent looking query:

SELECT  B.value
FROM    common_stat_names A, sample_sysstats B
WHERE   A.name = :p_statname
AND     B.common_stat_name_id = A.common_stat_name_id
AND     B.sample_id = :p_sample_id;

This query retrieves the value of one specified v$sysstat statistic from one specified data sample. (A “sample” in our application is analogous to a “snapshot” in Statspack or Automatic Workload Repository.) This query is so simple that there aren’t many possible execution plans. One way to execute this query would be to look up the specified statistic name in the common_stat_names table and then join to the sample_sysstats table. The access to common_stat_names would be via the leading column of a unique index, and the access to sample_sysstats would be via a unique index. We will call this the “better” execution plan.

Another way to execute this query would be to get all 200-plus rows from the sample_sysstats table that match the specified sample_id, and for each of these look up the row in the common_stat_names table to see if the name matches. We will call this the “unfortunate” execution plan.

In our production and test environments, Oracle 8i chose the better execution plan without a hint. Surprisingly, the Oracle 10g test database chose the unfortunate execution plan. Because the report executes this query more than 50 times, the poor choice of execution plans impacts the report response time in a noticeable way.

Here is the TKPROF output (including execution plan) from the Oracle 8i test database:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 47  (DBRX_OWNER)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS
      2   INDEX RANGE SCAN (object id 8383)
      1   INDEX UNIQUE SCAN (object id 8514)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
      1   NESTED LOOPS
      2    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_PK'
               (UNIQUE)
      1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'SAMPLE_SYSSTATS_PK'
               (UNIQUE)

Here is the corresponding output from the Oracle 10g test database.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        244          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        244          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57  (DBRX_OWNER)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS  (cr=244 pr=0 pw=0 time=893 us)
    234   INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=5 pr=0 pw=0 time=1152 us)(object id 49777)
      1   INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=239 pr=0 pw=0 time=9472 us)(object id 49623)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   NESTED LOOPS
    234    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'SAMPLE_SYSSTATS_PK'
               (INDEX (UNIQUE))
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'COMMON_STAT_NAMES_UK1'
               (INDEX (UNIQUE))

You could say that the difference in CPU time is reported to be 0.01 second, which becomes dubious because of Oracle’s sampling inaccuracies when it comes to CPU time. However, in practice the difference was quite significant because the report ran the query so many times.

Adding an ORDERED hint to the query caused Oracle 10g to choose the better execution plan. This represented a very simple application coding change. For learning purposes we first tried to use the SQL Tuning Advisor with its profiling option in order to get Oracle to use the better execution plan without having to change the application code. There will be more on this in the next section. We should point out that both of the tables in this query are index-organized tables, so perhaps Oracle 10g's query optimizer is IOT-challenged.

SQL Tuning Advisor

Oracle 10g boasts a whole family of advisors that claim to automate many tasks that often take up a lot of a DBA’s time. Of these, the SQL Tuning Advisor struck us as the most intriguing. This is probably because about one third of the work our firm does relates to Oracle performance management. (If functionality built into Oracle 10g is going to render one of my most marketable skills obsolete, then I want to know about it!)

In actuality, our application was running smoothly on Oracle 10g. As mentioned earlier, we had already added hints over the years to queries that Oracle 8i was otherwise unable to run efficiently, and we found very few queries that ran slower in Oracle 10g than Oracle 8i. So, we figured we would give some of our manually optimized queries to the SQL Tuning Advisor without the hints and see what the advisor could come up with.

The API for the SQL Tuning Advisor is very well thought out. You can access it through Enterprise Manager Database Control or by calling the dbms_sqltune PL/SQL package. You can tell the advisor to look at an explicit query that you provide, a query that is currently in the shared SQL area, or a query in the Automatic Workload Repository—among other options. You give the advisor a time limit, and then tell it to start analyzing. When the analysis is complete, you call a PL/SQL function to get a report from the advisor.

The documentation says that in some cases the advisor will come up with a recommended alternate execution plan and you will be able to store off a “profile” in the data dictionary so that whenever this SQL statement runs again in the future, the alternate data collected by the advisor can be taken into account. The intent here is to be able to get Oracle to run a SQL statement more efficiently without having to change the text of the SQL statement in the application. The idea sounds great.

The first query that we fed to the SQL Tuning Advisor is one that we discussed earlier. We removed the hint that we have been using and added a comment so we could easily find the query in the shared SQL area. We told the advisor to take up to ten minutes to analyze the query, but it finished its handiwork in just 14 seconds. Unfortunately, it had nothing useful to say:

SQL> SELECT dbms_sqltune.report_tuning_task
  2         ('Tuning case 47696', 'TEXT', 'ALL', 'ALL')
  3  FROM   SYS.dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47696','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tuning case 47696
Tuning Task ID     : 951
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 01/27/2005 13:42:34
Completed at       : 01/27/2005 13:42:48

-------------------------------------------------------------------------------
SQL ID  : b6c2qka14951z
SQL Text: SELECT   t.test_severity_id severity, i.instance_id,
          NVL (privs.instance_nickname, i.current_instance_name)
          inst_name,
                   ar.first_detected, t.short_description brief_description,
                   l.report_section_id /* tuning case 47696 */
          FROM     customer_user_instance_privs privs, customer_instances i,
          samples s, analysis_results ar, analysis_common_results
          acr,
                   analysis_tests t, lookup_report_40000_formats l
          WHERE    privs.user_id = :cp_user_id
          AND      privs.current_cust_user_priv_level IN ('admin', 'read
          only')
          AND      i.instance_id = privs.instance_id
          AND      privs.user_wishes_to_see = 'y'
          AND      s.instance_id = i.instance_id
          AND      s.sample_type IN ('ping', 'full_stat')
          AND      s.sample_date_db_local_time >
                   (
                   SELECT s2.sample_date_db_local_time -
                            (i.display_events_for_so_many_hrs / 24)
                   FROM   samples s2
                   WHERE  s2.sample_id =
                     rpt_util.most_recent_analyzed_sample (i.instance_id)
                   )
          AND      ar.sample_id = s.sample_id
          AND      acr.analysis_common_result_id =
          ar.analysis_common_result_id
          AND      t.test_id = acr.test_id
          AND      t.alert_type = 'event'
          AND      l.test_id = t.test_id
          ORDER BY severity, first_detected DESC, inst_name

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------

SQL>

The Oracle 10g documentation and Metalink documents make it sound like SQL Tuning Advisor will take the extra time necessary to ponder execution plans that can’t be considered during a regular parse due to time constraints, that the advisor will dynamically sample data and check data distributions more closely than a parse might, and that the extra time enables the advisor to find better execution plans than might be selected during a parse. In this case we know a better execution plan exists (our hint ensures it is being used in production), but the advisor gave up after just 14 seconds.

So we gave the advisor a much simpler query to work with:

SELECT  B.value /* tuning case 47694 */
FROM    common_stat_names A, sample_sysstats B
WHERE   A.name = :p_statname
AND     B.common_stat_name_id = A.common_stat_name_id
AND     B.sample_id = :p_sample_id;

We discussed this query in the previous section as well. As we had mentioned, if Oracle starts at the common_stat_names table, it can do an index lookup on a very selective column and then a unique index lookup on the sample_sysstats table. Our Oracle 8i production and test databases both chose this execution plan, but our Oracle 10g test database for some reason insisted on starting with an index range scan of sample_sysstats. Oracle 10g’s execution plan required 244 logical reads, while the better plan requires only 6. (The difference is significant because this query gets executed extremely frequently in production.)

So we figured this would be an easy task for the SQL Tuning Advisor—the query involves only two tables, one of them extremely small. Unfortunately, the advisor gave up after only one second:

SQL> SELECT dbms_sqltune.report_tuning_task
  2         ('Tuning case 47694', 'TEXT', 'ALL', 'ALL')
  3  FROM   SYS.dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47694','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tuning case 47694
Tuning Task ID     : 950
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 01/27/2005 13:32:02
Completed at       : 01/27/2005 13:32:03

-------------------------------------------------------------------------------
SQL ID  : g5pqqgcuq8pma
SQL Text: SELECT  B.value /* tuning case 47694 */
          FROM    common_stat_names A, sample_sysstats B
          WHERE   A.name = :p_statname
          AND     B.common_stat_name_id = A.common_stat_name_id
          AND     B.sample_id = :p_sample_id

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------

SQL>

This was very discouraging to us. To give the advisor another chance, we rewrote the query with literal values and asked the advisor to evaluate the specific instance of the query:

SQL> DECLARE
  2    v_sql_text        VARCHAR2(1000);
  3    v_task_name       VARCHAR2(100);
  4  BEGIN
  5    v_sql_text := '
  6  SELECT  B.value
  7  FROM    common_stat_names A, sample_sysstats B
  8  WHERE   A.name = ''user commits''
  9  AND     B.common_stat_name_id = A.common_stat_name_id
 10  AND     B.sample_id = 575783';
 11    v_task_name := dbms_sqltune.create_tuning_task
 12                   (
 13                   sql_text=>v_sql_text, user_name=>'DBRX_OWNER',
 14                   scope=>'COMPREHENSIVE', time_limit=>600,
 15                   task_name=>'Tuning case 47725', description=>NULL
 16                   );
 17    dbms_output.put_line ('Created tuning task ' || v_task_name || ' at ' ||
 18                          TO_CHAR (SYSDATE, 'hh24:mi:ss'));
 19    dbms_sqltune.execute_tuning_task (v_task_name);
 20    dbms_output.put_line ('Executed tuning task ' || v_task_name || ' at ' ||
 21                          TO_CHAR (SYSDATE, 'hh24:mi:ss'));
 22  END;
 23  /
Created tuning task Tuning case 47725 at 15:09:12
Executed tuning task Tuning case 47725 at 15:09:13

PL/SQL procedure successfully completed.

SQL>

Unfortunately, the SQL Tuning Advisor still came up empty handed:

SQL> SELECT dbms_sqltune.report_tuning_task
  2         ('Tuning case 47725', 'TEXT', 'ALL', 'ALL')
  3  FROM   SYS.dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47725','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tuning case 47725
Tuning Task ID     : 956
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 01/27/2005 15:09:12
Completed at       : 01/27/2005 15:09:13

-------------------------------------------------------------------------------
SQL ID  : 3kt66qm84bcnz
SQL Text: SELECT  B.value
          FROM    common_stat_names A, sample_sysstats B
          WHERE   A.name = 'user commits'
          AND     B.common_stat_name_id = A.common_stat_name_id
          AND     B.sample_id = 575783

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------

SQL>

At this point we figured we must be doing something wrong, so we wrote a simple query with a very basic flaw in it to see if the SQL Tuning Advisor would catch on. This time, the advisor provided a meaningful report:

SQL> SELECT dbms_sqltune.report_tuning_task
  2         ('Tuning case 47702', 'TEXT', 'ALL', 'ALL')
  3  FROM   SYS.dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNINGCASE47702','TEXT','ALL','ALL')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tuning case 47702
Tuning Task ID     : 952
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 01/27/2005 13:51:45
Completed at       : 01/27/2005 13:51:57

-------------------------------------------------------------------------------
SQL ID  : 9cz4z8xvtxbm1
SQL Text: SELECT instance_id, sample_type, sample_date_db_local_time
                 /* tuning case 47702 */
          FROM   samples
          WHERE  sample_id + 1 = :sample_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate "SAMPLES"."SAMPLE_ID"+1=:B1 used at line ID 1 of the execution
  plan contains an expression on indexed column "SAMPLE_ID". This expression
  prevents the optimizer from selecting indices on table
  "DBRX_OWNER"."SAMPLES".

  Recommendation
  --------------
    Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3806118825

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  4656 |   122K|  2375   (4)| 00:00:29 |
|   1 |  TABLE ACCESS FULL| SAMPLES |  4656 |   122K|  2375   (4)| 00:00:29 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / SAMPLES@SEL$1

-------------------------------------------------------------------------------

SQL>

The sample_id column is the primary key for the samples table. By adding a constant to this column value in the WHERE clause, we have defeated the index and forced Oracle to perform a full table scan. If we were to subtract the constant from the bind variable instead, Oracle would be able to perform a unique index lookup instead of a full table scan.

We had been looking forward to loading data exported from some of our customers’ sites into an Oracle 10g test database and using the SQL Tuning Advisor to see how its recommendations would stack up against the query optimizations that we had developed for these customers. But when the advisor couldn’t help us with our own queries, we decided to devote our time elsewhere.

The concepts behind the SQL Tuning Advisor are quite powerful and offer great potential. But unless the advisor can deliver useful results in realistic situations, it will remain simply an interesting topic of conversation. We should point out that our evaluation of the SQL Tuning Advisor was in no way exhaustive. We gave it a few SQL statements that have emerged in our environment as resource-intensive, and it was unable to help us. But this could be due to an anomaly in our environment or even user error.

Overhead and Infrastructure Resource Usage

As you might expect, Oracle 10g is bigger and more complicated than previous releases of Oracle. The infrastructure and internal machinery of Oracle is more sophisticated than ever before, and with this sophistication comes more resource requirements and overhead.

Our Oracle 8i production database had 2,303 objects in the SYS schema and 100 Mb of storage allocated to segments in the SYSTEM tablespace. Our Oracle 10g database contains over 21,000 SYS objects and started out with nearly 800 Mb allocated in the SYSTEM and SYSAUX tablespaces. This comparison is not strictly apples to apples because we were running Oracle 8i Standard Edition with a minimum of options installed, and our Oracle 10g database is Enterprise Edition with the default set of options installed from the starter database. Still, you get the idea of the order of magnitude increase in complexity.

To give a flavor of what overhead is like in our Oracle 10g environment, we’ll focus our discussion on memory usage, parsing, and resource consumption by Oracle 10g’s automation features.

Memory Usage

We discussed earlier that we had to increase our shared_pool_size parameter setting for Oracle 10g. Our overall SGA size increased from roughly 84 Mb in Oracle 8i to almost 200 Mb in Oracle 10g. (The bulk of the increase was in the shared pool; we did not change the size of the buffer cache.)

In addition to the extra memory required for the SGA, we observed that Oracle server processes used more memory as well. Our application does not have a large number of concurrent sessions, so we use the dedicated server architecture. Some statistics we collected regarding memory usage in Oracle dedicated server processes are as follows:

 Oracle Dedicated Server Processes   Oracle 8i   Oracle 10g 
Resident set size of Oracle process 97 Mb 224 Mb
Total virtual memory size of Oracle process 121 Mb 301 Mb
SGA size according to v$sgastat 84 Mb 197 Mb
Size of the Oracle executable 32 Mb 95 Mb

We measured Oracle process memory usage with the Solaris “prstat” command and verified it with the “top” utility. Both of these tools include the SGA in the virtual memory size for each Oracle process. To avoid double counting, we typically subtract off the SGA size from the virtual memory size. Even after doing so, it is still clear that our Oracle 10g server processes use more memory than our Oracle 8i server processes did. We must point out that we used the 32 bit version of Oracle 8i (in a 64 bit Solaris environment) and that Oracle 10g for Solaris is only available in a 64 bit version.

The transition from 32 bit to 64 bit is likely responsible for part of the increase in memory usage and Oracle executable size. Further, the move from Standard Edition without many options to Enterprise Edition with more options is probably responsible for part of the increase in Oracle executable size. Between the increased SGA size and increased resource usage for each Oracle process, we saw a significant memory increase when we upgraded to Oracle 10g.

Parsing

Hard parses in Oracle have always been expensive. That is one reason DBAs encourage the developers they work with to use bind variables, and perhaps why Oracle added the cursor_sharing parameter in Oracle 8i. In our experience, Oracle 10g raises the bar on the cost of a hard parse.

In an earlier section of this paper we discussed the Loader Daemon in our application, the 7,800 line PL/SQL package that executes roughly 100 SQL statements in the course of reading, parsing, and loading one agent file into the database. Our daemon code has no dynamic SQL with embedded literals, so after the first agent file is processed all parses in Loader Daemon sessions are soft.

To better understand the overhead involved in parsing SQL, we bounced our Oracle 8i test instance and traced the Loader Daemon as it processed its first agent file. As you would expect, just about every statement incurred a hard parse. Then we processed a second agent file and traced the Loader Daemon in a separate trace file. This time every parse was soft. By comparing the two trace files, we could get an approximate idea of how much effort Oracle had expended on the hard parses. Next, we repeated the exercise with the same agent files on our Oracle 10g test database.

As we discussed earlier, the Loader Daemon consumed a very similar amount of resources in our Oracle 8i and 10g test databases when no hard parses were required. However, the difference in resource utilization when hard parses were required was significant. The chart below summarizes the data we collected in this test:

 Resources used by Loader Daemon   Agent File 1 (hard parse)   Agent File 2 (soft parse)   Difference 
 Oracle 8i   Oracle 10g   Oracle 8i   Oracle 10g   Oracle 8i   Oracle 10g 
User SQL statements traced 110 127 110 127 0 0
Internal SQL statements traced 402 977 9 9 393 968
Unique SQL statements traced 139 149 109 110 30 39
Total OCI calls 9,094 10,754 1,800 1,784 7,294 8,970
CPU seconds 7.49 10.94 3.10 3.09 4.39 7.85
Logical reads 26,776 27,373 13,763 12,912 13,013 14,461
Physical reads 695 959 8 13 687 946

We believe that most of the difference in resource usage between the processing of the first and second agent file can be attributed to the effort required by Oracle to hard parse all of the statements. (Some of the difference can be attributed to imperfections in the test design. For example, the processing of the first agent file certainly encountered more misses in the buffer cache.)

If our belief is valid, then the above chart suggests that, in this case, our Oracle 10g test database required almost twice as much CPU time (and more than twice as many internal SQL statements) in order to hard parse the statements in our Loader Daemon code. The price of a hard parse in our environment is paid once—the first time a statement is executed after instance restart—and does not affect all further executions of the statement during the life of the instance. Therefore the increased cost of a hard parse was not a concern to us. However, we felt it was worth noting.

For those who might be interested, the summary section at the bottom of the TKPROF report from the Oracle 8i test database for the first run of the Loader Daemon is as follows:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      502      1.84       1.64          0       1540          1           0
Execute   3251      4.83       8.67        243       2953       9393        2115
Fetch     5341      0.82       3.57        452      12265        624        4268
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9094      7.49      13.88        695      16758      10018        6383

Misses in library cache during parse: 129
Misses in library cache during execute: 3

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       664        0.11          4.67
  file open                                       6        0.00          0.00
  db file scattered read                          5        0.02          0.06
  sort segment request                            1        1.04          1.04

  110  user  SQL statements in session.
  402  internal SQL statements in session.
  512  SQL statements in session.
********************************************************************************
Trace file: 8i_load.trc
Trace file compatibility: 8.00.04
Sort options: execpu
       1  session in tracefile.
     110  user  SQL statements in trace file.
     402  internal SQL statements in trace file.
     512  SQL statements in trace file.
     139  unique SQL statements in trace file.
   70921  lines in trace file.
      33  elapsed seconds in trace file.

The summary section at the bottom of the TKPROF report from the Oracle 10g test database for the first run of the Loader Daemon is as follows:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1103      0.56       0.66          0         68          0           0
Execute   3656      9.21      11.46        346       4907       9247        2115
Fetch     5995      1.17       3.54        613      13151          0        5363
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10754     10.94      15.67        959      18126       9247        7478

Misses in library cache during parse: 149
Misses in library cache during execute: 137

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       514        0.17          3.63
  db file scattered read                         88        0.03          0.86

  127  user  SQL statements in session.
  977  internal SQL statements in session.
 1104  SQL statements in session.
********************************************************************************
Trace file: 10g_load.trc
Trace file compatibility: 10.01.00
Sort options: execpu
       1  session in tracefile.
     127  user  SQL statements in trace file.
     977  internal SQL statements in trace file.
    1104  SQL statements in trace file.
     149  unique SQL statements in trace file.
   72971  lines in trace file.
      37  elapsed seconds in trace file.

The summary section at the bottom of the TKPROF reports for the second run of the Loader Daemon are substantially similar to those provided in the Query Optimization section earlier in this paper and will not be repeated here.

Resources Consumed by Oracle 10g’s Automation Features

It has been said that Oracle 10g could have been called Oracle 10a, with the “a” standing for “automatic.” Indeed, at any given moment there seems to be a beehive of activity within the Oracle 10g instance as various daemons putter around, managing the instance for you or at least collecting information that you will hopefully find useful.

ADDM, ASH, AWR, ASM, ASMM, and EM are more than just an alphabet soup of Oracle 10g features. Each involves backend processes that use up resources on your database server in order to do their job. In particular, we wanted to better understand what sort of overhead was involved. We wanted to know if we would need to add CPU capacity or disk I/O bandwidth to our database server in order to upgrade to Oracle 10g.

Our test environment was set up on a Sun Solaris server that was dedicated to the Oracle 10g upgrade project. The server ran nothing other than the Oracle 8i and 10g test databases being used for this project. Thus, we could start up and shut down Oracle databases and services at will.

We ran the Unix “sar” tool to collect CPU usage statistics. First, we shut down all Oracle processes in order to get a baseline measurement of CPU usage on the database server when absolutely nothing Oracle-related was running. The output from sar was as follows:

SunOS blue 5.8 Generic_108528-27 sun4u    02/02/05

02:00:03    %usr    %sys    %wio   %idle
02:05:03       0       4       0      96
02:10:03       0       4       0      96
02:15:03       0       4       0      96
02:20:03       0       4       0      96
02:25:03       0       4       0      96
02:30:03       0       4       0      96
02:35:03       0       4       0      96
02:40:03       0       4       0      96
02:45:03       0       4       0      96
02:50:03       0       4       0      96
02:55:03       0       4       0      96
03:00:03       0       4       0      96

Average        0       4       0      96

Next we started up the Oracle 10g test database and Enterprise Manager Database Control. We did not start up our application’s backend daemons or the web server that allows users to access the database via the web-based user interface. As far as we know, nothing ran on the database server other than the Oracle 10g test database with no application users connected. Now the output from sar looked like the following:

SunOS blue 5.8 Generic_108528-27 sun4u    02/02/05

13:00:05    %usr    %sys    %wio   %idle
13:05:05       5       6       3      87
13:10:05       3       6       2      89
13:15:05       3       6       4      88
13:20:05       3       6       2      89
13:25:05       4       6       2      89
13:30:05       4       6       2      88
13:35:05       3       6       2      89
13:40:05       3       5       2      89
13:45:05       3       6       2      90
13:50:05       3       6       2      89
13:55:05       4       6       2      89
14:00:05       4       6       2      88

Average        3       6       2      89

Overall idle time went from 96% with no Oracle processes running to 89% with Oracle 10g and Enterprise Manager Database Control running. This suggests that Oracle consumed about 7% of the CPU time on our one-CPU test server. This figure was lower than we expected and did not seem unreasonable to us. It seemed unlikely that we would need to think about adding another CPU to our production database server to support the automation overhead.

For comparison, we collected sar statistics while the Oracle 10g test database was shut down and the Oracle 8i test database was open. The output from sar was as follows:

SunOS blue 5.8 Generic_108528-27 sun4u    02/03/05

02:00:03    %usr    %sys    %wio   %idle
02:05:03       1       4       1      94
02:10:03       0       4       1      95
02:15:03       0       4       0      95
02:20:03       0       4       1      95
02:25:03       0       4       0      95
02:30:03       0       4       0      96
02:35:03       0       4       0      95
02:40:03       0       4       1      96
02:45:03       0       4       0      95
02:50:03       0       4       1      95
02:55:03       0       4       0      95
03:00:03       0       4       0      96

Average        0       4       0      95

Overall idle time went from 96% with no Oracle processes running to 95% with Oracle 8i running. So in our environment it appeared as if Oracle 8i’s background processes used roughly 1% of the server’s CPU time while the database was idle, as compared to Oracle 10g’s 7%.

We don’t really know how accurate this test was, because we don’t know how accurate sar is and there could be a flaw in this testing strategy. Another significant unknown is whether the overhead imposed by Oracle’s background processes is constant or not. It would seem that overhead would be greater while the database is active because, for example, Automatic Workload Repository would have more interesting data to file away in the repository and Active Session History would have more sessions to monitor every second. However, this is just speculation on our part.

We generated an Automatic Workload Repository report (basically the new incarnation of what we used to call a Statspack report) for the time period during which the Oracle 10g database and Enterprise Manager Database Control were running but with no user activity. The sar tool had led us to believe that Oracle 10g’s background processes consume roughly 7% of one CPU’s capacity when the database is idle. In a one hour period, this would amount to 252 CPU seconds.

The Automatic Workload Repository report only accounted for 49 CPU seconds. We aren’t sure where the rest of the CPU time went, but some of it could have gone to Oracle process activity that is not tracked by Oracle. Some of the discrepancy could have been caused by measurement inaccuracy or imperfections in the test design. The report also indicated that the idle database generated about 8 Mb of redo and executed over 27,000 statements during the hour of no user activity.

For those who might be interested, the first portion of the Automatic Workload Repository report is as follows:

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DBRX10G       3211571339 dbrx10g             1 10.1.0.3.0  NO      blue

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       435 02-Feb-05 13:00:58        30      16.4
  End Snap:       436 02-Feb-05 14:00:35        30      16.4
   Elapsed:               59.62 (mins)
   DB Time:                1.04 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:        40M      Std Block Size:         8K
           Shared Pool Size:       144M          Log Buffer:       256K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              2,428.02             12,371.52
              Logical reads:                 40.81                207.95
              Block changes:                 16.62                 84.70
             Physical reads:                  0.31                  1.58
            Physical writes:                  0.90                  4.60
                 User calls:                  3.23                 16.44
                     Parses:                  1.50                  7.63
                Hard parses:                  0.01                  0.03
                      Sorts:                  0.62                  3.13
                     Logons:                  0.04                  0.22
                   Executes:                  7.59                 38.65
               Transactions:                  0.20

  % Blocks changed per Read:   40.73    Recursive Call %:    83.66
 Rollback per transaction %:    8.26       Rows per Sort:    15.17

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.25    In-memory Sort %:  100.00
            Library Hit   %:   99.96        Soft Parse %:   99.66
         Execute to Parse %:   80.26         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   83.40     % Non-Parse CPU:   95.61

 Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   77.28   77.54
    % SQL with executions>1:   92.88   92.96
  % Memory for SQL w/exec>1:   91.35   91.42

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                        % Total
Event                                 Waits    Time (s)   DB Time     Wait Class
------------------------------ ------------ ----------- --------- --------------
CPU time                                             49     78.92
control file parallel write           1,176          27     43.42     System I/O
db file parallel write                1,408          27     42.90     System I/O
log file parallel write               1,865          16     26.20     System I/O
process startup                          73           9     14.12          Other
          -------------------------------------------------------------

Conclusion

Overall we have been pleased with Oracle 10g. It was easier than previous releases to install, the upgrade of our production database went smoothly, and we have not encountered compatibility issues. Although our application did not suddenly begin running faster after the upgrade to Oracle 10g, we expect to be able to get more out of our system once we start leveraging newer features (in a cautious manner with plenty of testing). Very few SQL statements in our application ran perceptibly slower after the upgrade, and they were easy to find and fix.

Oracle 10g imposes more overhead than previous releases of Oracle in many areas ranging from memory usage to hard parses and number of objects in the SYS schema. However, we found these increased needs to be manageable and tolerable—and a fair exchange for the multitude of new features now available to us. We were disappointed by the initial showing of the SQL Tuning Advisor, but we see a lot of potential for improvement in this area.

Again, please remember that each Oracle system is unique and will have its own challenges. In this paper we have only intended to relay our own experiences—we did not intend to make any broad judgments or general claims about Oracle 10g. The only way for you to know how your specific system will fare on Oracle 10g is to try it—in a test environment—and see.

If our experience can help you plan for your own upgrade in any way, then this paper will have achieved its goal.

About the Author

Roger Schrag, OCP, has been an Oracle DBA and application architect for over 15 years. He began his career at Oracle Corporation on the Oracle Financials development team. In 1995, he founded Database Specialists, Inc., a boutique consulting firm specializing in Oracle database technology, remote administration, and performance tuning. Since that time, Roger has focused his expertise in the area of performance optimization. Roger is a frequent speaker at Oracle OpenWorld and the International Oracle Users Group (IOUG) Live conferences, where he has frequently been voted in the Top 10% of speakers. Roger has been an Oracle Masters Class instructor for the IOUG and serves on the board of the Northern California Oracle Users Group (NoCOUG). He can be reached at rschrag@dbspecialists.com.

Still Looking for Help on this Subject?

Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
Complimentary Newsletter
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.


Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com