Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






                    The Best Oracle Resource on the Web

Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

Oracle 911 - Emergency Oracle Support

by Donald K. Burleson

I work in a worldwide Oracle emergency support center ( where I receive calls from panicked clients all over the world, each experiencing a serious loss of performance on their mission-critical Oracle databases. I have worked on systems that shut down entire factories due to poor performance, leaving thousands of workers sitting on their hands waiting for me to do something magical to restart their assembly line. I have worked with hospital image delivery systems when patients were literally in surgery, with their doctors waiting for the information they needed to save lives. I have worked on financial systems for which downtime is measured in tens of thousands of dollars per minute.

Oracle Silver Bullets

Contrary to the pontifications of theoreticians and ivory-tower academics, there are many silver bullets for Oracle performance tuning. By silver bullet, I mean a small set of commands that quickly relieves an acute performance bottleneck. Some of these techniques of just-in-time tuning have been codified in Oracle10g with the Automatic Memory Management (AMM) facility, in which the SGA regions are changed dynamically to meet changing demands in processing.

Let's take a close look at the real-world silver bullets that I have encountered over the past 12 years of emergency Oracle support. The following stories are true, verifiable accounts (I have witnesses!) of Oracle databases in which a fast fix was used to relieve an acute performance problem:

  • Fix missing CBO statistics
  • Replace an obsolete statistics gathering method
  • Initialize missing Oracle instance parameters
  • Add missing indexes
  • Implement cursor_sharing=force
  • Implement the KEEP pool for small-table full-scanned tables
  • Change the CBO optimizer parameters
  • Add additional SGA RAM
  • Employ materialized views
  • Create bitmap indexes
  • Add freelists
  • Windows Oracle issues

To those scientist DBAs who demand "proofs" that these methods worked, you are not going to find them in this article. Most of the production systems that I tune have hundreds of segments and thousands of concurrent users. Trying to test a hypothesis on a large running database is like trying to tune your car while it's flying down the freeway at 75 miles per hour. It is impossible to reproduce the conditions of a complex performance breakdown, and the emergency support DBA is forced to rely on experience and anecdotal evidence to guide their actions. (If you want proof that these techniques work, just send me an email. I have lots of witnesses!) 

Anyway, let's examine each of these silver bullets and see how a well-placed silver-bullet can save the day.

Fix Missing CBO Statistics

The call came in from a client in Florida who had just moved their system into production and was experiencing a serious performance problem. Upon inspection, we found optimizer_mode=choose and only one table with statistics. The DBA told me that she was running cost-based and she seemed totally unaware of the requirement to analyze the schema for CBO statistics. Here is how I recall the conversation:

DB: "How are you collecting statistics?"
DBA: "We have BMC Patrol"
DB: "No, No, how are you getting SQL optimizer statistics?"
DBA: "The Oracle sales rep said that the CBO was intelligent, so I assumed it was getting its own statistics."
You know, in a way, she was right. The problem started when she wanted to know the average row length for a table. She did a Google search and discovered that it was in the dba_tables.avg_row_len column. When she found it null, she went to MOSC and learned that an analyze table command would fill-in the avg_row_len column.

As we know, when using optimizer_mode=choose with only one table analyzed, any SQL that touches the table will be optimized as a cost-based query, and the CBO will dynamically estimate statistics for all tables with missing statistics. In this case, a multi-step silver bullet did the trick:

alter table customer delete statistics;           
exec dbms_stats ();
The system immediately returned to an acceptable performance level, and the DBA learned about the importance of providing complete and timely statistics for the CBO using the dbms_stats utility.

Repair Obsolete CBO Statistics Gathering

This shop called from Australia complaining about a serious degradation in SQL performance after implementing partitioned tablespaces in a 16-CPU Solaris 64-bit Oracle 9.0.4 system.  They said that they thoroughly tested the change in their development and QA instances, and they could not understand why they system was grinding to a halt.

Upon inspection, it turned out that they were using analyze table and analyze index commands to gather their CBO statistics. As we may know, the dbms_stats utility gathers partition-wise statistics. There was not time to pull a deep-sample collection, so a dbms_stats was issued with a 10 percent sample size. Note that I parallelized it with 15 parallel processes to speed-up the statistics collection:

exec dbms_stats.gather_schema_stats( -
   ownname          => 'SAPR4', -
   options          => 'GATHER AUTO', -
   estimate_percent => 10, -
   method_opt       => 'for all columns size repeat', -
   degree           => 15 -

This took less than 30 minutes and the improved CBO statistics tripled the performance of the entire database.

Adding Missing Oracle Indexes

An Oracle Financial application shop in New York called and said that their performance degraded as more data was entered into the tables. A quick check of v$sql_plan using my plan9i.sql script looked like this:

                     Full table scans and counts
OWNER      NAME                      NUM_ROWS  C  K   BLOCKS  NBR_FTS    
---------- ------------------------  --------  -  - --------  -------
APPLSYS    FND_CONC_RELEASE_DISJS       14,293 N       4,293  498,864    
APPLSYS    FND_CONC_RELEASE_PERIODS    384,173 N      67,915  134,864    
DONALD     PERSON_LOGON_ID          18,263,390 N     634,272   96,212
DONALD     SITE_AMDMNT               2,371,232 N      51,020   50,719
DONALD     CLIN_PTCL_VIS_MAP        23,123,384 N     986,395   11,273         

Here we see a huge number of large-table, full-table scans. A quick look into v$sql revealed that the rows returned by each query was small, and a common WHERE clause for many queries looked like this:

WHERE customer_status = :v1' and customer_age > :v2;
A quick creation of a concatenated index on customer_status and customer_age resulted in a 50x performance improvement and reduced disk I/O by over 600 percent.

In another memorable case on an 8.1.6 database, my access.sql script revealed suspect large-table, full-table scans:

                  Full table scans and counts
OWNER      NAME                     NUM_ROWS    C K   BLOCKS  NBR_FTS
---------- -------------------- ------------    - - -------- --------
APPLSYS    FND_CONC_RELEASE_DISJS     1,293,292 N K  65,282   498,864
APPLSYS    FND_CONC_RELEASE_PERIODS   4,373,362 N K  62,282   122,764
APPLSYS    FND_CONC_RELEASE_STATES      974.193 N K   9,204    98,122
APPLSYS    FND_CONC_PP_ACTIONS          715,021 N     6,309    52,036
APPLSYS    FND_CONC_REL_CONJ_MEMBER      95,292 N K   4,409    23,122
The DBA had created an index on the order_date column and was surprised that their order_date index was not being used, primarily because their boss was too cheap to pay for him to attend an Oracle8i new features class. Creating the function-based index on to_char(order_date,'MON-DD')  resulted in an immediate 5x performance improvement.
Changing CBO Optimizer Parameters

Another emergency situation involved an Oracle 9.0.2 client from Phoenix who called complaining about steadily degrading performance. A quick look into v$sql_plan view using my plan9i.sql script revealed loads of suspected unnecessary large-table, full-table scans.

In this case, the top SQL was extracted from v$sql and timed as-is and with an index hint. The query with the index hint ran almost 20x faster, but it was unclear why the CBO was not choosing the index. This was a production emergency, and I did not have the luxury of investigating the root cause of the CBO issue. I had to act fast, so I ran a script against v$bh and user_indexes and discovered that approximately 65 percent of the indexes were currently inside the data buffer cache.  Based on similar systems, I decided to lower optimizer_index_cost_adj to a value of 20, hopefully forcing the CBO to lower the relative costs of index access.


If you are Oracle9i and beyond, you can dynamically alter some parameters:

alter system set optimizer_index_cost_adj=20 scope = pfile;
This quick fix changed the execution plans for over 350 SQL statements and cut by half overall system response time. The client was elated, and I was then able to take my time and investigate the root cause of the problem.

Implement the KEEP Pool for Small-table, Full-scanned Tables

I worked on a database just last month in New Zealand (running that had a 16 CPU Solaris server with 8GB of RAM. The complaint was that performance had been degrading since the last production change. A STATSPACK top five timed events report showed that over 80 percent of system waits related to "db file scattered reads." A quick review of v$sql_plan using plan9i.sql showed lots of small-table, full-table scans, with many of the table not assigned to the KEEP pool (as denoted by the "K" column in the listing below):

                  Full table scans and counts
OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS
---------- -------------------- ------------ - - -------- --------
APPLSYS    FND_CONC_RELEASE_DISJS         39 N         44   98,864
APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K       21   78,232
APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   66,864
APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036
APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K      322   50,174
APPLSYS    FND_FILE_TEMP                   0 N        544   48,611
APPLSYS    FND_RUN_REQUESTS               99 N         98   48,606
INV        MTL_PARAMETERS                  6 N K       16   21,478
APPLSYS    FND_PRODUCT_GROUPS              1 N         23   12,555
APPLSYS    FND_CONCURRENT_QUEUES_TL       13 N K       10   12,257
AP         AP_SYSTEM_PARAMETERS_ALL        1 N K        6    4,521 

As you may know, rows fetched into the db_cache_size from full-table scans are not pinged to the Most-Recently-Used (MRU) end of the data buffer. Running my buf_blocks.sql script confirmed that the FTS blocks were falling off the least-recently-used end of the buffer, and had to be frequently reloaded into the buffer.

                           Contents of Data Buffers
                                             Number of Percentage             
                                             Blocks in of object
              Object           Object        Buffer    Buffer  Buffer    Block
Owner        Name              Type          Cache     Blocks  Pool       Size
------------ -------------------------- ----------- ---------- ------- -------
DW01         WORKORDER         TAB PART      94,856          6 DEFAULT   8,192
DW01         HOUSE             TAB PART      50,674          7 DEFAULT  16,384
ODSA         WORKORDER         TABLE         28,481          2 DEFAULT  16,384
DW01         SUBSCRIBER        TAB PART      23,237          3 DEFAULT   4,096
ODS          WORKORDER         TABLE         19,926          1 DEFAULT   8,192
DW01         WRKR_ACCT_IDX     INDEX          8,525          5 DEFAULT  16,384
DW01         SUSC_SVCC_IDX     INDEX          8,453         38 KEEP     32,768

In this case, I ran my buf_keep_pool.sql script to reassign all tables that experienced small-table, full-table scans into the KEEP pool. The output looks like this, and can be fed directly into SQL*Plus:

alter TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);
alter TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);
alter TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);
alter TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);
alter INDEX WIP.WIP_REQUIREMENT_OPERATIONS_N3 storage (buffer_pool keep);

With more efficient buffer caching, I fixed the problem in less than one hour and overall database performance more than doubled.

Add Additional SGA RAM

One of the most common silver bullets are databases that have a "working set" of frequently referenced data that cannot fit into the data buffer cache. This used to be a huge problem for the 32-bit Oracle server in which the total SGA size was difficult to grow beyond 1.7 gig without special tricks like AWE and NUMA. However, I still routinely see databases on dedicated servers with 8GB RAM with an SGA size less than 500MB. A quick increase in db_block_buffers or db_cache_size, and performance improves dramatically.

Employ Materialized Views

Once there was a call from a point-of-sale data warehouse in Germany. The IT manager spoke very little English and most of the conversation was done using Babelfish. The system was largely read-only with a short batch window for nightly updates. Once I connected, I immediately noticed that virtually every query in the system was performing a sum() or avg() function against several key tables. The v$sql_plan view (via plan9i.sql) showed loads of very-large-table, full-table scans, and the system was crippled with "db file scattered read" waits.

I was easily able to help by creating three materialized views and employing query rewrite to reduce physical disk I/O by over 2,000 percent; this improved performance by more than 30x a real silver bullet!

Implement Bitmap Indexes

I was called upon to troubleshoot and fix a State Police query system that was experiencing slow query performance. The system was read-only except for a 30-minute window at night for data loading. Upon inspection of the SQL, I noted complex combinational WHERE clauses:

WHERE color='BLU' and make='CHEVY' and year=1997 and doors=2;

The distinct values for each of these columns were less than 200, and concatenated indexes were employed. Replacing the b-tree indexes with bitmap indexes resulted in a stunning performance improvement for the entire system, taking queries from 3 seconds down to under one-tenth of a second.

Add Freelists

A client called from Michigan once with a complaint that the company order processing center was unable keep up with adding new orders into Oracle. The client had just expanded its telephone order processing department and had doubled the order processing staff to meet a surge in market interest. The VP was frantic, saying that 400 order-entry clerks were getting 30-second response time and they were forced to manually write-down order information.

I checked v$session and found 450 connected users, and a quick review of v$sql revealed that at virtually all the DML were inserts into a customer_order table. The top timed event was buffer busy wait and it was clear that there were enqueues on the segment header blocks for the table and its indexes.

The "proper" fix for this issue is to create a new tablespace for the table and index using Automatic Segment Space Management (ASSM), also known as bitmap freelists. I could then reorganize the table online with the dbms_redefinition utility and alter index cust_pk rebuild the index into the new tablespace. However, it would take me several hours to build and execute the jobs and the VP said that he was losing over $500 per minute.

The system was on release, so I was able to immediately relieve the segment header contention with these commands:

alter table customer_order storage (freelists 5);
alter index cust_pk        storage (freelists 5);
(Note:  I did not know the length of the enqueues on the segment header, so I added the additional freelists, one at a time, until the buffer busy waits disappeared).

He additional freelists did the trick and the segment header contention disappeared. However, I knew that this was only a stop-gap fix and as soon as they ran their weekly purge (a single process) that only one of the five freelists would get the released blocks, causing the table to extend unnecessarily.  

From MOSC note: 1029850.6:

As can be seen from the algorithms above, using multiple free lists may cause some empty blocks to go unused, causing the segment to extend.  If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used. 


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.