ASH is only available in Oracle 10g and higher and also requires the diagnostic package license. If you are are an older version of Oracle or don’t have the diagnostic package license then you have the option of simulating ASH (S-ASH) yourself. Your milage may vary on the scripts below. No guarentees on them working correctly on you r systems. Make sure you test and understand them.
For the scripts below it is highly recommended to put the repository on a different database than the database being monitored. I’ve only tested resource consumption for gathering data (less than 1% of 1 CPU) and not the resource usage of actually storing the data.
——————————————–
V1 - Unix install for Enterprise Edition - uses partitioning and sub-partioning
I’ve road tested these to some degree on Solaris and Linux systems and the seem to work alright, but I’m sure there is always more to be fixed and added. For example, I don’t collect RAC instance ids and I want to. This is a single shell script that is run both on the repository database and on the target database.
hostview.sql - SASH collects data for multiple databases. This sql script will change which database the SASH views fliter data for.
If using ASHMON on this version of SASH you need to run two scripts
ashmon views - create compatible views names on top of SASH views
wait groups - create wait groups
———————————————
V2 Installs on Windows or Unix on Standard Edition
The scripts below are brand newer so there will probably be some hickups in them. Please comment on the blog on any issues and/or solutions you find. I wrote these scripts as both an option for installing on Windows (since the first version was a shell script) and for allowing Standard Edition as a repostitory but still having data purging using “poor man’s partitioning”, ie having separte tables for each day of the week, and using view with union all of the 7 tables in the data mining scripts. I find the following scripts easier to follow, read, understand and change than the single script above.
Repository -
repo_0_user.sql - create SASH user
repo_1_tables.sql - connect as SASH and run. Installs schema on repository database. WARNING - don’t run as SYS or SYSTEM because it recreates a couple dba_ views.
Target - ie Monitored Database , run sys (possible to run as SYSTEM but have to change script not to use x$)
targ_1_pkg.sql - install collection package on each database to be monitored
targ_2_jobs.sql - start up collection in a job on each database to be monitored
Repository - optional, if you want automatic purging
repo_2_pkg.sql - create an automatic purge procedure on repository machine
repo_3_jobs.sql - start a job on repository machine to purge oldest day of data
Data Mining S-ASH
When running ASH scripts or ASHMON on SASH data there are a couple of issues.
1) WAIT GROUPS : SASH doesn’t collect wait groups because its set up mainly for version 8 and 9 (since ASH is already on 10g+), so the wait groups need to be created in order to run ASHMON. Ideally there would be a version check to get wait_groups from v$event_name in 10g or higher, but I haven’t done that yet.
repo_4_waitgroups.sql - set up wait groups
2) CURRENT DATABASE : SASH collects data for multiple databases into the same schema, thus scripts and ASHMON have to filter by the correct DBID. I do this by having a table SASH_TARGET that contains the DBID that I’m interested in. Then views like v$active_session_history include a filter on DBID from SASH_TARGET. Thus to change databases, I just change the DBID in SASH_TARGET.
repo_5_curdb.sql - change the DBID in SASH_TARGET
I installed the V.2 objects but I have problems with the targ_1_pkg.sql script. The package body seems to have a number of compilation errors.
At the end, you use a dbms_lock.sleep inbuilt but instead of specifying seconds, you enter the string sleep:
dbms_lock.sleep(sleep);
Is this correct? Should it not be (example):
dbms_lock.sleep(3);
Also, you reference SYS.v$database, amongst others, but I could not get this to work manually when prefixing the schema owner, and as v$ objects are all views, I simply omitted SYS. If you want to see v$ views as a normal user, I just grant SELECT_CATALOG_ROLE to be able to see all v$ views.
Also, there were a number of v$ views that were prefixed with a ‘g’:
select … from … gv$instance;
Is this correct or a typo? could not find any sash segments with this prefix.
Still would not compile after correcting these.
I did look at the poor mans partitioning though and it looks like it will work fine, but I have not tested.
On Standard Edition 9i, ashmon (via s-ash) is useful to complement eom for tuning oracle databases.
Here are some remarks from my experiences with the scripts:
-Use of s-ash for tuning database 9.2.0.7 works fine if you modify the package sash_pkg not to query v$sql_plan.
Otherwise you will get an error “ORA-07445″
This bug seems to be fixed in 9.2.0.8 (Metalink 4434689.8).
-Before execute script hostview.sql ( filtering) you have to verify that the field “host ” (table SASH_TARGETS) is correctly filled.for each dbid.
-default tablespace of user sash increases in size:
You have periodically to purge some tables ( eg: SASH_SQLSTATS_ALL)
andrew henry -
1)sleep is a variable - I just changed it to v_sleep to be less confusing
2)sys.v$database access - script needs to be run as SYS
3)gv$ - just changed these to v$
———————————–
Checinski -
Thanks for the good info.
1) There seems to be a bug on 9.2 querying v$sql_plan, so the work around is just to not collect the plan info on 9.2. I could add a version check for this, but that’s way down my list of things to do. Anyone else want to add a version check in the collection package?
2) For v1, sash_targets should be filled in with the target dbid automatically when installing sash_pkg on each target.
For v2, you see 0 rows in v$active_session_history its probably because the dbid filter isn’t set up.
For example
select count(*) from sash_all;
227
select count(*) from v$active_session_history;
0
@repo_5_curdb
HOST
——————————
KYLEHPD
KYLEHPLAP
Enter value for host: KYLEHPD
select count(*) from v$active_session_history;
227
3) yes, the sql plans, text and stats don’t get purged, only SASH does. Still to be done is a purge routine for SQL plans, text and stats. An easy purging method is to just delete from
sash_sqltxt
sash_sqlplans
sash_sqlstats
where sql_id in (select sql_id from sash_sqlids where last_found < sysdate - 7 )
or soemthing to that effect.
Deletes are expensive, instead of automating this deleting, I was still thinking of some way to partition the data, but didn’t come up with a method yet, so deleting is the only way right now.
If I run your script for the target DB (targ_1_pkg.sql) on either a 9.2.0.8 or 10.2.0.4 database (win32) I get compilation errors on the package body. I run the script as sys, the database link works fine and the sashnow view gets created without issues.
I just ran it through Toad to see where it was failing:
SASH.sash_curhost@SASHREPO;
This object; sah_curhost, is not created in your script, but is referenced in the package body. Do you have the definition for this table?
1) Andrew -yep - sash_curhost was wrong. Should have been sash_target. Changed.
2) added back in the use of gv$ as a workaround to ORA-02070
3) on 9i I as getting ora-22804 when selecting OBJECT_OWNER from v$sqlplan so I just changed it select ‘unknown’. I could revisit this to see if it works on 10g or maybe I could select values into a variable and then do the insert instead of doing the insert into the repository as a select from the target.
Thanks for the good work!
I just upgraded from ash simulator V1 to V2 at a set of five 10.2.0.2 SE-One databases on Linux, using one central repository.
I no longer get any SQLs in the repository. I found that most of the SQL-ids inserted have a value 0. Do you know why this can happen?
Further, I have a slight enhancement. I experienced that a user that is added to that database after the installation of the simulator will not be added to the repository. To deal with this, I have added the get_users call to the get_all procedure in the package. It works fine for me, but is there a reason why this hasn’t been done, or why I shouldn’t do this?
Thanks for the input. Turns out there was sql_id and sql_child_number were reversed in the insert into the repository. Should be fixed now with the latest version.
.
Yes the enhancement to collect new users would be useful. At this point I have no plans to do enhancements though I will look at fixes. If anyone wants to enhance the package, the code is welcome, and I’ll check it in on the site.
Thanks
Kyle
http://perfvision.com
Linux version of ASHMON available !!
http://www.perfvision.com/ftp/ashmon_linux.zip
still needs a small change to soft link tile0.7.8 to /usr/share/tcl :
cd /usr/share/tcl8.4
ln -s /opt/ashmon/lib/tile0.7.8 .
lrwxrwxrwx 1 root root 24 2008-10-14 12:27 tile0.7.8 -> /usr/share/tcl/tile0.7.8
Thanks to Simon Coter from Oracle Italy who got it working during my 2 day class this past week in Milan.