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;

Smart flash log is not created on one or more storage server

On our exadata , we got the following issue reported in exacheck

Smart flash log is not created on one or more storage server

we checked the status

CellCLI> list flashlog attributes name,size

CellCLI> list flashcache
cellnode_FLASHCACHE normal

We found Flashdisk is configured but not the flashlog

To create Exadata Smart Flash Log when Exadata Smart Flash Cache is already enabled, at the default sizings for both, enter the following "cellcli" commands as the "celladmin" user:

[root@cellnode1~]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Thu Nov 14 22:07:56 GMT+05:30 2013

Copyright (c) 2007, 2012, Oracle.  All rights reserved.
Cell Efficiency Ratio: 6,568

CellCLI> drop flashcache
Flash cache cellnode1_FLASHCACHE successfully dropped

CellCLI> create flashlog all
Flash log cellnode1_FLASHLOG successfully created

CellCLI> create flashcache all
Flash cache cellnode1_FLASHCACHE successfully created

CellCLI> list flashlog attributes name,size
         cellnode1_FLASHLOG       512M

CellCLI> exit


NOTE: Exadata Smart Flash Log is created by default with Exadata Storage Server Software version 11.2.2.4.0 and above.

NOTE: Exadata Smart Flash Log will be used by Oracle software 11.2.0.2 Bundle Patch 9 (or higher) or 11.2.0.3.0. The recommended Oracle software version levels are 11.2.0.2 Bundle Patch 11 (or higher) or 11.2.0.3 Bundle Patch 1 (or higher).

NOTE: The default Exadata Smart Flash Log size of 512MB is the recommended value.
NOTE: See also "Configure Storage Server Flash Memory as Exadata Smart Flash Cache"