Product Review: Thomson Course Technology's Oracle9i Database Performance Tuning

Tuesday Nov 23rd 2004 by Steve Callan
Share:

When it comes to IT training, how do you want to spend your hard-earned dollars? There are plenty of options, approaches, and vendors to choose from, so it helps if you have a specific or somewhat focused goal in mind before spending your money. Steve Callan begins his product review series with a look at Thomson's course on performance tuning.

When it comes to IT training, how do you want to spend your hard-earned dollars? There are plenty of options, approaches, and vendors to choose from, so it helps if you have a specific or somewhat focused goal in mind before spending your money. Let's look at some of the choices as they relate to goals just mentioned.

Options

How you get the training or education ranges from individual self-study to vendor specific classroom-based instructor led training. Regardless of your particular or favorite learning style, it certainly helps to have some direction, whether it is in the form of a logical sequence or it is grouped by functional category. Another type of outline is one that supports a certification exam test content checklist.

Approaches

What is it you want to get out of the training (or class)? Do you want to learn what is practical, that is, is what you learn going to be useful to you in your day-to-day work environment? Or are you looking to learn what it takes to pass an exam? There are trade-offs with each approach. What helps you on your job may have little to do with what is on an exam, and what helps you pass an exam may be totally irrelevant to what your job demands. Generally speaking then, you have to choose between the two because (with few exceptions) the options (from above) tend to support one approach or the other.

Vendors

This category includes local training providers who provide hands-on training using their own or someone else's material, product specific providers who are THE source of information (Oracle University in our case), publishers of series type books, and companies who provide a combination of both. To clarify the last type -- this means you can attend an ILT course or buy the training product, but in either case, you are using the same course material.

My background

What "qualifies" me to write about this and provide a product review? That's a fair question, so I thought it would be useful to say that my training background runs the gamut of what's out there, and in addition to academic classes, it includes the following:

  • Employer-paid attendance at Oracle University courses
  • An out-of-pocket expenditure of more than $2,000 for hands-on training at a local training provider (part of which was excellent and part of which was totally worthless).
  • Use of structured study material (Sybex, for example) and exam simulators (Self Test Software). These were used for the purposes of learning, and learning what it takes to pass an exam. The Sybex books really are quite useful as reference books at times.
  • Self-directed study using books I buy and documentation Oracle provides. When I say I have a lot of books, believe me, I have a lot of books. I pretty much do not have to take anyone's word about a book because more than likely, I already have it.
  • Use of a "combination" training provider, such as what Thomson Course Technology provides in its Course ILT series (http://www.courseilt.com/) in addition to having used their books in classes (Database Systems and Systems Analysis and Design, to name a couple).

Does the Thomson name sound familiar? If you have taken an OCP exam (or even a Microsoft, Sun, or Cisco exam, for that matter), more than likely you took it at a Prometric test site. That's "Prometric," as in "Thomson Prometric Testing Center." Having established who Thomson is, this leads into what this particular product review is about: Thomson Course Technology's Oracle9i Database Performance Tuning course.

What does this course cover?

Again, this comes back to the goal of what you want to get out of a training class: learn things for your job, or learn things to pass an exam. Here is Thomson's own description of the course:

Description

This course presents techniques for finding performance or configuration problems within the Oracle database and solving those problems. Manual techniques as well as those employing the specialty Packs within Oracle Enterprise Manager are considered. Workshop exercises allow participants the opportunity to examine the internal operation of an Oracle instance, isolate problems, test possible solutions and implement effective remedies.

Does the word "certification" appear anywhere in that description? The course, based on the "official" description, is more of a "help you do your job" course. However, the introductory chapter of the book does discuss the certification exam by mentioning that the "course considers subjects included within the OCP Examination." The test content checklist for the 1Z0-033 exam (Oracle9i performance tuning) has 14 major categories, and the Thomson book explicitly covers more than half of them. Moreover, just for comparison, Sybex's performance tuning book takes a hit on reviews for not covering everything in the test content checklist.

For $180 (the cost of the book), you receive a very straightforward coverage of many performance tuning-related topics. Additionally, each chapter has an accompanying workshop with solutions. The list of topics covered in the book can be seen at http://www.course.com/catalog/product.cfm?category=Databases&subcategory=Oracle&isbn=0-619-28864-7.

There are two topics in the book that you would not typically associate with Oracle9i, and those have to do with manual rollback segments and the UTLBSTAT/UTLESTAT utilities. So why would a training book on Oracle9i cover deprecated or antiquated items? An Instructor Note at the beginning of "Tuning Manual Rollback Segments" discusses why manual rollback segments are covered - and why this section can be skipped, depending on the audience. Even today, there are companies still using versions of Oracle as old as version 7, and plenty of people still use 8i. You may be working for one of them, and you may find yourself in a shop that uses pre-Oracle9i versions in addition to 9i. Overall, the coverage of manual rollback segments is good. The section on statistics utilities, where utlbstat and utlestat are covered, provides a nice summary of the V$ tables used.

Memory

Disk

V$DB_OBJECT_CACHE

V$BACKUP

V$LIBRARY_CACHE

V$DATAFILE

V$SYSSTAT

V$FILESTAT

V$SGASTAT

V$LOG

V$LOG_HISTORY

Instance/Database

V$BACKUP

Contention

V$DATABASE

V$LOCK

V$INSTANCE

V$ROLLNAME

V$OPTION

V$ROLLSTAT

V$PARAMETER

V$WAITSTAT

V$PQ_SYSSTAT

V$LATCH

V$PROCESS

V$SESSTAT

User/Session

V$WAITSTAT

V$LOCK

V$OPEN_CURSOR

V$PROCESS

V$SESSION

V$SESSTAT

V$TRANSACTION



Exposure to a database tuning utility


The trend from Oracle is that most, if not all, of a DBA's interface with managed databases/instances is heading towards GUIs. This is especially true if you consider how Oracle Applications, Application Server, and many aspects of Oracle10g are concerned. You have heard about it, now you can see it via a course of instruction, and that is the OEM Performance Manager. STATSPACK, AUTOTRACE, the "stat" utilities - you've been there, done that, and aren't you tired of trying to remember all of the views involved and what the output means?


Think of the evolution of these tools like the relationship between a slide rule and today's very inexpensive/hugely powerful graphing calculators. We are way past the days of entering "7734" and reading it upside down. Now, you can show the graph of the function and its first derivative which shows just how fast your rate of change to entering "7734" is increasing. Do you need a little shove or push to start using the GUI tools? When you see them, you are probably not going to want to go back to the "purist" way of doing business.


Go through the exercises based on the "old" utilities to gain a better understanding of what you see in Performance Manager. If you are totally new to performance tuning, you will not appreciate the amount of work you would have to do by hand to get the same data--in a text-based format--and try to interpret it graphically. On the other hand, there are many future DBAs who will grow up using only the GUI interface, so don't feel guilty about being able to rattle off the intersection column for the V$this and V$that dynamic views.


With one click, you get all of what is shown below. Take advantage of the "manual" lessons so you can translate that output with what Performance Manager does.


As another example, you can use the "select * from dba_blockers;" and "select * from dba_waiters;" statements via SQL*Plus to see:

Or take advantage of the one-click-away chart shown below:

Coverage of other topics

The Thomson book devotes two sections to LogMiner and the DBMS_REPAIR utilities. In comparison, the Sybex series treats these topics in its Backup and Recovery chapters. Why the difference? With LogMiner, it depends on the purpose for using it in the first place. As mentioned in the Database Administrator Guide, one of the potential uses for data stored in redo logs is shown in bold below.

Potential Uses for Data Stored in Redo Logs

All changes made to user data or to the data dictionary are recorded in the Oracle redo logs. Therefore, redo logs contain all the necessary information to perform recovery operations. Because redo log data is often kept in archived files, the data is already available. To ensure that redo logs contain useful information, you should enable at least minimal supplemental logging.

  • The following are some of the potential uses for data contained in redo logs:
  • Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun. An example of an error made at the application level could be if a user mistakenly updated a database to give all employees 100 percent salary increases rather than 10 percent increases. It is important to know exactly when corruption began so that you know when to initiate time-based or change-based recovery. This enables you to restore the database to the state it was in just before corruption.
  • Detecting and whenever possible, correcting user error, which is a more likely scenario than logical corruption. User errors include deleting the wrong rows because of incorrect values in a WHERE clause, updating rows with incorrect values, dropping the wrong index, and so forth.
  • Determining what actions you would have to take to perform fine-grained recovery at the transaction level. If you fully understand and take into account existing dependencies, it may be possible to perform a table-based undo operation to roll back a set of changes. Normally you would have to restore the table to its previous state, and then apply an archived redo log to roll it forward.
  • Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts. That information provides a historical perspective on disk access statistics, which can be used for tuning purposes.
  • Performing post-auditing. The redo logs contain all the information necessary to track any DML and DDL statements executed on the database, the order in which they were executed, and who executed them.

So, that justifies coverage of LogMiner in this particular case. DBMS_REPAIR, on the other hand, is more commonly viewed as a Backup and Recovery topic as opposed to having more to do with performance tuning.

What could be improved in future editions

I would like to see future editions of this book/course include sections on indexing and the CBO, and to take advantage of the new sample schema Oracle provides with release 9i. Knowledge of different indexing techniques, combined with how the CBO uses (or does not use) an index is essential in performing performance tuning. The sample schema can be used, as there is enough of it to see a noticeable difference in query execution times when an appropriate type of index is used.

In Closing

For the cost and amount of material covered, Thomson's Course Technology Oracle9i Performance Tuning book is a good deal. If you are new to performance tuning, this book provides an excellent combination of cost versus coverage when compared to "brand name" training providers and their typical rate of $500 per day. Either way, you are not going to walk away as an expert tuner, but if you want an inexpensive, broad coverage exposure to many performance tuning concepts, you are not going to go wrong with the Thomson book. There are plenty of other books and training programs you can read or attend after this one. Having a firm grasp of the concepts presented in this book will benefit you when the same material is covered in a more in-depth approach at a much higher dollar per day rate.

» See All Articles by Columnist Steve Callan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved