When you happen across a table with lots of indexes, what’s the first thought that crosses your mind? 

Okay, having gotten that bit of venting out of the way, what’s the second thought?  Are all of these indexes actually used?  That’s a legitimate question and one that comes up from time to time in our remote Oracle DBA practice.  Unneeded indexes not only waste space but also introduce unnecessary overhead to table updates.  So let’s talk about what can and should be done about it.

Index usage monitoring is a fairly common subject of chatter in the various Oracle-related discussion boards and blogs.  There are scripts available, some free and some for a price, which boast the ability to identify unused indexes easily enough and allow you to just drop them, thereby releasing tons of space and cutting update overhead dramatically.  Our take on the matter – not so fast!

Determining whether an index is a necessary part of the database is not as easy as turning on monitoring and running a few scripts.  Sure, turning on monitoring is easily done with the ALTER INDEX statement and then checking the V$OBJECT_USAGE view (as the index owner) will tell you whether the index has been accessed since monitoring was turned on.  But there are a couple of gotchas with that approach:

• In versions of 10g prior to 10.2.0.5 and 11gR1 gathering stats on a table with the CASCADE option set will mark an index as “used” in the V$OBJECT_USAGE table if monitoring is on.  See Bug # 430034.1 in Metalink.
• Indexes which support foreign keys are not flagged as used when a relevant update occurs even though the index was indeed used behind the scenes to avoid a table lock.

Thus indexes might be flagged as used even though they are not necessary and may not be flagged when they are indeed a vital part of the database.

Some alternatives exist to the V$OBJECT_USAGE view, such as querying AWR or Statspack data to determine whether an index was part of an execution plan.  But this approach has a separate downfall in that an index might have been incorrectly used by the optimizer.  Perhaps the index was used but is its existence justified?  Is it really necessary?  You might end up dropping an index that should have been used in favor of one that should not have been.

So what’s the point?  The point is that sometimes there is no good shortcut for diligence.  Let us keep the cart behind the horses and talk to the data modelers and application developers about whether indexes are necessary.  It is easy enough to identify redundant indexes such as those which are subsets of others on the same table so we could certainly make recommendations, but throwing the application into a tizzy by dropping indexes we declared useless through the running of our scripts is a good way to insure taking up residence in the proverbial dog house.

As Oracle consultants we often get caught up in the esoteric areas of performance, such as contention, complex execution plans, and obscure parameters.  But we forget that sometimes it’s the little things that count, many of which we learned in the DBA101 phase of our careers.

I was reminded of this recently at one of our remote DBA clients.  The customer was having issues with flashback queries in a RAC cluster.  We discovered that the undo_retention setting on one instance was much less than on the others.  At first we couldn’t figure out how this was happening; the settings were correct in the spfile.  Then we looked in the $ORACLE_HOME/dbs directory of the instance and saw that there was a pfile (aka, an init.ora file) in the directory, with different settings for undo_retention.

As is typical in RAC installations, there was supposed to be a pfile whose only contents were a pointer to the spfile (because the spfile was in ASM).  But what had happened was that someone had executed the command “create pfile from spfile” in order to read or copy the spfile settings.  Of course, this overwrote the pfile which pointed to the spfile.  It didn’t cause an issue until the next time the instance was restarted; when that was done the instance then had a setting for undo_retention from an earlier time.

For this reason, my personal best practice for creating an editable pfile from an spfile is to put it into the /tmp directory–

 create pfile='/tmp/something.ora' from spfile;

That way I can read it, edit it for whetever use (such as making a clone), or whatever I want without risking having it used unexpectedly at some time in the future.  Others use

 create pfile='?/dbs/init_<not_the_sid>.ora' from spfile; 

There are many options, but the point is to not use the default which results when you don’t specify a pfile name.

Of course, one could say “the DBA should always check and clean up everything after their tasks”, which is true.  But avoiding the need for the cleanup by following a standard practice is a more thorough solution, in my opinion.

Not everything we do as DBAs is complex, convoluted, or esoteric.  Taking care of the little things can be as important as being able to optimize a 300 line query.

As an implementer of commercial and open source database monitoring tools I find DatabaseRX has capabilities that clearly differentiate it from the rest of the market:

• Scalability - hundreds of databases and instances can be added with trivial monitoring overhead, due in part to a highly normalized data model, message based architecture and modular design (loader,analyzer,notifier).
• Security - DatabaseRX agents don’t require root/oracle or DBA privileges to fully report operational and trending issues.
• Network Access Control -  DatababaseRX does not expose databases externally - it only requires a periodic encrypted SMTP push from the database or proxy host.
• Documentation and Global Search - Delaying  problem resolution for lack of information is inexcusable - the DatabaseRX monitoring portal includes everything related to escalation contacts, vendor support, access control, prior events and reports.

The last point - “one place to go” for problem identification, resource engagement, resolution and root cause documentation has proven to me during live events that there can be significant reductions in unexpected downtime simply based on that efficiency.

There was education—everything from RAC and Exadata to SQL Developer and Java Server Faces.

There was food—everything from Prosciutto and Salmon to Biscotti and Chocolate-Dipped Strawberries.

There were prizes and giveaways—books from Oracle Press, duffel bags, Iron Man 2 posters, DVDs of the Oracle Database Appliance, and more.

There was networking—an opportunity to meet and greet Oracle luminaries such as Graham Wood, one of the key players in the creation of Statspack, and Kris Rice, the architect of SQL Developer.

A great time was had by all. We hope to see everybody again at the summer conference on August 19 at Chevron in San Ramon.

See the pictures

Download the NoCOUG Journal

On 12th May Oracle updated the 11.1.0.7 April CPU 2010 security patch (9369783) on all platforms.  The original April 13th patch causes 239 invalid objects in the database if the OLAP option is NOT installed.  So, if you were planning to apply this patch you should download the 12th May version.  You can find out the details in metalink note 1060969.1 “Critical Patch Update April 2010 Known Issues”

The presence of the invalid objects does not cause problems however ending up with an extra 239 invalids at the end of the patch that are not discussed in the patch notes would be a concern.  Of course, that is why Oracle have  document 1060969.1 to catch these things. 

If you applied the patch between its original release on April 13th and the update of the known issues on 12th May you should check for the presence of these invalids.  Metalink note 1091952.1 lists them and says ”As long as OLAP was not in use it is safe to drop all of the above objects”. 

I have not tested the April PSU patch (9352179), at the moment it has no similar update in its known issue document, which is note 1061315.1.

It hasn’t been easy trying to classify DatabaseRX.   Is it a monitoring, capacity planning, analysis, reporting, forensics, dashboard, KPIs, delta comparison tool - yes.  Is it a database management console or query tool - no.  As such I’d like to start with a simple area but one that’s easy to get wrong.

Alert Log Scanning

Very few tools do this well out of the box - and those that attempt to perform the task require significant setup or proprietary transport agents.  And to my knowledge with 11gR2 ADR  you still can’t add a syslog host destination for an instance alert log (talk to the folks at TimesTen for this enhancement Oracle).

DatabaseRX has some impressive features for alert log monitoring:

• Transport and consolidation are built in to a periodic passive e-mail send framework.
• Events of interest are rated for severity.
• Pre and post lines are displayed around the alert.
• RegEx style strings can exclude events on an instance-by-instance basis.

To the last point all events are monitored and you add exceptions over time.  While there could be more noise-to-signal in the beginning, you will not miss an unusual but critical new event.

I’ve setup various alert log monitoring systems in the past for RAC/non-RAC databases and what DatabaseRX does exceeds those tools - with far fewer resources.  One personal metric? I log into production hosts fewer times to investigate events and that not only saves time but helps keep the auditors happy.

As a database administer, it’s very important not to forget one of our main responsibilities; that being insuring that the database(s) we’re responsible for, can be recovered in the case of an emergency.  Read my short presentation here, for thoughts on this important topic.

The Importance of Testing Database Recovery, by Jay Stanley

Dear NoCOUG members and friends,

Happy New Year; may it be productive for all of us! The next NoCOUG conference will be held on Thursday, February 11 from 9 A.M. to 5 A.M. at the CarrAmerica conference center in Pleasanton. Top billing this time goes to Dr. Neil Gunther, the world’s foremost expert in database performance analysis and capacity planning. He is known internationally for developing the open-source performance modeling software Pretty Damn Quick and is the subject of an entire Wikipedia article.

Dr. Gunther will deliver a keynote address titled “Why Are There No Giants?” and a technical presentation titled “Performance Analysis For Those Who Can’t Wait.” The full agenda of the conference has been posted at http://www.nocoug.org/next.html. Please RSVP at http://www.nocoug.org/rsvp.html.

Most Oracle professionals will benefit a lot from attending a NoCOUG conference in 2010. However, the following categories will not benefit much:

1. Those Oracle professionals who are at the very top of their game already. This group is limited to a select handful of individuals such as Tom Kyte, Jonathan Lewis, Cary Millsap, Steven Feuerstein, and Craig Shallahamer.
2. Those Oracle professionals who believe that Oracle’s goal in buying Sun is to replace Oracle Database with MySQL. This is probably a very small group.
3. Those Oracle professionals who are leaning towards the “NoSQL” camp and now believe that databases are only needed for storing key-value pairs. Another small group.
4. Those Oracle professionals who believe that every man is an island and don’t want to network with other Oracle professionals. Gilligan and Robinson Crusoe come to mind.
5. Those Oracle professionals who don’t subscribe to the seventh habit of highly effective people taught by Stephen Covey in his bestseller The 7 Habits of Highly Effective People. The seventh habit is “Sharpen the Saw” which means preserving and enhancing the greatest asset you have—you. We haven’t met anybody who admits to belonging in this group.

Most of us don’t fall in the above categories so we fully expect that there will be heavy attendance as usual at NoCOUG conferences in 2010. Please forward this message to your colleagues and we hope to see you on February 11.

All the very best,

Hanan Hit,
NoCOUG President

Randy Samberg,
Director of Conference Programming

Iggy Fernandez,
NoCOUG Journal Editor

NoCOUG conferences and the NoCOUG Journal are made possible in part by the generous support of companies such as Database Specialists. Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how they can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit their website for no-cost resources, white papers, conference presentations, and handy scripts.

I was asked to tune a “Top N” query; a simplified version is shown below. Table t1 is first filtered by group (WHERE group_id = :group_id) then partitioned by subgroup (PARTITION BY subgroup_id) and sorted by timestamp (ORDER BY timestamp DESC). The record with the most recent timestamp in each partition is the one required (WHERE rank = 1).

SELECT
  t1.*,
  t2.*
FROM
  (
    SELECT
      t1.*,
      RANK () OVER (PARTITION BY subgroup_id ORDER BY timestamp DESC) as rank
    FROM t1
    WHERE group_id = :group_id
  ) t1
  LEFT JOIN t2 ON t2.id = t1.t2_id
WHERE rank = 1;

The query plan shows that most of the time is spent retrieving 192,000 records from table t1 using index lookups (TABLE ACCESS BY INDEX ROWID). However, only 479 of those records are retained after partitioning and sorting.

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER                |        |      1 |    479 |00:00:16.66 |   16051 |  16033 |
|*  2 |   VIEW                          |        |      1 |    479 |00:00:16.33 |   15424 |  15413 |
|*  3 |    WINDOW SORT PUSHED RANK      |        |      1 |    192K|00:00:16.06 |   15424 |  15413 |
|*  4 |     FILTER                      |        |      1 |    192K|00:00:14.23 |   15424 |  15413 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T1     |      1 |    192K|00:00:14.03 |   15424 |  15413 |
|*  6 |       INDEX RANGE SCAN          | T1_IX1 |      1 |    192K|00:00:04.80 |    1021 |   1021 |
|   7 |   TABLE ACCESS FULL             | T2     |      1 |  27142 |00:00:00.26 |     627 |    620 |
----------------------------------------------------------------------------------------------------

The solution was to create an index t1_ix2 on the triplet (group_id ASC, subgroup_id ASC, timestamp DESC). Note that the timestamp values are stored in descending order in this index instead of the default ascending order. An extra join was then introduced at the outset of the query. It used the new index to collect only the ROWIDs of the 479 records but not any data from table t1. As a bonus, sorting was no longer required (WINDOW NOSORT) for the “Top N” calculation since the new index was sorted in the required order. In the next join, we retrieved the required 479 records from table t1 using the ROWIDs collected in the first join (TABLE ACCESS BY USER ROWID). We did have to use several SQL hints (LEADING, USE_NL, USE_HASH, and NO_SWAP_JOIN_INPUTS) to make the optimizer see things our way but the modified query was five times faster than the original (3.11 seconds v/s 16.6 seconds).

SELECT
  /*+ LEADING(temp t1 t2) USE_NL(t1) USE_HASH(t2) NO_SWAP_JOIN_INPUTS(t2) */
  t1.*,
  t2.*
FROM
  (
    SELECT
      /*+ NO_MERGE */
      ROWID AS t1_rowid,
      RANK () OVER (PARTITION BY subgroup_id ORDER BY timestamp DESC) as rank
    FROM t1
    WHERE group_id = :group_id
  ) temp
  LEFT JOIN t1 ON (t1.ROWID = temp.t1_rowid),
  LEFT JOIN t2 ON (t2.id = t1.t2_id)
WHERE temp.rank = 1;

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER                |        |      1 |    479 |00:00:03.11 |    2129 |   1802 |
|   2 |   NESTED LOOPS OUTER            |        |      1 |    479 |00:00:02.82 |    1502 |   1182 |
|*  3 |    VIEW                         |        |      1 |    479 |00:00:02.37 |    1023 |   1016 |
|*  4 |     WINDOW NOSORT               |        |      1 |    192K|00:00:02.14 |    1023 |   1016 |
|*  5 |      INDEX RANGE SCAN           | T1_IX2 |      1 |    192K|00:00:01.89 |    1023 |   1016 |
|   6 |    TABLE ACCESS BY USER ROWID   | T1     |    479 |    479 |00:00:00.45 |     479 |    166 |
|   7 |   TABLE ACCESS FULL             | T2     |      1 |  27142 |00:00:00.23 |     627 |    620 |
----------------------------------------------------------------------------------------------------

P.S. I first saw the ROWID-to-ROWID trick used in SQL Tuning by Dan Tow.

Googling is an essential DBA skill. The Internet is a treasure trove of information that can help you in solving a problem. For example, user groups such as the Northern California Oracle Users Group (NoCOUG) have made vast collections of electronic presentations and white papers available on their web pages. Often a simple Google search will bring up an answer, but many specialized resources also are available.

The highest quality resource is of course the online Oracle documentation. Click the View Library link of any documentation set to get to the corresponding search page. Documentation for older versions of Oracle software going back to Oracle 7 is available here. Download the documentation you need to your desktop or laptop so you can browse the documentation while offline. Downloading the documentation to your computer is particularly advisable if you prefer the PDF versions. Because of the documents’ large size, it is more efficient to browse through them while offline.

You also can ask questions on the Oracle forums. Many Oracle experts donate a lot of time answering questions posted here.

The Oracle Technology Network (OTN) is an Oracle-sponsored site filled with useful resources including articles, sample code, and tutorials. It also contains links to the Oracle documentation and Oracle forums.

Oracle author Tom Kyte has been answering Oracle questions for many years on his web site Ask Tom. He’ll answer your question if he hasn’t already answered a similar question before and if the answer would be of wide interest.

Another good place to ask questions is the Usenet newsgroup comp.databases.oracle.server. Many Oracle experts donate a lot of time answering questions posted here. Most Internet service providers provide access to newsgroups, but you can also use a Google account to ask questions.

If you’ve exhausted other alternatives, consider asking your question to the subscribers of the Oracle-L mailing list. To subscribe or unsubscribe, send an e-mail message to oracle-l-requests@freelists.org with the word subscribe or unsubscribe in the subject line.

You can search the Oracle knowledge base (MetaLink) and obtain technical support from Oracle Support if you are paying annual support fees to Oracle and have a valid Customer Support Identifier (CSI). The support fees are typically 22 percent of the cost of your Oracle licenses. If you choose to forgo Oracle support, you will not be entitled to any patches (fixes for software bugs) or upgrades. If you cannot find the answer in the Oracle knowledge base, you can create a service request. The priority of the service request and the corresponding service-level commitment depend on the impact to your organization. For instance, a production outage is classified as Severity 1 and is given the highest level of attention.

Excerpted from Beginning Oracle Database 11g Administration