ASH data viewer in Mumbai version 2.2.1.824

Version 2.2.1.824 of Mumbai is available for download. Here’s the list of changes:

• Added ASH data viewer (supports ASH and BASH data)
• Added one-click 10046 tracing for scripts in the console window
• Lots of small tweaks and fixes

After releasing the BASH package that makes active session history data available without a Diagnostic Pack license, an ASH data viewer in Mumbai was the logical next step.

The layout as shown below is similar to what you might know from the ASH data shown in Enterprise Manager/Cloud Control/DB Console or from ASH Viewer, but there are, in my opinion, a few features and options that let you dig the ASH data a bit deeper.

At the top there is the ASH graph over a time scale and you can select an interval with the mouse or use an automatic selection like “Last 5 minutes” from the selection box on the toolbar. The yellow arrows mark the beginning and end of the selected interval.

At the lower half of the window you see details for the selected time period.

New ASH data is fetched every 15 seconds or whenever you click the update button on the toolbar.

The optional red line shows the CPU count for the database server.

Until here it’s probably what you know from other ASH visualizations.

The ASH data is grouped by minutes or hours (whatever you selected on the toolbar). The grouping by hour produces a more regularly shaped graph when you look at long time intervals. You can also set the number of hours that you want to see without scrolling the graph (“Page size [hours]“).

When looking at longer time intervals, days are separated by grey lines:

A very useful feature are the grouping and filter options, so you don’t have to look at the data by the standard WAIT_CLASS_OR_CPU, but you can group by wait event, terminal, module or whatever column there is in V$ACTIVE_SESSION_HISTORY.

Further there is a simple “equal” filter, in case you want to look at the data from just one session, module, user, etc. or just at one specific wait event or wait class.

For the detail data of the selected interval at the lower half of the window you can group by up to three different columns.

You can right click on the detail data grids to quickly filter the graph at the top by the contents of the selected cell. Further for some columns (e.g. SESSION_ID, SQL_ID) there are some menu actions available:

When you click the ASH icon in Mumbai you are first asked which data Mumbai should select from:

You can select the recent ASH or BASH data or the historical ASH or BASH data by selecting the appropriate views.

Further note that you can select data from more than one instance. This might be useful, e.g. for analyzing I/O load from multiple instances running against the same SAN. If you select the multi-instance option, the ASH data will have an additional column DB_SOURCE, so you can group and filter by the source database later.

Next you need to specify for which interval you want to fetch the ASH/BASH data:

The “Fetch new data every 15 seconds” option is only available when you select the latest “To” time (which is always rounded up to the next full hour).

I suggest you start with 2 hour intervals for recent ASH data or up to 24 hour intervals for historic ASH data to see if Mumbai can handle the amount of data and go up from there if you need to.

Some additional features:

• You can click on a series name in the legend to mark it yellow. Click again to go back to the default color
• Double click within the legend (but not on a series name) to move it to the other side in case it’s in you way.
• Note that as default series with values always below 0,1 Average Active Sessions are not shown in the graph, so that irrelevant series do not clutter up the graph. You might need to set it to 0 sometimes to see what you are looking for.

I’ve tested the ASH data viewer in Mumbai for about a months now and it was extremely useful for me (together with BASH). I hope you like it, too!

BASH – It’s ASH for the rest of us…

Note: This is a historic blog post, that is no longer being updated. Please go to the BASH page for up-to-date information.

If you, like me, work with Standard Edition, Standard Edition One or don’t have a license for Oracle’s Diagnostic Pack, you probably miss Oracle’s ASH (Active Session History) badly. While Statspack, in my opinion, still can fill most of the gap the unavailable AWR leaves on SE/SE1, at least I miss ASH very much.

Like lots of DBAs and consultants, I am a huge fan of Tanel Poder’s snapper script (which has an ASH mode) and I used it almost on a daily basis. However, one of its main feature is that nothing has to be installed, which on the other hand means that there is no historic information available. You have to know when it makes sense to start snapper.

BASH on the other hands replicates what ASH does. It samples V$SESSION every second, makes the samples available through the view BASH$ACTIVE_SESSION_HISTORY and persists the samples from every 10 seconds to BASH$HIST_ACTIVE_SESS_HISTORY, so it is meant a drop-in replacement for ASH.

You can download the BASH installation script here: BASH page

BASH needs Oracle 10g or 11g, a positive job_queue_processes parameter, but no Diagnostic Pack License.

EDIT: I learned that Kyle Hailey wrote S-ASH, which is similiar to BASH, a few years ago already. You might want to look into it: http://dboptimizer.com/ash-masters-2/s-ash/

Here is the header from the bashcreate.sql script with further information:

------------------------------------------------------------------------------------
--
-- Name:          bashcreate.sql - BASH Installation script for Oracle 10.2 to 11.2
--
-- Purpose:       It's ASH for the rest of us (no EE or no diagnostic pack license).
--
-- Requirements:  * Oracle 10.2 to 11.2 (tested with 10.2 and 11.2) 
--                * SE1, SE or EE - Diagnostic Pack NOT needed
--                * Parameter job_queue_processes > 0 
--                   (since the bash data collector permanently runs as a scheduler 
--                    job, you might want to consider raising the job_queue_processes 
--                    parameter by one)
--
-- Installation:  1.) Create a new tablespace for the BASH schema (optional, but recommended).
--                2.) Run: sqlplus sys/<sys_password>@<TNS_ALIAS> as sysdba @bashcreate.sql
--                3.) When asked, enter the password for the BASH user to be created and the 
--                    names for the permanent and temporary tablespace for the BASH user.
--                4.) When asked, enter "N" if you don't want to start the data 
--                    collector job right away. 
--
-- Uninstall:     1.) Execute bash.bash.stop;
--                2.) drop user bash cascade;
--
--
-- Usage:         *** CONTROLLING THE DATA COLLECTION *** 
--
--                The package BASH.BASH has the following procedures that let you
--                control the data gathering:
--
--                procedure run;
--                    Creates and start the bash data collector scheduler job.
--
--                procedure stop;
--                    Stops the bash data collector scheduler job.
--
--                procedure purge (days_to_keep NUMBER);
--                    Purges the data in BASH$HIST_ACTIVE_SESS_HISTORY
--
--                procedure runner;  
--                    Blocking procedure that collects the bash data. Might be
--                    usefull e.g. when scheduler jobs are not available and the
--                    data collector can not be run from a job session.
--
--
--                *** SETTINGS ***
--
--                The table BASH.BASH$SETTINGS has the following columns that let
--                you control how the BASH data is gathered:
--
--                 sample_every_n_centiseconds NUMBER (Default: 100 = 1 second)
--                     Number of centiseconds V$SESSION is sampled
--
--                 max_entries_kept NUMBER (Default: 30000)
--                     How many entries are kept in BASH$ACTIVE_SESSION_HISTORY
--
--                 cleanup_every_n_samples NUMBER (Default: 100)
--                     How often the data in BASH$ACTIVE_SESSION_HISTORY is purged  
--
--                 persist_every_n_samples NUMBER (Default: 10 )
--                     How many of the samples are persisted to BASH$HIST_ACTIVE_SESS_HISTORY
--
--                 logging_enabled NUMBER (Default: 0)
--                     If logging to BASH$LOG is enabled 
--
--                 keep_log_entries_n_days NUMBER (Default: 1)
--                     How many days log entries in BASH$LOG are kept
--
--                 updated NUMBER 
--                     An internally used column that tracks changes in the settings table
--                     through a trigger
--
--                 version NUMBER 
--                     The version number of BASH. Might be used with future update scripts.
--                     Do not change.
--
--                If you change a setting in the BASH.BASH$SETTINGS table and commit,
--                the updated setting will be used by the data collector the next time
--                it persists data to DBA_HIST_ACTIVE_SESS_HISTORY (default: every 10 seconds) 
--
--                The default values for sample_every_n_centiseconds and 
--                persist_every_n_samples replicate the ASH behaviour. 
--                
--
--                *** QUERYING THE COLLECTED BASH DATA *** 
--
--                BASH$ACTIVE_SESSION_HISTORY
--                  Replaces V$ACTIVE_SESSION_HISTORY (1-second samples)
--
--                BASH$HIST_ACTIVE_SESS_HISTORY
--                  Replaces DBA_HIST_ACTIVE_SESS_HISTORY (10-second samples)
--
--                BASH$LOG
--                  Logging table (logging is off by default)
--
--
--                If want to use scripts or tools (e.g. "Mumbai" or "ASH Viewer") that 
--                select from V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY,
--                you might want to replace the following public synonyms with synonyms 
--                pointing to BASH$ACTIVE_SESSION_HISTORY and 
--                BASH$HIST_ACTIVE_SESS_HISTORY:
--                
--                  CREATE OR REPLACE PUBLIC SYNONYM "V$ACTIVE_SESSION_HISTORY" 
--                      FOR BASH$ACTIVE_SESSION_HISTORY;
--
--                  CREATE OR REPLACE PUBLIC SYNONYM "DBA_HIST_ACTIVE_SESS_HISTORY" 
--                      FOR BASH$HIST_ACTIVE_SESS_HISTORY;
--                
--                Note that these synonyms will not work for user SYS, so selecting from 
--                V$ACTIVE_SESSION_HISTORY as user sys will still return the Oracle ASH data,
--                not BASH data.
--                
--                Also note that you are still not allowed to use Oracle Enterprise Manager,
--                Oracle Database Console or the Oracle supplied ASH scripts in RDBMS/ADMIN
--                against BASH data without a valid Diagnostic Pack license.
--
--
--                *** CLEANUP AND PURGING *** 
--
--                The data collected for BASH$ACTIVE_SESSION_HISTORY is automatically purged,
--                based on the max_entries_kept setting.
--
--                The data collected for BASH$HIST_ACTIVE_SESS_HISTORY is not purged 
--                automatically. You need to execute BASH.BASH.PURGE(<days_to_keep>) to purge
--                the data. You might want to create a scheduler job to do this on a
--                regular basis.
--
-- Background:    *** Performance impact of the BASH data collector ***
--
--                Oracle's own ASH uses a circular buffer in the SGA, which is something
--                a user process like the BASH data collector can not. After trying a few 
--                setups (global temporary tables, communications though DBMS_PIPE, etc.), I
--                decided to implement BASH as simple as possible using standard heap tables.
--                (The buffer cache is probably the closest thing to a separate memory area 
--                that can be used from a user session.)
--
--                I tested BASH on ten productive databases with quite different loads, both
--                on the OLTP and OLAP side. Since the load from BASH is not recorded by BASH 
--                (when sampling the sampler has to be ignored) I used Tanel Poder's snapper 
--                and latchprof scripts to check for load and excessive latch gets by the bash
--                data collector. The load was usually 0,01 AAS (usually on CPU), on some 
--                database with a large number of active session it sometimes was 0,02 AAS. 
--                The latchprof script showed only very low numbers of latch gets from the 
--                bash data collector.
--                
--                While the ASH setup with a circular buffer in the SGA and its latch-free 
--                access is definetly the superior architecture, I can not see any serious
--                side-effects with the down-to-earth BASH architecture.
--                
--                If you worry about the additonal 1-2% AAS load, you probably need BASH 
--                badly, to fix a few performance problems...  
--                
--                
--                *** Columns in BASH$ACTIVE_SESSION_HISTORY ***
--                
--                For compatibilty reasons with 3rd party tools that select from 
--                V$ACTIVE_SESSION_HISTORY (but actually BASH$ACTIVE_SESSION_HISTORY if
--                you decide to replace the V$ACTIVE_SESSION_HISTORY public synonym), I made
--                all columns from V$ACTIVE_SESSION_HISTORY available in 
--                BASH$ACTIVE_SESSION_HISTORY, however some columns are not really filled
--                with data and always NULL: qc_session_id, qc_instance_id
--                and blocking_session_serial# from the 10.2 version of 
--                V$ACTIVE_SESSION_HISTORY and a whole series of columns from the 11.2
--                version of V$ACTIVE_SESSION_HISTORY (see comments in PL/SQL code).
--                
--                On the other hand, there are three columns in BASH$ACTIVE_SESSION_HISTORY
--                orginating from V$SESSION that are not available in V$ACTIVE_SESSION_HISTORY, 
--                because I think they are useful: OSUSER, TERMINAL, USERNAME
--                
--
-- Author:        Marcus Monnig
-- Copyright:     (c) 2012, 2013 Marcus Monnig - All rights reserved.
--
-- Disclaimer:    No guarantees. Use at your own risk. 
--
-- Changelog:     v1: 2012-12-28 First public release 
--
------------------------------------------------------------------------------------

Presenting at DOAG 2012: Analyse und Visualisierung von Statspack und AWR Daten

Presenting at DOAG 2011 was a great experience, so I am very happy to present again at DOAG 2012. The title of the session is “Analysis and Visualizing Statspack and AWR data” (it will be in german language though).

Here’s the abstract:


Der Referent gibt eine Überblick über Statspack und Automatic Workload Repository (AWR), erläutert die Konzepte um die meist großen Mengen von vorhandenen Statspack/AWR Daten zu analysieren und führt in Demos vor, wie diese Konzepte in "Mumbai", einem frei-verfügbaren Windowsprogramm u.a. zur Oracle Performanceanalyse, implementiert wurden.
Der Referent zeigt die Probleme der Durchschnittsbildung, die Grenzen der Statspack/AWR Daten und der von Oracle mitgelieferten existierenden Reports und demonstriert die Analyse und graphische Visualisierung der Statspack/AWR Daten, mit denen sich beispielsweise Zeitintervalle finden lassen, für die sich eine noch weitergehende Analyse lohnt. Weiterhin wird gezeigt wie sich zeitliche Korrelationen zwischen unterschiedlichen aus Statspack/AWR Daten ermittelten Statistikwerten finden lassen, die ein tieferes Verständnis dafür eröffnen, was in der Vergangenheit in Ihrer Datenbank passiert ist.


Hope to see you in Nürnberg in November!

SNAP_ANYTHING: Tiny little solution for snapping and recording anything that you can query

No rocket science here, but since I use it so frequently, I thought this might be actually usefull for others, too.

Quote from the Readme.txt file:


WHAT IS THE POINT?
Once installed, the two tables, the PL/SQL package and the scheduler job
make it very easy to record regular snapshots of the results of SQL queries.

Since this is something I frequently need to do, e.g. for performance data
not snapped by STATSPACK/AWR, tracking a list of invalid objects in developer
schemas over longer periods of time, recording AUDIT information from remote
test/dev databases that are cloned from production every night, etc., I put
this into it's own schema/setup script for easy installation on different databases.

USAGE

All you have to do to do a snapshot of query result data in fixed intervals
is adding a row to the table SNAP_ANYTHING.HIST_QUERIES, like in this example:

INSERT INTO SNAP_ANYTHING.HIST_QUERIES
(
NAME,
QUERY_TEXT,
TARGET_TABLE,
RETENTION_MINUTES,
REPEAT_INTERVAL,
ENABLED
)
VALUES
(
'Snapshots of V$SESSION',
'SELECT * FROM V$SESSION',
'HIST$SESSIONS',
24*60,
'SYSDATE+(5/24/60)',
'Y'
);
COMMIT;

This will execute the query 'SELECT * FROM V$SESSION' every five minutes and
write the result data rows into the table 'HIST$SESSIONS' (in the SNAP_ANYTHING
schema). If the table doesn't exist, it will be created automatically. The target
table will have all columns returned by the query, plus the columns SNAP_TIME
(date/time when this data was recorded) and ORDER_ID (row_num of row for this
snapshot).

The data is automatically cleaned up based on the number in the RETENTION_MINUTES
column. Putting 1440 there will keep the snapshot data from the last 24 hours
(purging happens after every snapshot taken).

Columns in HIST_QUERIES:

NAME: Just a descriptive name so you know what it does.
QUERY_TEXT: The SELECT query that should be executed.
TARGET_TABLE: The target table where the recorded data should go. Doesn't have to exist yet.
REPEAT_INTERVAL: Repeat interval for the execution of the query, e.g.'SYSDATE+(5/24/60)' (every 5 minutes) or 'trunc(sysdate)+7+9/24' (once a week at 9:00am)
RETENTION_MINUTES: Number of minutes that you want to keep snapshot data for
ENABLED: Y or N. N disables the snapshot collection.

The scheduler job SNAP_ANYTHING.SNAP_ANYTHING_SNAPPER_JOB checks the table HIST_QUERIES for queries that need to be run, based on their REPEAT_INTERVAL setting.

The table HIST_QUERIES_LOG shows the log entries for the snapshots executed by the scheduler jobs. The entries in HIST_QUERIES_LOG are limited to the 10 days of snapshot executions.

You can download the zip package with the setup script here: snap_anything_v1.0.zip

Side-by-side view, package navigator and Statspack/AWR reports on the history of V$FILESTAT in Mumbai version 2.2.1.821

Version 2.2.1.821 of Mumbai is available for download.

Here’s the list of changes:

• There is now a second page control on the left side of the main window where you can drag and drop pages to or you can right-click on a tab and select “Move to other side” to have a side-by-side view:

• The bind variables panel in the console window can now be collapsed:

• Package detail view: A view which combines the package and package body into one view with a procedure/function navigation tree and a list of errors in the package if there are any. You can double-click on an entry in the navigation tree or in the error list to jump to the code position. Green icons are functions/procedures that are also defined in the package definition and thus can be called from outside the package.

• Added views for V$FILESTAT and V$TEMPSTAT and views that show the aggregated data from these V$ views (more about that here)

• AWR/SP reports: Added reports for aggregated data from STATS$FILESTAT and STATS$TEMPSTAT (respectively DBA_HIST_TEMPSTATXS and DBA_HIST_FILESTATXS) (more about that here)

• Added constraints view for “Table details view”

• The SQL parser used for reformatting now supports not so common syntax (WITH, listagg, etc.)

• Added column VISIBILITY in index list of table details view (>=Oracle 11.1)

• Fixed missing spaces in SQL text retrieved for sql_id/old_hash_value

• Fixed names of bind variables being truncated after 4 chars in 10046 trace file viewer

• Removed the listagg function call for the “Unindexed FK constraints” query for Oracle version below 11.2

• Added USES_DEFAULT_PASSWORD column in users view (>11.2 only through a join with DBA_USERS_WITH_DEFPWD)

• Fetching executions plans with workload statistics didn’t work for statements starting with “WITH … AS”

• After executing an SQL query/script against multiple databases in the console window, the SID for the connection was not updated, so tracing operations failed

• Lots of tiny tweaks and fixes

About V$FILESTAT, its SP/AWR history and SREADTIM/MREADTIM system statistics

The databases I look after in my day job always showed a pretty low MREADTIM value, just a little above SREADTIM. Recently, the disks in our SAN were changed and we now have a huge caching module installed and now I consistently see MREADTIM values significant below SREADTIM. It seems that with MREADTIM ‹ SREADTIM Oracle reverts to calculating these values as it does when only NOWORKLOAD statististics are available. When doing this, Oracle ends up with an MREADTIM › SREADTIM.

I still don’t understand the rationale for this behaviour, since my idea of system and object statistics is that they should represent the reality and if MREADTIM is lower than SREADTIM, well, then that’s the way it is. I am fine with a higher rate of Full Table Scans and Fast Full Index Scans if this is the faster access path. Maybe the fear is that there would be so many multiblock reads that the cache that is responsible for the low MREADTIM value would be less effective than the current ratio of SREADTIM/MREADTIM indicates, but in my opinion that could be controlled by regathering system statistics on a regular basis.

To tackle the problem I first wanted to get a good idea about the SREADTIM and MREADTIM values in our databases. When gathering system statistics for short intervals you end up with a value that only represents a partial load (in this databases and all other databases running on the same SAN). When you use a large gathering interval you end up with an average value that might neither represent your day-time transactional load, nor your night-time batch load.

So, I remembered Christian Antognini‘s approach to repetitively gather system statistics through a job and graph the values over time as explained here in his book “Troubleshooting Oracle Performance”. Since I was mainly interested in SREADTIM and MREADTIM I wondered if the same could be done with the readily available Statspack/AWR snapshots of V$FILESTAT.

There are values for PHYsical reads in this view, values for SINGLEBLK reads and if we subtract PHYRDS-SINGLEBLKRDS we end up with… well, NONSINGLEBLKRDS.

My assumption is that NONSINGLEBLKRDS = MULTIBLOCKRDS, but to be on the safe side and not risk to be summoned by the Oak Table Elders, I named the calculated columns NONSINGLEBLK…

I confirmed that direct path reads for multiple blocks are not counted against SINGLEBLCKRDS. I don’t see much relevance for single block direct path reads and I couldn’t think of a test case that triggers single block direct path reads to confirm that these are in fact counted against SINGLEBLCKRDS, so for now I simply declare my assumption to be true or at least pragmatic.

So, in Mumbai (for the next release – it’s not yet in the current version 2.2.1.809 it now is in the current version) I have added views that show the data from V$FILESTAT and V$TEMPSTAT for each data/temp file and that have the additional columns  NONSINGLEBLKRDS, NONSINGLEBLKRDTIM, SINGLEBLKRDTIM_AVG_MS, NONSINGLEBLKRDTIM_AVG_MS. Further, there are two views in Mumbai that show the aggregated values of all data/temp files.

V$FILESTAT view in Mumbai with additional calculated colums (from an idle test DB)

V$FILESTAT and V$TEMPSTAT show data since instance startup, so obviously, we are back at the problem of averages.

The new SP/AWR reports for aggregated data from STATS$FILESTAT and STATS$TEMPSTAT (respectively DBA_HIST_FILESTATXS and DBA_HIST_TEMPSTATXS) make much more sense.

Here are the graphs you can get from these reports:

Average read times per read operation (y-axis) over wall clock time (x-axis)

Normalized sum of read times per second (y-axis) over time (x-axis)

Normalized number of read operations per second (y-axis) over time (y-axis)

The first graph can be used to get a better idea about reasonable values for SREADTIM/MREADTIM system statistics.

Better support for running SQL scripts, data export functions and a few other fixes and enhancement in Mumbai version 2.2.1.809

Version 2.2.1.809 of Mumbai is available for download.

Here’s the list of changes:

• SQL*Plus “exec” command now is supported with “Run script” in console window
• When running a script against multiple databases the “Fetch DBMS_OUPTPUT” option is now applied correctly over new connections
• Fixed the position of the encrypt button in the connection dialog
• Added missing join predicate on OWNER columns in “Unindexed foreign keys” query
• Added columns constraint_name, referenced_owner, referenced_table and referenced_columns in “Unindexed foreign keys” view
• You can now retrieve a custom number of lines from the alert.log instead of the fixed number of 2000 lines
• Added context menu actions for “Run scheduler job” and “Stop scheduler job”
• Added support for “SET SERVEROUTPUT ON/OFF” in scripts
• Fixed the ismodified filter in parameter views
• Script output is now color coded and can be filtered by output type through the context menu
• Fixed the label “Disk reads per hour” with “Disk reads per second” in the SP/AWR SQL report graphs (thanks Nabil!)
• Same sql_ids now have the same color over all graphs in the SP/AWR SQL report
• The context menu for all data grid views now has a “General functions” item with the following sub-menu actions:
  1. Export to Excel
  2. Export to HTML
  3. Export to text
  4. Add filter by focused cell
  5. Cell multi-select