Database Monitoring

 

Use V$TRANSACTION view to check any uncommitted transactions.

 

Use V$UNDOSTAT view to check the history of system performance in terms of number of data being held for uncommitted transactions.

 

Some columns in V$ views will only be populated when parameter timed_statistics is set to TRUE, which has some impact on database performance. Even though after Oracle 9i, this parameter is default to true, the normal way is still to set it to FALSE. First set it to TRUE, carefully monitor database performance, and then set it to FALSE. The value can be dynamically changed by the “alter system” command, very useful for on-demand monitoring.

 

Net8 Connectivity Tracing

 

Oracle Net8 sits on the Layer 5 and 6 of OSI, one on the client side and one on the server side, where client application (including SQL*PLUS) and Oracle database server are Layer 7 of OSI. Net8 connectivity tracing is to see the data across the boundary of Layer 4 and Layer 5. If the client and server are running on the same local machine, host string shall be specified to avoid local loop back that ignores Net8. Host string can be found in listener.ora, usually is the SID.

 

Client Tracing

 

Add or modify the SQLNET.ORA file as following:

 

# 16 is the highest level. 0 means the tracing is turned off.

TRACE_LEVEL_CLIENT=16

# Must be the fully qualified name for the trace file.

TRACE_FILE_CLIENT=C:\oracle\ora90\network\trace\cli.trc

# Must be the fully qualified name for the trace directory.

TRACE_DIRECTORY_CLIENT = C:\oracle\ora90\network\trace

# Use one trace file for each different client.

TRACE_UNIQUE_CLIENT=ON

 

Server Tracing

 

Add or modify the SQLNET.ORA file as following:

 

# 16 is the highest level. 0 means the tracing is turned off.

TRACE_LEVEL_SERVER=16

# Must be the fully qualified name for the trace file.

TRACE_FILE_SERVER= C:\oracle\ora90\network\trace\svr.trc

# Must be the fully qualified name for the trace directory.

TRACE_DIRECTORY_SERVER = C:\oracle\ora90\network\trace

 

Listener Process Tracing

 

Add or modify the LISTENER.ORA file as following:

 

# 16 is the highest level. 0 means the tracing is turned off

TRACE_LEVEL_LISTENER=16

# Cannot put the directory or extension, otherwise

# “lsnrctl reload” will report error.

TRACE_FILE_LISTENER = lsnr

 

# Don’t uncomment it out, otherwise "lsnrctl reload" will complain.

# However, Oracle will put the trace file there.

# Don’t know why.

#TRACE_DIRECTORY_LISTENER= C:\oralce\ora90\network\trace

 

After making changes to the listener.ora file, it must re-read by issuing from the DOS prompt, lsnrctl reload. Changes to sqlnet.ora file do not need reload.

 

Database Tuning

 

Database tuning, in most of the time, is used to solve specific problems, not answer general design questions. Every tuning technique has side effects and tradeoffs. So design and build the database first, then develop a comprehensive testing plan to evaluate the performance, and later on consider the tuning. Every step of tuning must be carefully thought and well documented. Since tuning is more of art than science, it is hard to understand the rational behind a tuning by just looking at what has been done. Therefore the document shall explain the environment, what goes wrong, what the desired outcomes are, and what the side effects are. A history must be maintained for every tuning action and it is essential that each tuning action can be “undo”. Another reason for recording history is that by only looking at the final results, people cannot tell whether the database is not properly normalized or the database has been purposely de-normalized. A history would give people a clear road map.

 

Common tuning techniques are:

 

(1)   Add index targeted at popular queries to speed up data searching, including same type of index for join party and index covering.

(2)   Index on numeric columns than on character columns.

(3)   Avoid compound index. For compound index, don’t put too many columns.

(4)   Combine tables, duplicate columns or add interconnect tables to minimize joins.

(5)   Remove clustered index from sequential column to avoid hotspots.

(6)   Horizontally split the table. Put those rows that are small portion of the whole table but are very active or different in a separate table.

(7)   Vertically split the table. Put those columns that are small portion of the whole table but are very active or different in a separate table. Note that the two tables share the same primary key. A special case is the supertype/subtype problem, which is mainly the tradeoff between null columns, long rows and multiple read, joins.

(8)   Add a descending column to solve the descending sort problem since SQL server does not use index for descending sort.

(9)   Add columns to store calculation, comparison or statistical results so that they can be readily reused, especially when the results can be updated using triggers.

(10)           Split user tables and transaction logs to different segments. Split non-clustered indexes and data to different segments.

(11)           Use Sequence data type to automatically generate unique numbers for each row.

(12)           Use stored procedure for complicated transactions, instead of a set of interactive SQL queries between client and database server.

(13)           Use views for commonly used SQL queries, especially the queries use joins.

(14)           Use database provided constraints to enforce business rules instead of user applications or SQL statements. They are more reliable and the performance achieved by database optimizer is far beyond the human write SQL statements.