AWR ( Automatic Workload Repository ) and ASH ( Active
Session History ) are part of the Oracle Enterprise Edition Diagnostic Pack
Licensing.
0] AWR Management
0.1 Configuration
It’s important to change AWR
default settings to increase Snap Frequency & Retention : connect as
SYS
SQL> exec
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30 , retention
=> 50400 , topnsql => 50 ) ;
SQL> set
pages 5000 Lines 150
SQL> col
SNAP_INTERVAL for a20
SQL> select
dbid, SNAP_INTERVAL, RETENTION, TOPNSQL from DBA_HIST_WR_CONTROL ;
DBID SNAP_INTERVAL RETENTION TOPNSQL
------------------------
-----------------------------------
1504614898 +00000 00:30:00.0 +00035 00:00:00.0 50
Remark : STATISTICS_LEVEL
= TYPICAL | ALL
0.2 Remove a range of
snapshots
You can drop
a range of snapshots using the DROP_SNAPSHOT_RANGE procedure.
To view a
list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT
view.
For example,
you can drop the following range of snapshots:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
( low_snap_id => 1 , high_snap_id => 592 , dbid => 450695157 );
END;
/
select
sum(bytes/1024/1024) from dba_free_space where tablespace_name = 'SYSAUX' -- 71
select distinct
min(snap_id) over ( partition by dbid ) , max(snap_id) over ( partition by dbid
) , dbid from dba_hist_snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
( low_snap_id => 1 , high_snap_id => 966 , dbid => 450695157 );
END;
/
1] AWR Top SQL in a period
1.1 Top SQL Overall
|
Goal
|
Get Top SQL consumers order by Buffer Gets
|
|
Version
|
10gR2
|
|
Views
|
dba_hist_sqlstat dba_hist_snapshot
|
|
Log as
|
SYSTEM
|
|
Args
|
Snap interval
|
Searching for Big SQL in a
past period is very common. AWR is the powerful tool for that matter.
The interval of Snapshots-Time should include the end of the statement
we’re studying.
-- Top
SQL ? Buffer Gets
SELECT TO_char(min( sn.begin_interval_time ),'DD/MM/YYYY HH24:MI') "BEGIN_INTERVAL",parsing_schema_name schema,
s.sql_id, plan_hash_value "PLAN_HV" , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DK(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "ROWS",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / sum(s.executions_delta) ) "BG/EXEC",
'SELECT * FROM TABLE
(DBMS_XPLAN.display_awr ('''||s.sql_id||''','''||plan_hash_value||''', NULL,''ADVANCED''))' "AWR"
FROM dba_hist_sqlstat s, dba_hist_snapshot
sn
WHERE s.snap_id=sn.snap_id
AND begin_interval_time >= TO_DATE('12/09/2012
00:30:00','DD/MM/YYYY HH24:MI:SS')
AND begin_interval_time < TO_DATE('12/09/2012 08:30:00','DD/MM/YYYY HH24:MI:SS')
--
and parsing_schema_name = 'HR'
GROUP BY parsing_schema_name,s.sql_id,plan_hash_value,module
HAVING sum(s.executions_delta) > 0 AND sum(s.buffer_gets_delta) > 0 AND sum(s.elapsed_time_delta) > 0
ORDER BY 6 DESC ;
|
BEGIN_INTERVAL
|
SCHEMA
|
SQL_ID
|
PLAN_HV
|
MODULE
|
BG(M)
|
DK(K)
|
CPU(s)
|
ELAPSED
(min)
|
|
16/09/2011 02:00
|
<SCHEMA>
|
bjmy7bvnsg8y0
|
0
|
SQL*Plus
|
219,82
|
247,6
|
1927,27
|
36,37
|
|
16/09/2011 02:00
|
<SCHEMA>
|
04za5hx6nyt8r
|
426121101
|
SQL*Plus
|
138,19
|
0,18
|
1071,45
|
18,31
|
|
16/09/2011 02:00
|
<SCHEMA>
|
g7g1cwp5jcy6c
|
4156117912
|
SQL*Plus
|
29,97
|
0
|
366,08
|
7,23
|
|
16/09/2011 01:31
|
<SCHEMA>
|
bwwustytv7hj7
|
3862578566
|
httpd@srv-web-4.corp.fr (TNS V1-V3)
|
21,39
|
0
|
62,95
|
4,68
|
|
16/09/2011 02:00
|
<SCHEMA>
|
48gtnb8h8fg4n
|
0
|
SQL*Plus
|
17,45
|
2,51
|
290,56
|
6,12
|
|
16/09/2011 02:00
|
<SCHEMA>
|
4mstk59sgyhpc
|
0
|
SQL*Plus
|
16,97
|
2,99
|
213,92
|
4,58
|
|
16/09/2011 02:00
|
<SCHEMA>
|
7xw3bgbb48sq4
|
426121101
|
SQL*Plus
|
9,99
|
0
|
72,04
|
1,72
|
|
FETCH
|
ROWS
|
EXEC
|
BG/EXEC
|
AWR
|
|
0
|
1
|
1
|
219820197
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('bjmy7bvnsg8y0','0',
NULL,'ADVANCED'))
|
|
0
|
30639640
|
50069
|
2760
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('04za5hx6nyt8r','426121101',
NULL,'ADVANCED'))
|
|
0
|
185446
|
20850
|
1437
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr
('g7g1cwp5jcy6c','4156117912', NULL,'ADVANCED'))
|
|
63229
|
0
|
63526
|
337
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr
('bwwustytv7hj7','3862578566', NULL,'ADVANCED'))
|
|
0
|
1
|
1
|
17449928
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('48gtnb8h8fg4n','0',
NULL,'ADVANCED'))
|
|
0
|
1
|
1
|
16969553
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('4mstk59sgyhpc','0',
NULL,'ADVANCED'))
|
|
0
|
6536
|
6536
|
1529
|
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('7xw3bgbb48sq4','426121101',
NULL,'ADVANCED'))
|
1.2 Focus
It may be interesting to add a
condition on SCHEMA to focus on a specific application :
and parsing_schema_name = 'HR'
1.2.1 Search AWR for a given SQL
Text in the past
SELECT TO_char(min( b.begin_interval_time
),'DD/MM/YYYY
HH24:MI') "BEGIN_INTERVAL",parsing_schema_name schema,
a.sql_id, plan_hash_value "PLAN_HV" , module,
ROUND (sum(a.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(a.disk_reads_delta) / 1000, 2) "DK(K)",
ROUND (sum(a.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(a.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(a.fetches_delta) "FETCH",
sum(a.rows_processed_delta) "ROWS",
sum(a.executions_delta) "EXEC",
ROUND (sum(a.buffer_gets_delta) / sum(a.executions_delta) ) "BG/EXEC",
'SELECT * FROM TABLE (DBMS_XPLAN.display_awr
('''||a.sql_id||''','''||plan_hash_value||''',
NULL,''ADVANCED''))' "AWR"
FROM dba_hist_sqlstat a, dba_hist_snapshot b, DBA_HIST_SQLTEXT c
WHERE a.snap_id=b.snap_id and a.sql_id = c.sql_id
AND
begin_interval_time >= TO_DATE('01/10/2013 21:00:00','DD/MM/YYYY HH24:MI:SS')
AND begin_interval_time <
TO_DATE('01/10/2013
23:30:00','DD/MM/YYYY
HH24:MI:SS')
and
parsing_schema_name = 'SPE5_DWH'
and upper(sql_text) like 'SELECT%DISTINCT%DWH_ASM.NU_AGR_ASM%'
GROUP BY parsing_schema_name,a.sql_id,plan_hash_value,module
HAVING sum(a.executions_delta) > 0 AND
sum(a.buffer_gets_delta) > 0 AND sum(a.elapsed_time_delta) > 0
ORDER BY 6 DESC ;
1.2.2 Details on a particular
SQL_ID
AWR column gives the sql to run
in order to retrieve complete SQL+Execution Plan+Hints and even Bind arguments
SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('g7g1cwp5jcy6c','4156117912', NULL,'ADVANCED'))
PLAN_TABLE_OUTPUT
SQL_ID g7g1cwp5jcy6c
--------------------
UPDATE LDAP_LOAD_GROUP_USER
SET I2U =:B2 WHERE UM=:B1
Plan hash value: 4156117912
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 353 (100)| |
| 1 |
UPDATE |
LDAP_LOAD_GROUP_USER | | | | |
| 2 |
TABLE ACCESS FULL| LDAP_LOAD_GROUP_USER | 13 |
533 | 353 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------
Query Block Name / Object
Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / LDAP_LOAD_GROUP_USER@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_transitivity_retain' 'false')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
FULL(@"UPD$1"
"LDAP_LOAD_GROUP_USER"@"UPD$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by
position):
--------------------------------------
2 - :B1 (VARCHAR2(30), CSID=178):
'cn=gelec9,cn=users,dc=corp,dc=fr'
1.2.3 Get Bind Variables
values
--------------------------------------------------------------------------------
--- Display
all bound variables for a given sql_id
--------------------------------------------------------------------------------
SELECT b.snap_id, NAME, POSITION, value_string, datatype_string, last_captured
FROM dba_hist_sqlbind b, dba_hist_snapshot sn
WHERE b.snap_id = sn.snap_id
AND begin_interval_time >= TO_DATE('07/08/2012
10:00:00','DD/MM/YYYY HH24:MI:SS')
AND begin_interval_time < TO_DATE('07/08/2012 11:00:00','DD/MM/YYYY HH24:MI:SS')
AND sql_id = '94rtqj7b9p252'
ORDER BY snap_id DESC, POSITION, last_captured ;
|
SNAP_ID
|
NAME
|
POSITION
|
VALUE_STRING
|
DATATYPE_STRING
|
LAST_CAPTURED
|
|
39780
|
:KSLVAR16_
|
1
|
AGRANP
|
VARCHAR2(32)
|
02/11/2011 14:52
|
|
39780
|
:KSLVAR16_
|
2
|
AGRANP
|
VARCHAR2(32)
|
02/11/2011 14:52
|
|
39780
|
:KSLVAR15_
|
3
|
01/03/2012
|
VARCHAR2(32)
|
02/11/2011 14:52
|
|
39780
|
:KSLVAR16_
|
4
|
AGRANP
|
VARCHAR2(32)
|
02/11/2011 14:52
|
|
39780
|
:KSLVAR14_
|
5
|
31/03/2012
|
VARCHAR2(32)
|
02/11/2011 14:52
|
1.3 Behaviour over 2 months of
a single SQL_ID
To find if there has been any
changes in Execution Plan.
SELECT TO_CHAR(sn.begin_interval_time,'MM DD') "DAY", s.sql_id, plan_hash_value , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DK(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "ROWS",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "BG/EXEC",
ROUND (sum(s.disk_reads_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "DK/EXEC",
ROUND (sum(s.cpu_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000, 3 ) "CPU(s)/EXEC",
ROUND (sum(s.elapsed_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000,3 ) "ELAPSED(s)/EXEC",
ROUND (sum(s.disk_reads_delta)/DECODE(sum(s.buffer_gets_delta),0,1,sum(s.buffer_gets_delta))*100, 2) "DK%BG",
ROUND (sum(s.cpu_time_delta)/DECODE(sum(s.elapsed_time_delta),0,1,sum(s.elapsed_time_delta))*100, 2) "CPU%ELAP"
FROM dba_hist_sqlstat s, dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND sn.begin_interval_time >= trunc(sysdate-60)
AND sql_id in ('05amdgcorp052')
GROUP BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module
ORDER BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module ;
|
DAY
|
SQL_ID
|
PLAN_HV
|
MODULE
|
BG(M)
|
DK(K)
|
CPU(s)
|
ELAPSED
(min) |
|
10 26
|
05amdgcorp052
|
4206442378
|
kslstart.exe
|
434,62
|
0,08
|
1826,12
|
32,42
|
|
10 28
|
05amdgcorp052
|
4206442378
|
kslstart.exe
|
25,98
|
0
|
111,23
|
1,94
|
|
11 02
|
05amdgcorp052
|
4206442378
|
kslstart.exe
|
215,52
|
0,02
|
909,36
|
15,85
|
|
FETCH
|
ROWS
|
EXEC
|
BG/EXEC
|
DK/EXEC
|
CPU(s)/EXEC
|
ELAPSED(s)
/EXEC |
DK%BG
|
CPU%ELAP
|
|
806
|
10478
|
404
|
1075798
|
0
|
4,52
|
4,815
|
0
|
93,88
|
|
47
|
599
|
24
|
1082302
|
0
|
4,635
|
4,857
|
0
|
95,41
|
|
398
|
5174
|
200
|
1077616
|
0
|
4,547
|
4,756
|
0
|
95,59
|
2] AWR Top Consumers Weekly HTML
Report
2.1 Top SQL last X days
|
Goal
|
Get Top SQL consumers Buffer Gets
|
|
Version
|
10gR2
|
|
Views
|
dba_hist_sqlstat dba_hist_snapshot
|
|
Log as
|
SYSTEM
|
|
Args
|
Days Window
Buffers Get Millions Threshold
|
$ cat awr_top_sql_buffers_report.sql
-- ===========================================================================
-- Name : awr_7d_rep.sql
-- Version : V1.0
-- Goal : AWR report Top
SQL last 7 days
-- Level : Database
-- Run under SYSTEM catalog user
-- ===========================================================================
--
===========================================================================
-- Creation : 26/08/2010
-- Revision :
--
-- Auteur : AUC
-- ===========================================================================
set echo off;
set serveroutput on size 1000000
set flush on;
set lines 150 pages 5000
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
show user ;
variable a number
variable b number
begin
/* choose interval : 7 days = 8 */
--:a:=&1;
:a:=8;
/* Buffers Get Millions Threshold */
:b:=10;
end;
/
SELECT TO_char(min(
sn.begin_interval_time ),'DD/MM/YYYY HH24:MI')
"BEGIN_INTERVAL",parsing_schema_name "schema",
s.sql_id, plan_hash_value "PLAN_HV" , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DK(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2)
"ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "R OWS",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / sum(s.executions_delta) )
"BG/EXEC"
-- 'SELECT * FROM TABLE (DBMS_XPLAN.display_awr
('''||s.sql_id||''','''||plan_hash_value||''', NULL,''ADVANCED''))' "AWR_detail"
FROM dba_hist_sqlstat s,
dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND sn.snap_id >= ( select min(snap_id) from dba_hist_snapshot where
trunc(begin_interval_time) = trunc(sysdate-:a) group by
trunc(begin_interval_time) )
and sn.snap_id <= ( select max(snap_id) from dba_hist_snapshot where
trunc(begin_interval_time) = trunc(sysdate-1) group by
trunc(begin_interval_time) )
-- and parsing_schema_name = 'SCE_LOAD'
GROUP BY parsing_schema_name,s.sql_id,plan_hash_value,module
HAVING sum(s.executions_delta) > 0 AND sum(s.buffer_gets_delta)/1000000 > :b AND
sum(s.elapsed_time_delta) > 0
ORDER BY 6 DESC
/
exit
2.2 Get an HTML report by mail
To get an HTML array result
sent by mail, that SQL script could be called every week as follow
$ cat report_awr_all.ksh
#!/bin/ksh
#
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# report_awr.ksh
# PRD & PPR
# AWR Top SQL report
# AUC
# launched:
cron
# unix user:
oracle
# window:
number of days + 1
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
< SET you Oracle Unix Env here export ORACLE_HOME …
>
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 1. init
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# test args
if [ $# -lt 3 ] ; then
echo "Args missing"
echo "$0 Cmd window_days env" ; echo
"Cmd = awr_top_sql_buffers_report"
echo "./report_awr_all.ksh
awr_top_sql_buffers_report 8 {PRO|PPR|DEV}"
return 1 ; fi
Cmd=$1 #
Commande
# awr_top_sql_buffers_report | ...
wind=$2
env=$3
# Buffers Get seuil
BGs=10
export REP_SCRIPTS=<DIR_SCRIPT>
# calling example
# ./report_awr.ksh awr_top_sql_buffers_report <DB>
8
# directories
#BDc_low=$( echo ${BDc} | tr '[:upper:]' '[:lower:]' )
#log_dir=/work/${BDc_low}/dba/awr
log_dir=${REP_SCRIPTS}/awr
if [ ! -d ${log_dir} ] ; then mkdir -p ${log_dir} ; fi
awr_gen=${log_dir}/${Cmd}_$(date '+%d%m%y_%H%M').html
# final message subject
case ${Cmd} in
awr_top_sql_buffers_report)
msg_sub="[Oracle-${env}] AWR Top SQL report >${BGs}M Buffers Get
- $wind Jours $(date '+%d%m%y')" ;;
esac
# env
case ${env} in
PRO) db_1="DB05" ;
db_2="<DB> DB02" ; db_3="DB01" ;;
PPR) db_1="DB06" ;
db_2="PPRTR DB03" ; db_3="DB04" ;;
esac
dest_list="@mail_1,@mail_2"
EMAIL_HEAD="To: ${dest_list} \nFrom: \"DBA
MONITORING \"\nSubject: ${msg_sub} \nMIME-Version: 1.0\nContent-Type:
Multipart/Mixed;
boundary=Message-Boundary-$boundary\n\n--Message-Boundary-$boundary\nContent-Type:
text/html; charset=us-ascii\nContent-Transfer-Encoding: 7bit \n\n";
echo $EMAIL_HEAD>${awr_gen}
print "\n** ${msg_sub} **\n"
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 1. AWR report
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 2/ WORK
# 2.1 first exec, html format
for BDc in ${db_1} ; do
system_psw="<password>$( echo ${BDc} | cut
-c1 )$( echo ${BDc} | cut -c${#BDc} )"
sqlplus -s -MARKUP "HTML ON"
system/${system_psw}@${BDc} @${REP_SCRIPTS}/awr_top_sql_buffers_report.sql
${wind} ${BGs} | \
sed "s/USER is
"SYSTEM"/[${BDc}] AWR Top SQL report > ${BGs} M Buffers
Get - $wind Jours/" | \
sed -e "/\/html/d" -e "/\/head/d"
-e "/\/body/d" -e "/\/meta/d" | \
egrep -iv "Session|^old|new|procedure|rows"
>> ${awr_gen}
if (( $? != 0 )); then return 1 ; fi
done
# 2.2 central
for BDc in ${db_2} ; do
system_psw="<password>$( echo ${BDc} | cut
-c1 )$( echo ${BDc} | cut -c${#BDc} )"
sqlplus -s -MARKUP "HTML ON"
system/${system_psw}@${BDc} @${REP_SCRIPTS}/awr_top_sql_buffers_report.sql
${wind} ${BGs} | \
sed "s/USER is
"SYSTEM"/[${BDc}] AWR Top SQL report > ${BGs} M Buffers
Get - $wind Jours/" | \
sed -e "/html/d" -e "/head/d" -e "/body/d"
-e "/meta/d" | \
egrep -iv "Session|^old|new|procedure|rows"
>> ${awr_gen}
if (( $? != 0 )); then return 1 ; fi
done
# 2.3 last
for BDc in ${db_3} ; do
system_psw="<password>$( echo ${BDc} | cut
-c1 )$( echo ${BDc} | cut -c${#BDc} )"
sqlplus -s -MARKUP "HTML ON"
system/${system_psw}@${BDc} @${REP_SCRIPTS}/awr_top_sql_buffers_report.sql
${wind} ${BGs} | \
sed "s/USER is
"SYSTEM"/[${BDc}] AWR Top SQL report > ${BGs} M Buffers
Get - $wind Jours/" | \
sed "/Plus Report/d" | \
egrep -iv "Session|^old|new|procedure|rows"
>> ${awr_gen}
if (( $? != 0 )); then return 1 ; fi
done
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 2. Mail
# +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 3/ COMMUNICATION
sendmail -t ${dest_list} < ${awr_gen}
print "\n${NOMSCRIPT} ** end of script **\n"
return 0
2.3 Results
It’s even working in Lotus Notes !
3] Investigate several issues
3.1 TEMP overfilling
Get Direct Writes : add
them to the AWR reports between 2 dates or over 2 months :
SELECT TO_char(min( sn.begin_interval_time ),'DD/MM/YYYY HH24:MI') "BEGIN_INTERVAL",parsing_schema_name schema,
s.sql_id, plan_hash_value "PLAN_HV" , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DR(K)",
ROUND (sum(s.direct_writes_delta) / 1000, 2) "DW(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta) "ROWS",
sum(s.executions_delta) "EXEC",
ROUND (sum(s.buffer_gets_delta) / sum(s.executions_delta) ) "BG/EXEC",
'SELECT * FROM TABLE (DBMS_XPLAN.display_awr
('''||s.sql_id||''','''||plan_hash_value||''', NULL,''ADVANCED''))' "AWR"
FROM
dba_hist_sqlstat s, dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND begin_interval_time >= TO_DATE('27/05/2013 16:00:00','DD/MM/YYYY HH24:MI:SS')
AND begin_interval_time < TO_DATE('27/05/2013 19:30:00','DD/MM/YYYY HH24:MI:SS')
and parsing_schema_name = '<SCHEMA>'
GROUP BY parsing_schema_name,s.sql_id,plan_hash_value,module
HAVING sum(s.executions_delta) > 0 AND sum(s.buffer_gets_delta) > 0 AND sum(s.elapsed_time_delta) > 0
ORDER BY 8 DESC ;
SELECT TO_CHAR(sn.begin_interval_time,'MM DD') "DAY", s.sql_id, plan_hash_value , module,
ROUND (sum(s.buffer_gets_delta) / 1000000, 2) "BG(M)",
ROUND (sum(s.disk_reads_delta) / 1000, 2) "DR(K)",
ROUND (sum(s.direct_writes_delta) / 1000, 2) "DW(K)",
ROUND (sum(s.cpu_time_delta) / 1000000, 2) "CPU(s)",
ROUND (sum(s.elapsed_time_delta) / 1000000/60, 2) "ELAPSED(min)",
sum(s.fetches_delta) "FETCH",
sum(s.rows_processed_delta)
"ROWS",
sum(s.executions_delta)
"EXEC",
ROUND (sum(s.buffer_gets_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "BG/EXEC",
ROUND (sum(s.disk_reads_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta))) "DK/EXEC",
ROUND (sum(s.cpu_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000, 3 ) "CPU(s)/EXEC",
ROUND (sum(s.elapsed_time_delta) / DECODE (sum(s.executions_delta), 0, 1, sum(s.executions_delta)) / 1000000,3 ) "ELAPSED(s)/EXEC",
ROUND (sum(s.disk_reads_delta)/DECODE(sum(s.buffer_gets_delta),0,1,sum(s.buffer_gets_delta))*100, 2) "DK%BG",
ROUND (sum(s.cpu_time_delta)/DECODE(sum(s.elapsed_time_delta),0,1,sum(s.elapsed_time_delta))*100, 2) "CPU%ELAP"
FROM
dba_hist_sqlstat s, dba_hist_snapshot sn
WHERE s.snap_id=sn.snap_id
AND sn.begin_interval_time >= trunc(sysdate-60)
AND sql_id in ('7ra5n3u601547')
GROUP BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module
ORDER BY TO_CHAR(sn.begin_interval_time,'MM DD'), s.sql_id, plan_hash_value , module ;
DW (K) : Multiply by 8 KB to
get
3.2 Redolog or UNDO Blocks
over generation
3.2.1 Which Segment is
responsible for Redolog over generation ?
Search into AWR to get the top
db_block_changes Segments, as there is no equivalent to v$transaction.
-- which segment has
most DB_BLOCK_CHANGES_TOTAL ?
SELECT TO_char(min(begin_interval_time),'DD/MM/YYYY HH24:MI') "BEGIN_INTERVAL",
OWNER, OBJECT_NAME, OBJECT_TYPE,
CEIL (sum(DB_BLOCK_CHANGES_DELTA/1024)) "Block_Changes
(K)"
FROM DBA_HIST_SEG_STAT a, dba_hist_snapshot b , dba_objects c
WHERE a.snap_id=b.snap_id
AND
begin_interval_time >= TO_DATE('25/09/2013 00:01:00','DD/MM/YYYY HH24:MI:SS')
AND
begin_interval_time < TO_DATE('25/09/2013
23:59:00','DD/MM/YYYY
HH24:MI:SS')
and c.object_id=a.obj#
GROUP BY OWNER,
OBJECT_NAME,
OBJECT_TYPE
HAVING sum(DB_BLOCK_CHANGES_DELTA) > 1000000
ORDER BY 5 DESC ;
|
BEGIN_INTERVAL
|
OWNER
|
OBJECT_NAME
|
OBJECT_TYPE
|
Block_Changes (K)
|
|
25/09/2013 01:00
|
<SCHEMA>
|
SOURCE_ MACHINE
|
TABLE
|
13706
|
|
25/09/2013 00:30
|
<SCHEMA>
|
GROUPES_FAMILLES
|
TABLE
|
3289
|
|
25/09/2013 03:00
|
<SCHEMA>
|
GROUPES_MEMBERS
|
TABLE
|
3262
|
|
25/09/2013 00:30
|
<SCHEMA>
|
APPLIS
|
TABLE
|
3226
|
3.2.2 Find which session is
reponsible for undo over consumption
3.2.3 Get overall UNDO
Consumption in a period, by Hour
Within the period :
-- UNDOBLKS Represents the total number of undo blocks
consumed.
For the instance at the sampled time in the period : undo tablespace
-- ACTIVEBLKS
Total number of blocks in the active extents.
--
UNEXPIREDBLKS Total number of blocks in the unexpired extents.
-- EXPIREDBLKS Total number of blocks in the expired
extents.
with
blk_sz_MB as ( SELECT VALUE/1024/1024 blk_val FROM v$parameter WHERE NAME = 'db_block_size' )
select trunc(BEGIN_TIME,'HH24') ,
ceil(sum(UNDOBLKS*blk_val))
"UNDO Used size (MB)",
ceil(max(ACTIVEBLKS*blk_val))
"MAX UNDO Active size (MB)"
from dba_hist_undostat , blk_sz_MB
where to_char(BEGIN_TIME,'DD/MM/YYYY
HH24:MI:SS') between '22/09/2016
06:00:00' and '22/09/2016
15:00:00'
group by trunc(begin_time,'HH24') order by 1
|
BEG_TIME
|
UNDO
Used size (MB) |
UNDO
Active size (MB) |
|
21/09/2016 21:00
|
402
|
44
|
|
21/09/2016 22:00
|
158
|
84
|
|
21/09/2016 23:00
|
443
|
55
|
|
22/09/2016 00:00
|
1174
|
72
|
|
22/09/2016 01:00
|
907
|
79
|
|
22/09/2016 02:00
|
899
|
287
|
|
22/09/2016 03:00
|
1191
|
539
|
|
22/09/2016 04:00
|
302
|
716
|
|
22/09/2016 05:00
|
246
|
900
|
3.3 PGA Memory Leak
When experiencing AIX OS heavy
swapping and … Crash
***
2014-01-29 08:27:41.265
WARNING:
out of private memory [1]
DBA_HIST_PROCESS_MEM_SUMMARY
is a global overview of memory consumption. Unfortunately, it is not as
detailed as V$PROCESS_MEMORY.
select
begin_interval_time , num_processes , category , ceil(used_total/1024/1024) Used_MB , ceil(ALLOCATED_TOTAL/1024/1024)
ALLOCATED_TOTAL_MB ,
ceil(ALLOCATED_MAX/1024/1024)
ALLOCATED_MAX from
DBA_HIST_PROCESS_MEM_SUMMARY a ,
dba_hist_snapshot b
where a.snap_id=b.snap_id
AND
begin_interval_time between TO_DATE('29/01/2014
07:30:00','DD/MM/YYYY
HH24:MI:SS') AND TO_DATE('29/01/2014
08:30:00','DD/MM/YYYY
HH24:MI:SS')
order by 1 desc
|
BEGIN_INTERVAL_TIME
|
NUM_PROCESSES
|
CATEGORY
|
USED_MB
|
ALLOCATED_TOTAL_MB
|
ALLOCATED_MAX
|
|
29/1/14 8:00 AM
|
45
|
Other
|
|
15819
|
15759
|
|
29/1/14 8:00 AM
|
41
|
PL/SQL
|
1
|
1
|
1
|
|
29/1/14 8:00 AM
|
32
|
SQL
|
1
|
2
|
1
|
|
29/1/14 8:00 AM
|
13
|
Freeable
|
0
|
27
|
14
|
|
29/1/14 7:30 AM
|
69
|
Other
|
|
83
|
12
|
|
29/1/14 7:30 AM
|
64
|
PL/SQL
|
2
|
2
|
1
|
|
29/1/14 7:30 AM
|
57
|
SQL
|
5
|
9
|
2
|
|
29/1/14 7:30 AM
|
33
|
Freeable
|
0
|
26
|
3
|
Category name. Categories include "SQL",
"PL/SQL", "OLAP" and "JAVA".
Special categories are "Freeable" and
"Other".
Freeable memory has been allocated to the process by
the operating system, but has not been allocated to a category.
"Other" memory has been allocated to a
category, but not to one of the named categories
select c.username , CATEGORY, ceil(ALLOCATED/1024/1024) ALLOCATED_MB, ceil(USED/1024/1024) USED_MB, ceil(MAX_ALLOCATED/1024/1024) MAX_ALLOCATED_MB
from
v$process_memory a , v$process b , v$session c where a.pid=b.pid and b.addr=c.paddr order by 3 desc
3.4 last 30 days, CPU consumption
select trunc(sysdate,'MONTH') , MIN , MAX , AVG from (
select min(CPU_Use_Pct) MIN , round(avg(CPU_Use_Pct),2) AVG , max(CPU_Use_Pct) MAX from (
select 'OS Busy Time' series, to_char(snaptime, 'yyyy-mm-dd hh24') snap_time, round(busydelta / (busydelta +
idledelta) * 100, 2) CPU_Use_Pct
from ( select s.begin_interval_time
snaptime, os1.value - lag(os1.value) over (order by s.snap_id) busydelta,
os2.value - lag(os2.value) over (order by s.snap_id) idledelta from dba_hist_snapshot s, dba_hist_osstat os1, dba_hist_osstat os2
where s.snap_id = os1.snap_id and s.snap_id = os2.snap_id and os1.stat_name
= 'BUSY_TIME' and os2.stat_name
= 'IDLE_TIME'
and
begin_interval_time > sysdate-30 ) ) )
3.5 AWR see tablespace size
history
select tsname , size_MB_diff , days , days_preceding , size_MB ,
size_MB_preceding from (
select tsname , ceil(size_MB-size_MB_preceding) size_MB_diff , days , days_preceding , size_MB ,
size_MB_preceding from (
select days , snap_id , tsname , size_MB ,
ceil(lag(size_MB, 1, -1) over(partition by tsname order by snap_id))
size_MB_preceding -- (1) = 1 row, (-1) =
preceding
, lag(days, 1, -1) over(partition by tsname order by snap_id)
days_preceding -- (1) = 1 row, (-1) =
preceding
from (
with SH AS ( select max(snap_id) SS from DBA_HIST_SNAPSHOT
) , SL AS ( select min(snap_id) SS from DBA_HIST_SNAPSHOT )
SELECT TO_CHAR(sp.begin_interval_time,'DD-MM-YYYY') days ,
sp.snap_id ,
ts.tsname -- , tsu.tablespace_id
, ceil(tsu.tablespace_size*8192/1024/1024) size_MB
-- , max(tsu.tablespace_size*8192/1024/1024) over (
partition by TO_CHAR(sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname )
max_cur_size_per_day_MB
FROM
DBA_HIST_TBSPC_SPACE_USAGE tsu
,
DBA_HIST_TABLESPACE_STAT ts
,
DBA_HIST_SNAPSHOT sp
, SH , SL
WHERE
tsu.tablespace_id= ts.ts# AND tsu.snap_id = ts.snap_id AND tsu.snap_id = sp.snap_id AND ( tsu.snap_id = SL.SS OR tsu.snap_id = SH.SS )
ORDER BY ts.tsname, snap_id )
) WHERE size_MB_preceding != -1
) WHERE size_MB_diff > 100
;
TSNAME SIZE_MB_DIFF DAYS DAYS_PRECEDING SIZE_MB SIZE_MB_PRECEDING
HRTE 1135 07-01-2013 19-09-2018 3624 2489
HRXT 112 07-01-2013 19-09-2018 176 64
