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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle 911
Emergency Oracle Support

Oracle Tips by Burleson

I work in a worldwide Oracle emergency support center (www.remote-dba.net) 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.

Working late nights as an emergency support DBA comes with huge stress levels. Most of these databases are brand new to me, and I have only a few minutes to assess the situation and devise a plan to quickly relieve their bottleneck. I work with emergency Oracle performance problems every week, and I only get called when the easy remedies have already been tried and discarded. When I ask why their DBA is not helping, I generally hear one of the following:

  • We outsourced our DBA staff to India. They can?t figure it out and suggested that we call you.
  • Our DBA is on a three-week hiking vacation in the Rocky Mountains.
  • Our DBA quit six months ago and we haven?t had time to find a replacement.
  • We hired a guy and got him OCP certified, but we don?t think he knows what he is doing.
  • We called MOSC Support and we don?t understand their instructions. They said that we could call you.
When a production database is in crisis, costs are never an issue, but minimizing downtime is of the essence. The clients demand a fast fix, and this often requires unconventional methods.

Unconventional Methods of Emergency Support

Unlike academic and scientific Oracle DBAs who demand proofs and reproducible results before making an Oracle change, the emergency Oracle DBA has no such luxury. The emergency DBA must use every weapon at their disposal to get the client running as quickly as possible. These unconventional methods are almost always driven by the client who does not appreciate the benefits of an elegant, long-term fix for the root cause of the problem.

Clients are impatient, and they often insist on symptom-treating, stop-gap remedies that are neither elegant nor comprehensive. In many cases, the client does not want to hear the time-consuming tasks that are required to address the root cause of the problem.

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:

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.

Initialize Missing Oracle Instance Parameters

I got a call from a client in California who said that their performance was getting progressively worse as more customers accessed the Oracle database. Upon inspection I discovered that their db_cache_size parameter was not present in the init.ora file. A quick instance bounce (to re-set sga_max_size and db_cache_size) resulted in a 400 percent performance improvement.

In another memorable case, I received a call from a data warehouse client in California who complained that their performance degraded as the database grew. A quick look revealed that the sort_area_size parameter was missing and defaulting to a tiny value. Again, a change of sort_area_size=1048575, a quick bounce of the instance, and overall database performance improved by more than 50 percent.

Adding Missing 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,212DONALD     SITE_AMDMNT               2,371,232 N      51,020   50,719DONALD     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,864APPLSYS    FND_CONC_RELEASE_PERIODS   4,373,362 N K  62,282   122,764APPLSYS    FND_CONC_RELEASE_STATES      974.193 N K   9,204    98,122APPLSYS    FND_CONC_PP_ACTIONS          715,021 N     6,309    52,036APPLSYS    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 Oracle8 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.

Important Note:  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats

As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important. 

Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

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.

optimizer_index_cost_adj=20
optimizer_index_caching=65

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 cursor_sharing=force

I worked on a 9.0.2.4 database in Toronto, Canada, about which the end users had complaints of poor performance right after a new manufacturing plant was added to the existing database. A quick look at the STATSPACK top five timed events looked like this:

Top 5 Wait Events~~~~~~~~~~~~~~~~~                                             Wait     % TotalEvent                                               Waits  Time (cs)   Wt Time-------------------------------------------- ------------ ------------ -------enqueue                                            25,901      479,654   46.71db file scattered read                         10,579,442      197,205   29.20db file sequential read                           724,325      196,583    9.14latch free                                      1,150,979       51,084    4.97log file parallel write                           148,932       39,822    3.88

My first look was into the SQL section of the STATSPACK report, where I noted that almost all of the SQL used ?literals? in the WHERE clause of all queries.

WHERE customer_state = "Alabama" and customer_type = "REDNECK";

This was a vendor package with dynamically generated SQL, so cursor_sharing was the only fast solution. Setting cursor_sharing=force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

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

I worked on a database just last month in New Zealand (running 9.2.0.4) 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,864APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K       21   78,232APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   66,864APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K      322   50,174APPLSYS    FND_FILE_TEMP                   0 N        544   48,611APPLSYS    FND_RUN_REQUESTS               99 N         98   48,606INV        MTL_PARAMETERS                  6 N K       16   21,478APPLSYS    FND_PRODUCT_GROUPS              1 N         23   12,555APPLSYS    FND_CONCURRENT_QUEUES_TL       13 N K       10   12,257AP         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    BlockOwner        Name              Type          Cache     Blocks  Pool       Size------------ -------------------------- ----------- ---------- ------- -------DW01         WORKORDER         TAB PART      94,856          6 DEFAULT   8,192DW01         HOUSE             TAB PART      50,674          7 DEFAULT  16,384ODSA         WORKORDER         TABLE         28,481          2 DEFAULT  16,384DW01         SUBSCRIBER        TAB PART      23,237          3 DEFAULT   4,096ODS          WORKORDER         TABLE         19,926          1 DEFAULT   8,192DW01         WRKR_ACCT_IDX     INDEX          8,525          5 DEFAULT  16,384DW01         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=2015 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 9.2.0.4, so I was able to immediately relieve the segment header contention with these commands:

alter table customer_order freelists 5;
alter index cust_pk 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.  

Windows Oracle Issues

Windows Oracle databases are always the most fun to tune because they are often implemented by someone with a very limited knowledge of Oracle. The following are my favorite Windows Oracle Silver Bullets.

  • Norton Anti-Virus ? I got a call from a new client in England who said that their database had slowed to the point where sub-second queries were taking 15 minutes. A review of a STATSPACK report shows giant waits of up to 10 seconds on read I/O. A review of the external Windows environment revealed that a well-intentioned support person was told to install Norton Antivirus on all Windows servers. Upon every block read, Norton was conducting a virus check!
     
  • A really cool screen saver ? Another case involved a Windows Oracle system that was experiencing sporadic CPU shortages, periods when over half of the PU was being consumed by some external process. Because I was dialed in, I could not see the obvious. An on-site person informed me that the screen saver was called 3D Flowerbox. This was a hugely CPU intensive screen saver that performed thousands of calculations per second to generate the cool display.

Conclusion

For adrenaline junkies, being an emergency Oracle support DBA is great fun. As I have illustrated, this kind of support often requires a unique set of techniques:

  • Fix the symptom first: You can come back and address the root cause later.
     
  • Time is of the essence: When you must provide a quick fix, instance-wide adjustments are often your best hope.
     
  • Be creative: Traditional (i.e., time consuming) tuning methods don't apply in an emergency.




 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational