Trying Out S-ASH: Historical Performance Data for the Under-Licensed

Apr 12, 2013 / By Marc Fielding

Tags: , , , , , , , , ,

I was recently playing with an Oracle XE database. The main benefit of Oracle XE is that it can be used free of charge. And unlike the other database editions on the Oracle Technology Network, it is free of the restrictions in the OTN license agreement that restrict products to “developing, testing, prototyping and demonstrating your application” only; you can even use Oracle XE in production if you choose to. XE also has the side benefit of being slightly newer (11.2.0.2 instead of 11.2.0.1, effectively 6 months worth of bugfixes incorporated). And, for fans of Red Hat Linux, the installation is dead easy: download and install the RPM, run a setup script, and you have a database (and application server) ready to run.

To avoid cannibalizing product revenues, Oracle has imposed a number of technical limits that make XE unsuitable for most places you would use an Oracle database:

  • Database resource manager is hard-coded to restrict CPU usage to one CPU core’s worth.
  • RAM usage is limited to 1GB.
  • Data size is limited to 11GB.
  • You can’t circumvent these restrictions by running multiple databases or software installs.

On top of that, to keep the software download size to 300mb (as compared to 2GB+ for a full 11.2.0.3 install), a few large features have been removed, notably the built-in Java virtual machine.

And the same features unavailable in Oracle Standard Edition One aren’t available on XE either. One in particular that’s rather useful is the historical performance data available from Active Session History (ASH).

Fortunately, a workaround exists: simulated ASH by Kyle Hailey and now maintained by Marcin Przepiorowski. S-ASH (not to be confused with Oracle Sash) is a straight of SQL scripts that take periodic snapshots of session state and stores it in Oracle tables for later retrieval. Naturally it has a bit higher overhead than ASH, but for this type of test environment, it isn’t a big concern.

Installing for the First Time

The first step is to download the latest version. As I write this, the latest version is 2.3, released a bit over a year ago. Since it consists entirely of SQL scripts, the download is all of 46kb in size.

S-ASH has the concept of target and repository databases. The target databases are the databases to be monitored, and the repository database holds the actual historical data. These can be separate databases on separate systems, both reducing performance impact on the target system and allowing several databases’ historical performance data to be aggregated in a single location. But for the purpose of this test, I’m going to put both target and repository on the same XE database.

I’m also doing this install on a Linux virtual machine (the latest and greatest 64-bit Oracle Linux 6.4, newly supported for Oracle 11g release 2). A version of Oracle XE also exists for Windows 32-bit.

Unzipping the archive:

[oracle@ora11gr2a sash]$ unzip sash-v2.3.zip
Archive:  sash-v2.3.zip
  inflating: adddb.sql
  inflating: ask.sql
  inflating: checklog.sql
  inflating: config.sql
  inflating: installation steps.txt
  inflating: job_stat.sql
  inflating: repo_schema.sql
  inflating: repo_sys_procedure.sql
  inflating: repo_user.sql
  inflating: sash_pkg.sql
  inflating: sash_repo.sql
  inflating: sash_xplan.sql
 extracting: start.sql
 extracting: stop.sql
  inflating: target_user_view_10g.sql
  inflating: target_user_view_11g1.sql
  inflating: target_user_view_11g2.sql
  inflating: target_user_view_9i.sql
  inflating: test.sql
  inflating: top_10_sql_with_waits.sql
  inflating: waitgroups.sql

And note the file installation steps.txt, with install docs, which I will follow. Next up is to create the sash user, grant it selects to performance views, and create a new sashnow view that collects session status data from the Oracle fixed table x$ksuse, which is the basis for the better-known view x$ksuse. Querying the x$ table directly avoids a lot of the overhead of joins inherant in querying v$session. But as a fixed view, it can only be directly accessed by the SYS superuser. So hence the sashnow view, created as SYS, allowing the lower-privilege sash user to see the data.

[oracle@ora11gr2a sash]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 10 07:27:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @target_user_view_11g2
"SASH user will be created and used only by repository connection via db link"
"SASH privileges are limited to create session and select on system objects listed in script"
Enter SASH password ? test123
Enter SASH default tablespace [or enter to accept USERS tablespace] ?
"SASH default tablespace is: " users

User created.

Grant succeeded.

grant select on sys.sashnow to sash
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

...
Grant succeeded.

"SASHNOW view will be created in SYS schema. This view will be accesed by repository database via DB link using user sash"

View created.

Note the error: The install script attempted to grant privileges on the sashnow view before it got created. This looks like a bug in the script, and has a simple workaround. Run the grant after the view creation:

[oracle@ora11gr2a sash]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 10 07:29:11 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> grant select on sys.sashnow to sash;

Grant succeeded.

Next step: creating the repository. I’ll re-use the SQL*Plus window I already have open. Since I’m using the same database for the target and the repository, I’ll name the repository user sash_repo_owner.

SQL> @config
"------------------------------------------------------------------------------------"
Creating repository owner and job kill function using SYS user
"------------------------------------------------------------------------------------"
Enter user name (schema owner) [or enter to accept username sash] ? sash_repo_owner
Enter user password ? test123
Enter SASH user default tablespace [or enter to accept USERS tablespace] ? USERS
SASH default tablespace is: USERS
"------------------------------------------------------------------------------------"
Existing sash_repo_owner user will be deleted.
If you are not sure hit Control-C , else Return :
"------------------------------------------------------------------------------------"
drop user sash_repo_owner cascade
          *
ERROR at line 1:
ORA-01918: user 'SASH_REPO_OWNER' does not exist

This error is expected: the user is newly created.

New sash_repo_owner user will be created.

Warning: Procedure created with compilation errors.

Connected.
"------------------------------------------------------------------------------------"
Installing SASH objects into sash_repo_owner schema
"------------------------------------------------------------------------------------"
Create sequence
Create tables
Crating SASH_REPO package
No errors.

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY SASH_REPO:

248/5    PL/SQL: Statement ignored
248/5    PLS-00201: identifier 'SYS.KILL_SASH_SESSION' must be declared
Crating SASH_PKG package
No errors.
No errors.
"------------------------------------------------------------------------------------"
Instalation completed. Starting SASH configuration process
Press Control-C if you do not want to configure target database at that time.
"------------------------------------------------------------------------------------"

I don’t like to see errors. Using control-c to cancel and troubleshooting:

SQL> select * from user_objects where status = 'INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SASH_REPO
                                    20303                PACKAGE BODY
10-APR-13 10-APR-13 2013-04-10:07:40:35 INVALID N N N          2

Attempting a recompile:

SQL> alter package sash_repo compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY SASH_REPO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
248/5    PL/SQL: Statement ignored
248/5    PLS-00201: identifier 'SYS.KILL_SASH_SESSION' must be declared

Becoming the superuser again and looking up the object:

SQL> conn / as sysdba
Connected.
SQL> select owner, object_name, object_type, status from dba_objects where object_name like 'KILL_SASH%';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
SYS
KILL_SASH_SESSION
PROCEDURE           INVALID

So the procedure exists, but in the SYS schema: probably a missing grant. But there’s a bigger problem: KILL_SASH_SESSION itself is invalid.

SQL> alter procedure kill_sash_session compile;

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE KILL_SASH_SESSION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3      PL/SQL: SQL Statement ignored
9/20     PL/SQL: ORA-00942: table or view does not exist

There’s a missing object (or perhaps just the grant).

SQL> select text from user_source where name = 'KILL_SASH_SESSION' and line = 9;

TEXT
--------------------------------------------------------------------------------
                insert into sash.sash_log (action, message,result) values ('kill_sash_session'
,'killing job ' || vsql, 'I');

SQL> select owner, object_name, object_type, status from dba_objects where object_name like 'SASH_LOG';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
PUBLIC
SASH_LOG
SYNONYM             VALID

SASH_REPO_OWNER
SASH_LOG
TABLE               VALID

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

So the SASH_LOG table exists, but in the repository SASH_REPO_OWNER user we created, not SASH as requested. Looks like another bug: hardcoded SASH user. Digging up the SQL script that has the error:

[oracle@ora11gr2a sash]$ grep sash.sash_log *
repo_sys_procedure.sql:         insert into sash.sash_log (action, message,result) values ('kill_sash_session','killing job ' || vsql, 'I');

And looking at repo_sys_procedure, I also see the reason why the grant didn’t work:

grant execute on kill_sash_session to sash;

Since we call our repository user sash_repo_owher, this should be changed too.

In this case, I’m simply going to user perl to do an in-place file replace to change the name, backing up the original with a .orig extension. (In case you aren’t familiar with it, the perl in-place replace is an incredibly useful one-liner. Here’s a good reference.)

[oracle@ora11gr2a sash]$ perl -pi.orig -e 's/sash\.sash_log/sash_repo_owner.sash_log/;s/to sash;/to sash_repo_owner;/' repo_sys_procedure.sql
[oracle@ora11gr2a sash]$ grep sash.sash_log *
repo_sys_procedure.sql.orig:            insert into sash.sash_log (action, message,result) values ('kill_sash_session','killing job ' || vsql, 'I');

Repeating the same grep command as before, we now see that only the .orig file has sash.sash_log. Doing a sanity check on repo_sys_procedure.sql; it only has two commands (a CREATE OR REPLACE PROCEDURE and a grant) so it can safely be re-run.

[oracle@ora11gr2a sash]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 10 08:01:55 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @repo_sys_procedure

Procedure created.

Grant succeeded.

Much better! Recompiling the sash_repo package:

SQL> conn sash_repo_owner
Enter password:
Connected.
SQL> alter package sash_repo compile body;

Package body altered.

Moving ahead, I’m going to use the adddb.sql script to add a database, since we cancelled the initial database addition to resolve the errors.

SQL> @adddb
Enter database name XE
Enter number of instances [default 1]1
Enter host name for instance number 1 localhost
Enter instance name for instance number 1 [ default XE ] XE
Enter listener port number [default 1521] 1521
Enter SASH password on target database test123
"------------------------------------------------------------------------------------"
Database added.
"------------------------------------------------------------------------------------"

Checking just status:

SQL> @job_stat

SASH_PKG_COLLECT_XE1           10-APR-13 08.06.32.983557 AM +10:00 10-APR-13 08.06.32.000000 AM +10:00 RUNNING                     0
SASH_PKG_GET_ALL_XE1           10-APR-13 08.06.33.119275 AM AUSTRA 10-APR-13 08.21.33.000000 AM AUSTRA SCHEDULED                   0
                               LIA/BRISBANE                        LIA/BRISBANE

SASH_REPO_PURGE                                                    11-APR-13 12.00.00.000000 AM +10:00 SCHEDULED                   0
SASH_REPO_WATCHDOG             10-APR-13 08.06.33.453629 AM +10:00 10-APR-13 08.11.33.000000 AM +10:00 SCHEDULED                   0

It’s having trouble with formatting timezones, but we do see the collection job is running, as expected. Checking for errors in collection:

SQL> @checklog.sql

W 2013-04-10 08:06:32       add_db                                           no db link - moving forward XE1
I 2013-04-10 08:06:32       configure_db                                     get_event_names
I 2013-04-10 08:06:32       configure_db                                     get_users
I 2013-04-10 08:06:32       configure_db                                     get_params
I 2013-04-10 08:06:32       configure_db                                     get_data_files
I 2013-04-10 08:06:32       configure_db                                     get_metrics
I 2013-04-10 08:06:32       add_instance_job                                 adding scheduler job sash_pkg_collect_XE1
I 2013-04-10 08:06:32       add_instance_job                                 adding scheduler job sash_pkg_get_all_XE1
I 2013-04-10 08:06:33       create_repository_jobs                           adding new repository job

Now, to actually use SASH. SASH has a set of views that mimic the familiar names from the “real” AWR/ASH.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
ALL_OBJECTS
DBA_DATA_FILES
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_SQLSTAT
DBA_HIST_SYSMETRIC_HISTORY
DBA_USERS
SASH
SASH_ALL
SASH_PLAN_TABLE
V$ACTIVE_SESSION_HISTORY
V$DATABASE
V$INSTANCE
V$IOSTAT_FUNCTION
V$PARAMETER
V$SQL
V$SQLSTATS
V$SQLTEXT_WITH_NEWLINES
V$SQL_PLAN
V$SYSMETRIC_HISTORY

19 rows selected.

The SASH1 through SASH31 are probably like SAR: daily data tables, in a format that can be easily truncated much like partitions.

SQL> set serveroutput on
SQL> declare
  2  cursor c1 is select view_name from user_views;
  3  c1rec c1%rowtype;
  4  v_rowcount number;
  5  begin
  6    for c1rec in c1 loop
  7       execute immediate 'select count(*) from ' || c1rec.view_name into v_rowcount;
  8       dbms_output.put_line('View ' || c1rec.view_name || ' has ' || v_rowcount || ' rows');
  9    end loop;
 10  end;
 11  /
View ALL_OBJECTS has 6 rows
View DBA_DATA_FILES has 4 rows
View DBA_HIST_ACTIVE_SESS_HISTORY has 0 rows
View DBA_HIST_SQLSTAT has 5 rows
View DBA_HIST_SYSMETRIC_HISTORY has 1710 rows
View DBA_USERS has 14 rows
View SASH has 1053 rows
View SASH_ALL has 1057 rows
View SASH_PLAN_TABLE has 32 rows
View V$ACTIVE_SESSION_HISTORY has 1057 rows
View V$DATABASE has 1 rows
View V$INSTANCE has 1 rows
View V$IOSTAT_FUNCTION has 14 rows
View V$PARAMETER has 344 rows
View V$SQL has 8 rows
View V$SQLSTATS has 3 rows
View V$SQLTEXT_WITH_NEWLINES has 8 rows
View V$SQL_PLAN has 32 rows
View V$SYSMETRIC_HISTORY has 1710 rows

PL/SQL procedure successfully completed.

So we have data being collected. The only table without data is dba_hist_active_sess_history. Looking at its creation DDL in repo_schema.sql:

create or replace view dba_hist_active_sess_history
     as
     select * from v$active_session_history
     where rownum < 1;

Where rownum < 1: is guaranteed never to return any rows. So I guess the message is: use v$active_session_history instead, as it doesn’t suffer from the same limited lifespan as the “real” ash. I do still have trouble understanding why the view can’t be defined to select all of v$active_session_history to maintain compatibility for scripts, though.

The idea is that you can now tap the large library of ASH mining scripts out there on the Internet. However, some scripts may expect dba_hist_ tables like dba_hist_sqltext. S-ASH supplies the same information in the v$ views, leaving two choices: either modify the table name in the script, or create DBA_HIST views that mirror the v$ views already in place. And naturally, S-ASH doesn’t capture the range of data that AWR does, so if you’re looking for, say, file I/O information, you’d need to add logic in S-ASH to collect it.

And that’s it. Readers, I’m always happy to hear about your S-ASH experiences. Feel free to comment below!

5 Responses to “Trying Out S-ASH: Historical Performance Data for the Under-Licensed”

  • Alberto says:

    Hi Marc,
    I’ve implemented S-ASH to monitor some databases version 10G and 11G a long time ago. I Forked the Marcin project on Github and give him a little help to testing (at least in my enviroment) for this great project. All work fine, for me it’s very very usefull tool because permit to store in local XE Oracle Database, all datas for a good analisys for every DB. In my case I added further feature for me, very important. I would give long life to this project, and a wider use.
    Ciao
    Alberto

  • [...] Marc Fielding was recently playing with an Oracle XE database, and when Marc plays, it stayed played. [...]

  • Uwe Hesse says:

    Great exemplification, Marc, thank you for that! I always wanted to try S-ASH myself but never found the time to actually do it :-)

  • Yasser says:

    Can we use S-ASH for capturing ASH information of Active Dataguard using dblink from Primary to Standby ? Could you please come clues to capture V$ACTIVE_SESSION_HISTORY

  • Chuan Wang says:

    Just ran the install on one of the client databases today and noticed that GLOBAL_NAMES needs to be FALSE in order to use S-ASH (unless you want to “hack” the script to not use database link in its own database).
    S-ASH creates names of db links in [db_name][instance_number] format which will fail to work if GLOBAL_NAMES is TRUE. You will get ora-02085 error because the db link and database name are different.
    Just an FYI in case somebody runs into the same problem as I did today.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>