Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

edb360 taking a long time

with 2 comments

In most cases edb360 takes less than 1hr to execute. But I often hear of cases where it takes a lot longer than that. In a corner case it was taking several days and it had to be killed.

So the question is WHY edb360 takes that long?

Well, edb360 executes thousands of SQL statements sequentially (intentionally). Many of these queries read data from AWR and in particular from ASH. So, lets say your ASH historical table has 2B rows, and on top of that you have not gathered statistics on AWR tables in years, thus CBO under-estimates cardinality and tends to use index access and nested loops. In such extreme cases you may end up with suboptimal execution plans that expect to return a few rows, but actually read a couple of billion rows using index access operations and nested loops. A query like this may take hours to complete!

As of version v1515, edb360 has a shortcut algorithm that ends an execution after 8 hours. So you may get an incomplete output, but it ends normally and the partial output can actually be used. This is not a solution but a workaround for those long executions.

How to troubleshoot edb360 taking long?

Steps:

1. Review files 00002_edb360_dbname_log.txt, 00003_edb360_dbname_log2.txt, 00004_edb360_dbname_log3.txt and 00005_edb360_dbname_tkprof_sort.txt. First log shows the state of the statistics for AWR Tables. If stats are old then gather them fresh with script edb360/sql/gather_stats_wr_sys.sql

2. If number of rows on WRH$_ACTIVE_SESSION_HISTORY as per 00002_edb360_dbname_log.txt is several millions, then you may not be purging data periodically. There are some known bugs and some blog posts on this regard. Review MOS 387914.1 and proceed accordingly. Execute query below to validate ASH age:

SELECT TRUNC(sample_time, 'MM'), COUNT(*)
  FROM dba_hist_active_sess_history
 GROUP BY TRUNC(sample_time, 'MM')
 ORDER BY TRUNC(sample_time, 'MM')
 /

3. If edb360 version (first line on its readme) is older than 1 month, download and use latest version: https://github.com/carlos-sierra/edb360/archive/master.zip (link is also provided on the right-hand side of this blog under downloads).

4. Consider suppressing text and or csv reports. Each for an estimated gain of about 20%. Keep in mind that when suppressing reports, you start loosing some functionality. To suppress lets say text and csv reports, place the following two commands at the end of script edb360/sql/edb360_00_config.sql

DEF edb360_conf_incl_text = ‘N’;
DEF edb360_conf_incl_csv = ‘N’;

5. If after going through steps 1-4 above, edb360 still takes longer than a few hours, feel free to email author carlos.sierra.usa@gmail.com and provide 4 files from step 1.

Advertisements

Written by Carlos Sierra

May 6, 2015 at 8:19 pm

2 Responses

Subscribe to comments with RSS.

  1. Hi Carlos, first of all congrats on a great tool. I am becoming more and more, a fan of it.I would apreciate if it could be possible to select only the metrics to see, for example:
    – Only view Database Information section or IO related events section.
    This would improve the time of execution immensly.

    Also, since this is taking almost the 8h limit of this new version, isn’t creating a “false posive” on ASH/AWR data on the DB instance? For instance, the SQL Elapsed time should be a query from this very tool, which in turn can somehow mask other real issues.

    Another great feature could be the possibility to create a line graph for the historical of all the edb360 previous runs, and thus getting a overview how a system has been behaving throuout both time and changes in parameters/Hardware/etc etc.

    Regards
    Ricardo

    Ricardo Pereira

    May 7, 2015 at 11:02 am

    • Ricardo,
      1. With edb360 you can produce the output for a particular column of the report or for a particular section. See instructions on readme.txt.
      2. edb360 window of execution excludes itself. Lets say I start execution of edb360 at 20:00hrs. Then edb360 window goes from 31 days ago to today at 20:00. That means you won’t see edb360 SQL from this one execution.
      3. I could measure performance of edb360, but as of today I think the TKPROF it produces for itself is plenty. For the most part when it takes long it relates to bad CBO statistics or too much unpurged data. If you find otherwise please report it to me

      Carlos Sierra

      May 7, 2015 at 4:31 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

%d bloggers like this: