slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Ten Database Mysteries Chris Lawson Database Specialists, Inc. PowerPoint Presentation
Download Presentation
Ten Database Mysteries Chris Lawson Database Specialists, Inc.

Loading in 2 Seconds...

play fullscreen
1 / 36

Ten Database Mysteries Chris Lawson Database Specialists, Inc. - PowerPoint PPT Presentation

  • Uploaded on

Ten Database Mysteries Chris Lawson Database Specialists, Inc. Focus of Presentation. Explore some “strange” database problems that have baffled some DBAs

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Ten Database Mysteries Chris Lawson Database Specialists, Inc.

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ten Database MysteriesChris LawsonDatabase Specialists,
focus of presentation
Focus of Presentation
  • Explore some “strange” database problems that have baffled some DBAs
  • Most of the mysteries occurred on critical production systems, although some were on development systems
  • ALL of the mysteries were eventually explained
  • Depending on your personal experience, some of these “mysteries” will seem trivial or commonplace; others will indeed seem mysterious
  • Most mysteries have a simple explanation
  • Most mysteries have a simple fix
why spend time on these database mysteries
Why Spend Time on These Database Mysteries?
  • Each DBA has a unique set of experiences and biases. What one DBA thinks is obvious, another will not.
  • An Oracle “detective” is part scientist, part artist. Many solutions require creativity, not just logic.
  • A superior DBA will look for ways to “stretch” and learn ways to handle difficult problems.
  • Without working out difficult problems, you will not advance as a DBA.
  • You will be the “hero” if you encounter a mystery and solve it; remember the solution--you may see it again!
a word about oracle versions
A Word About Oracle Versions
  • This presentation was originally written in 1998
  • Most of these mysteries involve Oracle 7 databases
  • Although some of the mysteries might not apply directly to Oracle 8i, they still offer insight into the problem-solving process
the case of the berserk application


The Case of the Berserk Application

Clue #1: “Big Phone Company” 1997

  • Using HPUX, Oracle
  • Help desk application (Vantive) that connects to Oracle database suddenly goes berserk, creating thousands of connections
  • Program had worked normally for many months
  • DBAs watch helplessly as CPU load driven from 1 to 50
  • As DBAs kill extra processes, more take their place
  • Alert log and recent trace files show nothing unusual
  • DBAs are united in accusing the application as the culprit
berserk application continued
Berserk Application(continued)

Clue #2: “Big Publisher Ltd.” 1998

  • Running Sun Solaris, Oracle
  • Users complain that performance has degraded in recent months
  • Manager states that “something must be wrong with the network”
  • Application is CORIS, a document management/printing application
  • DBA investigates. Discovers that time to connect in SQL*Plus is 30-45 seconds, even though server load is low
  • Connect time is bad whether remote (PC) or directly on server
  • Server load (file I/O and CPU) is generally low
berserk application solution
Berserk Application: Solution
  • OTRACE is the culprit. It is active by default on many 7.3 Oracle versions
  • Excerpt from Oracle Corporation Alert:

“Problems described here can occur when Oracle Trace is not configured and is widely enabled.”

berserk application solution continued
Berserk Application: Solution(continued)

To Detect:

  • Check directory ORACLE_HOME/rdbms/otrace: As size of files process.dat and regid.dat approach 10mb, problems arise

-rw-r--r-- 1 oracle dba 3161424 Jun 05 09:43 process.dat

-rw-r--r-- 1 oracle dba 263808 Jun 05 09:43 regid.dat

  • To correct: simply remove these two files, then issue command otrccref
berserk application solution continued1
Berserk Application: Solution(continued)

To Prevent:

  • Add line to listener.ora for each database (after ORACLE_HOME):


  • Set and export environment variable EPC_DISABLED=TRUE for all users. Put standard profile in /etc directory
  • Restart all databases and restart listener
the case of the reluctant patch


The Case of the Reluctant Patch


  • To correct several bugs, decision is made to upgrade from to (HPUX)
  • Patch is obtained from Oracle and applied to test server. DBA notes that patch ran very quickly and runs again “just to be sure”
  • Bug is now gone on test server
the case of the reluctant patch continued
The Case of the Reluctant Patch(continued)


  • Patch is similarly applied to production server--same operating system and version.
  • Production application is tested, but bug is still there!
  • Another DBA reviews patch file, location, etc. All seem correct.
the case of the reluctant patch solution
The Case of the Reluctant Patch: Solution
  • DBA happens to notice that upon SQL*Plus startup, database is!
  • The patch was really only applied on the second run. This is apparently a quirk in the patch readme file.
  • The command what oracle (then grep for patch#) can be used to determine which patches are applied
the case of the sleazy sql


The Case of the Sleazy SQL
  • “Big Publisher Ltd.” runs an MRP system called “AVALON,” similar to Oracle Manufacturing. Database stores inventory, part information, vendors, etc.
  • Server is ATT3555, running NCR UNIX. Database is Oracle 7.1.6
  • Issue: Users report that certain common operations are very slow
the case of the sleazy sql continued
The Case of the Sleazy SQL(continued)
  • DBA investigates and queries v$sqlarea using:

SELECT sql_text FROM v$sqlarea

WHERE disk_reads/executions > 1000;

  • Query yields troublesome SQL statement, with these stats:



the case of the sleazy sql continued1
The Case of the Sleazy SQL(continued)
  • Statement has been accidentally designed to ensure worst possible performance by making index usage impossible:


NVL (COL_W) = NVL (:1) AND

NVL (COL_X) = NVL (:2) AND

NVL (COL_Y) = NVL (:3) AND

NVL (COL_Z) = NVL (:4);

  • TABLE ABC SIZE = 3mb, about 25,000 rows
  • DBA requests developers to alter statement to eliminate NVL (COL_N) functions
  • DBA advised that no resources available to make change
  • Problem: If code can’t be changed, what can be done to improve performance?
the case of the sleazy sql solution
The Case of the Sleazy SQL:Solution
  • CACHE the table! For example: alter table xyz cache;
  • Normally, blocks from full-table scans are designated for rapid age-out; otherwise, they would “wipe-out” the db cache. Cache of table causes blocks to be treated “normally.”
  • Caching table disables rapid age-out of this table
  • Logical reads will not be reduced, but disk reads approach zero!
  • Note: DB_BLOCK_BUFFERS was slightly increased to compensate for the cached table that now consumes a few megabytes of database cache
the case of the non optimal optimizer


The Case of the Non-Optimal Optimizer
  • A large software company based in “Cedar Shores” has designed a large financials application. Program has been tuned for Rule Based Optimizer.
  • The application runs very well, is a mature product, which is used in thousands of companies around the world.
  • Some users clamor for new features: more horns and whistles
the case of the non optimal optimizer continued
The Case of the Non-Optimal Optimizer(continued)
  • The new development team, afraid to become obsolete, wants to convert to Cost-Based Optimizer (CBO). They also wisely consider that Oracle recommends using CBO on new projects.
  • The older developers, now nearing peaceful retirement, predict disaster if the database is switched to CBO, because the execution plans will change.
  • Issue: How can Optimizer be selectively switched to CBO without changing the code?
the case of the non optimal optimizer solution
The Case of the Non-Optimal Optimizer: Solution
  • Simply substitute a view having a “hint” for the table needing CBO

For example:

rename DEPT to DEPT_ORIG

create view DEPT as select / * + ALL_ROWS */

* from DEPT_ORIG;

  • Now, application will use the VIEW when it looks for DEPT
  • All queries using DEPT will use CBO
  • Note: Upon renaming a table, the indexes and constraints will “move” with the table; however, synonyms and grants may need to be reset.
the case of the forgetful memory


The Case of the Forgetful Memory
  • A new internet-transaction application, ECXpert, and its database have been installed on a Sun Ultra Enterprise Server
  • Sun Solaris 2.5.1, Oracle 7.2.3
  • Application appears to run smoothly for several months, although it occasionally creates large dump files
  • Trace files appear occasionally with ORA-4030 “Out of Process Memory” and recommends “increase process memory quota”
  • Server seems to hang occasionally. Server reboot fixes
  • SysAdmin checks kernel parameters related to memory. All correct and match other servers. Not using any large stored procedures
  • Problem: What is causing memory/hang problems?
the case of the forgetful memory solution
The Case of the Forgetful Memory: Solution
  • DBA checks /tmp (swap area on server) and notes 99% consumed
  • Investigation reveals that application occasionally goes berserk and consumes ENTIRE SWAP area with log files
  • Deletion of log files does not return disk space, since application is still “holding” the files
  • Reboot of server cleaned up /tmp area, thereby correcting problem
  • Suggestion: If memory-related error messages exist, check swap area first
the reluctant index affair


The Reluctant Index Affair


  • DBA asked to analyze and tune Australia manufacturing database. Database is running CBO. One particularly bothersome SQL statement is identified
  • The WHERE condition is perfect for a new index, because of its excellent selectivity
  • Index is quickly created. Table is also analyzed


  • Even though index is a “perfect” solution to the query, a full table scan is used instead
the reluctant index affair solution
The Reluctant Index Affair:Solution
  • The values in the table are very lopsided. Optimizer, however, will assume uniform distribution, which is incorrect in many cases
  • Re-analyze and specify histogram:


  • This creates histogram of 75 “buckets” for each indexed column
  • With these statistics, optimizer will “know” how values are distributed, and will more often make right decision to use an index or not
mystery of the hanging database


Mystery of the Hanging Database
  • At random intervals, a database hangs. No trace files, and nothing unusual in the alert log.
  • When problem occurs, no response to new connections requests; over 1200 existing connections “hang.”
  • Oracle Support is alerted to priority 1 problem; experts across the world investigate for weeks
  • DBA is using OEM Lock Manager tool and notices user who is blocking about 25 other users. The hang occurs soon after.
  • Oracle Australia recommends checking indexes. This suggestion led to the solution.


  • How did index problems hang database?
mystery of the hanging database solution
Mystery of the Hanging Database: Solution
  • Application design flaw
  • There are hundreds of foreign keys in the database; 99% had indexes. A few did not, violating good design practice. When batch program began updates, locking increased rapidly.
  • Without FK index, updates on parent table completely block updates on child (vice versa for 7.1.6)
  • Reference: Server Application Developers Guide
  • Although not admitted as database “bug,” database was overwhelmed by the locks
  • Once indexes on all FK’s created, problems disappeared
the case of the mysterious package


The Case of the Mysterious Package
  • Manufacturing application was installed on a Sun Ultra 3000 server. A small database was created for testing purposes. Oracle version 7.2.3. Shared pool size about 60mb.
  • At first, all went well. Then, seemingly randomly, when the users began to try new features, they would receive a “funny” error message and the application failed.
  • A trace file recommended increasing shared pool


  • How can application fail with such a sizable shared pool?
  • Aside from massive increase in shared pool, what can be done?
the case of the mysterious package solution
The Case of the Mysterious Package: Solution
  • The application uses about 20 massive PL/SQL packages. Some are 5x the SYS.STANDARD package. When a package load is attempted, it will not fit in the shared pool.
  • Memory-intensive packages should be “pinned” or “kept” in shared pool after database startup

EXECUTE SYS.dbms_shared_pool.keep ('OBJECT_NAME');

  • But first, must find the “big” packages (will also list SYS.STANDARD):

SELECT owner, name, sharable_mem FROM v$db_object_cache

WHERE sharable_mem > 100000;

the case of the mysterious package solution continued
The Case of the Mysterious Package: Solution(continued)
  • Example script to find “big” packages and generate SQL script to “pin” them in memory

SELECT 'EXECUTE SYS.dbms_shared_pool.keep('''||


FROM v$db_object_cache

WHERE sharable_mem > 100000




the case of the uncooperative rollback


The Case of the Uncooperative Rollback
  • In mid-afternoon, DBA (running “OEM Top Sessions”) notices many users “ACTIVE” but showing 0 file I/O. Lock Manager reveals one user performing big update blocking all.
  • Culprit tracked down--agrees to be terminated. DBA disconnects session.
  • Locks are not released, but user is “marked for kill.”
  • Very little file I/O activity. Alert log shows very slow switching of redo logs.
  • DBA performs shutdown abort then startup. Database starts up after 2 minutes. All is well.


  • Why did user not rollback and release locks?
the case of the uncooperative rollback solution
The Case of the Uncooperative Rollback: Solution
  • If session is terminated, speed of rollback is proportional to init.ora parameter CLEANUP_ROLLBACK_ENTRIES
  • If default value (20) is used, rollback of killed session can take 50x time of original update. Alternatively, shutdown abort/startup cleans up database much faster.
  • Rationale: Parameter prevents rollback of one user from hogging all the resources on a busy system
  • Solution: Increase parameter to reduce rollback time (since shutdown abort is usually not an option)
the singular case of the phantom users


The Singular Case of the Phantom Users
  • A manufacturing database in Sydney, Australia, needed performance tuning. SQL tuning on US databases had yielded good results.
  • The table v$sqlarea was queried to find resource-intensive SQL statements. Several commonly-run statements were isolated. Performance was improved through index additions.
  • Statistics were re-examined over the next 4 hours, in order to confirm improvements.
  • However, repeated looks at execution statistics showed no change.
  • DBA puzzles over enigma for several hours, then realizes that NOTHING is WRONG! What did he finally realize?
the singular case of the phantom users solution
The Singular Case of the Phantom Users: Solution
  • Nothing is wrong because the users were still asleep. It was only 5:00am in Sydney!
the case of the slow physician bonus mystery


The Case of the Slow Physician(Bonus Mystery)
  • Health application is experiencing slow run times. Analysis shows following SQL statement causing 3000 disk reads
  • COSIGN_VIEW is a join of 2 tables (DOCS + COSIGN), joined on patient_id (indexed)
  • Search criteria ‘DR. MCKENZIE’ is very selective; thus, nested loop IS expected choice for optimizer, with DOCS as Driving table.


the case of the slow physician continued
The Case of the Slow Physician(continued)
  • Even with index on DOCS(doctor_id), optimizer (CBO) insists on using hash-join, and refuses to ever use index on doctor_id!
  • Repeated analyze table commands do not correct
  • Substituting query not using a view yields expected NL result


  • Why does using the view cause optimizer to make the “wrong” choice?
the case of the slow physician solution
The Case of the Slow Physician:Solution
  • Everything seemed to point to a problem with the view, because all worked normally as long as the view was excluded
  • Finally, DBA compared view definition (in OEM Schema Manager) to definition seen using “describe table” syntax. The columns did NOT match!
  • Examining the object-create script revealed that the view switched column names, so that column DOC_ID in the view did NOT match DOC_ID in the table!
  • Once the correct column was indexed, a Nested-Loop Join was selected by the optimizer
contact information
Contact Information

Chris Lawson

Database Specialists, Inc.

388 Market Street, Suite 400

San Francisco, CA 94111