AWR REPORT (Automatic Workload Repository)

AWR REPORT (Automatic Workload Repository)

Taking snapshot

Sql>exec dbms_workload_repository.create_snapshot;

Generating AWR report

Sql>@$ORACLE_HOME/rdbms/admin/awrrpt.sql;

Modifying the default setting of snapshots:

Sql> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200

,interval=>30);

Generating An AWR compare Period Report

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Dropping the snapshots

Sql>exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>22,

        high_snap_id=>32);

Wait event in awr report

a)db_file scattered read :=>this wait event usually cause by large full table scan

b)db file sequential read:=>this wait event usually cause when problem with join

     operation.and wait on range or index scan.

c)buffer busy:=>this event is caused by concurrent acces to buffer in buffer cache.

d)free buffer:=>this event indicate that oracle waited many times for free buffer in buffer

     cache

f)latch free:=>this event is often caused by not using bing variable in sql statement;

ADDM(Automatic Database Diagnostic Monitor)

Generating addm report

Sql>@$ORACLE_HOME/rdbms/admin/addmrpt.sql;

Generating Ash report

Sql>$ORACLE_HOME/rdbms/admin/ashrpt.sql;

SQL Tracing

·         Set the sql_trace parameter

·          Trace the Query

·          From tkprof utility convert trace file to readable output file.

SYS :

Sql>alter session set sql_trace=true;

Sql>select * from u1.emp where empno=1212;

Sql>alter session set sql_trace=false;

A trace file will be generated in  user_dump_dest location .

Converting trace file into human readable format using tkprof utility

$tkprof  u1.emp.trc    trace.output

Tracing the query from user session through dbms_monitor Pl/sql package

 Sql>exec   dbms_monitor.session_trace_enable(SESSION_ID=>34,SERIAL_NUM=>23);

·         Ask the user to run the query from his own session

·         Once the query is processed then disable the trace session.

Sql>exec dbms_monitor.session_trace_disable(SESSION_ID=>34,SERIAL_NUM=>23);

* From tkprof utility convert trace file to readable output file

EXPLAIN PLAN

Sql>explain plan set statement_id=’ID1’ for select * from u1.emp where empno=66;

Sql>select operation,options from plan_table where statement_id=’ID1’;