15 Responses to “S-ASH”  

1. 1 andrew henry May 7th, 2008 at 11:49 am

   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.

2. 2 Checinski May 7th, 2008 at 2:29 pm

   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)

3. 3 khailey May 7th, 2008 at 8:05 pm

   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.

4. 4 andrew henry May 8th, 2008 at 2:14 pm

   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?

5. 5 khailey May 8th, 2008 at 5:16 pm

   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.

6. 6 Carel-Jan Jul 31st, 2008 at 9:49 pm

   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?

7. 7 khailey Jul 31st, 2008 at 11:50 pm

   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

8. 8 khailey Oct 15th, 2008 at 9:56 pm

   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.

9. 9 Bart Cozijnsen Jan 6th, 2009 at 9:13 pm

   Great Job,

   I get the feeling that there is a restriction missing
   in procedure get_sqlstats of V2 targ_1_pkg.sql.
   It is now inserting all rows from gv$sql.
   Is this correct?

   In repo_1_tables.sql table sash_sqlstat contains a field:
   elasped.
   Also the correct field elapsed is there.

   best regards Bart

10. 10 khailey Jan 21st, 2009 at 8:12 pm

    Stats are only collected for SQL that has been found running but yes it’s collected for all the sql that has bee found running. This should probably be limited to the most executed or at least those execute recently.
    I these enhancements are on the todo list but I won’t be writing any more code as I’m now working on a production version of similar code at Embarcadero, though I can post any improvemtns people want to submit.

    Interesting error on sash_sqlstat. Oracle doesn’t complain creating a table with two fields with same name.
    Changed and uploaded a version with just one field.

11. 11 loifmkir Apr 22nd, 2010 at 1:40 pm

    Hi Kyle
    just got an email from you on DB Optimizer 2.5 beta opportunity.
    Are you going to release a new version of S-ASH soon or you closed that project being occupied with your new Embarcadero assignment? Are you doing at all any support of this Blog? I see you have problems with SPAM. As a side note I suggest upgrading your WP to 2.8.6 or 2.9.2 version as you are on the old one

    – Kirill Loifman, http://www.dadbm.com

12. 12 khailey May 3rd, 2010 at 2:14 pm

    Yes, too occupied with DB Optimizer to do work on S-ASH. Maybe some day.
    Check out DB Optimizer though, the sampling and ASH like collection is solid but even better is the new visual SQL tuning which is something not available anywhere else.

13. 13 Kyle Hailey Sep 10th, 2010 at 6:12 pm

    New SASH 2.1 now available!
    See

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

    Thanks to Marcin Przepiorowski for these improvements.

    NOTE: people are encouraged to improve the code. I wrote the original 2 versions and now Marcin has stepped up with 2.1.
    I can no longer change the code and give it away for free as I’m full time at Embarcadero and such code would be owned by Embarcadero.
    Marcin has set up the code at source forge as well, see

    http://sourceforge.net/projects/orasash/

    as well as Marcin’s site http://oracleprof.blogspot.com/

1. 1 » Best Oracle Peformance Tools? Pingback on Jan 12th, 2011 at 6:58 pm
2. 2 Gokhan Atil's Oracle Blog: Simulating ASH (S-ASH) Pingback on Apr 26th, 2012 at 9:23 pm

Leave a Reply

You must log in to post a comment.