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’
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.