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';
