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

Got some pennies, brother? For just 33 cents—plus a little more for shipping and handling—you can have a used copy of the best book on Oracle internals that was ever written: Oracle8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams. Yes it doesn’t have the word RAC in it, but what is RAC but OPS (Oracle Parallel Server) by a new name? It is one of the three really great Oracle books listed below that you may be tempted to ignore because the titles have the word 8i in them.

Why haven’t the authors kept updating their books? As I’ve experienced myself, there is very little money in writing books and it’s very hard work besides. And, as Mogens Norgaard recounts, when Jonathan Lewis was asked if his book Practical Oracle8i would ever be published in an updated 9i version, his response was to suggest that we put a sticker with a “9” over the “8” on the cover of his book, because the advice, methods and techniques described in the book were still very valid with Oracle9i.

There’s definitely some downside to reading a book that deals with an older version of a technology but, in the case of these three books, I believe that the upsides outweigh the downsides.

P.S. If you haven’t got a penny, James Morle has made his book available for free download.

Oracle8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams (Paperback - Oct 11, 1999) 19 used from $0.33

Scaling Oracle8i(TM): Building Highly Scalable OLTP System Architectures by James Morle (Paperback - Jan 2, 2000) 16 used from $1.00

Practical Oracle8i(TM): Building Efficient Databases by Jonathan Lewis (Paperback - Dec 28, 2000) 30 used from $2.49

I have a quick quiz for you.  What does the acronym “OCM” stand for?

A. Ohio College of Massotherapy
B. Optoelectronic Components and Materials
C. Orbit Correction Maneuver
D. Olympic Council of Malaysia
E. All of the above

If you answered (E), you get a gold star.  And you’re either good with tests or have quite the diverse set of interests.  Or you know how to do a Google search.

Focusing a bit more on your job and perhaps pacifying your boss who might be peering over your shoulder at this moment, let’s have a look at the possibilities in the Oracle world:

A. Oracle Certified Master
B. Oracle Connection Manager
C. Oracle Configuration Manager
D. Oracle Change Management
E. All of the above
F. A and C

If you answered (E) again, we’ll give you a silver star this time, but with an asterisk.  Technically you’re right, but from a material standpoint, you’re missing the boat to a certain extent.  Oracle Connection Manager is more regularly referred to as CMAN and “Oracle Change Management” is now commonly called “Change Management Pack”. 

If you answered (A), you get partial credit and should spend a bit less time at your local Oracle University center.  Likewise, if you answered (B) you get partial credit, but to you goes the gold star because that’s what I wanted to talk about today.

In fairness to those who answered (A), my Google search for “ocm oracle” yielded about 1.4 million results.  I decided that going through all of the results might involve a bit more time that I had for this posting, but of the first fifty that I did check, 48 of them had to do with Oracle certification.  Only two addressed the other OCM, Oracle Configuration Manager, which is interesting food for thought.

OCM (okay, let’s just focus on Oracle Configuration Manager now, class) has been around for more than two years now.  For those of you still waiting for Oracle 10g to stabilize before you upgrade, OCM is Oracle’s attempt at improving the effectiveness of requests for support, as well as encouraging customers to take a more proactive approach.  The general idea is that the OCM collector will gather data about your operating environment and Oracle configuration and upload it seamlessly to Oracle Support for private viewing.  Theoretically, this information exchange will help streamline the process of filing a new request and provide further information to support staff that may assist in deflecting, er, I mean, solving your problem, and give you more information about the health of your databases. 

The question begs, however, if this is such a great idea, why isn’t it catching on?  According to the blog posting My Oracle Support and Oracle Configuration Manager by OCM Product Manager Joshua Solomin from blogs.oracle.com, dated 12-3-2008,

“Note that customers need to specifically enable Oracle Configuration Manager in order for it to start collecting configuration information and securely sending this information to Oracle Support. Furthermore, if a customer’s security policies prohibit the automatic sending of configuration information outside of the organization, Oracle Configuration Manager can be configured to work in a “disconnected mode”, allowing the customer’s systems and security administrators the ability to review the information prior to it being sent out to Oracle Support.”

That may well be the case, but if so, Oracle has done a poor job of communicating this message to loyal customers.  If the customer is the primary focus, wouldn’t it be preferable to go out of their way to keep us informed?  And I’m not talking about posting news flashes on Metalink.  How many of us actually have the time to surf Metalink for the latest developments in the world of Oracle Support?

Not to contradict the afore-mentioned blog, but Database Specialists has seen cases where a seemingly unrelated patch install resulted in the OCM daemon starting up and a new cron job appearing on its own.  Recently we were assaulted with an email storm regarding an old 9i database in our testing lab, presumably prompted by missing OCM data on the bugger.  I must say, this does not bolster my opinion of the product, in general.

Another strike against this approach is in the complexity involved.  There are no less than seven documents on OCM on Oracle’s documentation portal, describing subjects from installation and administration to “unauthenticated registrations”, whatever the heck that is.  I mean, who wants to be the OCM administrator at your company?  Don’t everyone jump up and volunteer at once!

So the question remains, if OCM has been around for two years and is still not catching on, nay still raising hackles I must admit, is it really the right approach?  Is it ahead of its time, or just a bad idea?  In Oracle Support’s defense, their assignment is a difficult one.  And I think it’s fair to say that their performance could stand a shade of improvement here and there.  So I can’t blame them for taking bold action and prompting change from their customers.  But I’m not convinced that this approach is correct.  The stealthy nature of OCM and lack of communication from Oracle Support about it lends the more suspicious among us to believe that darker motives are at play.  That seems totally unnecessary. 

Okay, let’s here it from the community:  how do you like this new mindset that Oracle is imposing?

A popular saying is “It ain’t so much the things we don’t know that get us into trouble. It’s the things we know that just ain’t so.” Wikiquote attributes it to a 19th century American humorist named Artemus Ward but The Quote Verifier: Who Said What, Where, and When attributes it to another 19th century humorist named Josh Billings who actually wrote: “I honestly beleave it iz better tew know nothing than two know that ain’t so.” Another versions attributed to Josh Billings are “It iz better to kno less than to kno so much that ain’t so” and “You’d better not kno so much than know so many things that ain’t so.” The misattribution to Artemus Ward is just another example of the problem that Josh Billings was talking about.

Tom Kyte has an interesting twist: “It ain’t so much the things we don’t know that get us into trouble. It’s the things you know that just ain’t so or just ain’t so anymore or just ain’t always so.” I was reminded of it today when the need arose to delete a data file from an Oracle database. When I was being interviewed for a database administrator position in early 2004, the interviewing manager asked me only one question: “Is it possible to drop an Oracle data file?” He did not ask me any other questions because he knew very little about Oracle. I probably got the job on the strength of my OCP certificate and being available immediately though the manager did mention that I had the best-formatted resume he had every seen.

At that time, it was not possible to drop a data file from an Oracle database; the capability was introduced in Oracle Database 10gR2 which was released in late 2004. Here is a demonstration.

SQL> -- Check the database version
SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.1.0

SQL> -- Create a tablespace named Test
SQL> CREATE TABLESPACE test DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST01.dbf'
  3  SIZE 1 m;

Tablespace created.

SQL> -- Create a table named Test in the new tablespace
SQL> CREATE TABLE test TABLESPACE test
  2  AS SELECT * FROM DUAL;

Table created.

SQL> -- Add a second data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST02.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- Add a third data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- Manually allocate an extent to the new table
SQL> ALTER TABLE test ALLOCATE EXTENT (DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf'
  3  SIZE 64 k);

Table altered.

SQL> -- Add a fourth data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST04.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- List the data files
SQL> SELECT file_id
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'TEST';

   FILE_ID
----------
         6
         7
         8
         9

SQL> -- List the extents of the table
SQL> SELECT extent_id, file_id, BYTES / 1024 AS kb
  2  FROM dba_extents
  3  WHERE  tablespace_name = 'TEST';

 EXTENT_ID    FILE_ID         KB
---------- ---------- ----------
         0          6         64
         1          8         64

SQL> -- Drop the data files
SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST01.dbf';
ALTER TABLESPACE test DROP DATAFILE
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST02.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf';
ALTER TABLESPACE test DROP DATAFILE
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST04.dbf';

Tablespace altered.

SQL> -- List the data files again
SQL> SELECT file_id
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'TEST';

   FILE_ID
----------
         6
         8

Obviously you cannot drop an Oracle data file if it contains data. For extra credit, what are the other restrictions on dropping Oracle data files?

It’s a fact. A comic book is easier to read than a novel. A graph is easier to read than a table. One picture is worth ten thousand words. Oracle query execution plans can be very hard to read when the number of tables involved is more than a few. Query execution plans are explained in Chapter 17 of my book. A sample query execution plan is shown below; the way to read it is (page 399): Perform operations in the order in which they are listed except that if the operations listed after a certain operation are more deeply indented in the listing, then perform those operations first (in the order in which those operations are listed).

Tabular Query Execution Plan

If you’re like me, you’ll have a really hard time figuring out the sequence in which the operations listed in the above table are performed. But if you concentrate really hard, you’ll eventually figure out that the operations are performed in the following sequence: 5, 7, 12, 14, 13, 11, 15, 10, 9, 16, 8, 6, 4, 3, 2, and 1.

What we really need is a pictorial version of the above table with the operations properly numbered. You’ll immediately agree that the following version is much easier to read.

Graphical Query Execution Plan

The above picture was produced using the Graphviz tool. You can find instructions for producing graphical query execution plans in the article that I wrote for the journal of the Northern California Oracle Users Group (NoCOUG). You can also download the PL/SQL package and the SQL query mentioned in the article.

The SQL query generates Graphviz commands that produce a graphical execution plan for a specified SQL ID and child number. Each node in the graph is labeled with the execution statistics for the last execution of the query.

The life of a DBA is often led in isolation.  In many IT organizations, you’ll have a variety of disciplines, programmers, analysts, systems administrators, network engineers, project managers, etc.  And then there’s the DBA, often working alone, wedged between the developers and the SAs.  Maybe we like it that way.  We’re captains of our respective ships, doing things our way.  But speaking from first-hand experience, working alone is a great way to foster a false sense of security.  It is the input from a variety of backgrounds and experiences that lends quality and credibility to any solution. 

At Database Specialists, I am fortunate to be surrounded by a great group of skilled yet unselfish cohorts.  I like to think I know a thing or two about what it takes to be a good DBA, but the collective intelligence of the group far exceeds any level I’ll ever be able to reach by myself. 

What is my point?  Well, if you are as fortunate as me, then do yourself a favor and be a good team player.  If not, then I encourage you to reach out to other DBAs.  Oracle user groups like NoCOUG, RMOUG, and PSOUG are great for learning from other DBAs, networking, and sharing your own skill set.  Become a member.  Attend the meetings.  Contribute to their journals.  Volunteer your time.  You won’t regret it.

I was recently asked if I had any metrics on an individual tables growth over time. I immediately thought of the DBMS_SPACE package’s OBJECT_GROWTH_TREND procedure. Here’s a quick snippet of my findings:

SQL> select * from table(dbms_space.OBJECT_GROWTH_TREND('PROD_SCHEMA','BIG_TABLE','TABLE'));

TIMEPOINT                    SPACE_USAGE SPACE_ALLOC QUALITY
---------------------------- ----------- ----------- --------------------
08-MAR-09 05.13.14.068141 PM  1715776904  2521825280 INTERPOLATED
09-MAR-09 05.13.14.068141 PM  1793318743  2521825280 GOOD
10-MAR-09 05.13.14.068141 PM  1408296977  2521825280 INTERPOLATED
11-MAR-09 05.13.14.068141 PM  1524659382  2521825280 GOOD
12-MAR-09 05.13.14.068141 PM  1626828349  2521825280 GOOD
13-MAR-09 05.13.14.068141 PM  1734537314  2521825280 GOOD
14-MAR-09 05.13.14.068141 PM  1784205219  2521825280 GOOD
15-MAR-09 05.13.14.068141 PM  1871027953  2521825280 INTERPOLATED
16-MAR-09 05.13.14.068141 PM  1982543510  2521825280 GOOD
17-MAR-09 05.13.14.068141 PM  1983452023  2521825280 PROJECTED
18-MAR-09 05.13.14.068141 PM  1984360537  2521825280 PROJECTED
19-MAR-09 05.13.14.068141 PM  1985269050  2521825280 PROJECTED
20-MAR-09 05.13.14.068141 PM  1986177563  2521825280 PROJECTED
21-MAR-09 05.13.14.068141 PM  1987086076  2521825280 PROJECTED

Looks like it can grow anywhere from 100MB+ per day and on some days less that 1MB.

  may be the tagline for a major retailer, but it’s not always a reliable term in Oracle. A question at a panel session here at the Hotsos Symposium made me think of a situation with a client just last week.

Can you tell which of these execution plans is better?

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=36111)

  HASH (GROUP BY) (Cost=36111 Card=679428 Bytes=48918816)

    HASH JOIN (OUTER) (Cost=24847 Card=679428 Bytes=48918816)

      INDEX (RANGE SCAN) OF “INDEX_1″ (Cost=5139 Card=559570 Bytes=31335920)

      INDEX (RANGE SCAN) OF “INDEX_2″ (Cost=10913 Card=1206767 Bytes=19308272)

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6)

  HASH (GROUP BY) (Cost=6 Card=1 Bytes=72)

    NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=72)

      INDEX (RANGE SCAN) OF “INDEX_1″ (Cost=3 Card=1 Bytes=56)

      INDEX (RANGE SCAN) OF “INDEX_2″ (Cost=2 Card=1 Bytes=16)

At first glance, most people would say “the second one, with a cost of 6″.  However, in actuality the second execution plan averaged an elapsed time of 5076 seconds, while the first, with the much higher “cost”, averaged 80 seconds.

The plan which performed poorly was derived based on statistics that the optimizer had, which apparently didn’t properly represent the data sought by the query.  So its “cost” is going to be inaccurate, just as the execution plan is not optimal.