Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

eAdam 3.0

eAdam 3.0 is now available. A little bit of history is needed: eAdam was born on 2014 as version 1.0. Old version 1.0 extracted less than 30 views (DBA_HIST, GV$ and V$) as text files in order to facilitate the offline analysis of performance related metadata of large databases. eAdam 3.0 extracts raw data from over 200 views.

Why the need of eAdam in the first place? Well, in 2014 I was challenged with a requirement to explore some large databases, and report on opportunities to improve their performance and scalability. Access to the actual systems was off-limits, and the state of the AWR was suboptimal to put it nicely. So, any query against any AWR table would take very long (hours in some cases), and resources on such production environments were scarce. Back then, I suggested to extract AWR (and some GV$/V$) views and do all the analysis using a local test environment for which I would get some restricted access. And this is how eAdam was born: out of the necessity to perform an Oracle database health-check, consuming computational resources for such analysis on a lower environment, while minimizing the amount of resources consumed on the source production database. The solution was: extract from the source database the metadata needed to perform its analysis on a different target database (local or remote).

eAdam 1.0 was a great success, but its scope was maybe too small. With less than 30 objects, there were many follow-up questions that would require going back to the source database and execute new queries. Later on, eDB360 was created as the next logical step after eAdam. This 2nd tool eDB360 expanded the initial functionality of eAdam by executing such new tool on the same database being analyzed. This way, every new query to answer more elaborated questions was added to eDB360, and the footprint of metadata queried grew from less than 30 to more than 200 views (DBA_HIST, DBA, GV$ and V$). After some maturing time for eDB360, eAdam was used less often and its code got frozen with its less than 30 views. But even 3 years later on 2017, eAdam is still used by some  users around the globe to perform its initial task: extract a performance related repository and restore it on another database for further analysis. Bear in mind that eAdam was created well before AWR Warehouse was made available, and eAdam still has some advantages such as: the repository on the target system can be of same, lower or higher version than the source database; and eAdam is free software, so it can be easily customized for special needs.

What is then new on eAdam 3.0? With this version, there are 3 different methods (or versions), and all 3 are available on GitHub under the same master directory.

eAdam1: This is the original version. Less than 30 views. Still available for those using eAdam 1.0 for years and happy with what it provides. No enhancements are expected.

eAdam2: Completely new code. Similar concept than 1.0, but expands from less than 30 to over 200 views. Uses text files for the repository, so it installs nothing on the source database. Drawback is that some CLOB columns may get incomplete data due to some limitations on the technology stack. XMLTYPE columns are copied with null data, affecting two sql directives 12c views.

eAdam3: Completely new code. It uses compressed external tables. More efficient than 2.0 and preserves all metadata including CLOB and XMLTYPE columns, which are tricky to transport as text. The only disadvantage compared to 2.0 is that it requires to create some objects on the source database. It does not consume tablespace on the source database although, and this method is highly preferred over eAdam1 and eAdam2.

With the eAdam repositories from versions 2.0 and 3.0 there is a big bonus, and it is the compatibility with eDB360. What does that mean? Well, if you restore the eAdam 2.0 or 3.0 repositories on a target system, then you can produce the comprehensive eDB360 output on such target system, and get a 360-degree view of the source database. Of course if you can run eDB360 entirely on the source database that is preferred, but if doing so on the source database requires over 24 hours of execution time, then you may want to consider to leverage the power of eAdam and combine the power of these two: eAdam 3.0 plus eDB360.

 

Advertisements

Written by Carlos Sierra

April 16, 2017 at 7:03 pm

%d bloggers like this: