ORACLE Performance Tuning (i) Diagnostics and tuning tools

First, diagnostic tools

1. Warning Log file

Warning log files contain system information and error logs that contain important errors (such as: ORA-600) and block checksum error messages (ora-1578,ora-1498). Monitor database operations such as CREATE DATABASE, STARTUP, SHUTDOWN, ARCHIVE log, and RECOVER, and record Non-default initialization parameter values. When Log_checkpionts_to_alert is set to true, the warning log file also records the start and end times of checkpoints, incomplete checkpoint information, time to archive, start and finish times of instance recovery, deadlock, and timeout information

2, background process tracking files

Typically, Oracle places error messages for background processes under the path specified by the initialization parameter backgroud_dump_dest, and in general, the background process's trace file also has an abbreviation for the background process, such as an archive process file that might be oracle_arc0_1996. TRC, in general, background process tracking files do not include the information we are tuning the database.

3, User tracking files

A user trace file records the statistics that are executed by a user's SQL statement. Start user tracking can be at the instance level and session level.

Instance level: Sets the Sql_trace initialization parameter to TRUE and generally does not advocate tracking user actions at the instance level

Session level: Executes the dbms_system.set_sql_trace_in_session function, as follows

EXECUTE dbms_system.set_sql_trace_in_session (8,12,true); The Dbms_system package is created using Catproc.sql. You can also use the ALTER session SET Sql_trace=true to implement conversation-level user action tracking

4, views and tools

Related Tools Oracle Enterprise Manager, statspack, dynamic performance view, data dictionary view, database wait event, Utlbstat,utlestat script

Statspack:

To create a perfstat user and create a default tablespace for the user before using Statspack, the table space should be no less than 80M based on Oracle's claim, but it is recommended to be no less than 200M. Then execute the Spcreate.sql script to create the tables and packages required for Statspack. When it's all done, you can work on the statistics.

1 Create a snapshot of database performance data to perfstat the user to Oracle and perform STATSPACK.SNAP, then a statistical snapshot of the current database performance is established. You can use this tool to establish a baseline for database performance data

2 automatically set up database performance data snapshot, perform Spauto.sql, the default after the script execution, Oracle will take a snapshot every hour. You can modify the time interval by modifying the script

3 When the snapshot is more than two times, the report can be generated by spreport.sql.