General
================================
AWR, ADDM, STATSPACK, AWRDDRPT reports.
gen_awr.sh
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrrpt.sql
gen_addm.sh
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/addmrpt.sql
gen_ash.sh
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/ashrpt.sql
gen_statspack.sh
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/spreport.sql
#for specific SQL
#@sqlplus perfstat/iwantawr@igt @/software/oracle/111/rdbms/admin/sprepsql.sql
awrddrpt.sql
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrddrpt.sql
The awrddrpt.sql script is the AWR "diff" report for comparing two AWR reports.
The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.
This awrddrpt.sql script compares two time periods.
To do this, the awrddrpt.sql script accepts four snapshot values.
Start and end for the first AWR report.
Start and end for the second AWR report.
Once the four snapshot numbers are collected, the awrddrpt.sql script creates two AWR reports and then compares the two reports.
The awrddrpt.sql script is very useful for comparing performance during two elapsed time periods.
======================
To see and change AWR settings:
======================
To see current settings:
SELECT snap_interval, retention FROM DBA_HIST_WR_CONTROL;
To set interval to 60 minutes an retention to 14 days (60*24*14=43200)
EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(INTERVAL => 60,RETENTION => 20160);
================================
perfstat Purge old intervals data
================================
perfstat Purge old intervals data
================================
Use this solution to create a job to delete old snapshots
When checking for big tables, following table occupy a lot of space on WORKAREA Tablespace:
Option A - Delete few of the snapshots
sqlplus perfstat/iwantawr@igt
/software/oracle/112/rdbms/admin/sppurge.sql
Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to. Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.
It is NOT possible to rollback changes once the purge begins.
You may wish to export this data before continuing.
Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 10030
Using 10030 for lower bound.
Enter value for hisnapid: 11949
Using 11949 for upper bound.
Deleting snapshots 10030 - 11949.
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'
ORA-06512: at "PERFSTAT.STATSPACK", line 1330
ORA-06512: at "PERFSTAT.STATSPACK", line 1501
ORA-06512: at line 4
Option B - Truncate statspack tables
@$ORACLE_HOME/rdbms/admin/sptrunc.sql
SQL> @/software/oracle/112/rdbms/admin/sptrunc.sql
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>
Enter value for begin_or_exit:
Entered at the 'begin_or_exit' prompt
... Starting truncate operation
Table truncated.
Table truncated.
Table truncated.
...
...
... Truncate operation complete
Error ORA-20200: Database/Instance 1278761441/1 does not exist in STATS$DATABASE_INSTANCE
This error means that no statistics were gathered in the past.
To create perfstat schema:
@${ORACLE_HOME}/rdbms/admin/spcreate.sql
Step 1.
sqlplus system/xxxxxxxx
GRANT CREATE JOB TO perfstat;
Step 2.
conn perfstat/xxxxxxxx
@${ORACLE_HOME}/rdbms/admin/spauto.sql
EXECUTE statspack.snap;
To gather interval stats now:
CONNECT perfstat/my_perfstat_password
EXECUTE statspack.snap;
To create the job to gather stats in automated way:
SQL> @/software/oracle/193/rdbms/admin/spauto.sql
SQL> @${ORACLE_HOME}/rdbms/admin/spauto.sql
begin
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 3
In Oracle 19, the owner of might not have privilege to execute create job, for collecting stats
To fix it:
as system:
GRANT CREATE JOB TO perfstat;
as perfstat:
SQL> @/software/oracle/193/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.
Now, the job was created
Run statspack report for every 10 minutes
SET LINESIZE 120
COL what FOR A40
COL interval FOR A30
SELECT job, what, interval
FROM USER_JOBS
WHERE what LIKE '%stat%';
JOB WHAT INTERVAL
--- ---------------- -------------------------
124 statspack.snap; trunc(SYSDATE+1/24,'HH')
BEGIN
DBMS_JOB.next_date(124, TO_DATE('20211007 14:00:00','YYYYMMDD hh24:mi:ss'));
DBMS_JOB.interval(124,'TRUNC(SYSDATE+10/1440,''MI'')');
commit;
END;
/
================================
Run perfstat from crontab
================================
Run perfstat from crontab
================================
How to run STATSPACK report from crontab?
1. Copy/paste oracle scripts to the local directory.
2. Create a Linux wrapper script:
- set env variables
- populate the parameters for my_spreport.sql
- clean up old report files
3. Create my_spreport.sql, a copy paste from oracle my_spreport.sql, to holde parameters from script, and pass them to sprepins.sql. Normally these parameters are passed from prompt, but here they are passed from script.
4. Create a crontab entry, to run once every hour.
crontab
5 * * * * /software/oracle/oracle/scripts/statspack/cron_statspack.sh
cron_statspack.sh
#!/bin/bash
BAS_ORACLE_LIST=igt
ORA_VER=1120
ORACLE_SID=igt
ORACLE_BASE=/software/oracle
ORACLE_HOME=/software/oracle/112
WORK_DIR=/software/oracle/oracle/scripts/statspack
cd ${WORK_DIR}
DAYS_TO_KEEP=14
get_last_snap_id () {
sqlplus -s perfstat/iwantawr@igt <<!
set heading off
set feedback off
set pages 0
select MAX(snap_id) FROM stats_snapshot;
!
}
max_snap_id=$(get_last_snap_id)
echo max_snap_id = $max_snap_id
prev_snap_id=$((max_snap_id-1))
echo prev_snap_id=$prev_snap_id
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
report_name=perfstat_${RUN_DATE}.rep
#sqlplus perfstat/iwantawr@igt /software/oracle/1910/rdbms/admin/spreport.sql
sqlplus perfstat/iwantawr@igt @my_spreport.sql $prev_snap_id $max_snap_id $report_name
find ${WORK_DIR} -type f -name "perfstat_*rep" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
my_spreport.sql
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just c;
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
define begin_snap = &1;
define end_snap = &2;
define report_name = &3;
@@sprepins.sql
EXIT;
Bug in perfstat - Data Guard is listed as a main wait event
After running spreport.sql Data Guard is listed as a main wait event
cp /software/oracle/1910/rdbms/admin/sppurge.sql /software/oracle/1910/rdbms/admin/sppurge.sql_orig
Edit existing file /software/oracle/1910/rdbms/admin/sppurge.sql
ORACLE_HOME=/software/oracle/1910
After running spreport.sql Data Guard is listed as a main wait event
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
-------------------------- ------ -------- ------ ------
Data Guard: Timer 6 181 30208 30.6
Data Guard: Gap Manager 3 181 60414 30.6
pman timer 58 174 3008 29.4
CPU time 15 2.5
log file sync 653 13 20 2.2
"Data Guard: Timer", "Data Guard: Gap Manager", "pman timer" - are idle events that should not be listed as wait events
Issue is due to a bug #Bug 28523746 – STATSPACK: TOP 5 TIMED EVENTS CONTAINS IDLE WAIT EVENTS
Solution:
Update the table STATS$IDLE_EVENT
INSERT INTO STATS$IDLE_EVENT
SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'
MINUS
SELECT event FROM STATS$IDLE_EVENT;
commit;
==========================
Error ORA-20200: Database/Instance does not exist
==========================
ERROR at line 1:
ORA-20200: Database/Instance 1278761441/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23
This error can occur if spreport.sql is ran before any snapshots have been taken.
Try creating a snapshot using execute statspack.snap and rerun spreport.sql.
sqlplus perfstat/xxxxxx@igt
select * from stats$database_instance;
no rows selected
EXECUTE statspack.snap;
select * from stats$database_instance;
--Data is there
==========================
Drop duplicate statspack.snap job
==========================
sqlplus perfstat/xxxxxx@igt
COL SCHEMA_USER FOR A30
COL WHAT FOR A50
SELECT job, schema_user
FROM DBA_JOBS
WHERE UPPER(what) LIKE '%STATSPACK%';
BEGIN
DBMS_JOB.remove(21);
commit;
END;
/
==========================
Automate Delete
==========================
Step A.
cp /software/oracle/1910/rdbms/admin/sppurge.sql /software/oracle/1910/rdbms/admin/sppurge.sql_orig
Edit existing file /software/oracle/1910/rdbms/admin/sppurge.sql
Add the lines in blue
In this example: purge data older than 30 days
In this example: purge data older than 30 days
prompt
prompt
prompt Snapshots for this database instance
prompt ====================================
select s.snap_id
, to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss') snap_date
, s.baseline
, s.snap_level "level"
, di.host_name host_name
, s.ucomment
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.startup_time = s.startup_time
order by db_name, instance_name, snap_id;
-------------------------------
-- Populate HiSnapId and HiSnapId
-------------------------------
COL min_snap_id new_val LoSnapId
COL max_snap_id new_val HiSnapId
SELECT MIN(SNAPSHOTS.snap_id) min_snap_id,
MAX(SNAPSHOTS.snap_id) max_snap_id
FROM STATS$SNAPSHOT SNAPSHOTS
WHERE SNAPSHOTS.snap_time < TRUNC(SYSDATE) - 30;
SELECT &&LoSnapId as low_value from DUAL;
SELECT &&HiSnapId as high_value from DUAL;
--------------------------------
Step B.
Create a crontab task
Create a crontab task
purge_perfstat.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
ORACLE_HOME=/software/oracle/1910
TNS_ADMIN=${ORACLE_HOME}/network/admin
PATH=${PATH}:${ORACLE_HOME}/bin
sqlplus perfstat/iwantawr@igt @/software/oracle/1910/rdbms/admin/sppurge.sql
crontab
4 6 * * * /software/oracle/oracle/scripts/awr/purge_perfstat.sh
==========================
Summary
==========================
sqlplus /nolog
connect / as sysdba
define perfstat_password=iwantawr
define default_tablespace=WORKAREA
define temporary_tablespace=TEMPORARY
@?/rdbms/admin/spcreate.sql
GRANT CREATE JOB TO perfstat;
as perfstat
connect perfstat/&&perfstat_password
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
var job number;
BEGIN
DBMS_JOB.SUBMIT
(job => :job,
what => 'declare snap number; begin snap := statspack.snap (i_snap_level=>7); end;',
next_date => trunc(sysdate+1/24,'HH'),
interval => 'trunc(SYSDATE+1/24,''HH'')'
);
COMMIT;
END;
/
BEGIN
DBMS_JOB.SUBMIT
(job=>:job,
what=>'statspack.purge(i_purge_before_date=>sysdate-14,i_extended_purge=>true);',
next_date => TRUNC(SYSDATE+1)+3/24,
interval => 'TRUNC(SYSDATE+1)+3/24'
);
commit;
END;
/
INSERT INTO STATS$IDLE_EVENT
SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'
MINUS
SELECT event FROM STATS$IDLE_EVENT;
commit;
