When you diagnose performance problems, awr is an indispensable tool? Rdbmsadminawrrpt. SQL may be used more often, and sometimes you may feel that the input parameter is still
When you diagnose performance problems, awr is an indispensable tool? /Rdbms/admin/awrrpt. SQL may be used too much, and sometimes the input parameters may be
When you diagnose performance problems, awr is an indispensable tool? /Rdbms/admin/awrrpt. SQL may be used too much, and sometimes it may be too cumbersome to input parameters. Copying a snapshot list is not convenient.
For example, if you want to view snapshots from to a day and generate an awr report, you may have to calculate how many days the snapshot takes, it is complicated to copy one by one from a lengthy snapshot list. I always felt a little inconvenient in the process of generating awr. Today I made up my mind to customize it myself.
First, solve the snapshot list problem. Generally, we need to get the following list.
Instance DB Name Snap Id Snap Started Level
--------------------------------------------------------
TEST01 TEST01 1274 19 Oct 2014 1
1275 19 Oct 2014 1
1276 20 Oct 2014 00: 00 1
1277 20 Oct 2014 0:00 1
1278 20 Oct 2014 1
I used the following script for customization. For example, I needed a snapshot from to on July 22, October 19 two days ago, then I can directly use the script to get a simple list of snapshots.
The output result is as follows. Each day is well-off for one hour. In this way, I can simply select which snapshots are needed. View it at any time.
Current Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NUM INST_NAME
---------------------------------------------
3645037571 TEST01 1 TEST01
DB_NAME SNAP_ID SNAPDAT LVL
-------------------------------------------------------
TEST01 1262 19 Oct 2014 1
1263 19 Oct 2014 1
1264 19 Oct 2014 1
1265 19 Oct 2014 1
1266 19 Oct 2014 1
1267 19 Oct 2014 1
1268 19 Oct 2014 1
1269 19 Oct 2014 1
8 rows selected.
The script content is as follows:
Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID < Break on db_name
Set pages 50
Set linesize 100
Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~
Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v \ $ database d,
V \ $ instance I;
Select
Di. db_name
, S. snap_id
, To_char (s. end_interval_time, 'dd Mon YYYY HH24: mi ') snapdat
, S. snap_level lvl
From dba_hist_snapshot s
, Dba_hist_database_instance di
Where
(Di. dbid, di. instance_number) in
(Select d. dbid
, I. instance_number inst_num
From v \ $ database d,
V \ $ instance I)
And di. dbid = s. dbid
And di. instance_number = s. instance_number
And di. startup_time = s. startup_time
And to_char (END_INTERVAL_TIME, 'yyyymmdd') = '$1'
And EXTRACT (hour from END_INTERVAL_TIME) between $2-1 and $3 + 1
Order by db_name, instance_name, snap_id;
EOF