Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Meet “edb360″: a free tool that provides a 360-degree view of an Oracle database

with 36 comments

Besides been what I consider a horrendous color, “edb360” also stands for Enkitec’s “database 360-degree” view. Simply put: edb360 is a new free tool that provides a 360-degree view of an Oracle database.

What is “edb360“?

This “edb360” tool is the product of a collaborative effort of some very smart guys, and me. Special thanks to Frits Hoogland, Karl Arao, Randy Johnson, Martin Bach, Kyle Hailey, Tanel Poder, Alex Fatkulin, Mauro Pagano, Abel Macias, Jon Adams and Jack Agustin. These guys helped me to envision edb360, some directly and some indirectly, but their help and shared knowledge motivated me to develop edb360 and make it available today.

The edb360 tool started as a quick and dirty “script” to gather basic information about a database without knowing anything about it before hand. The first rule for edb360 was: it has to install nothing in the database. The second rule became: it has to provide some insight about a database.

The output is presented for the most part into 3 formats: HTML, Text and Comma-separated Values (CSV). Why? HTML and Text can be easily used to consolidate important findings into a Word report. Sometimes HTML is more useful and sometimes Text is better. Then CSV is used to produce charts out of Performance Trends. Some people can visualize trends easier with a graph (me included).

What about other tools?

Of course there are wonderful tools that can help in this arena, like Oracle Enterprise Manager (OEM) or Oracle’s Automatic Workload Repository (AWR). So why not using those tools? Well, if I had access to OEM or I knew before hand which time intervals I want to analyze with AWR, then I would not have a strong need to use edb360. The reality that we consultants face when we are getting acquainted of a system, is that we are not given any access to the database of interest (usually production). And asking for a server account feels like asking for coke’s secret formula: then we simply cannot poke the database at our own will, and that is understandable. So, what is our second best?: please run this script that installs nothing and generates a zip file with some metadata from your system.  The script is plain text and its output is also plain text (html, text and csv files). So, any DBA or System Administration can validate that no customer confidential data is extracted or exposed. A win-win!

If the system we want to understand is an Exadata system, we can also request for an Exacheck output, if  not an Exadata system but a RAC cluster, there is Raccheck. These two tools, available though My Oracle Support (MOS) make a good companion for the edb360. In other words, edb360 is not a replacement for the other two but more of an add-on or companion.

Why is edb360 free?

Why not? Often I get asked: why do you give away the tools and scripts you develop? The answer is simple: tools, scripts, white papers, blog entries like this, in my mind they all represent the same: sharing knowledge with our Oracle community. I wish for a community where knowledge (and tools) flows for all to benefit. Let’s say my personal time I invest building tools and scripts kinds of make it up for my lame blog postings. ;-)

What is the catch?

No catch. Just be aware that edb360 makes use of some DBA_HIST views and ASH data, and those are part of the Oracle Diagnostics Pack. So when executing the tool it will ask  to indicate if your site has those licenses. Your answer determines the scope of the output. So if you specify you have a license for the Oracle Diagnostics Pack then your edb360 output includes pieces from AWR and ASH, else AWR and ASH are not accessed.

About versions, feedback and support

For the most part, I am committed to maintain this tool as my personal time permits. That means I can only work on it during odd hours and not every day. Nothing different than SQLTXPLAIN during the first few years of its existence, so I am not scared. Keep also in mind this edb360 is work in progress, and version v1405 is the first one I feel comfortable sharing with the community. In other words, it is far from perfect and I foresee it growing in multiple directions.

If you like this tool, and want to enhance its output, get SQLHC from MOS 1366133.1, and place the sqlhc.sql script into the same db360/sql directory. By doing so, you will also get 3 SQL health-checks. In other words, edb360 is SQLHC aware.

Conclusion

If you like free tools and have a use for this edb360 tool, you might as well download it and give it a shot. Nothing to lose (besides a few minutes of your spare time). A sample output is also available under same link above.

Life is Good!

Written by Carlos Sierra

February 19, 2014 at 7:34 pm

36 Responses

Subscribe to comments with RSS.

  1. Looking forward to your new pet project Carlos! Wish I could still walk over to your desk outside Karl’s office and chat. Sure could use some of that super human brain power you have in such abundance ;)

    Mike F. Smith

    February 19, 2014 at 9:24 pm

    • Thanks Mike

      Carlos Sierra

      February 20, 2014 at 6:05 am

    • Thanks for making this available Carlos. I’ve been trialliing it on several test platforms and found that it is only valid for 11g upwards as it uses a CLOB datatype in EXECUTE IMMEDIATE statements and this is not supported in Oracle 10g.

      10g produces ‘PLS-00382: Expression is of wrong type’ in the edb360_pre_one.sql script.

      It’s not a major bug, though, it just fails to reproduce the report SQL and row_count values. The useful data is still presented.

      Tony Sleight

      February 25, 2014 at 11:22 am

  2. Hi Carlos!
    Was going to download it but my antivirus says that there is a problem with a “Trojan-Downloader.JS.Agent.gzi”
    Regards
    /Johan

    Johan

    February 20, 2014 at 2:52 am

  3. Too Good Sir…

    Thanks for sharing these Toolll

    preethi

    February 20, 2014 at 3:24 am

  4. Looking forward to having a look.
    Is it possible to host the download from enkitec website rather than dropbox which is often blocked by corporate filter rules?

    Dom Brooks

    February 20, 2014 at 6:08 am

    • I will check. That is a good idea! Thx

      Carlos Sierra

      February 20, 2014 at 6:10 am

      • Yes, Dropbox is blocked at work and I want to download the files. Thanks!

        Enrique Aviles

        February 20, 2014 at 8:11 am

      • Dropbox seems to be blocked for some at work. Send me an email and I will send you the tool by email.

        Carlos Sierra

        February 20, 2014 at 8:14 am

  5. […] Meet "edb360": a free tool that provides a 360-degree view of an Oracle database. […]

  6. Thanks for making this available Carlos.

    steveharville

    February 20, 2014 at 11:36 am

  7. Thanks for sharing these Script

    Abdel-Gawad Othman

    March 6, 2014 at 7:05 am

  8. Hi
    can we add you script to our DBA Bundle script please check the below post for Oracle Database Administration Scripts | DBA Bundle

    http://dbamind.wordpress.com/2014/02/16/oracle-database-administration-scripts-dba-bundle/

    Abdel-Gawad Othman

    March 6, 2014 at 8:24 am

    • Abdel-Gawad,
      Sure you can. Just be aware that we will upload new versions often.
      Carlos

      Carlos Sierra

      March 6, 2014 at 8:47 am

      • Dear Mr.Carlos,

        Many thanks for you and we will mention that this script is owned by you and your blog as reference. also our bundle script is free for any DBA can use it . Thanks
        Abdel-Gawad Othman

        Abdel-Gawad Othman

        March 6, 2014 at 8:59 am

      • cool, thx

        Carlos Sierra

        March 6, 2014 at 3:26 pm

  9. Hi Carlos,

    Be sure to include “set wrap on” at the beginning of the script. If it happens to be off, the script fails, because of the content of &&common_prefix._query.sql, which is always:
    SELECT ROWNUM row_num, v0.* FROM (

    So “set wrap on” is a must.

    Nice script by the way. Many thanks.

    joaquingonzalez

    March 7, 2014 at 5:33 am

    • Hi Carlos,
      Thank you very much for sharing edb360. It is very useful to get a quick health check on a database.
      I have a question regarding the Segment Shrink Recommendations ( we do not have the Diagnostic / Tuning packs ).
      edb360 displays an error : ORA-06512: at “SYS.DBMS_sPACE”, line 1102
      I thought it came from my dba_advisor* views which were not documented but this is not the case.
      When I run the query against the database, I get 139 rows.
      Thanks for your help.
      Jean-michel A., Nemours, FRANCE

      alzingre

      March 25, 2014 at 9:24 am

      • Jean-michel,
        If you execute SQL below do you get that error?
        SELECT * FROM TABLE(dbms_space.asa_recommendations());
        Carlos

        Carlos Sierra

        March 25, 2014 at 10:03 am

  10. No Carlos. I have also 139 rows returned.
    I run the sql this way ( with the downloaded shell ) :
    @sql/edb360_main N Y Y N

    Jean-michel

    alzingre

    March 25, 2014 at 11:14 am

    • Wait a minute Carlos.
      I am going to run edb360 a second time because I have an idea.
      Thank you
      Jean-michel

      alzingre

      March 25, 2014 at 11:34 am

      • Unfortunalety it was not what I thought about.
        The request still returns 0 rows at the bottom of the html page for segment shrink recommendations.
        Thx

        Jean-michel

        alzingre

        March 25, 2014 at 11:46 am

      • This function dbms_space.asa_recommendations returns recommendations using the stored results of the auto segment advisor. This function returns results from the latest run on any given object.

        Carlos Sierra

        March 25, 2014 at 12:04 pm

  11. Hi Carlos,
    Just downloaded edb360 and testing on my test database. The script was running OK until it got to the following:
    “Percentage of System I/O “RMAN backup & recovery I/O” Waits, taking less than (or more) than N milliseconds”
    the script has stopped. I can see the corresponding html file of the same name with a size of 0kB. I left it for 30 mins but it still hasn’t finished.
    I started with @ebd360 T 31 Y N N Y.

    Sheridan Jones

    April 4, 2014 at 12:19 pm

    • Hitting “return” started it running.

      Sheridan Jones

      April 4, 2014 at 12:25 pm

    • Kit “Return” a couple of times and let me know if it completes.

      Carlos Sierra

      April 4, 2014 at 12:27 pm

  12. Hi Carlos!
    Great Blog! Thanks for sharing info.
    One question: Oracle Tuning pack for tuning sqls & using profiles ? Automatic sql tuning ? Is it worth investment in your opinion?
    Any other alternative tools if you have alot of sqls to tune ?
    SQLT is one great tool, but time consuming if have alot of sqls.
    Thanks,L.

    L

    May 4, 2014 at 12:23 pm

    • L,
      I do not use SQL Tuning Advisor, but some customers are please with it. To reduce number of SQL statements to tune, be sure you are on recent version of Oracle, you gather Stats using all defaults form DBMS_STATS, and you do not change default settings of CBO parameters. With those 3, the number of outstanding queries to tune should be small. I would use SQLT on them.

      Carlos Sierra

      May 5, 2014 at 7:35 am

    • Have to add. To use SQL Monitor Report you have to have the Oracle Tuning Pack license. SQL Monitor is very useful for SQL Tuning.

      Carlos Sierra

      May 5, 2014 at 9:05 am

  13. Hi Carlos ! Very good . Continue with this great job…
    Thanks

    Wellington Prado

    June 25, 2014 at 12:34 pm


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,511 other followers

%d bloggers like this: