Thursday, February 25, 2016

Installing statspack on an Oracle RDS on Amazon Web Services

One of our clients has advised us that they are running RDS on AWS and are having performance problems. Quite a co-incidence since my last few posts have been about creating and monitoring one of these. It just shows that it's a good idea to keep up with this stuff as much as possible.

They are running SE1, V11, so don't have AWR.

Since they don't have host access, I wondered how I could install statspack - but AWS has thought of this and there is a way to do it.

The steps are to create an Option Group with STATSPACK, then assign that group to the instance, then connect with SQLDeveloper and change the perfstat account and set up a job to run a snapshot periodically.

They also have an RDSADMIN procedure to generate the snapshot reports.

So, this is how to do it.

Sign onto the AWS console and select the 'RDS' icon.

Select ‘Option Groups’

Click on ‘Create Group’

Give the Group a name and select the RDS type from the drop-down menus

Click on ‘Create’

Click the box next to the new Group to select it, then click on ‘Add Option’

Use the drop-down to select ‘statspack’ then click on ‘Yes’ next to ‘Apply Immediately’

Click on ‘Add Option’

 It will show the ‘STATSPACK’ option

Select ‘Instances’

Click on the box next to the instance to select it, then use the drop-down ‘Instance Actions’ to select ‘Modify’

Scroll down to the ‘Database Options’ section, select the new group from the drop-down, check the ‘Apply Immediately’ box and click on ‘Continue’

At the next page, click on ‘Modify DB Instance’

Click on the magnifying glass icon, and the status will be shown

Click on the ‘refresh’ icon at the top of the page after a minute or so to check on the progress – it shouldn’t take long.

It should show ‘in sync’

You now have to enable the perfstat account and create a job to take periodic snapshots.
Sign in to the instance using SQLDeveloper or SQLPlus as the master user created at RDS set up.

Change the password and unlock the account

Create a new connection as the perfstat user, connect, and create the job

variable jn number;
execute dbms_job.submit(:jn, 'statspack.snap;',sysdate,'trunc(SYSDATE+1/24,''HH24'')');

Statspack is now installed and taking a snapshot every hour.

You can take a manual snapshot in the usual way

To generate and view a statspack report

Find the snapids

select snap_id, to_char(snap_time,'dd-mm-yy hh24:mi:ss') from stats$snapshot order by 1;

Run the RDS procedure

The reports can be found under the ‘Logs’ section

The report should be easy to find. You need to download it, the view button shows an error

Click on the ‘Download’ button, then right-click on the link and save it

 The report is the standard statspack report

So, pretty easy.