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’
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'')');
commit;
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
exec RDSADMIN.RDS_RUN_SPREPORT(,); 
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.
