Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

eAdam

with 2 comments

Enkitec’s Oracle AWR Data Mining Tool

eAdamEAdam is a free tool that extracts from an Oracle database a subset of data and metadata with the objective to perform some data mining using a separate staging Oracle database. The data extracted is relevant to Performance Evaluations (PE) and/or to Sizing and Provisioning (SP) projects. Most of the data eAdam extracts is licensed by Oracle under the Diagnostics Pack, and some under the Tuning Pack. Therefore, in order to use this eAdam tool, the source database must be licensed to use both Oracle Packs (Tuning and Diagnostics).

To a point, eAdam is similar to eDB30; both access the Data Dictionary in order to produce some reports. The key difference is that eDB360 generates all the reports doing some intensive processing at the source database, while eAdam simply extracts a set of flat files into a TAR file, using a very light-weight script, delaying all the intensive processing for a later time and on a separate staging system. This feature can be very attractive for busy systems where the amount of processing of any external monitoring tool needs to be minimized.

On the source system, eAdam only needs to execute a short script to extract the data and metadata of interest, producing a dense TAR file. On a staging system, eAdam does the heavy lifting, requiring the creation of a repository, the load of this repository and finally the computation of meaningful reports. The processing of the TAR file into the staging system is usually performed by the requestor, using a lower-level database, or a remote one.

EAdam has two primary uses, listed here in order of frequency of use: 1) Performance Evaluation (PE) of an Oracle database, and 2) Sizing and Provisioning (SP) project for an Oracle database. Of course the list of uses is not comprehensive; as you may appreciate from the objects extracted, at the very least Active Session History (ASH) can be used to view performance data in more than one dimension. The list of objects eAdam extracts as flat files from the source database includes the following:

dba_hist_active_sess_history
dba_hist_database_instance
dba_hist_event_histogram
dba_hist_osstat
dba_hist_parameter
dba_hist_pgastat
dba_hist_sga
dba_hist_sgastat
dba_hist_snapshot
dba_hist_sql_plan
dba_hist_sqlstat
dba_hist_sqltext
dba_hist_sys_time_model
dba_hist_sysstat
gv$active_session_history
gv$log
gv$sql_monitor
gv$sql_plan_monitor
gv$sql_plan_statistics_all
gv$sql
gv$system_parameter2
v$controlfile
v$datafile
v$tempfile

EADAM works on 10gR2, 11gR2, and on higher releases of Oracle; and it can be used on Linux or UNIX Platforms. It has not been tested on Windows. An eAdam sample output is available at this Dropbox location; after downloading the sample output, look for the 0001_eadam36_N_dbname_index.html file and start browsing.

Instructions – Source Database

Download the tool, uncompress the master ZIP file, and look for file eadam-master/source_system/eadam_extract.sql. Review and execute this single and short script connecting to the source database as SYS. Locate the TAR file produced, and send it to the requestor.

Be aware that the TAR file produced by the extraction process can be large, so be sure you execute this extract script from a directory with at least 10 GB of free space. Common sizes of this TAR file range between 100 MB and 1 GB. Execution time for this extraction process may exceed 1 hour, depending on the size of the Data Dictionary.

Instructions – Staging Database

Be sure you have both the eAdam tool (eadam-master.zip) and the TAR file produced on a source system. Your staging database can be of equal, higher or lower release level than the source, but equal or higher is recommended. The Platform can be the same or different.

To install, load and report on the staging database, proceed with the following steps:

  1. Create on the staging system a file directory available to Oracle for read and write. Most probably you want to create this directory connecting to OS as Oracle and create a new directory like /home/oracle/eadam-master. Put in there the content of the eadam-master.zip file.
  2. Create the eAdam repository on the staging database. This step is needed only one time. Follow instructions from the readme.txt.  Basically you need to execute eadam-master/stage_system/eadam_install.sql connected as SYS. This script asks for 4 parameters: Tablespace names for permanent and temporary schema objects, and the username and password of the new eAdam account. For the username I recommend eadam, but you can use any valid name.
  3. Load the data contained in the TAR file into the database. To do this you need first to copy the TAR file into the eadam-master/stage_system sub-directory and execute next the stage_system/eadam_load.sql script while on the stage_system sub-directory and connecting as SYS. This script asks for 4 parameters. Pass first the directory path of your stage_system sub-directory, for example /home/oracle/eadam-master/stage_system (this sub-directory must contain the TAR file). Pass next the username and password of your eadam account as you created them. Pass last the name of the TAR file to be loaded into the database.
  4. The load process performs some data transformations and it produces at the end an output similar to eDB360 but smaller in content. After you review the eAdam output, you may decide to generate new output for shorter time series, in such case use the eadam-master/stage_system/eadam_report.sql connecting as the eadam user. This reporting process asks for 3 parameters. Pass the EADAM_SEQ_ID which identifies your particular load (a list of values is displayed), then pass the range of dates using format YYYY-MM-DD/HH24:MI, for example 2014-07-27/17:33.

Download

EADAM @ GitHub is available as free software. You can see its readme.txt, license.txt or any other piece of the tool before downloading it. Use this link eadam-master.zip to actually download eAdam as a compressed file.

Feedback

Please post your feedback about this eAdam tool at this blog, or send and email directly to the tool author: Carlos Sierra.

Written by Carlos Sierra

July 27, 2014 at 6:25 pm

2 Responses

Subscribe to comments with RSS.

  1. […] Metrics are easier to digest if visualized trough some Line Charts. OEM, eDB360, eAdam and other tools use them. If you already have a SQL Statement that provides the Performance Metrics […]

  2. […] eAdam tool link: http://carlos-sierra.net/2014/07/27/eadam/ […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,689 other followers

%d bloggers like this: