Mechanism
No interfere with your Oracle database: no SQL connection.
Oracle Measures : STATSPACK, AWR in texte format. compressed into a zipfile
Flexibility use :
Pro-actif : one day report (24 snapshots of 60 minutes).
Performance monitoring : monthly report (30 snapshots of 24h for example).
Specific analysis : weekly report (7 daily snapshots of 1h between 1am and 2am for example). |
 |
Compatibility :
AWR : 10.1.x, 10.2.x, 11.1.0.7, 11.2.x
STATSPACK : 8.1.7, 9.0.x, 9.2.x, 10.1.x, 10.2.x, 11.1.x, 11.2.x
BSTAT/ESTAT : 7.3.4, 8.0.6, 8.1.7, 9.0.x, 9.2.x
User guide
Snapshots must be taken at a regular interval. It is recommended to have 24 AWR reports in text format (with an one hour elapsed time) in the zip file. ONDATAPERF can not process a capture with only one report, it can not make charts with one snap. ONDATAPERF can process captures with more than 50 snaps (99 max), but the performance analysis report become unreadable.
By default AWR takes snapshots every hour, so 24 per day. It is recommended not to exceed 1 hour elapsed time between 2 reports. With the following script, you will automatically have your zip file for one day: get_snap_awr.sql (for AWR) or get_snap_sp.sql (for STATSPACK).. If you want to analyze 2 days, then run the script 2 times and then have one zip file for each day. You will get 2 zip files and you can manually merge the 48 AWR reports in your zip file to upload.
With too much elapsed time, an AWR report contains overflows, and may pass beside a bottleneck because of the average made on the elapsed time. With too few AWR reports in a ZIP file, you will not see change in charts and miss the performance problem.
ONDATAPERF has designed a 5 steps procedure.
Follow the procedure to find out how it works.
1) Capture tools installation (Statspack, AWR)
2) Schedule
snaps ( statspack or AWR)
3) Generate many plain-text statspack or
AWR outputs and zip them into a file.
4) Login into
www.ondataperf.com with your username and password et upload the zip
file.
5) Get the ONDATAPERF performance analysis report
1) Capture tools installation (statspack, AWR)
If the tools are already installed goto the next step.
a) Statspack installation
Run a « spcreate » after logon as «
sqlplus / as sysdba”
For more information about «
Statspack Installation » please see the following Metalink Note
“Installing and Configuring StatsPack Package Note:149113.1
NOTE METALINK STATSPACK”
Snapshots can be collected a
various levels, each increasing level collecting a greater amount of
information about the database
|
Statspack level 0
|
This level captures general statistics
|
|
Statspack level 5
|
This level captures general statistics + SQL
|
|
Statspack level 6
|
This level captures general statistics + SQL + capturing SQL
plan and SQL plan usage
|
|
Statspack level 7
|
This level captures general statistics + SQL + capturing SQL
plan and SQL plan usage + segment level statistics
|
|
Statspack level 10
|
This level captures general statistics + SQL + capturing SQL
plan and SQL plan usage + segment level statistics + Child Latch
statistics
|
A statspack with a level 7 is strongly recommended
b) AWR ( no installation required)
To get started with the AWR, nothing needs to be installed other
than the Oracle10g or Oracle 11g.
AWR collects data without
running any script
These collections are done every hour.
DBA_HIST_SNAPSHOT displays information about
these collections in the Workload Repository.
The interval can be modified with the package
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
c) utlbstat, utlestat
Obsolete but supported in ONDATAPERF
2) Schedule many captures at a regular interval
a) Oracle only: Use the dbms_job
For example : Run statspack snapshot every 30 minutes
SQL>
SQL>
DECLARE
jobno number;
instno number;
BEGIN
SELECT instance_number INTO instno FROM v$instance;
DBMS_JOB.SUBMIT(jobno, 'statspack.snap(i_snap_level => 7);',
trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60),
'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, instno);
COMMIT;
END;
/
Don’t
forget to set the parameter job_queue_processes.
b) UNIX : Please use the crontab
c) Windows : Create a scheduled task
3) Generated plain-text statspack or AWR outputs and zip them into a file.
The snaps created by Statspack ou AWR are snapshots taken every
hour.
Reports between the snap n and n+1 must be generated
IT IS STRONGLY RECOMMENDED TO RUN AN ONDATAPERF SCRIPT
All platforms
These scripts are UNIX and Windows compatible.
It generates the
zip file at the end.
For Statspack:
GET_SNAP_SP.sql
For AWR:
GET_SNAP_AWR.sql
4) Log into www.ondataperf.com
Use an ONDATAPERF account
If you are not already registered, Please Register
The Free Edition
is available with limited features.
Click on Upload & Analysis.

Choose the zip file to upload

When the zip file is uploaded.
ONDATAPERF makes a first analysis

You have to choose the report language, the frequency, the
processor, and the database volume.
The click « Deep Analysis » button will generate a PDF
or HTML report with accurate information about the database.

5) Get the Performance analysis report
Click on "My ONDATAPERF reports", you will get a
performance Analysis Report in HTML or PDF format.