AWR REPORT (Automatic Workload Repository) Practical

AWR REPORT (Automatic Workload Repository)

~]$ export ORACLE_SID=dev

~]$ sqlplus / as sysdba

SQL> select name,instance_name,status from v$database,v$instance;    (open)

Taking snapshot

SQL> exec dbms_workload_repository.create_snapshot;

SQL> select DBID,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot;

Generating AWR report

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify the Report Type

Enter value for report_type: html

Specify the number of days of snapshots to choose from

Enter value for num_days: 1                          (base on snap id how much there)

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

End Snapshot Id specified: 2

Specify the Report Name

Enter value for report_name: awrrpt.html

SQL> exit

~]$ firefox awrrpt.html

~]$ export ORACLE_SID=dev

~]$ sqlplus / as sysdba

Dropping the snapshots

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

high_snap_id=>22);

~]$ export ORACLE_SID=dev

~]$ sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 3

End Snapshot Id specified: 3

Specify the Report Name

Enter value for report_name: addm.html

Sql> exit

~]$ Firefox addm.html

~]$ export ORACLE_SID=dev

~]$ sqlplus / as sysdba

Generating Ash report

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

Specify the Report Type

Enter value for report_type: html

Instances in this Workload Repository schema

Using instance number(s): 1

Specify the Report Name

Enter value for report_name: ash.html

SQL> exit

~]$ firefox ash.html

~]$ export ORACLE_SID=prod

~]$ sqlplus / as sysdba

SQL> grant connect,resource to u1 identified by u1;

SQL> conn u1/u1

SQL> create table emp (empno number(10),empname varchar(10));

SQL> insert into emp values(&empno,'&empname');

(Again 4 time / for insert values) then               sql> commit;

SQL> conn / as sysdba

SQL> alter session set sql_trace=true;

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

SQL> alter session set sql_trace=false;

SQL> exit

A trace file will be generated in user_dump_dest location

~]$ cd app

app]$ cd diag/

diag]$ cd rdbms/

rdbms]$ cd prod/

prod]$ cd prod/

prod]$ cd trace/

trace]$ ls –ltr

Converting trace file into human readable format using tkprof utility

trace]$ tkprof prod_ora_23552.trc

:q!

~]$ export ORACLE_SID=prod

~]$ sqlplus / as sysdba

SYS>grant connect,resource to riaz identified by riaz;

SYS>conn riaz/riaz

Open new terminal

~]$ export ORACLE_SID=prod

~]$ sqlplus / as sysdba

SYS>

SYS>select SERIAL#,USERNAME,SID from v$session where username='RIAZ';

SYS>exec dbms_monitor.session_trace_enable(SESSION_ID=>1,SERIAL_NUM=>23);

SYS>exec dbms_monitor.session_trace_disable(SESSION_ID=>1,SERIAL_NUM=>23);

~]$ export ORACLE_SID=prod

~]$ sqlplus / as sysdba

SYS>create user u1 identified by u1;

SYS>grant connect,resource to u1;

SYS>conn u1/u1

U1>create table emp (eno number(10),ename varchar2(10));

U1>insert into emp values(&eno,'&ename');

(Again 4 time / for insert values) then               sql> commit;

U1>select ename, rowid, rownum from emp;

U1>explain plan set statement_id='ID1' for select * from emp where ename='JACK';

U1>select * from table(dbms_xplan.display('PLAN_TABLE','ID1','BASIC'));

U1>select operation,options from plan_table where statement_id='ID1';