Friday, September 10, 2010

Simulated ASH 2.1 (SASH 2.1)

V2.1 - Installs on Windows or Unix on Standard EditionI 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.

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

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

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

Starting collection jobexec sash_repo.start_collecting_jobs;

Stoping collection jobexec sash_repo.stop_collecting_jobs;

Data retentionCollection 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 MBof 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 directlyMarcin Przepiorowski

Facebook

Kyle Hailey

Before working at Embarcadero I redesigned the performance pages in Oracles OEM 10g (see a before and after example). I am on the patent from the team for our tuning methodology (Patents: 20060059205) . I freely provide graphical monitoring tools such as ASHMON (TCL/TK) as well as scripts to profile database load such as S-ASH ie Simulated ASH (PLSQL). S-ASH, ASHMON and more are available at my old site http://ashmasters.com/ . I have made available C code to directly read profiling data out of Oracle's memory structures, called Direct Memory Access. I speak at conferences and give classes around the world.This blog has some of my new ideas on Visual SQL Tuning (VST), work on DB Optimizer, possibly some past material on DB performance monitoring as well as lots of references and pointers to other information on the web that I find important and worth emphasizing.