SQL tuning Advisor in 10g

Tuning task created for specific a statement from the AWR.

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 36351,
                          end_snap    => 37452,
                          sql_id      => 'g2xhmn9fzz995',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'g2xhmn9fzz995_AWR_tuning_task',
                          description => 'Tuning task for statement g2xhmn9fzz995 in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute the task 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g2xhmn9fzz995_AWR_tuning_task');

Get the result

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('g2xhmn9fzz995_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

Generic ASM metadata

SPOOL ASM<#>_GENERIC_ASM_METADATA.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2 & 12.1
SET MARKUP HTML ON
SET ECHO ON

SET PAGESIZE 200

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;


SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';

SELECT * FROM V$INSTANCE;

SELECT * FROM GV$INSTANCE;

SELECT * FROM V$ASM_DISKGROUP;

SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER; 

SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;


SELECT * FROM V$ASM_CLIENT;

SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C 
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;

SELECT * FROM V$ASM_ATTRIBUTE;

SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;


SELECT * FROM V$VERSION;

SPOOL OFF

Database trend analysis

Active Session

select to_char(end_interval_time,'mm-dd hh24') snap_time
, instance_number
, avg(v_ps) pSec
from (
select end_interval_time
, instance_number
, v/ela v_ps
from (
select round(s.end_interval_time,'hh24') end_interval_time
, s.instance_number
, (case when s.begin_interval_time = s.startup_time
then value
else value - lag(value,1) over (partition by sy.stat_id
, sy.dbid
, s.instance_number
, s.startup_time
order by sy.snap_id)
end)/1000000 v
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
from dba_hist_snapshot s
, dba_hist_sys_time_model sy
where s.dbid = sy.dbid
and s.instance_number = sy.instance_number
and s.snap_id = sy.snap_id
and sy.stat_name = 'DB time'
and s.end_interval_time > to_date(&start_time,'MMDDYYYY')
and s.end_interval_time < to_date(&end_time,'MMDDYYYY') ))
group by to_char(end_interval_time,'mm-dd hh24'), instance_number
/

DB Growth

-- Get the datbase block size from dba_hist_datafile
WITH ts_info as (
select dbid, ts#, tsname, max(block_size) block_size
from dba_hist_datafile
group by dbid, ts#, tsname),
-- Get the maximum snaphsot id for each day from dba_hist_snapshot
snap_info as (
select dbid,to_char(trunc(end_interval_time,'DD'),'MM/DD/YY') dd, max(s.snap_id) snap_id
from dba_hist_snapshot s
where s.end_interval_time > to_date(&start_time,'MMDDYYYY')
and s.end_interval_time < to_date(&end_time,'MMDDYYYY')
group by dbid,trunc(end_interval_time,'DD'))
-- Sum up the sizes of all the tablespaces for the last snapshot of each day
select s.dd, s.dbid, sum(tablespace_size*f.block_size)
from dba_hist_tbspc_space_usage sp,
ts_info f,
snap_info s
where s.dbid = sp.dbid
and s.snap_id = sp.snap_id
and sp.dbid = f.dbid
and sp.tablespace_id = f.ts#
group by s.dd, s.dbid
order by s.dd
/

CPU Usage

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, os.instance_number
, os.value
from dba_hist_snapshot s
, dba_hist_osstat os
where s.dbid = os.dbid
and s.instance_number = os.instance_number
and s.snap_id = os.snap_id
and os.stat_name = 'LOAD'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and os.instance_number=1
order by to_char(trunc(s.end_interval_time,'hh24'),'mm-dd hh24'), os.instance_number
/

Active Session per wait class

select to_char(end_time,'mm-dd hh24') snap_time
, wait_class
, sum(pSec) avg_sess
from
(select end_time
, wait_class
, p_tmfg/1000000/ela pSec
from (
select round(s.end_interval_time,'hh24') end_time
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
, s.snap_id
, wait_class
, e.event_name
, case when s.begin_interval_time = s.startup_time
then e.time_waited_micro_fg
else e.time_waited_micro_fg
- lag(time_waited_micro_fg) over (partition by event_id
, e.dbid
, e.instance_number
, s.startup_time
order by e.snap_id)
end p_tmfg
from dba_hist_snapshot s
, dba_hist_system_event e
where s.dbid = e.dbid
and s.instance_number = e.instance_number
and s.snap_id = e.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and e.wait_class != 'Idle'
union all
select trunc(s.end_interval_time,'hh24') end_time
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
, s.snap_id
, t.stat_name wait_class
, t.stat_name event_name
, case when s.begin_interval_time = s.startup_time
then t.value
else t.value
- lag(value) over (partition by stat_id
, t.dbid
, t.instance_number
, s.startup_time
order by t.snap_id)
end p_tmfg
from dba_hist_snapshot s
, dba_hist_sys_time_model t
where s.dbid = t.dbid
and s.instance_number = t.instance_number
and s.snap_id = t.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and t.stat_name = 'DB CPU'))
group by to_char(end_time,'mm-dd hh24'), wait_class
order by to_char(end_time,'mm-dd hh24'), wait_class

/

Buffer Cache

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, g.instance_number
, g.bytes/1044/1024 mbytes
from dba_hist_snapshot s
, dba_hist_sgastat g
where s.snap_id = g.snap_id
and s.instance_number = g.instance_number
and s.dbid = g.dbid
and g.name = 'buffer_cache'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
order by to_char(s.end_interval_time,'mm-dd hh24:mi')
, g.instance_number

/

IO stat file type 

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, instance_number
, sum(megabytes) / 1024 Gigabytes
from
(
select end_interval_time
, instance_number
, megabytes
from
(
select s.snap_id
, s.instance_number
, s.dbid
, s.end_interval_time
, case when s.begin_interval_time = s.startup_time
then nvl(ft.small_read_megabytes+large_read_megabytes,0)
else nvl(ft.small_read_megabytes+large_read_megabytes,0) -
lag(nvl(ft.small_read_megabytes+large_read_megabytes,0),1)
over (partition by ft.filetype_id
, ft.instance_number
, ft.dbid
, s.startup_time
order by ft.snap_id)
end megabytes
from dba_hist_snapshot s
, dba_hist_iostat_filetype ft
, dba_hist_iostat_filetype_name fn
where s.dbid = ft.dbid
and s.instance_number = ft.instance_number
and s.snap_id = ft.snap_id
and s.dbid = fn.dbid
and ft.filetype_id = fn.filetype_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and fn.filetype_name = 'Data File'
)
)
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
/

Logical reads sysstat

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, instance_number
, avg(pSec) perSec
from ( select end_interval_time
, instance_number
, greatest(v/ela,0) pSec
from (
select /*+ leading(s,sn,sy) */ s.snap_id
, s.instance_number
, s.dbid
, s.end_interval_time
, case when s.begin_interval_time = s.startup_time
then sy.value
else sy.value - lag(sy.value,1) over (partition by sy.stat_id
, sy.instance_number
, sy.dbid
, s.startup_time
order by sy.snap_id)
end v
, (cast(end_interval_time as date) - cast(begin_interval_time as date))*24*3600 ela
from dba_hist_snapshot s
, dba_hist_sysstat sy
, dba_hist_stat_name sn
where s.dbid = sy.dbid
and s.instance_number = sy.instance_number
and s.snap_id = sy.snap_id
and s.dbid = sn.dbid
and sy.stat_id = sn.stat_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and sn.stat_name = 'session logical reads'
)
)
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
/

PGA usage

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, g.instance_number
, g.value/1048576 mbytes
from dba_hist_snapshot s
, dba_hist_pgastat g
where s.snap_id = g.snap_id
and s.instance_number = g.instance_number
and s.dbid = g.dbid
and g.name = 'total PGA allocated'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
order by to_char(s.end_interval_time,'mm-dd hh24:mi')
, g.instance_number
/


SQL Elapsed

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, sql_id
, sum(elapsed_time_delta/1000000)/decode(sum(executions_delta),0,null,sum(executions_delta))
avg_elapsed
from dba_hist_sqlstat sq
, dba_hist_snapshot s
where sq.sql_id (+) = &sql_id
and sq.dbid (+) = s.dbid
and sq.instance_number (+) = s.instance_number
and sq.snap_id (+) = s.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), sql_id
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24')
/

AWR command reference

1.To check the setup

select * from dba_hist_wr_control;

2.To change retention and/or period

SQL>  exec dbms_workload_repository.modify_snapshot_settings(retention=>90*24*60, interval=>30);

Above command makes retention to 90 days
and inter to 30 mins

Script to monitor export/import status

EXPDP/IMPDP:
set lines 300
set pagesize 3000
COLUMN percent FORMAT 999.99
select sid "Session Id",sofar*8/1024/1024 "Completed Gb",TOTALWORK*8/1024/1024 "Total Size in Gb" ,ROUND(SOFAR/TOTALWORK*100,2) "Completed
%",time_remaining/60/60 "Expected Time in Hr" FROM V$SESSION_LONGOPS  WHERE OPNAME LIKE '%EXP%' AND OPNAME NOT LIKE '%aggregate%' AND
TOTALWORK !=0 AND SOFAR <> TOTALWORK;

Script to check RMAN backup status

RMAN:
set lines 300
set pagesize 3000
COLUMN percent FORMAT 999.99
select sid "Session Id",sofar*8/1024/1024 "Completed Gb",TOTALWORK*8/1024/1024 "Total Size in Gb" ,ROUND(SOFAR/TOTALWORK*100,2) "Completed  %",time_remaining/60/60 "Expected Time in Hr" FROM V$SESSION_LONGOPS  WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND
TOTALWORK !=0 AND SOFAR < TOTALWORK;