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.

  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.

We are frequently asked by clients whether they should upgrade to Oracle 11g. Oracle Corporation’s communications understandably promote using the latest version, and tout its new features. The answer to the question isn’t a simple one, and (like so much else in our world), “it depends”.

Our preference is to offer a slight variation on the “no first release” rule. We try to avoid any major version or release upgrade until it has been publicly available for a year or two. The reasoning behind this view is that every Oracle version/release/patchset is going to have some bugs or unexpected issues, no matter how well Oracle has tested it. The bugs may or may not be showstoppers. But if you rush to upgrade your production database to the newest version, you get to be the company which discovers some of these bugs. If, however, you wait a while, more bugs are likely to be discovered by other users. Oracle Support learns about these bugs and creates bug fixes. By the time you come across the bug, it’s easy to find information on the bugs and issues, and there may already be a solution.

Another consideration is the concept of “if it ain’t broke, why fix it?” If your database is serving your application well now, and you don’t have need of the new features of 11g (which are described in Arup Nanda’s series on the Oracle Technology Netork– www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html), then why rush to a new version? Or why move to a new version at all?

One reason to move to the new version would be because Oracle is desupporting the version you are on (see www.oracle.com/support/lifetime-support-policy.html). Premier support for Oracle 10gR2 ends in July 2010, for 11gR1 in August 2012. Premier support generally ends five years after a release’s general availability date. So if 11gR2 is released late this year, it will have premium support until late 2014. Most companies do not want a version which has been desupported.

But even that is not a hard and fast rule. If your company is using a packaged application which has been working perfectly well for years, and which you are not making any changes to, you may not care about support. We have clients running quite happily on Oracle 8i or 9i. The database meets their needs and they’re not changing anything.

One factor that is critical in upgrading (as Tom Kyte says several times in the above referenced posting on his site) is that you must test, test, test. And test some more. Before you even think of putting a new version into production you need to hammer on it, stress it, try to break it, in a test environment. You need to put loads greater than you’re likely to see in production, and you need to use all of the application code that is used in production. That is the only way to see if the new version is problematic. And this is a reason why some companies are slow to adopt a new version. Testing isn’t free. It consumes resources of personnel, hardware, and cash.

So our advice can be summarized as follows—Check to see if any of the new features of 11g will significantly help your application and if you have the appropriate license (some new features may only be available with Enterprise Edition or may be extra-cost options). If so, consider upgrading. If not, wait a while to upgrade (while keeping desupport dates in mind). And whatever you do, test, test, test.

Most Oracle professionals who focus on performance optimization are familiar with Method-R, which Cary Millsap tirelessly promotes.  Briefly stated, this method focuses on a specific process which is not performing satisfactorily.  We trace the process and see precisely what the process is doing.

We recently had a problem in which client connections were failing about half the time.  While this wasn’t a performance optimization problem, we used the same basic concepts that we use for performance problems.

1. We decided to do Oracle Net tracing to get more information. This is similar to doing a session trace when we’re investigating a performance problem. Unless it’s quickly obvious from a system level what queries are causing problems, a trace is one of the primary methods of further investigation. In the connection case, pings and tnspings are good starts, but if it’s not obvious from them what’s happening, Net tracing should be used.
2. We determined the problem was ours to figure out. The people at the client site had done what investigation they could, and asked us for help. Database Specialists has always been strong at trying to help solve issues, even if they appear to “not be our fault”, rather than pointing fingers or just saying “don’t know”. In this case it turned out that the issue was related to networking settings, so being willing to dig in wound up giving us all a learning experience that will help us in other installations.
3. We opened a Service Request with Oracle Support, and also bounced things off our Database Specialists team. I’ll admit that opening an SR tends to be more of a last resort. Bringing in the team is important because each of us has a slightly different skill set, and we also think about things in different ways. In most cases, we’ll resolve the problem faster based on DSI team input than with Oracle Support’s help.
4. We slogged through the trace files. I won’t claim to be an expert at interpreting Oracle Net trace files. But looking through quickly for address-like items, when the errors indicate addressing as an issue, isn’t too hard. I found an address that didn’t resolve, Iggy found that connections were being redirected (to an incorrect alias), and we were able to resolve the issue.  Oracle Support came to the same conclusion not too long after this.

This was just a case of using the standard practices of Database Specialists to break a problem down in manageable pieces and then the solution(s) became obvious.  By tracing the activity, and utilizing a team approach, we were able to make a customer happy.