Oracle Performance

Home


Alert log file:

If an error occurs while your oracle instance is running , the error messages writted to the Alert log file.

During the startup of the database, if Alert log file does not exist , the oracle create new one.

The database Alert log file is chronological log of messages and errors. Oracle server users the Alert log file as an alternative to display such information.
Check the alert log file regularly to:

1) Detect internel errors (ORA-600) and block corruption errors.
2) Monitor database operations.
3) View the nondefault initializaion parameters
init.ora parameter tht controls the location of the alert log file is BACKGROUND_DUMP_DEST

name of the log aler_SID.log

————————————————————————–

General Session-Related Statistics:

The oracle Server displays all calculated session statistics in the V$SESSSTAT view.
you can query this view to find session cumulative totals since instance started.

Ex: Determine the sessions that consume more than 30,000 bytes of program

select username,name,value from v$statname n, v$session s,v$sesstat t where s.sid=t.sid and n.statistic#=t.statistic# and s.type=’USER’and s.username is not null and n.name=’session pga memory’ and t.value > 30000;

Session waiting events statistics:

The V$SESSIOn_EVENT view shows, by session , the total waits for a particular event since instance startup

---

SGA Global statistics parameter:

SELECT * FROM V$sgastat;

---

User Trace files : Trace files can also be generated by server processes, at the user’s request , to display resource consumption during statement processing.

The following statement enables tracing particular session:
EXECUTE dbms_system.set_sql_trace-in_session (8,12,TRUE);

The following statement enables tracing for the session of the connected users:
ALTER SESSIOn SET sql_trace=TRUE;

Initilization parameters:

BACKGROUND_DUMP_DEST
USER_DUMP_DEST
MAX_DUMP_FILE_SIZE
SQL_TRACE