Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

How to configure eDB360?

leave a comment »

eDB360 has two execution parameters. The first one specifies if the database is licensed to use the Tuning or Diagnostics packs (or none). The second parameter is optional, and if entered it specifies the name of a custom configuration file that allows to change the behavior of eDB360. With this optional configuration file you can make changes such as: reduce the scope of the output to maybe a single column, a section, or even a range of sections. You can also change the time window from the default (last 31 days) to a larger window (if your AWR retention period allows) or smaller window. You can also specify a particular range of dates, or include additional report formats such as text, csv or xml.

If you are an experienced user of eDB360, you may want then to familiarize yourself with the configuration file sql/edb360_00_config.sql (listed below) and the sample custom configuration file provided (sql/custom_config_01.sql). Then you may want to assembly your own configuration file as a subset of the DEF values from sql/edb360_00_config.sql, and provide the name of your custom configuration script as the second execution parameter for eDB360. Your configuration file should reside under edb360-master/sql and the filename is case sensitive.

-- edb360 configuration file. for those cases where you must change edb360 functionality

/*************************** ok to modify (if really needed) ****************************/

-- section to report. null means all (default)
-- report column, or section, or range of columns or range of sections i.e. 3, 3-4, 3a, 3a-4c, 3-4c, 3c-4
DEF edb360_sections = '';

-- edb360 trace
DEF sql_trace_level = '1';

-- history days (default 31)
DEF edb360_conf_days = '31';

-- range of dates below superceed history days when values are other than YYYY-MM-DD
DEF edb360_conf_date_from = 'YYYY-MM-DD';
DEF edb360_conf_date_to = 'YYYY-MM-DD';

-- working hours are defined between these two HH24MM values (i.e. 7:30AM and 7:30PM)
DEF edb360_conf_work_time_from = '0730';
DEF edb360_conf_work_time_to = '1930';

-- working days are defined between 1 (Sunday) and 7 (Saturday) (default Mon-Fri)
DEF edb360_conf_work_day_from = '2';
DEF edb360_conf_work_day_to = '6';

-- maximum time in hours to allow edb360 to execute (default 24 hrs)
DEF edb360_conf_max_hours = '24';

-- include GV$ACTIVE_SESSION_HISTORY (default N)
DEF edb360_conf_incl_ash_mem = 'N';

-- include GV$SQL_MONITOR (default N)
DEF edb360_conf_incl_sql_mon = 'N';

-- include GV$SYSSTAT (default Y)
DEF edb360_conf_incl_stat_mem = 'Y';

-- include GV$PX and GV$PQ (default Y)
DEF edb360_conf_incl_px_mem = 'Y';

-- include DBA_SEGMENTS on queries with no filter on segment_name (default Y)
-- note: some releases of Oracle produce suboptimal plans when no segment_name is passed
DEF edb360_conf_incl_segments = 'Y';

-- include DBMS_METADATA calls (default Y)
-- note: some releases of Oracle take very long to generate metadata
DEF edb360_conf_incl_metadata = 'Y';

/**************************** not recommended to modify *********************************/

-- excluding report types reduce usability while providing marginal performance gain
DEF edb360_conf_incl_html = 'Y';
DEF edb360_conf_incl_xml  = 'N';
DEF edb360_conf_incl_text = 'N';
DEF edb360_conf_incl_csv  = 'N';
DEF edb360_conf_incl_line = 'Y';
DEF edb360_conf_incl_pie  = 'Y';

-- excluding awr reports substantially reduces usability with minimal performance gain
DEF edb360_conf_incl_awr_rpt = 'Y';
DEF edb360_conf_incl_addm_rpt = 'Y';
DEF edb360_conf_incl_ash_rpt = 'Y';
DEF edb360_conf_incl_tkprof = 'Y';

-- top sql to execute further diagnostics (range 0-128)
DEF edb360_conf_top_sql = '48';
DEF edb360_conf_top_cur = '4';
DEF edb360_conf_top_sig = '4';
DEF edb360_conf_planx_top = '48';
DEF edb360_conf_sqlmon_top = '0';
DEF edb360_conf_sqlash_top = '0';
DEF edb360_conf_sqlhc_top = '0';
DEF edb360_conf_sqld360_top = '16';
DEF edb360_conf_sqld360_top_tc = '0';

/************************************ modifications *************************************/

-- If you need to modify any parameter create a new custom configuration file with a
-- subset of the DEF above, and place on same edb360-master/sql directory; then when
-- you execute edb360.sql, pass on second parameter the name of your configuration file

Written by Carlos Sierra

April 24, 2016 at 8:30 pm

Posted in edb360, Scripts, Tools

Leave a Reply

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

You are commenting using your 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: