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

April 12, 2013

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: [code] [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 [/code] 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. [code] [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. [/code] 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: [code] [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. [/code] 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. [code] 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. "------------------------------------------------------------------------------------" [/code] I don't like to see errors. Using control-c to cancel and troubleshooting: [code] 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 [/code] Attempting a recompile: [code] 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 [/code] Becoming the superuser again and looking up the object: [code] 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 [/code] 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. [code] 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 [/code] There's a missing object (or perhaps just the grant). [code] 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 [/code] 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: [code] [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'); [/code] And looking at repo_sys_procedure, I also see the reason why the grant didn't work: [code] grant execute on kill_sash_session to sash; [/code] 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.) [code] [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'); [/code] 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. [code] [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. [/code] Much better! Recompiling the sash_repo package: [code] SQL> conn sash_repo_owner Enter password: Connected. SQL> alter package sash_repo compile body; Package body altered. [/code] 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. [code] 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. "------------------------------------------------------------------------------------" [/code] Checking just status: [code] 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 [/code] It's having trouble with formatting timezones, but we do see the collection job is running, as expected. Checking for errors in collection: [code] 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 [/code] Now, to actually use SASH. SASH has a set of views that mimic the familiar names from the "real" AWR/ASH. [code] 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. [/code] The SASH1 through SASH31 are probably like SAR: daily data tables, in a format that can be easily truncated much like partitions. [code] 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. [/code] 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: [code] create or replace view dba_hist_active_sess_history as select * from v$active_session_history where rownum < 1; [/code] 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!

Insight and analysis of technology and business strategy

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

Installing for the First Time

Top Categories

Tell us how we can help!