from http://kerryosborne.oracle-guy.com/
1.
create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,'dd-mon-yyyy');
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
==================================
table_stat1.sql <<<<<<<<<<<<<<--------------
set echo off feed off
set serveroutput on size 1000000
accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '
set lines 80
define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200
declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_avg_col_len number ;
v_max_low number ;
v_max_high number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;
cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, nvl(to_char(a.sample_size),' ') sample_size,
display_raw(low_value,data_type) low_value, display_raw(high_value,data_type) high_value
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table
order by a.column_name;
cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;
procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);
-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;
begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');
v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
v_ct := 0 ;
select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');
v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);
end if ;
select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1,
max(length(avg_col_len)) + 1,
max(length(display_raw(low_value,DATA_TYPE))) + 1,
max(length(display_raw(high_value,DATA_TYPE))) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl, v_max_avg_col_len, v_max_low, v_max_high
from all_tab_columns
where owner = v_owner
and table_name = v_table ;
if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;
if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;
if v_max_smpl < 7 then
v_max_smpl := 7;
end if;
if v_max_avg_col_len < 7 then
v_max_avg_col_len := 7;
end if;
if v_max_low < 4 then
v_max_low := 4;
end if;
if v_max_high < 4 then
v_max_high := 4;
end if;
dbms_output.put_line('=============================================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('=============================================================================================================');
dbms_output.put_line('' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
lpad(' NDV',v_max_ndv) || ' ' || lpad('Density',8) || ' ' ||
lpad('# Nulls',v_max_nulls) || ' ' || lpad('# Buckets',v_max_bkts) || ' ' ||
lpad('Sample',v_max_smpl) || ' ' || lpad('Avg Len',v_max_avg_col_len) || ' ' ||
lpad('Min',v_max_low) || ' ' || lpad('Max',v_max_high));
dbms_output.put_line('==============================================================================================================');
for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
lpad(v_rec.num_distinct,v_max_ndv) || ' ' ||
to_char(v_rec.density,'9.999999') || ' ' ||
lpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
lpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
lpad(v_rec.sample_size,v_max_smpl) || ' ' ||
lpad(v_rec.avg_col_len,v_max_avg_col_len) || ' ' ||
lpad(v_rec.low_value,v_max_low) || ' ' ||
lpad(v_rec.high_value,v_max_high));
end loop ;
select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;
if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;
if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;
select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);
/* Histogram data commented out
if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');
v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/
v_ct := 0;
select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;
if v_ct > 0 then
dbms_output.put_line('============================================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('============================================================================================================');
v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;
print_table (v_query);
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;
end ;
/
=====================================
SQL> @table_stat1
Owner : SCOTT
Table : EMP
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
old 3: v_owner varchar2(30) := upper('&&ownname');
new 3: v_owner varchar2(30) := upper('SCOTT');
old 4: v_table varchar2(30) := upper('&&tabname');
new 4: v_table varchar2(30) := upper('EMP');
==========================================================================================
Table Statistics
==========================================================================================
TABLE_NAME : EMP
LAST_ANALYZED : 09-SEP-2010 22:00:06
DEGREE : 1
PARTITIONED : NO
NUM_ROWS : 14
CHAIN_CNT : 0
BLOCKS : 5
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 37
MONITORING : YES
SAMPLE_SIZE : 14
-----------------
=============================================================================================================
Column Statistics
=============================================================================================================
Name Analyzed Null? NDV Density # Nulls # Buckets Sample Avg Len Min Max
==============================================================================================================
COMM 09/09/2010 4 .250000 10 1 4 2 0 1400
DEPTNO 09/09/2010 3 .333333 0 1 14 3 10 30
EMPNO 09/09/2010 NOT NULL 14 .071429 0 1 14 4 7369 7934
ENAME 09/09/2010 14 .071429 0 1 14 6 ADAMS WARD
HIREDATE 09/09/2010 13 .076923 0 1 14 8 17-dec-1980 23-may-1987
JOB 09/09/2010 5 .200000 0 1 14 8 ANALYST SALESMAN
MGR 09/09/2010 6 .166667 1 1 13 4 7566 7902
SAL 09/09/2010 12 .083333 0 1 14 4 800 5000
============================================================================================================
Index Information
============================================================================================================
INDEX_NAME : PK_EMP
INDEX_TYPE : NORM
LAST_ANALYZED : 09-SEP-2010 22:00:06
DEGREE : 1
PARTITIONED : NO
BLEVEL : 0
LEAF_BLOCKS : 1
DISTINCT_KEYS : 14
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 1
BLOCKS_IN_TABLE : 5
ROWS_IN_TABLE : 14
-----------------
==========================================================================================
Index Columns Information
==========================================================================================
Index Name Pos# Order Column Name Expression
==========================================================================================
Friday, 4 February 2011
Oracle Awr report run for Last 24 Hr .. on unix prompt
snap id info
alter session set "_push_join_predicate" = FALSE ; If awr running slow !!!!
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrrpt.sql --> basic AWR report
@?/rdbms/admin/awrsqrpt.sql --> Standard SQL statement Report
@?/rdbms/admin/awrddrpt.sql --> Period diff on current instance
@?/rdbms/admin/awrrpti.sql --> Workload Repository Report Instance (RAC)
@?/rdbms/admin/awrgrpt.sql --> AWR Global Report (RAC)
@?/rdbms/admin/awrgdrpt.sql --> AWR Global Diff Report (RAC)
@?/rdbms/admin/awrinfo.sql --> Script to output general AWR information
ADDM report !!!
SET LONG 500000 PAGESIZE 0
SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_20529') FROM DUAL;
col snap_id new_value last_snap
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
select snap_id,CON_ID,begin_interval_time,end_interval_time from dba_hist_snapshot
order by begin_interval_time desc
fetch first 3 row only ;
column BEGIN_INTERVAL_TIME format a25
column END_INTERVAL_TIME format a25
column STARTUP_TIME format a25
set lines 1000
select instance_number, snap_id , BEGIN_INTERVAL_TIME, END_INTERVAL_TIME,
STARTUP_TIME from dba_hist_snapshot where dbid=(select dbid from v$database) and BEGIN_INTERVAL_TIME
between to_date('12-sep-2021','dd-mon-yyyy') and to_date('13-sep-2021','dd-mon-yyyy')
and instance_number in (select instance_number from v$instance)
order by BEGIN_INTERVAL_TIME asc;
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;
define p_inst=1
define p_days=2
column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999
select * from (
select min(snap_id) as snap_id,
to_char(start_time,'dd-MM-YY') as dt, to_char(start_time,'HH24') as hr
from (
select snap_id, s.instance_number, begin_interval_time start_time,
end_interval_time end_time, snap_level, flush_elapsed,
lag(s.startup_time) over (partition by s.dbid, s.instance_number order by s.snap_id) prev_startup_time,
s.startup_time
from dba_hist_snapshot s, gv$instance i
where begin_interval_time between trunc(sysdate)-&p_days and sysdate
and s.instance_number = i.instance_number
and s.instance_number = &p_inst
order by snap_id
)
group by to_char(start_time,'dd-MM-YY') , to_char(start_time,'HH24')
order by snap_id, start_time )
pivot (sum(snap_id) for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23'))
order by dt
;
define num_days=2
SET LINESIZE 200 PAGESIZE 200
--UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999
SELECT
s.startup_time,
di.dbid,
di.instance_name,
MIN(snap_id) snap_start,
MAX(snap_id) snap_end,
MIN(end_interval_time) start_interval,
MAX(end_interval_time) end_interval,
EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Days(s) '
|| EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Hour(s) '
|| EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
|| ' Minute(s) ' range_interval,
MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM
dba_hist_snapshot s,
dba_hist_database_instance di
WHERE
di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) )
GROUP BY
s.startup_time,
di.dbid,
di.instance_name
ORDER BY startup_time ASC;
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=================
For Awr Report different spool file name
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
TODAY=$(date)
DATE=`date +%d%m%Y:%H:%M:%S`
# DATE=`date +%d%m%Y`
l_awr_log_file="Awrrpt_$DATE.log"
#echo $l_awr_log_file
DATE=`date +%d%m%Y:%H:%M:%S`
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
--spool select ' awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- spool $DATE
spool $l_awr_log_file
-- spool print x
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=========================
set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(task_name, 'TEXT', 'ALL') as ADDM_report
from dba_advisor_tasks where task_id=( select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');
select output
from table(
dbms_workload_repository.ash_report_text(
(select dbid from v$database),
1, -- instance id
sysdate - 2/24, -- startdate
sysdate - 1/24, -- enddate
0)
) ;
--- global Report
set head off pages 0 lines 300 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );
============
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
sqlplus -s /nolog <
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=================
For Awr Report different spool file name
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
TODAY=$(date)
DATE=`date +%d%m%Y:%H:%M:%S`
# DATE=`date +%d%m%Y`
l_awr_log_file="Awrrpt_$DATE.log"
#echo $l_awr_log_file
DATE=`date +%d%m%Y:%H:%M:%S`
sqlplus -s /nolog <
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
--spool select ' awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- spool $DATE
spool $l_awr_log_file
-- spool print x
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=========================
set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(task_name, 'TEXT', 'ALL') as ADDM_report
from dba_advisor_tasks where task_id=( select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');
select output
from table(
dbms_workload_repository.ash_report_text(
(select dbid from v$database),
1, -- instance id
sysdate - 2/24, -- startdate
sysdate - 1/24, -- enddate
0)
) ;
--- global Report
set head off pages 0 lines 300 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );
--- With Spool file
set head off pages 0 lines 300 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
column awr new_val X
select to_char(:BgnSnap||'_'||:EndSnap) awr from dual;
spool awr_&X._file.txt
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );
spool off
============
from
https://blog.yannickjaquier.com/oracle/script-generate-series-awr-reports.html
for last 4 awr text report ..
cat awr.sh
#!/bin/ksh
LOGFILE=/tmp/awr.log
sqlplus -s / as sysdba << EOF > $LOGFILE
set lines 130
set feedback off
set pages 0
select
'export dbid=' || d.dbid as dbid
from
v\$database d;
select
'export db_name=' || d.name as db_name
from
v\$database d;
select
'export inst_num=' || i.instance_number as inst_num
from
v\$instance i;
select
'export inst_name=' || i.instance_name as inst_name
from
v\$instance i;
select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;
select 'export endnum=' ||max(snap_id) as endnum from dba_hist_snapshot ;
EOF
while read line
do
eval $line
done < $LOGFILE
sqlplus -s / as sysdba << EOF
set lines 130
set pages 1000
select
snap_id,
to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat
from dba_hist_snapshot
order by snap_id;
EOF
echo ininum = $ininum
echo endnum = $endnum
# ininum=39050
# endnum=39052
while [ $ininum -lt $endnum ];
do
nxtnum=`expr $ininum + 1`
repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.txt'
sqlplus -s / as sysdba << EOF
define inst_num = $inst_num;
define num_days = 100;
define inst_name = $inst_name;
define db_name = $db_name;
define dbid = $dbid;
define report_type = 'text';
define begin_snap = $ininum;
define end_snap = $nxtnum;
define report_name = $repnam;
@@?/rdbms/admin/awrrpti
EOF
ininum=$nxtnum
done
=======================
for last 4 awr html report ..
cat awr.sh
#!/bin/ksh
LOGFILE=/tmp/awr.log
sqlplus -s / as sysdba << EOF > $LOGFILE
set lines 130
set feedback off
set pages 0
select
'export dbid=' || d.dbid as dbid
from
v\$database d;
select
'export db_name=' || d.name as db_name
from
v\$database d;
select
'export inst_num=' || i.instance_number as inst_num
from
v\$instance i;
select
'export inst_name=' || i.instance_name as inst_name
from
v\$instance i;
select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;
select 'export endnum=' ||max(snap_id) as endnum from dba_hist_snapshot ;
EOF
while read line
do
eval $line
done < $LOGFILE
sqlplus -s / as sysdba << EOF
set lines 130
set pages 1000
select
snap_id,
to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat
from dba_hist_snapshot
order by snap_id;
EOF
echo ininum = $ininum
echo endnum = $endnum
# ininum=39050
# endnum=39052
while [ $ininum -lt $endnum ];
do
nxtnum=`expr $ininum + 1`
repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.html'
sqlplus -s / as sysdba << EOF
define inst_num = $inst_num;
define num_days = 100;
define inst_name = $inst_name;
define db_name = $db_name;
define dbid = $dbid;
define report_type = 'html';
define begin_snap = $ininum;
define end_snap = $nxtnum;
define report_name = $repnam;
@@?/rdbms/admin/awrrpti
EOF
ininum=$nxtnum
done
============
set linesize 300 pagesize 300
select snap_id,instance_number ,con_id,snap_id, end_interval_time from dba_hist_snapshot
--WHERE begin_interval_time > TO_DATE('2011-06-07 07:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE end_interval_time > SYSDATE - 1
and instance_number=1
order by 1
;
VAR dbid NUMBER
var bid NUMBER
var eid NUMBER
exec :bid := '34710'
exec :eid := '34711'
BEGIN
SELECT dbid INTO :dbid FROM v$database;
END;
/
/
SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF
--*** Node 1
-- HTML
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 1, :bid, :eid));
-- Text
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 1, :bid, :eid));
================
--*** Node 2
-- HTML
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 2, :bid, :eid));
-- Text
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 2, :bid, :eid));
*** Global
-- SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:dbid, CAST(null AS VARCHAR2(10)), &bid, &eid));
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_text(:dbid, CAST(null AS VARCHAR2(10)), :bid, :eid));
====
col OUTPUT for A150
select * from table(SYS.DBMS_WORKLOAD_REPOSITORY.awr_report_text( (select dbid from v$database), 1, (select max(snap_id) from dba_hist_snapshot) - 1, (select max(snap_id) from dba_hist_snapshot)));
===========
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
******
if below error
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) )
*
ERROR at line 1:
ORA-20020: Database/Instance/Snapshot mismatch
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 16546
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 2660
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1189
ORA-06512: at line 1
use below code !!!!
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
====
set serveroutput on
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.txt');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
===============
for html
set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('Set heading off trimspool off linesize 1500 termout on feedback off');
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
====
For Global (RAC all node) !!!
set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('Set heading off trimspool off linesize 1500 termout on feedback off');
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_Global_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML( '||c1.dbid||','||''''''||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
====
define instance_number=1
set trimspool on trimout on
set lines 1500
set echo off
set heading off
set pages 0
set feedback off
set verify off
set trimspool on trimout on
define instance_number=1
--spool generate_awr_reports1..sql
select 'set heading off' || chr(10) ||
'set feedback off' || chr(10) ||
'set linesize 5000' || chr(10) ||
'set trimspool on trimout on' || chr(10) ||
'spool awr_'|| to_char(instance_number) || '_' || to_char(snap_id) || '_' || to_char(snap_id+1) || '.html' || chr(10) ||
'select output from table(dbms_workload_repository.awr_report_html(' || to_char(dbid) || ',' || to_char(instance_number) || ',' ||
to_char(snap_id) || ',' || to_char(snap_id+1) || '));' || chr(10) ||
'spool off'
from DBA_HIST_SNAPSHOT
where 1=1
and instance_number=1
and snap_id < ( select max(snap_id) from dba_hist_snapshot where instance_number=&instance_number)
order by snap_id;
set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhdi.DBID,
dhs.snap_id,
to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= sysdate -2
order by db_name, instance_name, snap_id;
define num_days = 2;
define db_name = 'RAC';
define dbid = 1222414252;
define begin_snap = 10319;
define end_snap = 10320;
define report_type = 'html';
define instance_numbers_or_ALL = 'ALL'
define report_name = awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type
@?/rdbms/admin/awrgrpti
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
define begin_snap=:BgnSnap
define EndSnap=:EndSnap
define num_days = 3;
define db_name = 'Database';
define dbid = :DID;
define begin_snap = :BgnSnap;
define end_snap = :EndSnap;
define report_type = 'html';
define instance_numbers_or_ALL = 'ALL'
define report_name = awrrpt_RAC_&begin_snap._&end_snap
@?/rdbms/admin/awrgrpti
=====
Awr dump !!!!
define 3="TIMESTAMP'2023-01-31 19:00:00'"
define 4="TIMESTAMP'2023-02-01 19:00:00'"
define DB_DIR='DATA_PUMP_DIR'
set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
--and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
AND begin_interval_time BETWEEN &3 AND &4
order by di.db_name, i.instance_name, s.snap_id
;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
--dbms_output.put_line('Set heading off trimspool off linesize 1500 termout on feedback off');
--dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('begin dbms_swrf_internal.awr_extract
( '||'DMPFILE =>' ||'''awr_data'||c1.begin_snap_id||''''||','||'dmpdir => '|| '''&DB_DIR'''||',' ||' bid =>'||c1.begin_snap_id||','||'eid => '||c1.end_snap_id||','||'dbid =>'||c1.dbid||'); ' );
dbms_output.put_line('dbms_swrf_internal.clear_awr_dbid;');
dbms_output.put_line('end; ');
dbms_output.put_line('/');
end if;
end loop;
end;
/
=================================!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Gobal report
set head off pages 0 lines 132 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBERS varchar2(20);
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
select listagg (INSTANCE_NUMBER,',') within group (order by INSTANCE_NAME) into :INST_NUMBERS from gv$instance;
-- awr text.sql
select output from table(dbms_workload_repository.awr_global_report_text(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))
/
-- awr html.sql
select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))
/
ADDM
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
DECLARE
task_name VARCHAR2(30) := 'SYSTEM_ADDM';
task_desc VARCHAR2(30) := 'ADDM Feature Test';
task_id NUMBER;
BEGIN
select count(*)
into task_id
from dba_advisor_tasks
where task_name = 'SYSTEM_ADDM';
if task_id = 0 then
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
else
dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');
end if;
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);
dbms_advisor.execute_task('SYSTEM_ADDM');
END;
/
select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;
=====================
mini Awr report
http://anuj-singh.blogspot.com/2023/
Oracle Mini Awr report ....
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBERS varchar2(20);
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
select listagg (INSTANCE_NUMBER,',') within group (order by INSTANCE_NAME) into :INST_NUMBERS from gv$instance;
VAR dbid NUMBER
VAR inst_num NUMBER
VAR eid NUMBER
VAR bid NUMBER
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBERS varchar2(20);
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
BEGIN
SELECT dbid, USERENV('instance') INTO :dbid, :inst_num FROM v$database;
SELECT MAX(snap_id) INTO :eid FROM dba_hist_snapshot WHERE dbid = :dbid AND instance_number = :inst_num;
SELECT MAX(snap_id) INTO :bid FROM dba_hist_snapshot WHERE dbid = :dbid AND instance_number = :inst_num AND snap_id < :eid;
END;
/
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(:dbid, :inst_num, :BgnSnap, :EndSnap));
Thursday, 3 February 2011
Oracle Drill down with ASH ( active session history )
Oracle Drill down with ASH...
SQL> select distinct wait_class from gv$active_session_history ;
WAIT_CLASS
----------------------------------------------------------------
Administrative
Application
Commit
Concurrency
Configuration
Idle
Network
Other
System I/O
User I/O
11 rows selected.
set linesize 200 pagesize 500
col sql_text for a100 wrap
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,a.sql_id,b.event, sum(b.time_waited) "time waited",a.sql_text from gv$sqlarea a, gv$active_session_history b
where b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
-- and b.wait_class = 'user i/o'
and b.sql_id = a.sql_id
and a.inst_id=b.inst_id
group by ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''',a.sql_id,a.sql_text,b.sql_id,b.event
having sum(b.time_waited)>0
order by 3 desc;
col kill for a15
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,sql_id,b.sample_time,event,p1 "file#", p2 "block#", p3 "class#"
from gv$active_session_history b
where 1=1
and b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
and event like 'direct path read temp%'
order by 2;
For Object info
select tablespace_name, owner, segment_name, segment_type from dba_extents
where file_id =206 and 1247360 between block_id and block_id + blocks - 1;
set long 10000
select sql_fulltext from gv$sql where sql_id='&sql_id';
set linesize 300 pages 2000 long 9999999
select sql_fulltext from gv$sqlarea where sql_id='&sql_id' ;
set pages 0
col sql_text for a32000
prompt ### The Statement (DBA_HIST_SQLTEXT):
select sql_text from dba_hist_sqltext where sql_id='&sql_Id' and rownum=1;
set lines 238
For Explain Plan
set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'basic')) t
where sql_id='&sql_id'
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS')) t
where sql_id='&sql_id'
set pages 300 lines 200
-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
Oracle datafile More then 20% disk I/O
SELECT
TO_CHAR(sn.end_interval_time,'yyyy-mm-dd HH24:MI:SS') end_interval_time,
new.filename file_name,
new.phywrts-old.phywrts writes
FROM dba_hist_filestatxs old, dba_hist_filestatxs new,
dba_hist_snapshot sn
WHERE
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
AND new.snap_id = sn.snap_id
AND old.snap_id = sn.snap_id-1
AND new.filename = old.filename
AND (new.phywrts-old.phywrts)*20 > (SELECT(newsnap.value-oldsnap.value) writes
FROM
dba_hist_sysstat oldsnap, dba_hist_sysstat newsnap, dba_hist_snapshot sn1
WHERE
sn.snap_id = sn1.snap_id
AND newsnap.snap_id = sn.snap_id
AND oldsnap.snap_id = sn.snap_id-1
AND oldsnap.stat_name = 'physical writes'
AND newsnap.stat_name = 'physical writes'
AND (newsnap.value-oldsnap.value) > 0);
TO_CHAR(sn.end_interval_time,'yyyy-mm-dd HH24:MI:SS') end_interval_time,
new.filename file_name,
new.phywrts-old.phywrts writes
FROM dba_hist_filestatxs old, dba_hist_filestatxs new,
dba_hist_snapshot sn
WHERE
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
AND new.snap_id = sn.snap_id
AND old.snap_id = sn.snap_id-1
AND new.filename = old.filename
AND (new.phywrts-old.phywrts)*20 > (SELECT(newsnap.value-oldsnap.value) writes
FROM
dba_hist_sysstat oldsnap, dba_hist_sysstat newsnap, dba_hist_snapshot sn1
WHERE
sn.snap_id = sn1.snap_id
AND newsnap.snap_id = sn.snap_id
AND oldsnap.snap_id = sn.snap_id-1
AND oldsnap.stat_name = 'physical writes'
AND newsnap.stat_name = 'physical writes'
AND (newsnap.value-oldsnap.value) > 0);
Oracle ADDM Report for last 24 hr .....
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE DBID NUMBER
VARIABLE inst_num number
exec select max(snap_id) -24 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :inst_num from v$instance ;
DECLARE
task_name VARCHAR2(30) := 'ADDM_ANUJ';
task_desc VARCHAR2(30) := 'ADDM ANUJ';
task_id NUMBER;
BEGIN
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'START_SNAPSHOT', :bid);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'END_SNAPSHOT', :eid);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'INSTANCE', :inst_num);
dbms_advisor.set_task_parameter('ADDM_ANUJ', 'DB_ID', :DBID );
dbms_advisor.execute_task('ADDM_ANUJ');
END;
/
PL/SQL procedure successfully completed.
SET LONG 1000000
SET PAGES 0
SET LONGCHUNKSIZE 1000
COL get_clob FORMAT a80
SELECT DBMS_ADVISOR.GET_TASK_REPORT('ADDM_ANUJ','TEXT','TYPICAL') FROM dual;
DETAILED ADDM REPORT FOR TASK 'ADDM_ANUJ' WITH ID 3300
------------------------------------------------------
Analysis Period: from 02-FEB-2011 14:00 to 03-FEB-2011 14:00
Database ID/Instance: 1257031792/1
Database/Instance Names: ORCL/orcl
Host Name: apt-lnxtst-01
Database Version: 10.2.0.4.0
Snapshot Range: from 3052 to 3076
Database Time: 316 seconds
Average Database Load: 0 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 36% impact (114 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)
ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
call dbms_stats.gather_database_stats_job_proc ( )
RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1
times and had an average elapsed time of 47 seconds.
RECOMMENDATION 2: SQL Tuning, 7.2% benefit (23 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753
times and had an average elapsed time of 0.00072 seconds.
RECOMMENDATION 3: SQL Tuning, 7.1% benefit (22 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
RATIONALE: SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 1441
times and had an average elapsed time of 0.019 seconds.
RECOMMENDATION 4: SQL Tuning, 4.6% benefit (14 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"bunssq950snhf".
RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and
PLAN_HASH 2694099131
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number,
SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select
:snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR,
ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 24
times and had an average elapsed time of 0.6 seconds.
RECOMMENDATION 5: SQL Tuning, 4.5% benefit (14 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8hk7xvhua40va".
RELEVANT OBJECT: SQL statement with SQL_ID 8hk7xvhua40va
INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE,
METRIC_GUID, STRING_VALUE, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, '
'), :3, :4, :5, :6)
RATIONALE: SQL statement with SQL_ID "8hk7xvhua40va" was executed 2452
times and had an average elapsed time of 0.0057 seconds.
FINDING 2: 17% impact (55 seconds)
----------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 17% benefit (55 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 11 transactions per minute
with an average redo size of 8087 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 17% benefit (55 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 6
K and the average time per write was 6 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(17% impact [55 seconds])
FINDING 3: 16% impact (50 seconds)
----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)
ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
call dbms_stats.gather_database_stats_job_proc ( )
RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1
times and had an average elapsed time of 47 seconds.
RATIONALE: Average time spent in PL/SQL execution was 6.5 seconds.
RECOMMENDATION 2: SQL Tuning, 4.8% benefit (15 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753
times and had an average elapsed time of 0.00072 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.00029 seconds.
RECOMMENDATION 3: SQL Tuning, 3.4% benefit (11 seconds)
ACTION: Investigate the SQL statement with SQL_ID "6mcpb06rctk0x" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 6mcpb06rctk0x
call dbms_space.auto_space_advisor_job_proc ( )
RATIONALE: SQL statement with SQL_ID "6mcpb06rctk0x" was executed 1
times and had an average elapsed time of 10 seconds.
RATIONALE: Average time spent in PL/SQL execution was 9.3 seconds.
RECOMMENDATION 4: SQL Tuning, 3.3% benefit (10 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 2b064ybzkwf1y
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 2869
times and had an average elapsed time of 0.0037 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.0035 seconds.
RECOMMENDATION 5: SQL Tuning, 3.1% benefit (10 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8szmwam7fysa3".
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and
PLAN_HASH 2976124318
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 13
times and had an average elapsed time of 0.73 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.71 seconds.
FINDING 4: 6.7% impact (21 seconds)
-----------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 4.4% benefit (14 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 640 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "512 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(8% impact [25 seconds])
FINDING 5: 3.3% impact (10 seconds)
-----------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 3.3% benefit (10 seconds)
ACTION: Investigate application logic to keep open the frequently used
cursors. Note that cursors are closed by both cursor close calls and
session disconnects.
RECOMMENDATION 2: DB Configuration, 3.3% benefit (10 seconds)
ACTION: Consider increasing the maximum number of open cursors a session
can have by increasing the value of parameter "open_cursors".
ACTION: Consider increasing the session cursor cache size by increasing
the value of parameter "session_cached_cursors".
RATIONALE: The value of parameter "open_cursors" was "300" during the
analysis period.
RATIONALE: The value of parameter "session_cached_cursors" was "20"
during the analysis period.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
The database's maintenance windows were active during 33% of the analysis
period.
====
for PDB
alter session set container=ANUJ;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '5bbqkc6pmfhwy',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '5bbqkc6pmfhwy_tuning_task',
description => 'Tuning task for statement 5bbqkc6pmfhwy');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2. Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5bbqkc6pmfhwy_tuning_task');
3. Get the Tuning advisor report.
set long 50000 longchunksize 5000 linesize 300
col REPORT_TUNING_TASK for a100 wrap
select dbms_sqltune.report_tuning_task('5bbqkc6pmfhwy_tuning_task') REPORT_TUNING_TASK from dual;
====================
--- Finding All Snapshot List
SQL>
SELECT SNAP_ID,
SNAP_LEVEL,BEGIN_INTERVAL_TIME,
TO_CHAR(BEGIN_INTERVAL_TIME, 'dd/mm/yy hh24:mi:ss') BEGIN
FROM
DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID desc;
SNAP_ID SNAP_LEVEL BEGIN_INTERVAL_TIME BEGIN
---------- ---------- --------------------------------------------------------------------------- -----------------
39004 2 18-JAN-22 02.00.23.142 AM 18/01/22 02:00:23
39004 2 18-JAN-22 02.00.23.175 AM 18/01/22 02:00:23
39003 2 18-JAN-22 01.00.13.555 AM 18/01/22 01:00:13
39003 2 18-JAN-22 01.00.13.591 AM 18/01/22 01:00:13
39002 2 18-JAN-22 12.00.04.937 AM 18/01/22 00:00:04
39002 2 18-JAN-22 12.00.04.996 AM 18/01/22 00:00:04
39001 2 17-JAN-22 11.00.07.027 PM 17/01/22 23:00:07
39001 2 17-JAN-22 11.00.06.984 PM 17/01/22 23:00:06
39000 2 17-JAN-22 10.00.48.335 PM 17/01/22 22:00:48
39000 2 17-JAN-22 10.00.48.366 PM 17/01/22 22:00:48
--- Executing Oracle Package DBMS_ADVISOR to Generate advisory task.
SQL>
BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '39001_39003_AWR_SNAP',
task_desc => 'Advisor for snapshots 39001 to 39003');
-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '39001_39003_AWR_SNAP',
parameter => 'START_SNAPSHOT',
value => 39001);
DBMS_ADVISOR.set_task_parameter (
task_name => '39001_39003_AWR_SNAP',
parameter => 'END_SNAPSHOT',
value => 39004);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '39001_39003_AWR_SNAP');
END;
PL/SQL procedure successfully completed.
------ Showing ADDM Report data.
SQL>
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('39001_39003_AWR_SNAP') AS report FROM dual;
====
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1
group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
/
or
-- with task name
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID,a.TASK_NAME, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1
group by b.ATTR1,a.TASK_NAME
order by max(a.BENEFIT) desc) where rownum < 6;
SQL_ID TASK_NAME Benefit
---------------- ------------------------------ --------------
g8ayhu2g4wh1f ADDM:3316548098_1_571783 14128696816
1rn43zb7tm2jg ADDM:3316548098_1_571783 14128696816
1rn43zb7tm2jg ADDM:3316548098_1_571785 13952920387
9783wcf3s3634 ADDM:3316548098_1_571646 13662000000
87a333ujtm7q3 ADDM:3316548098_1_571862 13178202405
set linesize 300 pagesize 300
col TASK_NAME for a30
select task_name, execution_end ,task_id from dba_advisor_tasks
where advisor_name='ADDM'
and status='COMPLETED'
and owner='SYS'
--and instr(TASK_NAME,'_3_',1,1)=xx
order by execution_end desc;
/
TASK_NAME EXECUTION TASK_ID
------------------------------ --------- ----------
ADDM:1222414252_2_40551 23-MAR-14 164533
from above task name !!!!
SET linesize 300 LONG 999999 pages 1000 longchunksize 999999
col MESSAGE for a50 wrap
col IMPACT_TYPE for a30
SELECT
r.type,
r.Rank,
r.benefit,
f.impact_type,
f.impact,
f.message
FROM
dba_advisor_recommendations r,
dba_advisor_findings f
WHERE 1=1
and r.task_name = 'ADDM:1222414252_2_40551'
AND r.finding_id = f.finding_id
AND r.task_id = f.task_id
ORDER BY r.rank ASC, r.benefit DESC;
/
SET linesize 300 LONG 999999 pages 1000 longchunksize 999999
select DBMS_ADVISOR.GET_TASK_REPORT('ADDM:1222414252_2_40551', 'TEXT', 'TYPICAL', 'ALL', 'SYS') from dual;
/
====
ADDM
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
DECLARE
task_name VARCHAR2(30) := 'SYSTEM_ADDM';
task_desc VARCHAR2(30) := 'ADDM Feature Test';
task_id NUMBER;
BEGIN
select count(*)
into task_id
from dba_advisor_tasks
where task_name = 'SYSTEM_ADDM';
if task_id = 0 then
dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
else
dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');
end if;
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);
dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);
dbms_advisor.execute_task('SYSTEM_ADDM');
END;
/
select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;
======================================
ANALYZE_DB
SET LINESIZE 200 pagesize 300
COLUMN begin_interval_time FORMAT A30
COLUMN end_interval_time FORMAT A30
COLUMN startup_time FORMAT A30
SELECT snap_id, begin_interval_time, end_interval_time, startup_time
FROM dba_hist_snapshot
WHERE begin_interval_time > sysdate -1
ORDER BY snap_id;
DECLARE
l_task_name VARCHAR2(30) := '121913_121915_addm_db';
BEGIN
DBMS_ADDM.analyze_db (
task_name => l_task_name,
begin_snapshot => 121913,
end_snapshot => 121915);
END;
/
SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 PAGESIZE 0 TRIM ON TRIMSPOOL ON ECHO OFF FEEDBACK OFF
SELECT DBMS_ADDM.get_report('121913_121915_addm_db') FROM dual;
===============
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '1b6bfz2hqvz5k',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '1b6bfz2hqvz5g_tuning_task',
description => 'Tuning task1 for statement 1b6bfz2hqvz5k');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1b6bfz2hqvz5k_tuning_task');
set long 65536 longchunksize 65536 linesize 100
select dbms_sqltune.report_tuning_task('1b6bfz2hqvz5k_tuning_task') from dual;
=====
set long 500000
SELECT dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id = (SELECT max(t.task_id)
FROM dba_advisor_tasks t, dba_advisor_log l
WHERE t.task_id = l.task_id
AND t.advisor_name = 'ADDM'
AND l.status = 'COMPLETED'
/
SET LONG 100000 PAGESIZE 0
SELECT dbms_advisor.get_task_report(task_name) AS report
FROM dba_advisor_tasks
WHERE task_id = (SELECT MAX(task_id) FROM dba_advisor_log
WHERE advisor_name = 'ADDM' AND status = 'COMPLETED'
);
=========
SET SERVEROUTPUT ON SIZE UNLIMITED LONG 1000000
DECLARE
-- We must provide a name because it is an IN/OUT parameter
v_task_name VARCHAR2(100) := 'MY_ADDM_REPORT_' || TO_CHAR(SYSDATE, 'HH24MISS');
v_begin_snap NUMBER;
v_end_snap NUMBER;
v_dbid NUMBER;
v_report CLOB;
BEGIN
-- 1. Fetch the necessary IDs
SELECT dbid INTO v_dbid FROM v$database;
SELECT max(snap_id) - 1, max(snap_id)
INTO v_begin_snap, v_end_snap
FROM dba_hist_snapshot
WHERE dbid = v_dbid;
-- 2. Call the procedure using the 4 parameters shown in your DESC output
-- TASK_NAME (IN/OUT), BEGIN_SNAPSHOT (IN), END_SNAPSHOT (IN), DB_ID (IN)
DBMS_ADDM.ANALYZE_DB(
task_name => v_task_name,
begin_snapshot => v_begin_snap,
end_snapshot => v_end_snap,
db_id => v_dbid
);
-- 3. Retrieve and output the report
v_report := DBMS_ADDM.GET_REPORT(v_task_name);
DBMS_OUTPUT.PUT_LINE('Task Created: ' || v_task_name);
DBMS_OUTPUT.PUT_LINE(v_report);
END;
/
BEGIN
DBMS_ADDM.DELETE('MY_ADDM_REPORT_123456');
END;
/***************************************************************
from https://coskan.wordpress.com/2011/11/18/awr-generator-on-demand-for-many-node-cluster/
for snap---
WITH snaps AS (
SELECT
s.dbid,
s.instance_number,
s.snap_id AS begin_snap,
LEAD(s.snap_id, &_interval)
OVER (PARTITION BY s.instance_number ORDER BY s.snap_id) AS end_snap,
s.begin_interval_time
FROM dba_hist_snapshot s
WHERE s.dbid = (SELECT dbid FROM v$database)
AND s.instance_number = (SELECT instance_number FROM v$instance)
AND s.begin_interval_time BETWEEN
TO_DATE('&_begin_interval_date','DDMMYY HH24:MI:SS')
AND TO_DATE('&_end_interval_date','DDMMYY HH24:MI:SS')
)
SELECT *
FROM snaps
WHERE end_snap IS NOT NULL
ORDER BY begin_snap;
--===
define _interval=1 --1 for half hour 2 for 1 hour (30 mins snapshots) / 1 for hour 2 for 2 hour (60 mins snapshots)
define _database_name='ANUJ' ---- database name
define _begin_interval_date='290126 07:00:00' ----begin date DDMMYY HH24:MI:SS
define _end_interval_date='300126 06:00:00' ----end date DDMMYY HH24:MI:SS
define _folder='/home/oracle' ---report output location
define _option=2 ---1 without ADDM 2 with ADDM
define _global=0 ---0 if you don't want Global Reports for RAC - It will not generate report for Single Instance with any setting
SET termout OFF
SET heading OFF
SET feedback OFF
SET verify OFF
SET echo OFF
SET linesize 2000
SET pagesize 0
SET long 1000000
SET longchunksize 1000PROMPT set verify off PROMPT set feedback off PROMPT set termout off PROMPT set linesize 2000 PROMPT set pagesize 0 PROMPT set long 1000000 PROMPT set longchunksize 1000 PROMPT PROMPT REPORT GENERATION STARTED PROMPT WITH snaps AS ( SELECT s.dbid, s.instance_number, s.snap_id AS begin_snap, LEAD(s.snap_id, &_interval) OVER (PARTITION BY s.instance_number ORDER BY s.snap_id) AS end_snap, s.begin_interval_time FROM dba_hist_snapshot s WHERE s.dbid = (SELECT dbid FROM v$database) AND s.instance_number = (SELECT instance_number FROM v$instance) AND s.begin_interval_time BETWEEN TO_DATE('&_begin_interval_date','DDMMYY HH24:MI:SS') AND TO_DATE('&_end_interval_date','DDMMYY HH24:MI:SS') ) SELECT CASE &_option WHEN 1 THEN 'spool &_folder/awrrpt_'||'&_database_name'||'_'|| begin_snap||'_'||end_snap||'.html'||CHR(10)|| 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_html('|| dbid||','||instance_number||','|| begin_snap||','||end_snap||',8));'||CHR(10)|| 'spool off'||CHR(10) WHEN 2 THEN 'spool &_folder/awrrpt_'||'&_database_name'||'_'|| begin_snap||'_'||end_snap||'.html'||CHR(10)|| 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_html('|| dbid||','||instance_number||','|| begin_snap||','||end_snap||',8));'||CHR(10)|| 'spool off'||CHR(10)|| 'BEGIN'||CHR(10)|| ' BEGIN DBMS_ADVISOR.delete_task(''ADDM_'|| begin_snap||'_'||end_snap||''');'||CHR(10)|| ' EXCEPTION WHEN OTHERS THEN NULL; END;'||CHR(10)|| ' DBMS_ADVISOR.create_task(advisor_name=>''ADDM'','|| 'task_name=>''ADDM_'||begin_snap||'_'||end_snap||''');'||CHR(10)|| ' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'|| begin_snap||'_'||end_snap||''',parameter=>''START_SNAPSHOT'',value=>'|| begin_snap||');'||CHR(10)|| ' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'|| begin_snap||'_'||end_snap||''',parameter=>''END_SNAPSHOT'',value=>'|| end_snap||');'||CHR(10)|| ' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'|| begin_snap||'_'||end_snap||''',parameter=>''INSTANCE'',value=>'|| instance_number||');'||CHR(10)|| ' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'|| begin_snap||'_'||end_snap||''',parameter=>''DB_ID'',value=>'|| dbid||');'||CHR(10)|| ' DBMS_ADVISOR.execute_task(task_name=>''ADDM_'|| begin_snap||'_'||end_snap||''');'||CHR(10)|| 'END;'||CHR(10)||'/'||CHR(10)|| 'spool &_folder/addm_'||'&_database_name'||'_'|| begin_snap||'_'||end_snap||'.txt'||CHR(10)|| 'SELECT DBMS_ADVISOR.get_task_report('|| '''ADDM_'||begin_snap||'_'||end_snap||''') FROM dual;'||CHR(10)|| 'spool off'||CHR(10) END FROM snaps WHERE end_snap IS NOT NULL ORDER BY begin_snap; spool /home/oracle/awrrpt_ANUJ_822_823.html SELECT * FROM TABLE(dbms_workload_repository.awr_report_html(3960921757,1,822,823,8)); spool off BEGIN BEGIN DBMS_ADVISOR.delete_task('ADDM_822_823'); EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_ADVISOR.create_task(advisor_name=>'ADDM',task_name=>'ADDM_822_823'); DBMS_ADVISOR.set_task_parameter(task_name=>'ADDM_822_823',parameter=>'START_SNAPSHOT',value=>822); DBMS_ADVISOR.set_task_parameter(task_name=>'ADDM_822_823',parameter=>'END_SNAPSHOT',value=>823); DBMS_ADVISOR.set_task_parameter(task_name=>'ADDM_822_823',parameter=>'INSTANCE',value=>1); DBMS_ADVISOR.set_task_parameter(task_name=>'ADDM_822_823',parameter=>'DB_ID',value=>3960921757); DBMS_ADVISOR.execute_task(task_name=>'ADDM_822_823'); END; /
*****
DEFINE _interval = 1 -- 1 = half hour, 2 = 1 hour
DEFINE _database_name = 'ANUJ' -- database name
DEFINE _begin_interval_date = '290126 07:00:00'
DEFINE _end_interval_date = '300126 06:00:00'
DEFINE _folder = '/home/oracle' -- report output folder
DEFINE _option = 2 -- 1 = no ADDM, 2 = with ADDM
DEFINE _global = 0 -- 1 = generate global report (CDB only), 0 = skip
-- =========================
-- SQL*Plus Settings
-- =========================
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 2000
SET LONG 1000000
SET LONGCHUNKSIZE 1000
-- =========================
-- Generate AWR + ADDM per-instance
-- =========================
SPOOL &_folder/gen_awr.sql
SELECT
'spool &_folder/awrrpt_'||'&_database_name'||'_'||instance_number||'_'||begin_snap||'_'||end_snap||'.html'||CHR(10)||
'SELECT * FROM TABLE(dbms_workload_repository.awr_report_html('||dbid||','||instance_number||','||begin_snap||','||end_snap||',8));'||CHR(10)||
'spool off'||CHR(10)||
CASE &_option
WHEN 2 THEN
'BEGIN'||CHR(10)||
' BEGIN DBMS_ADVISOR.delete_task(''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||'''); EXCEPTION WHEN OTHERS THEN NULL; END;'||CHR(10)||
' DBMS_ADVISOR.create_task(advisor_name=>''ADDM'', task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''');'||CHR(10)||
' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''', parameter=>''START_SNAPSHOT'', value=>'||begin_snap||');'||CHR(10)||
' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''', parameter=>''END_SNAPSHOT'', value=>'||end_snap||');'||CHR(10)||
' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''', parameter=>''INSTANCE'', value=>'||instance_number||');'||CHR(10)||
' DBMS_ADVISOR.set_task_parameter(task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''', parameter=>''DB_ID'', value=>'||dbid||');'||CHR(10)||
' DBMS_ADVISOR.execute_task(task_name=>''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''');'||CHR(10)||
'END;'||CHR(10)||
'/'||CHR(10)||
'spool &_folder/addm_'||'&_database_name'||'_'||instance_number||'_'||begin_snap||'_'||end_snap||'.txt'||CHR(10)||
'SELECT DBMS_ADVISOR.get_task_report(''ADDM_'||instance_number||'_'||begin_snap||'_'||end_snap||''') FROM dual;'||CHR(10)||
'spool off'
ELSE ''
END AS awr_gen
FROM (
WITH snaps AS (
SELECT instance_number, dbid, snap_id AS begin_snap,
LEAD(snap_id, &_interval) OVER (PARTITION BY instance_number ORDER BY snap_id) AS end_snap
FROM dba_hist_snapshot
WHERE begin_interval_time >= TO_DATE('&_begin_interval_date','DDMMYY HH24:MI:SS')
AND begin_interval_time <= TO_DATE('&_end_interval_date','DDMMYY HH24:MI:SS')
)
SELECT instance_number, dbid, begin_snap, end_snap
FROM snaps
WHERE end_snap IS NOT NULL
ORDER BY instance_number, begin_snap
);
SPOOL OFF
Subscribe to:
Posts (Atom)
