Page 745
Your major Oracle diagnostic tools are as follows:
To tune applications, you can use SQL_TRACE and TKPROF. You must set the init.ora parameter TIMED_STATISTICS=TRUE to get timing information. The timing is good to 1/100 of a second. Real-time database systems might have to use alternative timing methods, such as writing your own timer or sampling the real-time OS clock on which the database system resides. You can optionally set the USER_DUMP_DEST to the directory of your choice, and also set MAX_DUMP_FILE_SIZE to the maximum number of bytes you want your trace file to grow. Set SQL_TRACE=TRUE at either the system level (for all sessions), through the init.ora parameter, or at the session level through ALTER SESSION SET SQL_TRACE=TRUE;.
Execute the application, and then set SQL_TRACE back to FALSE if desired. In effect, you're not planning on running the application for timing purposes again in the near future. Use TKPROF to format the trace file. (The TKPROF utility is run from the command line.) Statistics gathered include the query execution cycle component times (parse, execute, and fetch) and logical versus physical reads. The usage, syntax, and interpretation of SQL_TRACE and TKPROF will be covered in detail in Chapter 30.
Another application tuning tool is EXPLAIN PLAN. You must run utlxpln.sql to create the PLAN_TABLE, or create it yourself interactively. EXPLAIN PLAN FOR <SQL statement>; will explain the execution plan for that SQL statement without executing it. (This is similar to the NO EXEC option with some other interpreted languages.) You can use this with or without SQL_TRACE and TKPROF. I recommend you review the code first (read it), run SQL_TRACE with TKPROF next, and then use EXPLAIN PLAN last if you're still having difficulty with an application that's performing poorly. This will be addressed in Chapter 31, along with how the Oracle optimizer works, which will help explain how EXPLAIN PLAN works.
To help tune memory (Chapter 31) and tune I/O (Chapter 32), all of the Oracle products, such as SQL*DBA, Server Manager, and Enterprise Manager, rely on the V$ dynamic performance views. These views are grouped into instance, database, memory, disk, user, session, and
Page 746
contention aspects of performance. They are based on the internal X$ base tables. DESCRIBE V$FIXED_TABLE and SELECT the columns you want from the V$FIXED_TABLE table to get a listing of the V$ views. The X$ tables are typically not queried directly. Their names and contents change from version to version and within a version.
The V$ views are called dynamic because they are populated at instance startup and are truncated at shutdown. The V$ views (and X$ tables) also form the basis of the standard Oracle tuning scripts, utlbstat/utlestat, which query them using SQL scripts and format the output that is returned. Therefore, if utlbstat/utlestat do not give you what you want, you can use Server Manager and the V$ views to either supplement or supplant those utilities. The least common denominators for all Oracle platforms, older and newer, are SQL scripts, V$ views, and either SQL*DBA or Server Manager line mode (svrmgrl on UNIX, and svrmgr23.exe on Windows NT, for example). This means that if you have developed your own performance scripts, you can run them within these command-line utilities. And there's always SQL*Plus, of course.
The Server Manager Monitor, which is becoming obsolete because of the advent of Enterprise Manager, offers several screens relating to performance. These screens monitor the MultiThreaded Server, logical and physical I/O, process information, locks and latches, shared pool information, and rollback information. The screens are updated in real time and, as mentioned, are based on the V$ views and X$ tables.
Enterprise Manager's Performance Pack is extremely useful. It offers several components that can save a lot of time over command-line methods and scripting, especially for experienced DBAs or those who know precisely what to look for. Enterprise Manager allows you to quickly get to that information. The statistics are based on the V$ views and X$ tables but are reformatted into a much more readily digestible GUI form.
The components in the Performance Pack help analyze your logical and physical design. They also monitor locks, a variety of performance issues (throughput, redo, rollback, I/O, memory, and so on), the top user sessions with regards to resource consumption, your tablespace storage (data files, fragmentation, and so on), and application events through tracing.
The most commonly used Oracle diagnostic utilities by far are the utlbstat/utlestat pair. A DBA runs utlbstat before running his or her application or simulation. The utlbstat.sql script builds the beginning tables necessary to collect and store the performance data. Then the DBA runs utlestat.sql, which builds the ending tables and the difference tables, computes the performance differences (the deltas) between the utlbstat run and this utlestat run (in effect, the application duration), formats the output data (including comments and some explanations), and writes it to the default file, report.txt. This file must be interpreted
Page 747
by the DBA, either directly or indirectly (by taking some of the output values given and using them as inputs into simple formulas).
Interpretation of this data means comparing these final figures to more or less established guidelines, keeping the ROI strategy in mind, and categorizing the findings as acceptable or not for that given area of performance. The output in report.txt nearly covers all the bases, either directly or indirectly. So a DBA must simply apply the ROI strategy, the established guidelines, and his or her application knowledge and determine whether or not the memory performance is acceptable, the I/O performance is acceptable, and so forth.
We've presented a fairly high-level overview of the major Oracle diagnostic tools. Third-party performance monitoring tools exist, and they have relatively large market shares within the installed Oracle customer base. Some examples include offerings from BMC, Platinum, and Compuware. We'll use utlbstat/utlestat and report.txt as our diagnostic tools for Chapters 31 and 32. And, of course, we'll use EXPLAIN PLAN and SQL_TRACE with TKPROF for the next chapter.l