Category Archives: Tuning

Troubleshooting Long Running Queries

Troubleshooting Flow For Long Running Queries

Contents
___________________________________________________________________________________________________

1. Flow Chart
2. Logon to Database
3. Find Long Running Sessions
4. Blocking sessions
5. Kill Sessions
6. SQL History
7. Find Force Matching Signature
8. SQL Tuning Adviser
9. SQLT
10. SQL Health Check
11. SQL Plan Flip
12. Find Stale Stats
13. Gather Stats
14. PIN Optimal Plan
15. Find Fragmentation
16. De-Fragmentation
_________________________________________________________________________________________________


1. Flow Chart

*** Please click on each box in flow chart below to take you to respective script section

2. Logon to Database

select name,open_mode from v$database;


3. Find Long Running Sessions

/*************************************************************************
Check the ALL Active/Inactive session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER'
 order by logon_time;

/*************************************************************************
Check the all Active session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.status='ACTIVE'
 order by logon_time;


/*************************************************************************
Check the ALL Active/Inactive sessions by SID
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.SID='&SID'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;

/*************************************************************************
Check the ALL Active/Inactive sessions by Username
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.username='&username'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;


/*************************************************************************
SQL Monitor
**************************************************************************/
set lines 1000 pages 9999 
column sid format 9999 
column serial for 999999
column status format a15
column username format a10 
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20

SELECT * FROM
       (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
     MODULE,program,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
         ROUND(cpu_time    /1000000)                      AS "CPU (s)",
         substr(sql_text,1,30) sql_text
       FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%' 
       ORDER BY sql_exec_start  desc
       );

/*************************************************************************
---- Sql-Monitor report for a sql_id         ( Like OEM report)
**************************************************************************/
column text_line format a254
set lines 750 pages 9999
set long 20000 longchunksize 20000
select 
 dbms_sqltune.report_sql_monitor_list() text_line 
from dual;

select 
 dbms_sqltune.report_sql_monitor() text_line 
from dual;


4. Blocking sessions

**** To find Blocking GOOD query 

set lines 750 pages 9999
col blocking_status for a100 
 select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' )  is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2
order by s1.inst_id;

**** Check who is blocking who in RAC, including objects

SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
WHERE (id1, id2, gv$lock.type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;


5. Kill Sessions

select 'alter system kill session ' || '''' || sid || ',' || serial# ||',@'|| inst_id || '''' || ' immediate;' from gv$session where sid='&sid';


6. SQL History

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


7. Find Force Matching Signature

col exact_matching_signature for 99999999999999999999999999
col sql_text for a50
set long 20000
set  lines 750 pages 9999
select sql_id, exact_matching_signature, force_matching_signature, SQL_TEXT from v$sqlarea where sql_id='&sql_id';
UNION ALL
select sql_id,force_matching_signature,SQL_TEXT from dba_hist_sqltext where sql_id='&sql_id'
/

-- If you want to find Bind variable for SQL_ID

col VALUE_STRING for a50  
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';


8. SQL Tuning Advisor


9. SQLT


10. SQL Health Check

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)


11. SQL Plan Flip

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


12. Find Stale Stats

http://www.br8dba.com/statistics/

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

*** statistics of objects of a specific sql id 

set lines 300 set pages 300
col table_name for a40
col owner for a30 
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
  from dba_tab_statistics
  where (owner, table_name) in
  (select distinct owner, table_name
          from dba_tables
          where ( table_name)
          in ( select object_name
                  from gv$sql_plan
                  where upper(sql_id) = upper('&sql_id') and object_name is not null))
  --and STALE_STATS='YES'
/


13. Gather Stats


14. PIN Optimal Plan

-- Run below script, you can download this script from SQLT
-- Reference: http://www.br8dba.com/how-to-create-custom-sql-profile/
@coe_xfr_sql_monitor.sql <sql_id> <plan_hash_value> -- to create SQL profile
The coe_xfr_sql_profile.sql script would create another sql file, which should be run to create manual sql profile for the sql 
The new sql file created.


ls -ltr coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql
cat coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql

@coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


15. Find Fragmentation

*** Table Fragmentation

select 
   table_name,round((blocks*8),2) "size (kb)" , 
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from 
   dba_tables
where  owner='&OWNER' and table_name='&TABLE_NAME' and 
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;


16. De-Fragmentation

*** There are many methods.

Option 1: Shrink command

alter table  enable row movement;
/*
Using the enable row movement clause can reduce the clustering_factor for a primary access index, causing excessive I/O.  Oracle introduced the sorted gash cluster as a way to keep an index in-order with the table rows, a technique that greatly reduces I/O for common queries. 
Beware that using "enable row movement" can cause Oracle to move rows to discontinuous data blocks, causing a performance problem.  Remember, the physical placement of rows on data blocks can still make a huge difference in query performance. 
*/
alter table  shrink space compact;
alter table  shrink space cascade;

http://www.dba-oracle.com/t_enable_row_movement.htm

Option 2: Table move

Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same.  The alter table move syntax also preserves the index and constraint definitions.

ALTER TABLE <table_name>  MOVE;

Option 3: EXPORT / IMPORT

** EXPORT
** DROP ALL RESPECTIVE OBJECTS
** IMPORT FROM EXPORT BACKUP


Option 4: EXPORT / IMPORT WITH TABLE_EXISTS_ACTION=REPLACE

** EXPORT
** IMPORT USING TABLE_EXISTS_ACTION=REPLACE

Option 5: Other methods

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Sugivaradhan & Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +

TRCA – Trace Analyzer

Trace Analyzer TRCANLZR (TRCA)

1. Overview
2. How to get Trace Analyzer
3. Unzip
4. Install TRCA
5. How to trace session
6. How to find trace file
7. How to Run Trace Analyzer
8. Transfer trca_*zip to local machine
9. Uninstall TRCA


1. Overview

Trace Analyzer (TRCA) is a tool that inputs an EVENT 10046 SQL Trace file, connects to the database, and outputs a comprehensive report for process performance analysis and tuning.

Free download and use

9i, 10g and 11g databases

When you generate a raw Oracle SQL Trace file you are faced with the arduous task of analyzing this trace file. 

Of course, TKPROF has always been there to help you with this analysis.

It is about another, lesser known, utility called Trace Analyzer, that is everything TKPROF is and more. 

Why is Trace Analyzer better than TKPROF?

Provides the actual values of the bind variables in SQL.

Provides the hottest blocks, optimizer statistics for indexes and tables and other information not available through TKPROF.

Separates user recursive and internal recursive calls, unlike TKPROF.

Provides more detailed wait event information.

Generates an HTML report that is more readable and extensive than the text output generated by TKPROF.

However it should be noted that unlike TKPROF, Trace Analyzer requires objects in the database and that means more configuration work on each database that needs the utility installed.

On top of this, TKPROF executes more quickly than Trace Analyzer, due to Trace Analyzer conducting more analysis than TKPROF.


2. How to get Trace Analyzer

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)


3. Unzip

[oracle@rac2 ~]$ unzip trca.zip
Archive:  trca.zip
   creating: trca/
   creating: trca/run/
  inflating: trca/run/trcanlzr.sql
  inflating: trca/run/trcasplit.sql
   creating: trca/dict/
  inflating: trca/dict/trcadictexp.sql
  inflating: trca/dict/trcadictpurge.sql
  inflating: trca/dict/trcadictimp.sql
  inflating: trca/trca_instructions.html
   creating: trca/doc/
  inflating: trca/doc/trca_changes.html
   creating: trca/install/
  inflating: trca/install/tacpkgg.pkb
  inflating: trca/install/setup.sql
  inflating: trca/install/tasqdirset.sql
  inflating: trca/install/tacpkgx.pkb
  inflating: trca/install/tacpkge.pkb
  inflating: trca/install/install.sql
  inflating: trca/install/tacpkgr.pkb
  inflating: trca/install/tacpkgi.pkb
  inflating: trca/install/tadobj.sql
  inflating: trca/install/tacpkgt.pkb
  inflating: trca/install/tacpkgx.pks
  inflating: trca/install/tacpkgt.pks
  inflating: trca/install/tacreate.sql
  inflating: trca/install/tacpkgp.pkb
  inflating: trca/install/tacpkg.sql
  inflating: trca/install/tacpkge.pks
  inflating: trca/install/tacpkgp.pks
  inflating: trca/install/tacvw.sql
  inflating: trca/install/tacpkgr.pks
  inflating: trca/install/tacommon1.sql
  inflating: trca/install/uninstall.sql
  inflating: trca/install/tadrop.sql
  inflating: trca/install/tadusr.sql
  inflating: trca/install/tacusr.sql
  inflating: trca/install/tacpkgi.pks
  inflating: trca/install/tautltest.sql
  inflating: trca/install/tacpkgg.pks
  inflating: trca/install/tacobj.sql
   creating: trca/utl/
  inflating: trca/utl/tacdirs.sql
  inflating: trca/utl/trcapurge.sql
  inflating: trca/utl/tacdiri1.sql
  inflating: trca/utl/tacdiri2.sql
[oracle@rac2 ~]$

[oracle@rac2 ~]$ ls -ld trca
drwxr-xr-x 7 oracle oinstall 4096 Mar  2  2013 trca
[oracle@rac2 ~]$


4. Install TRCA

[oracle@rac2 ~]$ cd trca/install/
[oracle@rac2 install]$ ls -ltr tacreate.sql
-rw-r--r-- 1 oracle oinstall 3635 Apr  5  2013 tacreate.sql
[oracle@rac2 install]$

Connect as SYS
[oracle@rac2 install]$ sqlplus / as sysdba
SQL> @tacreate.sql

Output

[oracle@rac2 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 22:23:01 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @tacreate.sql
        zip warning: name not matched: *_ta*.log

zip error: Nothing to do! (TRCA_installation_logs_archive.zip)
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users TRCANLZR and TRCADMIN. Press RETURN to continue.
------------------- PRESS ENTER HERE ------------------
SQL>
SQL> REM If DROP USER command fails then a session is currently connected.
SQL> PRO

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER TRCANLZR CASCADE
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER TRCADMIN CASCADE
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE TRCA_USER_ROLE
DROP ROLE TRCA_USER_ROLE
          *
ERROR at line 1:
ORA-01919: role 'TRCA_USER_ROLE' does not exist


SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
  adding: 161102222307_01_tacreate.log (deflated 90%)


Define the TRCANLZR user password (hidden and case sensitive).

Specify TRCANLZR password:    <------
Re-enter password:

User TRCANLZR created
User TRCANLZR altered
User TRCADMIN created
User TRCADMIN altered

Set up TRCANLZR temporary and default tablespaces

Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Wait...


TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------
CAT                                       99
USERS                                    301

Above is the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS  <-----

Choose the TRCANLZR user temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Wait...

TABLESPACE_NAME
------------------------------
TEMP
TEST_TEMP

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP  <------

The main application user of TRCA is the schema
owner that generated the SQL Trace to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more TRCA users after this installation
is completed simply grant them the TRCA_USER_ROLE
role.

Main application user of TRCA: SCOTT <------

Type of TRCA repository

Create TRCA repository as Temporary or Permanent objects?
Enter T for Temporary or P for Permanent.
T is recommended and default value. <-------

Type of TRCA repository [T]: <------


TACUSR completed.
No errors.
  adding: 161102222327_02_tacusr.log (deflated 80%)

TAUTLTEST completed.
  adding: 161102222427_09_tautltest.log (deflated 60%)




TACOBJ completed.
  adding: 161102222427_03_tacobj.log (deflated 85%)

SQL> PRO Dropping Libraries for TRCA
Dropping Libraries for TRCA
SQL> SET TERM OFF;
tool_repository_schema: "TRCANLZR"
tool_administer_schema: "TRCADMIN"
role_name: "TRCA_USER_ROLE"
Creating Procedures
Creating Package Specs TRCA$G
No errors.
Creating Package Specs TRCA$P
No errors.
Creating Package Specs TRCA$T
No errors.
Creating Package Specs TRCA$I
No errors.
Creating Package Specs TRCA$E
No errors.
Creating Package Specs TRCA$R
No errors.
Creating Package Specs TRCA$X
No errors.
Creating Views
Creating Package Body TRCA$G
No errors.
Creating Package Body TRCA$P
No errors.
Creating Package Body TRCA$T
No errors.
Creating Package Body TRCA$I
No errors.
Creating Package Body TRCA$E
No errors.
Creating Package Body TRCA$R
No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Libraries

Tool Version
----------------
11.4.5.8 <----

Install Date
----------------
20161102

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /u01/app/oracle/diag/rdbms/cat/cat/trace
TRCA$INPUT2(VALID)      /u01/app/oracle/diag/rdbms/cat/cat/trace
TRCA$STAGE(VALID)       /u01/app/oracle/diag/rdbms/cat/cat/trace
user_dump_dest          /u01/app/oracle/diag/rdbms/cat/cat/trace
background_dump_dest    /u01/app/oracle/diag/rdbms/cat/cat/trace

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.5.1 2012/11/27 carlos.sierra $ */
VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.5.7 2013/04/05 carlos.sierra $ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
TACPKG completed.
  adding: 161102222432_04_tacpkg.log (deflated 80%)

TAUTLTEST completed.
  adding: 161102222443_09_tautltest.log (deflated 59%)


TRCA users must be granted TRCA_USER_ROLE before using this tool.

TACREATE completed. Installation completed successfully.
SQL>


5. How to trace session

For more information on trace. Please click here http://www.br8dba.com/trace/

[oracle@rac2 ~]$ sqlplus scott/tiger;

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

-- OR --

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Output

[oracle@rac2 ~]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 23:29:48 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*) from BIG_TABLE;

  COUNT(*)
----------
       100

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL>


6. How to find trace file

For more information. Please click here http://www.br8dba.com/trace/#7

SQL> show parameter user_dump_dest

NAME             TYPE        VALUE
---------------- ----------- ------------------------------
user_dump_dest   string      /u01/app/oracle/diag/rdbms/cat
                             /cat/trace
SQL>

[oracle@rac2 ~]$ cd /u01/app/oracle/diag/rdbms/cat/cat/trace
[oracle@rac2 trace]$ ls -ltr *MY_TRACE*
-rw-r----- 1 oracle oinstall   93 Nov  2 23:34 cat_ora_7583_MY_TRACE.trm
-rw-r----- 1 oracle oinstall 3083 Nov  2 23:34 cat_ora_7583_MY_TRACE.trc
[oracle@rac2 trace]$


7. How to Run Trace Analyzer

connect as application user
cd trca/run
sqlplus [apps user]/[apps pwd]
SQL> start trcanlzr.sql [name of your trace file]
SQL> start trcanlzr.sql largesql.trc <== your trace file 
SQL> start trcanlzr.sql trace_file.txt <== your text file

Output

[oracle@rac2 run]$ ls -ltr /home/oracle/trca/run/trcanlzr.sql
-rw-r--r-- 1 oracle oinstall 8587 Nov 23  2012 /home/oracle/trca/run/trcanlzr.sql
[oracle@rac2 run]$

cd /u01/app/oracle/diag/rdbms/cat/cat/trace

[oracle@rac2 trace]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 23:46:04 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/trca/run/trcanlzr.sql cat_ora_7583_MY_TRACE.trc

PL/SQL procedure successfully completed.


Parameter 1:
Trace Filename or control_file.txt (required)


Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: cat_ora_7583_MY_TRACE.trc

Analyzing cat_ora_7583_MY_TRACE.trc

DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR)
***
*** NOTE:
*** If you get one of these errors it means TRCANLZR is not installed:
***   PLS-00201: identifier 'TRCADMIN.TRCA$P' must be declared
***   ORA-00904: "TRCADMIN"."TRCA$G"."VALIDATE_USER": invalid identifier
*** In such case look for errors in NN_*.log files created during install.
***
... please wait ...
To monitor progress, login into another session and execute:
SQL> SELECT * FROM TRCADMIN.trca$_log_v;

... analyzing trace(s) ...



Trace Analyzer completed.
Review first trcanlzr_error.log file for possible fatal errors.
Review next trca_e53968.log for parsing messages and totals.

Copying now generated files into local directory


TKPROF: Release 11.2.0.3.0 - Development on Wed Nov 2 23:46:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



TKPROF: Release 11.2.0.3.0 - Development on Wed Nov 2 23:46:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  adding: trca_e53968.html (deflated 87%)
  adding: trca_e53968.log (deflated 85%)
  adding: trca_e53968_nosort.tkprof (deflated 78%)
  adding: trca_e53968_sort.tkprof (deflated 78%)
  adding: trca_e53968.txt (deflated 83%)
  adding: trcanlzr_error.log (deflated 82%)
test of trca_e53968.zip OK
deleting: trcanlzr_error.log
Archive:  trca_e53968.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
    72567  11-02-2016 23:46   trca_e53968.html
    18765  11-02-2016 23:46   trca_e53968.log
     5486  11-02-2016 23:46   trca_e53968_nosort.tkprof
     5518  11-02-2016 23:46   trca_e53968_sort.tkprof
    36026  11-02-2016 23:46   trca_e53968.txt
---------                     -------
   138362                     5 files

File trca_e53968.zip has been created  

TRCANLZR completed.
SQL>


8. Transfer trca_*zip to local machine

Transfer local machine using WINSCP or FTP or Other

unzip trca_e53968.zip

trca_e53968.html
trca_e53968.log
trca_e53968_nosort.tkprof
trca_e53968_sort.tkprofUninstalling TRCA
trca_e53968.txt

For sample zip file. Please click here trca_e53968


9. Uninstall TRCA

Execute tadrop.sql connected as SYS
TRCANLZR schema objects and user are dropped
No parameter values are requested
No spool files are created during uninstall

Connect as SYS
Execute trca/install/tadrop.sql

Example:
cd trca/install
sqlplus /nolog
SQL> connect / as sysdba
SQL> start tadrop.sql

Output

[oracle@rac2 ~]$ cd trca/install
[oracle@rac2 install]$ ls -ltr tadrop.sql
-rw-r--r-- 1 oracle oinstall 1308 Nov 22  2012 tadrop.sql
[oracle@rac2 install]$

[oracle@rac2 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 3 00:01:31 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @tadrop.sql <----
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users TRCANLZR and TRCADMIN. Press RETURN to continue.

------------------- PRESS ENTER HERE ------------------

SQL>
SQL> REM If DROP USER command fails then a session is currently connected.
SQL> PRO

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER TRCANLZR CASCADE

User dropped.

SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER TRCADMIN CASCADE

User dropped.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE TRCA_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

Note 224270.1 Trace Analyzer
Note 215187.1 SQLTXPLAIN
Note 39817.1 Interpreting Raw SQL_Trace

Thank you,
Rajasekhar Amudala

CONTROL_MANAGEMENT_PACK_ACCESS

How to Disable CONTROL_MANAGEMENT_PACK_ACCESS

1. Overview
2. Display current value
3. Disbale control_management_pack_access

1. Overview

We can disable Oracle 11g ADDM, AWR using control_management_pack_access Parameter

we can disable the ADDM in Oracle 11g using control_management_pack_access initialization parameter. 

The values permitted for this parameter are

NONE Both below packs are not available

TUNING Only tuning packs (SQL Tuning advisor, SQLAccess Advisor) are avilable

DIAGNOSTIC+TUNING – This enables Diagnostic (AWR, ADDM) and tuning packs to the database. 

By default the database will be enabled with DIAGNOSTIC+TUNING as the value for the parameter.

When we disable the parameter by putting value as NONE the V$ACTIVE_SESSION_HISTORY table will be empty and ASH, AWR report will not show any content.


2. Display current value

SQL> show parameter control_management_pack_access

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>


SQL> SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
        64 <----


3. Disable control_management_pack_access

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

System altered.

SQL>

SQL> show parameter control_management_pack_access

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      NONE
SQL>

SQL> SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY;

  COUNT(*)
----------
         0 <-----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar

ASH

ASH Report – ASH (Active Session History) Reports

1. Overview
2. Ways of Gathering ASH
3. Desc V$active_session_history
4. ASH Parameters


1. Overview

Oracle ASH report introduced on Oracle 10GR2.

ASH is helpful to identify performance issues in old sessions

ASH collects samples of active sessions every second (waiting for non-idle events, or on the CPU working) from v$sessions (inactive sessions are not captured).

Sampled data collected to circular buffer in SGA and the same can be accessed through V$ views. V$ACTIVE_SESSION_HISTORY view provides the sampled session activity for the instance. 

Using the Active Session History you can examine and perform the detailed analysis on the current data in the V$ACTIVE_SESSION_HISTORY and the past data in the DBA_HIST_ACTIVE_SESS_HISTORY view.

ASH report is a small report compared to the AWR report which will provide the db/instance details for a short period of time.

ASH report covers a much shorter period of time (e.g. 5 min) compared to an AWR report (e.g. 30 min or 1 hour). 

ASH report captures the following things.

SQL identifier of SQL statement
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier

Major ASH report sections are:

Top User Events
Top Background Events
Top Cluster Events
Top Service/Module
Top SQL Command Types
Top Phases of Execution
Top Remote Instances
Top SQL with Top Events
....


2. Ways of Gathering ASH

2.1 Using ORADEBUG

ASHDUMP needs to be collected DURING the time of the issue
In case collecting from RAC , Gather ASH Dumps from ALL database instances at approximately the same time
SQL> oradebug setmypid 
SQL> oradebug unlimit
SQL> oradebug dump ashdump 5   # this will gather 5 minutes of ASH data, you may increase this if you feel necessary but try to keep it under 1 hour 
SQL> oradebug tracefile_name   # displays the trace file


2.2 Using ashrpt.sql or Enterprise Manager

ashrpt.sql
	SQL> @?/rdbms/admin/ashrpt
	Enter value for report_type:  HTML or TEXT [ HTML is default ]
	Enter value for begin_time: 
		Defaults to -15 mins
		To specify absolute begin time: [MM/DD[/YY]] HH24:MI[:SS]
		To specify relative begin time: (start with '-' sign) -[HH24:]MI
	Enter value for duration: Defualt is till current time
	Enter value for report_name:

2.3 Using v$active_session_history

Please note covering only ashrpt.sql here.

Output: Only ashrpt.sql

Example: If you want to take ASH report between 

Start time: 31-Oct-16 14:30:00
End time  : 31-Oct-16 14:45:00

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @?/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3175693255 W148P               1 w148p


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3175693255        1 W148P        w148p        rac2.rajasek
                                                har.com


Defaults to current database

Using database id: 3175693255

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  31-Oct-16 16:07:48   [     64 mins in the past]
Latest ASH sample available:  31-Oct-16 17:11:43   [      0 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: 10/31/16 14:30:00 <---- MM/DD/YY HH24:MI:SS
Report begin time specified: 10/31/16 14:30:00

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 15 <--- Enter duration in minutes starting from begin time
Report duration specified:   15

Using 31-Oct-16 14:30:00 as report begin time
Using 31-Oct-16 14:45:00 as report end time
..
..
..
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_1031_1445.html.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: raj_ash.html <----

Using the report name raj_ash.html

Summary of All User Input
-------------------------
Format         : HTML
DB Id          : 3175693255
Inst num       : 1
Begin time     : 31-Oct-16 14:30:00
End time       : 31-Oct-16 14:45:00
Slot width     : Default
Report targets : 0
Report name    : raj_ash.html
..
..
Report written to raj_ash.html
SQL>


3. Desc V$active_session_history


TIME_WAITED

TIME_WAITED  If the session was in a WAITING state, this column will show how long it had been waiting when Active Session History took the sample It captures information on active sessions every second
WAIT_TIME  If the session is doing productive work—not in a WAITING state—this column will show how long the session waited for the last wait event.


DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. 
This view contains snapshots of V$ACTIVE_SESSION_HISTORY.


4. ASH Parameters

_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:
–https://savvinov.com/2013/10/14/ash-basics/

Thank you,
Rajasekhar Amudala

AWR

AWR Report – AWR (Automatic Workload Repository) reports

0. Overview
1. AWR Setup
2. Find AWR snapshot interval and retention settings
3. Display snapshots
4. AWR Report Generation
5. Change AWR snapshot interval
6. Change AWR snapshot retention
7. Take AWR snapshot manually
8. Purge AWR Snapshots
9. How to find the snap_id for sql_text
10. Generating an AWR Report for a Single SQL Statement
11. Useful views

0. Overview

AWR Report – AWR (Automatic Workload Repository) reports are a useful diagnostic tool for the determination of the potential cause of database wide performance issues. 

Typically when a performance issue is detected you would collect an AWR report covering the period of the poor performance.

It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost.

Capture performed by MMON process

Captures data from V$ performance views

		–  Cumulative data e.g. V$SYSSTAT
		–  Data based on deltas e.g. V$SQL
		–  Active Session History
		–  Metrics
		
Stores data in AWR tables in SYSAUX  tablespace

     – WRH$_xxx for data 
     – WRM$_xxx for metadata
	 
Access data directly using  DBA_HIST_xxx  views

AWR snapshots resides in SYSAUX tablespace. 

By default snapshot are generated once every 60 min and maintained for 7 days. 

Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in "dba_hist_snapshot" view. 

$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR reports can be generated by running various SQL scripts to satisfy various requirements. 

Each report is available in HTML or TXT format:

 awrrpt.sql - Displays various statistics for a range of snapshots Ids

awrrpti.sql - Displays statistics for a range of snapshot Ids on a specified database and instance

awrsqrpt.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.

awrsqrpi.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

awrddrpt.sql - Compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql - Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

awrgrpt.sql - AWR GLOBAL Report(RAC)

awrgrpti.sql - Workload Repository RAC (Global) Report

awrgdrpt.sql - AWR GLOBAL DIFF Report (RAC)

awrinfo.sql - Script to Output General AWR Information

awrblmig.sql - AWR Baseline Migrate

awrload.sql  - AWR LOAD: load awr from dump file

awrextr.sql  - AWR Extract

awrddinp.sql - Get inputs for diff report

awrgdinp.sql - Get inputs for global diff reports

awrgdrpi.sql - Workload Repository Global Compare Periods Report

awrginp.sql  - AWR Global Input

awrinpnm.sql - AWR Input Name

awrinput.sql - Get inputs for AWR report


1. AWR Setup

SQL> show parameter statistics_level

NAME                  TYPE        VALUE
--------------------- ----------- ----------
statistics_level      string      TYPICAL  

SQL>

To active the AWR change the system parameter statistics_level to one of three values. 

BASIC - this option disables the AWR
TYPICAL (default) - activates standard level of collection
ALL - same as typical but includes execution plans and timing info from the O/S

If the parameter is set to BASIC, you simply need to modify the parameter in order to start gathering AWR statistics for your database

alter system set statistics_level=TYPICAL scope=both;


SQL> set pages 999
SQL> set lines 180
SQL> col STATISTICS_NAME for a50
SQL> SELECT statistics_name, activation_level, system_status FROM v$statistics_level;

STATISTICS_NAME                                    ACTIVAT SYSTEM_S
-------------------------------------------------- ------- --------
Buffer Cache Advice                                TYPICAL ENABLED
MTTR Advice                                        TYPICAL ENABLED
Timed Statistics                                   TYPICAL ENABLED <--
Timed OS Statistics                                ALL     DISABLED
Segment Level Statistics                           TYPICAL ENABLED
PGA Advice                                         TYPICAL ENABLED
Plan Execution Statistics                          ALL     DISABLED
Shared Pool Advice                                 TYPICAL ENABLED
Modification Monitoring                            TYPICAL ENABLED
Longops Statistics                                 TYPICAL ENABLED
Bind Data Capture                                  TYPICAL ENABLED
Ultrafast Latch Statistics                         TYPICAL ENABLED
Threshold-based Alerts                             TYPICAL ENABLED
Global Cache Statistics                            TYPICAL ENABLED
Active Session History                             TYPICAL ENABLED
Undo Advisor, Alerts and Fast Ramp up              TYPICAL ENABLED
Streams Pool Advice                                TYPICAL ENABLED
Time Model Events                                  TYPICAL ENABLED
Plan Execution Sampling                            TYPICAL ENABLED
Automated Maintenance Tasks                        TYPICAL ENABLED
SQL Monitoring                                     TYPICAL ENABLED
Adaptive Thresholds Enabled                        TYPICAL ENABLED
V$IOSTAT_* statistics                              TYPICAL ENABLED

23 rows selected.

SQL>


2. Find AWR snapshot interval and retention settings

In below example the snapshot interval is one hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

SQL> 

-- OR --

SQL> select
   extract( day from snap_interval) *24*60+
  2    3     extract( hour from snap_interval) *60+
  4     extract( minute from snap_interval ) "Snapshot Interval Minutes",
  5     extract( day from retention) *24*60+
  6     extract( hour from retention) *60+
  7     extract( minute from retention ) "Retention Interval Minutes"
  8  from
  9     dba_hist_wr_control;

Snapshot Interval Minutes Retention Interval Minutes
------------------------- --------------------------
                       60                      11520 <--- 11520/60/24=8 Days

SQL>


3. Display snapshots

SQL> set lines 180
SQL> col MIN(BEGIN_INTERVAL_TIME) for a30
SQL> col max(BEGIN_INTERVAL_TIME) for a30
SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME)       MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME)
------------ ------------------------------ ------------ ------------------------------
          94 26-OCT-16 02.42.53.000 PM                98 29-OCT-16 02.51.39.000 PM

SQL>

SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ----------------------------------------
        96 26-OCT-16 03.30.53.159 PM
        98 29-OCT-16 02.51.39.000 PM
        94 26-OCT-16 02.42.53.000 PM
        97 26-OCT-16 04.30.55.068 PM
        95 26-OCT-16 02.53.52.122 PM

SQL>


4. AWR Report Generation

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 7 <---

Listing the last 7 days of Completed Snapshots

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                 94 26 Oct 2016 14:53      1
                                 95 26 Oct 2016 15:30      1
                                 96 26 Oct 2016 16:30      1
                                 97 26 Oct 2016 19:16      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 95 <---
Begin Snapshot Id specified: 95 <----

Enter value for end_snap: 96
End   Snapshot Id specified: 96



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_95_96.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: test_report.html  <--- Specify Report Name
..
-- < Output of report is shown across the screen > ---
..
Report written to test_report.html
SQL>

SQL> !ls -ltr test_report.html
-rw-r--r-- 1 oracle oinstall 488688 Oct 29 14:55 test_report.html

SQL>


5. Change AWR snapshot interval

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        interval  => 30); <--- Minutes (1/2 Hour)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL> 

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

The above example the snapshot interval is 1/2 hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.


6. Change AWR snapshot retention

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <-- Minutes (7 Days)
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (604800)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 2


SQL>

---------
10080 Minutes = This value i given, convert to seconds 10080*60=604800 (604800/60/60/24 = 7 Days)
691200 Seconds =  691200/60/60/24 = 8 days
---------

SQL> col BASELINE_NAME for a30
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  8 <---

SQL>


SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 7);
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  7 <--

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <---- Minutes (7 Days)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00007 00:00:00.0    DEFAULT 

SQL>

Now retention changed from 8 to 7 days.


7. Take AWR snapshot manually

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         102 <---

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         103 <---

SQL>


8. Purge AWR Snapshots

If you decide you do not need old snaps then you can purge using below example

SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
          94          103

SQL>

exec dbms_workload_repository.drop_snapshot_range(94,99);

-- OR ---

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 94,
    high_snap_id => 99);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

In the below output we don't see snaps b/w 94 to 99 <---
SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         100          103

SQL>


9. How to find the snap_id for sql_text

SQL> conn scott/tiger;
Connected.
SQL> set lines 180
SQL> select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

SQL> show user
USER is "SYS"
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL>

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         107 <---

SQL>
------------------------------------
col parsed format a6
col sql_text format a40
set lines 200
set pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 and
 lower(sql_text) like '%&t%' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;
------------------------------------

SQL> col parsed format a6
SQL> col sql_text format a40
SQL> set lines 200
SQL> set pages 300
SQL>
SQL> select
  2   sql_text,
  3   parsing_schema_name as parsed,
  4   elapsed_time_delta/1000/1000 as elapsed_sec,
  5   stat.snap_id,
  6   to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
  7   txt.sql_id
  8  from
  9   dba_hist_sqlstat stat,
 10   dba_hist_sqltext txt,
 11   dba_hist_snapshot snap
 12  where
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 13   14   15   snap.begin_interval_time>=sysdate-1 and
 16   lower(sql_text) like '%&t%' and
 17   parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
 18  order by elapsed_time_delta asc;
Enter value for t: select * from emp where ename
old  16:  lower(sql_text) like '%&t%' and
new  16:  lower(sql_text) like '%select * from emp where ename%' and

SQL_TEXT                                 PARSED ELAPSED_SEC    SNAP_ID SNAPTIME       SQL_ID
---------------------------------------- ------ ----------- ---------- -------------- -------------
select * from emp where ename='SMITH'    SCOTT      .020678        107 29.10 18:17:32 cgf95c3k5mszx

SQL> 


10. Generating an AWR Report for a Single SQL Statement

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Output

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1 <---

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                100 29 Oct 2016 16:00      1
                                101 29 Oct 2016 16:30      1
                                102 29 Oct 2016 16:41      1
                                103 29 Oct 2016 16:42      1
                                104 29 Oct 2016 17:00      1
                                105 29 Oct 2016 17:30      1
                                106 29 Oct 2016 18:00      1
                                107 29 Oct 2016 18:17      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 106 <--
Begin Snapshot Id specified: 106

Enter value for end_snap: 107
End   Snapshot Id specified: 107 <---




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cgf95c3k5mszx <----
SQL ID specified:  cgf95c3k5mszx

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_106_107.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: cgf95c3k5mszx_txt1 <----

Using the report name cgf95c3k5mszx_txt1


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
CAT            730180430 cat                 1 29-Oct-16 14:51 11.2.0.3.0  NO

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       106 29-Oct-16 18:00:44        28       1.1
  End Snap:       107 29-Oct-16 18:17:32        30       1.1
   Elapsed:               16.80 (mins)
   DB Time:                0.01 (mins)

SQL Summary                                   DB/Inst: CAT/cat  Snaps: 106-107

                Elapsed
   SQL Id      Time (ms)
------------- ----------
cgf95c3k5mszx         21
Module: SQL*Plus
select * from emp where ename='SMITH'

          -------------------------------------------------------------

SQL ID: cgf95c3k5mszx                         DB/Inst: CAT/cat  Snaps: 106-107
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select * from emp where ename='SMITH'

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   3956160932                     21             1           107            107
          -------------------------------------------------------------


Plan 1(PHV: 3956160932)
-----------------------

Plan Statistics                               DB/Inst: CAT/cat  Snaps: 106-107
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                   Statement   Per Execution % Snap
--------------------------- ---------- -------------- -------
Elapsed Time (ms)                   21           20.7     2.5
CPU Time (ms)                        2            2.0     0.2
Executions                           1            N/A     N/A
Buffer Gets                         85           85.0     0.5
Disk Reads                           8            8.0    14.5
Parse Calls                          1            1.0     0.1
Rows                                 1            1.0     N/A
User I/O Wait Time (ms)             18            N/A     N/A
Cluster Wait Time (ms)               0            N/A     N/A
Application Wait Time (ms)           0            N/A     N/A
Concurrency Wait Time (ms)           0            N/A     N/A
Invalidations                        0            N/A     N/A
Version Count                        1            N/A     N/A
Sharable Mem(KB)                    19            N/A     N/A
-------------------------------------------------------------

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
cgf95c3k5msz select * from emp where ename='SMITH'


Report written to cgf95c3k5mszx_txt1
SQL>


11. Useful views

dba_hist_active_sess_history --	ASH info (see below)
dba_hist_baseline -- baseline info
dba_hist_database_instance -- environment data
dba_hist_sql_plan -- sql execution path data
dba_hist_wr_control	-- AWR settings
dba_hist_snapshot -- snapshot info in the AWR
-- how to find the sqls between two snaps manually --
select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 100 and 107;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.datadisk.co.uk/html_docs/oracle/awr.htm
http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html

Thank you,
Rajasekhar Amudala

Export and Import statistics

Export and Import schema statistics from one database to another

On Source

0. Environment
1. Create table to store statistics
2. Store schema stats to table STATS_TABLE
3. Export the table STATS_TABLE using datapump
4. Transfer the dump to target server

On Target

5. Delete the stats before import on target server
6. Import using impdp
7. Importing stats into same schema dbms_stats
8. Importing into different schema


Environment:

Source:

OS: Oracle Linux 5.7
Database version : 11.2.0.3.0
Database Name: orcl
Schema Name: SH
Host name: rac1.rajasekhar.com

Target:

OS: Oracle Linux 5.7
Database version : 11.2.0.3.0
Database Name: cat
Schema Name: SH
Host name: rac2.rajasekhar.com


Step 1: Create table to store statistics

SQL> show user
USER is "SYS"
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('SH', 'STATS_TABLE');

PL/SQL procedure successfully completed.

SQL> select OWNER, OBJECT_NAME,object_type, CREATED from dba_objects where OBJECT_NAME='STATS_TABLE';

OWNER    OBJECT_NAME          OBJECT_TYPE         CREATED
-------- -------------------- ------------------- ---------
SH       STATS_TABLE          TABLE               31-AUG-16
SH       STATS_TABLE          INDEX               31-AUG-16

SQL>

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
         0

SQL>


Step 2: Export schema stats – will be stored in the ‘STATS_TABLE’

SQL> exec dbms_stats.export_schema_stats(ownname => 'SH',stattab => 'STATS_TABLE');

PL/SQL procedure successfully completed.

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
      3966

SQL>


Step 3: Export the table STATS_TABLE using datapump

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [orcl] ?
[oracle@rac1 ~]$ expdp directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=SH.STATS_TABLE

Export: Release 11.2.0.3.0 - Production on Wed Aug 31 16:59:34 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=SH.STATS_TABLE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SH"."STATS_TABLE"                          425.6 KB    3966 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/stats.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:58

[oracle@rac1 ~]$


Step 4: Transfer the dump to target server

[oracle@rac1 ~]$ cd /u01/app/oracle/admin/orcl/dpdump/
[oracle@rac1 dpdump]$ scp stats.dmp oracle@rac2.rajasekhar.com:/u01/app/oracle/admin/cat/dpdump/
stats.dmp 						 100%  532KB 532.0KB/s   00:00
[oracle@rac1 dpdump]$

On Target
===========

Step 5: Delete the stats before import on target server

-- before delete stats, please have backup but i am not taking here because it is test machine.
EXEC DBMS_STATS.CREATE_STAT_TABLE('SH', 'STATS');
exec dbms_stats.export_schema_stats(ownname => 'SH',stattab => 'STATS');
SQL> EXEC DBMS_STATS.delete_schema_stats('SH');

PL/SQL procedure successfully completed.

SQL>


Step 6: Importing STATS_TABLE table in scott schema

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [cat] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ impdp directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=impstats.log TABLES=SH.STATS_TABLE

Import: Release 11.2.0.3.0 - Production on Wed Aug 31 17:08:15 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=impstats.log TABLES=SH.STATS_TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."STATS_TABLE"                          425.6 KB    3966 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 17:08:20

[oracle@rac2 ~]$


SQL> select OWNER, OBJECT_NAME,object_type, CREATED from dba_objects where OBJECT_NAME='STATS_TABLE';

OWNER   OBJECT_NAME          OBJECT_TYPE         CREATED
------- -------------------- ------------------- ---------
SH      STATS_TABLE          INDEX               31-AUG-16
SH      STATS_TABLE          TABLE               31-AUG-16

SQL> select count(*) from sh.STATS_TABLE;

  COUNT(*)
----------
      3966

SQL>


Step 7: Importing into same schema(SH – SH), then ignore step 8

SQL> exec dbms_stats.import_schema_stats(OWNNAME=>'SH', STATTAB=>'STATS_TABLE');

PL/SQL procedure successfully completed.

SQL>


Step 8: Importing into different schema( USER A – USER B), then skip step 7

update newschema.STATS_TABLE set c5='NEW_SCHEMA_NAME';
commit;
dbms_stats.import_schema_stats(OWNNAME=>'NEW_SCHEMA_NAME', STATTAB=>'STATS');

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:
How to Transfer Statistics from one Database to Another (Doc ID 333742.1)
How to Use DBMS_STATS to Move Statistics to a Different Database (Doc ID 117203.1)

Statistics

About Oracle Statistics

1. Introduction
2. About *_TAB_MODIFICATIONS
3. Identify STALE STATS
4. Gather STATS
       4.1 DATABASE Level
       4.2 SCHEMA Level
       4.3 TABLE Level
       4.4 INDEX Level
5. SYSTEM STATISTICS
6. How lock/unlock statistics on table


Introduction

What are statistics?

Ans: Input to the Cost-Based Optimizer, Provide information on

User Objects
	Table, Partition, Subpartition
	Columns
	Index, Index Partition, Index Subpartition
System
Dictionary
Memory structure (X$)

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed
(total number of inserts, deletes, updates) in the table. 
Oracle monitors the DML activity for all tables and records it in the SGA. 
The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view.

Why do we care about statistics?

Poor statistics usually lead to poor plans
Collecting good quality stats is not straightforward
Collecting good quality stats may be time consuming
Improving statistics quality improves the chance to find an optimal plan (usually)
The higher the sample the higher the accuracy
The higher the sample the longer it takes to collect
The longer it takes the less frequent we can collect fresh stats!

If your data changes frequently, then

If you have plenty of resources:
Gather statistics often and with a very large sample size

If your resources are limited: 
Use AUTO_SAMPLE_SIZE (11g)
Use a smaller sample size (try to avoid this)

If your data doesn’t change frequently: 
Gather statistics less often and with a very large sample size


Recommended syntax

/*
Assuming we want Oracle to determine where to put histograms (instead of specifying the list manually):

In 10g avoid AUTO_SAMPLE_SIZE

exec dbms_stats.gather_table_stats('owner', 'table_name', estimate_percent => NNN,granularity => “it depends”);

In 11g use AUTO_SAMPLE_SIZE but keep an eye open. 
exec dbms_stats.gather_table_stats('owner', 'table_name');
*/


About *_TAB_MODIFICATIONS

When querying *_TAB_MODIFICATIONS view you should ensure that you run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before doing so in order to obtain accurate results.

Before

--  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- 

SQL> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name='BIG_TABLE';

no rows selected

SQL>


After 


SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name='BIG_TABLE';

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
BIG_TABLE                             100          0          0

SQL>


Identify STALE STATS:

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OR

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';


Gather STATS

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : You can specify one column between “” instead of all columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS : Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : Collect histograms for columns have skewed value should test skewness first
FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.


DATABASE Level
Gathering statistics for all objects in database, cascade will include indexes

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');
OR
-- For faster execution
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);
OR
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,CASCADE => TRUE,degree => 4);


SCHEMA level

Gathering statistics for all objects in a schema, cascade will include indexes. If not used Oracle will determine whether to collect it or not.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

set timing on

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
OR
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
-- CASCADE is not included here. Let Oracle will determine whether to collect statatics on indexes or not.
OR
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name'); Will gather stats on 100% of schema tables.


TABLE Level

-- The CASCADE parameter determines whether or not statistics are gathered for the indexes on a table.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

exec dbms_stats.gather_table_stats(ownname=>'&Schema_name',tabname=>'&Table_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree =>4);
OR
-- Gather statistics on the table with histograms being automatically created
exec dbms_stats.gather_table_stats('&SCHEMA_NAME','&Table_name');


Index Statistics

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);


SYSTEM STATISTICS

What is system statistics:

System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.

Why gathering system statistics:

Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

NOWORKLOAD statistics:

This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats(); 

WORKLOAD statistics:

This will gather statistics during the current workload [which supposed to be representative of actual system I/O and CPU workload on the DB].
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60); 

Check the system values collected:

col pname format a20
col pval2 format a40
select * from sys.aux_stats$; 

cpuspeedNW:  Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim:    The sum of seek time, latency time, and OS overhead time.
iotfrspeed:  I/O transfer speed,tells optimizer how fast the DB can read data in a single read request.
cpuspeed:      Stands for CPU speed during a workload statistics collection.
maxthr:          The maximum I/O throughput.
slavethr:      Average parallel slave I/O throughput.
sreadtim:     The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim:     The average time (seconds) for a sequential multiblock read.
mbrc:             The average multiblock read count in blocks.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.

Delete system statistics:

SQL> execute dbms_stats.delete_system_stats();


How lock/unlock statistics on table

1. Create table and verify 

SQL> create table raj ( x number );

Table created.

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
	 <---- Output NULL. Hence table unlocked. It will allow to gather stats on this table

SQL>

2. Lock stats

SQL> exec dbms_stats.lock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL> 

3. Verify

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
ALL <---- Hence table locked. It will not allow to gather stats on this table

SQL>

Tryied to gather stats, but fails

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');
BEGIN dbms_stats.gather_table_stats('sh', 'raj'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)  <-- LOCKED
4. Unlock


SQL> exec dbms_stats.unlock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

STATT
-----
	<----it's unlocked, It will allow to gather stats on this table

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');

PL/SQL procedure successfully completed.

SQL>

Locked: ALL
Unlocked: NULL

Other:

select status from dba_autotask_client where client_name = ‘auto optimizer stats collection’;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. We do not guarantee it will work for you. Ensure that you run it in your test environment before using.

Reference:
Master Note: Optimizer Statistics (Doc ID 1369591.1)
DBA_TAB_MODIFICATIONS Refreshed Only Once a Day from 10g (Doc ID 1476052.1)
https://blogs.oracle.com/optimizer/entry/improvement_of_auto_sampling_statistics_gathering_feature_in_oracle_database_11g
https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto
http://dba-tips.blogspot.in/2012/11/all-about-statistics-in-oracle.html