Sources of tuning information | Oracle DBA Tutorial pdf

Sources of tuning information

=> Alert log file

This file records information and error messages for various database activities.
This file is located at BACKGROUND_DUMP_DEST folder. The name format is alert_SID.log.

=> Trace files

Background process trace files contains session information for process that created them. These files are available in BACKGROUND_DUMP_DEST folder. Name format for the trace file is usually ID_PROCESS_nnnn.trc e.g. mdb_lgwr_1598.trc.
User trace files are found in USER_DUMP_DEST folder. These are created when error occurs in user’s server process. User tracing can be enabled using

ALTER SESSION SET SQL_TRACE = TRUE command or by executing package as
EXEC
SYS.DBMS_SYSTEM.SET_SQL_TRACT_IN_SESSION
(sid, serial#, TRUE)

=> Views commonly used in tuning

V$SGASTAT
V$EVENT_NAME
V$SYSTEM_EVENT
V$SESSION_EVENT
V$SESSION_WAIT
V$STATNAME
V$SYSSTAT
V$SESSTAT
V$SESSION
V$WAITSTAT

DBA_TABLES
DBA_INDEXES
DBA_STATS
DBA_DATA_FILES
DBA_SEGMENTS
DBA_HISTOGRAMS