Trace Analyzer

Oracle has provided a utility that was initially designed for performance tuning Oracle Applications. Trace Analyzer is provided in the form of a PL/SQL package (trcanlzr). Trace Analyzer is available via download on the Oracle Metalink web site. This utility supports only version 8.1.6 and above due the requirement of being able to read OS files from PL/SQL into the database. As of version 9.2, the Trace Analyzer utility is still not shipped with the Oracle DBMS.

Trace Analyzer requires that a one-time configuration be performed. During this configuration, many objects are installed in the database to serve as a tracing repository. Once downloaded from Metalink and installed, a SQL script can be executed passing in the name of the trace file. Trace Analyzer will then read the trace file and provide useful statistical information. The trace file used by Trace Analyzer is the same .trc file generated by any session trace.

First, tracing needs enabled at the appropriate level. For example, to provide maximum trace data, a Level 12 trace can be started for the current session:

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

After the session executes for enough time to gain needed data, the trcanlzr SQL script can be executed. It requires the name of a directory object. This object points to the physical operating system directory for the user_dump_dest. The installation of the utility will automatically create the directory object required (named UDUMP). Once executed, the Trace Analyzer output will be displayed on the screen.

SQL>@d:\trcanlzr.sql UDUMP asg920xr_ora_13033.trc

Trace Analyzer has the following benefits:

1. Provides the actual values of the bind variables in SQL. No longer are DBAs faced with wondering what the values were at runtime – Trace Analyzer provides them. For instance, given the following SQL statement listed in the output:

DELETE FROM HISTORY WHERE ALERT_TIME <= :b1 AND INSTANCE_NUMBER = :b2

Trace Analyzer would also display:

0:"2/4/2003 15:57:35" 1:1

which equates to the actual SQL statement of:

DELETE FROM HISTORY WHERE ALERT_TIME <= :"2/4/2003 15:57:35" AND INSTANCE_NUMBER = 1

2. Displays the hottest blocks, optimizer statistics for indexes and tables and other information not available through TKPROF. The output shows the SQL statement, the execution plan and statistics for each object in the SQL.

The output indicates that the EMPLOYEE table does not have statistics.

3. Trace Analyzer separates user recursive and internal recursive calls, unlike TKPROF.

4. Trace Analyzer provides more detailed wait event information – very useful to those DBAs that prefer wait-based tuning methodologies.

Trace Analyzer takes tuning to a new level that is not provided in other utilities shipped with Oracle.

orakill

The orakill utility is provided with Oracle databases on Windows platforms. The executable (orakill.exe) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database.

In the Unix world, a DBA can kill a shadow process by issuing the kill –9 command from the Unix prompt. Unix is able to provide this capability given that the Unix operating system is based on processes that fork other processes. All processes can be listed by using the ps Unix command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process.

Unlike the Unix operating system, Windows systems are thread-based. The background processes and sessions are all contained within the ORACLE.EXE executable and are not listed in the “Processes” tab of Windows Task Manager. Each session creates its own thread within ORACLE.EXE and therefore is not exposed to the Windows user. Killing the ORACLE.EXE process in Windows would crash the entire database.

The orakill utility serves the same purpose as kill –9 in Unix. The command requires the instance and the SPID of the thread to kill. The following query will return the SPID for each user connected to the database:

select a.username, a.osuser, b.spid
   from v$session a, v$process b
   where a.paddr = b.addr
     and a.username is not null;

USERNAME                       OSUSER                         SPID
------------------------------ ------------------------------ -----
SCOTT                          Scott                          3116
AMOORE                         Alex                           4760
DMOORE		               Dave		               768

Given the SPID for each user listed above, the session for any user can be killed with the orakill command.

C:\oracle9i\bin>orakill ORCL92 4760

Kill of thread id 4760 in instance ORCL92 successfully signalled.

SQL> select a.username, a.osuser, b.spid
  2  from v$session a, v$process b
  3  where a.paddr = b.addr
  4  and a.username is not null;

USERNAME                       OSUSER                         SPID
------------------------------ ------------------------------ -----
SCOTT                          Scott                          3116
DMOORE                         Dave                            768

Notice that SPID 4760, user AMOORE is gone.

Why does Oracle provide a utility to kill sessions from the DOS prompt when a DBA could kill a user session from within Oracle? The following command will also kill the user session:

alter system kill session(sid, serial#);

The sid and serial# used in the command above can be obtained from the v$session view. There are a few reasons a DBA could use orakill instead of the alter system kill session command.

1. The alter system statement will not clear the locks if any exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.

2. A DBA may not be able to gain access to a SQL prompt due to a runaway query consuming all system resources. In this case, the session can be killed without ever logging in to the database.

The above may be reasons to kill threads directly from the DOS prompt, but they still don’t address how to get the required information from the database if database access is unavailable. How can a DBA obtain the SPID?

One way to obtain the SPID outside of Oracle is to use a tool like QuickSlice from Microsoft (free download) that will display Windows threads and their IDs.

The DBA can quickly identify the most consumptive thread within an executable and decide what to do. The TID column (Thread ID) in QuickSlice is a Hex value and matches the decimal value for the spid column from v$session. For example, a TID value of 300 (Hex) equals Session spid 768 (Decimal). Therefore, the command to kill this session would be:

C:\oracle9i\bin>orakill ORCL92 768

In this example, the thread (Oracle session) was killed in the operating system without ever logging into the database.

Before killing the session, the DBA may decide to view the SQL being executed by the session. This can be obtained by using the TID above (300) in the following SQL statement:

select b.username, a.sql_text from 
v$sqltext_with_newlines a, v$session b, v$process c
where    c.spid  = to_number('300', 'xxx') 
    and  c.addr  = b.paddr 
    and  b.sql_address = a.address;

The orakill utility should be used as a last resort only. If the session cannot be killed more gracefully (via alter system kill session), or the instance is inaccessible via SQL, then orakill can be used to terminate the offending session.

Background processes should not be terminated, only user sessions. Killing a background process can cause serious Oracle errors and can bring the database down. To confirm that it is not a background session being killed, the following query will return the SPID for the background processes:

select c.name, b.spid, a.sid 
from v$session a, v$process b, v$bgprocess c
where c.paddr  <> '00' 
        and c.paddr =  b.addr 
        and b.addr  =  a.paddr;

NAME  SPID                SID
----- ------------ ----------
PMON  1680                  1
DBW0  1828                  2
LGWR  1844                  3
CKPT  1852                  4
SMON  1848                  5
RECO  2060                  6
CJQ0  2064                  7
QMN0  2072                  8

maxmem

The maxmem utility can be used on Unix systems to anticipate when the ORA-04300 error will occur. Utilizing this utility, the DBA can determine the number of sessions that will be able to connect to the database before the ORA-04030 error message is encountered.

The maxmem utility is a simple program with no command-line options:

$ maxmem
Memory starts at:     141728 (   229a0)
Memory ends at:    268025856 ( ff9c000)
Memory available:  267884128 ( ff79660) 

maxmem returns three data items, although only one that is really useful to the DBA. “Memory available” indicates the number of bytes of RAM that are available. This is critical to know since ORA-04030 errors will typically occur when this number is less than 1000000 (1 MB).

When another session connects to the database, the maxmem utility will reflect a reduction in the memory available:

SQL> connect scott/tiger@ASG920;
Connected.

$ maxmem
Memory starts at:     141728 (   229a0)
Memory ends at:    267075583 ( feb3fff)
Memory available:  266933855 ( fe9165f)  

Based on the delta in the memory available, the memory consumed by this one connection to the database is 950273 bytes, roughly 1 MB. Subsequent tests indicate that memory allocated for each connection may vary but it is always close to 1 MB. Given that a session on this host will grab 1 MB of RAM, awk can be used as part of the maxmem command to indicate the number of sessions it will be able to support.

This command will display the third field (divided by 1 MB) of any output line that contains “available” in the second field. This number will represent the number of additional sessions that can be handled by the database assuming that each will take 1 MB. Based on the output above, the database can handle approximately 251 additional database connections before an Oracle memory error occurs. This number is an approximation based on the earlier benchmark that determined 1 MB is used per connection. The DBA should include this command as part of their regular Oracle monitoring scripts on Unix databases.

SQL*Plus COPY

The SQL*Plus COPY command copies data between two databases via SQL*Net. The preferred method to doing this is to use SQL*Plus on the host where the database resides. If performing the COPY command from a client SQL*Net connection, the data is transferred through the client machine.

The COPY command copies data from one Oracle instance to another. The data is simply copied directly from a source to a target. The format of the copy command is:

COPY FROM database TO database action -
  destination_table (column_name, column_name...) USING query

The action can include CREATE, REPLACE, INSERT or APPEND.

SQL> copy from scott/tiger@ORCL92 - 
     to scott/tiger@ORCL92- 
     create new_emp – 
     using select * from emp;

Once the command above is executed, the copy utility displays the values of three parameters, each of which can be set with the SQL*Plus set command. The arraysize specifies the number of rows that SQL*Plus will retrieve from the database at one time. The copycommit parameter specifies how often a commit is performed and is related the number of trips – one trip is the number of rows defined in arraysize. Finally, the long parameter displays the maximum number of characters copied for each column with a LONG datatype.

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table NEW_EMP created.

   1400 rows selected from scott@ORCL92.
   1400 rows inserted into NEW_EMP.
   1400 rows committed into NEW_EMP at scott@ORCL92.

The command above did not specify column names for the new table (new_emp). As a result, the new table will have the same column names as the table being copied. If different column names are required, they can be specified after the table name:

create new_emp (col1, col2, …) –                  

A DBA could perform this same function with a database link on one database pointing to another. The appeal of the copy command is that it only requires SQL*Net service names and proper privileges to get the job done. For those environments that restrict the usage of database links, the copy utility can be leveraged. In addition, the copy command provides many options as defined by the actions – create, replace, insert and append.

If the copy command is executed from a client PC to copy data from remote database DB0 to remote database DB1, the data will be copied from DB0 to the client PC and then to DB1. For this reason, it is best to use SQL*Plus from either remote host and not require the data to travel through a client machine in order to reach its final destination.

Consider utilizing the COPY command instead of exporting and importing tables from one database to another.

DBMS_XPLAN

In version 9, Oracle finally provides a utility that formats the contents of the plan table. The plan table is one that is used to hold the results of an “Explain Plan” for a particular SQL statement. Explain Plan is used to generate and show the optimizer execution plan for a particular SQL statement.

The output from the explain plan shows the anticipated optimizer execution path, along with the estimated cost of the statement without actually executing the statement against the database.

The DBA or developer first needs to create the plan table. The DDL for this table is in the $ORACLE_HOME/rdbms/admin/utllxplan.sql file. The next step in using dbms_xplan is running Explain Plan for a statement.

explain plan for select * from employee where emp_id = 64523;

The command above will populate the plan table with the data returned from the optimizer. Next, the dbms_xplan utility can be used to view the output.

The output shows the query execution plan complete with formatting. This is the starting point for all expert SQL tuners.

dbms_xplan provides a useful feature to DBAs and developers. Although most DBAs and developers have explain plan scripts that they’ve used in prior versions of Oracle, the ease of this package makes it much more efficient. DBAs should grant public access (execute) to this package and encourage developers to use it. By putting more tools into the hands of those who write the SQL, the better the database will perform.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan. You can buy it direct from the publisher for 30% off directly from Rampant TechPress.