February 2008

February 2008
Ask the Specialists
Child Cursors

This month’s question comes to us from Ravi Kulkarni of Phoenix, AZ:

“I’ve noticed a lot of child cursors in one of my databases. What exactly are they, what is causing them, why are they bad, and what can I do about them?”

Terry Sutton of the Database Specialists team responds:

Whenever an SQL statement is submitted for execution, Oracle computes its “signature” (a.k.a. “hash value”) and looks for stored query execution plans with that signature in the library cache. If a stored plan is not found, Oracle creates a plan and stores it in the library cache for potential future reuse. If a stored plan is found, Oracle must verify that that there are no impediments to reusing it and, if an impediment is found, Oracle must create another execution plan a.k.a. a “child cursor.” For example, if the base tables referenced by synonyms in an SQL statement are not the same for all users, a single execution plan will not work for all users.

There is a long list of potential impediments that could prevent a query execution plan from being reused. The V$SQL_SHARED_CURSOR view lists the reasons why each child cursor was generated; a value of “Y” in any column indicates that the corresponding test failed. Child cursors are also commonly encountered when the cursor_sharing=similar setting is used. They can also be expected when using Oracle Database 11g which uses an adaptive approach to query optimization.

Child cursors take up space and increase the fragmentation of the shared pool; this could lead to ORA-04031 errors. They can also lead to contention for the library cache latch.

The V$SQL_SHARED_CURSOR view is a good place to start your investigation. If all columns contain a value of “N,” you may be encountering an Oracle bug and should research the problem using the Metalink knowledgebase or get advice from Oracle Support.

I hope this short answer helps you. Best of luck to you.

You are invited to send your questions about Oracle Database to askdbspecialists@dbspecialists.com. Please include your name and telephone number in your message.
Performance Tuning by Example
Introduction

Every month, beginning next month, The Specialist will present a fresh installment of the Performance Tuning by Example series of case studies created by the Database Specialists team. Here is an introduction to the systematic methodology used in each case study.

In his most excellent book The Art and Science of Oracle Performance Tuning, Chris Lawson, a past member of the Database Specialists team, outlined a systematic five-step method for solving any performance tuning problem. Oracle Database versions may change and software tools may change but the five steps always remain the same. A problem may be simple and require only a few minutes of your time or it may be tremendously complex and require weeks of your time but the five steps always remain the same.
  1. The first step is to define the problem. This requires patient listening, skillful questioning, and even careful observation. “The database is slow” is an example of a poorly defined problem. “The database is slow between 10 a.m. and 11 a.m. every day” is more precise. “This report now takes twice as long as it used to take only a week ago” is another example of a precisely defined problem.
  2. The second step is to investigate the problem and collect as much pertinent evidence as possible. Examples include Statspack reports, “sar” data, and session traces.
  3. The third step is to analyze the data collected in the second step and isolate the cause of the performance problem. This is often the most challenging part of the performance tuning exercise.
  4. The fourth step is to solve the problem by creating a solution that addresses the root cause. Solutions are not always obvious and, therefore, this part of the exercise might require a great deal of ingenuity and creativity.
  5. The fifth step is to implement the solution in a safe and controlled manner. An appropriate level of testing should be conducted. “Before” and “after” measurements should be obtained if possible in order to quantify the performance improvement.

No matter how simple or complex the performance problem, the steps leading to its solution are always the same. The original paper written by Chris Lawson can be downloaded from the Database Specialists website.

Do it Right
Use Best Practices When Building Database Systems

If you build it, they will come. If you build it right, it will run. Rich Headrick of the Database Specialists team was asked to rebuild a highly unstable RAC database system. Once rebuilt using best practices (including dual NICs for the private interface), the RAC database enjoyed an unprecedented 180 days of uptime and was still up at time of writing. If you need help with your RAC database system, click here to arrange a free consultation with a senior Database Specialists team member.

Script of the Month
Unindexed Foreign Keys 

In his excellent book Expert Oracle Database Architecture, Tom Kyte says: “I sometimes wish I had a dollar for every time I was able to solve the insolvable hanging issue by simply running the query to detect unindexed foreign keys and suggesting that we index the one causing the problem – I would be very rich.”

Read Tom’s explanation why foreign keys should be indexed, then follow the provided link to the script itself.
Conference Roundup
RMOUG Training Days 2008

RMOUG Training Days is a jam-packed annual event organized in February every year by the Rocky Mountain Oracle Users Group. It features almost one hundred technical presentations by Oracle experts from around the globe and attracts nearly one thousand attendees.

For the fifth year in a row, the Database Specialists team is represented on the technical agenda. Iggy Fernandez will deliver two presentations: Oracle Annoyances for Geeks: Deadlocks and Livelocks and Optimal Query Execution Plans – An Impossible Dream?

RMOUG Training Days 2008 will be held on February 13 and February 14; more information can be found on the RMOUG website. Several members of the Database Specialists team will be in attendance and would enjoy meeting you there.

NoCOUG Winter Conference

The Northern California Oracle Users Group will hold its Winter Conference, a whole day event, on February 19, 2008 at the Oracle Conference Center in Redwood Shores. The keynote address will be delivered by Juan Loaiza, a twenty-year Oracle veteran who knows more about Oracle’s vision and strategy than most people; the complete agenda is posted on the NoCOUG website.

Rich Headrick of the Database Specialists team reports that he was able to able to make dramatic performance improvements in a RAC database system by using the suggestions made by a speaker at a past NoCOUG conference. An annual NoCOUG membership costs only $80 and includes free admission to four whole day conferences and four issues of the NoCOUG Journal. Walk-ins are welcome at the conferences; pay just $40 for the day (or purchase an annual membership) at the registration desk.

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Visit http://dbspecialists.wpengine.com for no-cost resources, white papers, conference presentations and handy scripts. We can help increase your uptime, improve performance, minimize risk, and reduce costs. Click here to arrange a free consultation with a senior Database Specialists team member.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

Leave a Reply

Your email address will not be published. Required fields are marked *