Simulated ASH 2.1 (SASH 2.1)

Thanks to Marcin Przepiorowski for improving the SASH code!

see http://ashmasters.com/ash-simulation/simulation-v21/

V2.1 – Installs on Windows or Unix on Standard Edition I would like to present a new version of S-ASH package. It is based on Kyle’s version v2 and it has been modified by me to meet a customer requirements related to minimize changes on production database and some bug fixing and new functionality has been added. In addition to that a new project called OraSASH has been created on SourceForge to keep tracking of all changes and bug fixing.

In version 1 and version 2 collection job has been implemented on production database and PL/SQL package has to been deployed on it too. In new version collection job is running on repository database and it is accessing target database via DB link and list of changes on production database has been limited to one user and one view.

————————————————————————————

Installation

Please find a list of installation steps:

1. create a SASH user – run repo_0_user.sql – as user SYS on repository database

2. add sys procedure to kill jobs – run repo_1_sys_procedure.sql – as user SYS on repository database

3. create repository – run – repo_1_tables.sql – as user SASH on repository database

4. create repository management package – run – repo_2_pkg.sql – as user SASH on repository database

5. create user and view on target – run – targ_1_userview_9i.sql for 9i ortarg_1_userview_10g.sql for 10g and above – as SYS user on database being monitored

6. create database link using – run – repo_3_dblink.sql – as user SASH on repository database

7. create a sash packages – run – repo_4_packages.sql – as user SASH on repository database

8. fill setup tables – login into SQL*Plus as SASH user and execute following statements exec sash_pkg.set_dbid; exec sash_pkg.get_event_names; exec sash_pkg.get_users; exec sash_pkg.get_params; sash_pkg.get_data_files ; commit;
   

1. If target database is in version 9i fill up wait classes table – run repo_5_waitgroups.sql – as user SASH on repository database

2. If target database is in version 10g or above run following statement as SASH user on repository database update sash_event_names sen set sen.wait_class = ( select wg.wait_class from gv$event_name@sashprod wg where wg.name=sen.name); commit;
   

1. setup jobs – login into SQL*Plus as SASH user and execute following statements exec sash_repo.setup_jobs

—————————————– Maintenace All commands have to be executed as SASH user connected to repository database.

Starting collection job exec sash_repo.start_collecting_jobs;

Stoping collection job exec sash_repo.stop_collecting_jobs;

Data retention Collection job is using “poor” man partitioning trick and number of tables have been increased from 7 in version 2 into 31 in version 2.1. It allow to keep performance data for one month. Whole solution has been tested on database with 300 connected sessions and it gathered about 100 MB of performance data daily.

Retention setup: exec sash_repo.set_retention(’x’);

where x is one of: – d – last week – w – last month – h – last 24 h – m – last 30 minutes

If you have any problems or questions feel free to add comments here or contact me directly Marcin Przepiorowski