I’m happy to introduce a new free tool that allows to get initial insights about a database using eDB360/SQLd360 like charts without Diagnostic Pack!!!
(TUN)ing with (A)ctive (s)essions, aka TUNAs360, requires no installation and no parameter when executed; it observes the workload for few minutes and then collects a set of reports on such load.
The idea behind the tool is to help getting started in those situations where there is a concern with database performance but the reason is unknown, the classic “the database is slow but I don’t know why”.
In case further investigation is necessary then eDB360 or SQLd360 can be leveraged.
Once executed TUNAs360 provides several reports for the whole database (cluster and node specific) plus additional reports to help drill into the details of the top 5 sessions and top 5 SQLs during the time the load was observed.
The tool belongs to the “360 family” so it has the same look and many of the charts of its bigger brothers, the main page looks like this
To execute the tool:
- Download the tool from the link on the right side of the screen.
- Unzip file tunas360-master.zip
- Navigate into tunas360-master folder
- Connect as a DBA user and run script tunas360.sql
The tool will run for a few minutes and it will generate a zip file named tunas360_<dbname>_<host>_YYYYMMDD_HH24MI.zip.
As every other tool on its first version I don’t expect it to be perfect so please please please let me know if you run into problems so that I can fix them 😀
Feedbacks and suggestions are welcome as usual too!!!
Mauro Pagano's Blog 
March 22, 2016 at 4:26 am
Thank you for this cool new tuning-tool!!
LikeLiked by 1 person
March 23, 2016 at 10:55 am
Nice tools!
LikeLiked by 1 person
March 23, 2016 at 1:02 pm
Thanks for making this available. Can’t wait to try it out.
LikeLike
July 21, 2016 at 6:53 am
Hi Mauro ,
Nice tools.
I have taken index usage script from edb360, but its not working as expected . Any help?
———————————————————–
set linesize 95 trimspool on pagesize 80
–Script for Index Usage —
DEF exclusion_list = “‘ANONYMOUS’,’APEX_030200′,’APEX_040000′,’APEX_SSO’,’APPQOSSYS’,’CTXSYS’,’DBSNMP’,’DIP’,’EXFSYS’,’FLOWS_FILES’,’MDSYS’,’OLAPSYS’,’ORACLE_OCM’,’ORDDATA’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’OWBSYS'”
DEF exclusion_list2 = “‘SI_INFORMTN_SCHEMA’,’SQLTXADMIN’,’SQLTXPLAIN’,’SYS’,’SYSMAN’,’SYSTEM’,’TRCANLZR’,’WMSYS’,’XDB’,’XS$NULL’,’PERFSTAT’,’STDBYPERF'”
DEF sq_fact_hints = ‘MATERIALIZE NO_MERGE’;
DEF ds_hint = ‘DYNAMIC_SAMPLING(4)’;
DEF top_level_hints = ‘NO_MERGE’;
— history days (default 31)
DEF edb360_conf_days = ’31’;
DEF diagnostics_pack = ‘D’;
DEF license_pack = ‘T’;
DEF history_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’;
— get dbid
COL edb360_dbid NEW_V edb360_dbid;
SELECT TRIM(TO_CHAR(dbid)) edb360_dbid FROM v$database;
— snaps
SELECT startup_time, dbid, instance_number, COUNT(*) snaps,
MIN(begin_interval_time) min_time, MAX(end_interval_time) max_time,
MIN(snap_id) min_snap_id, MAX(snap_id) max_snap_id
FROM dba_hist_snapshot
GROUP BY
startup_time, dbid, instance_number
ORDER BY
startup_time, dbid, instance_number
/
COL history_days NEW_V history_days;
— range: takes at least 31 days and at most as many as actual history, with a default of 31. parameter restricts within that range.
SELECT TO_CHAR(LEAST(CEIL(SYSDATE – CAST(MIN(begin_interval_time) AS DATE)), GREATEST(31, TO_NUMBER(NVL(TRIM(‘&&edb360_conf_days.’), ’31’))))) history_days FROM dba_hist_snapshot WHERE ‘&&diagnostics_pack.’ = ‘D’ AND dbid = (SELECT dbid FROM v$database);
COL edb360_date_from NEW_V edb360_date_from;
COL edb360_date_to NEW_V edb360_date_to;
SELECT CASE ‘&&edb360_conf_date_from.’ WHEN ‘YYYY-MM-DD’ THEN TO_CHAR(SYSDATE – ‘&&history_days.’, ‘YYYY-MM-DD’) ELSE ‘&&edb360_conf_date_from.’ END edb360_date_from FROM DUAL;
SELECT CASE ‘&&edb360_conf_date_to.’ WHEN ‘YYYY-MM-DD’ THEN TO_CHAR(SYSDATE + 1, ‘YYYY-MM-DD’) ELSE ‘&&edb360_conf_date_to.’ END edb360_date_to FROM DUAL;
–PRO
–PRO Parameter 2: Days of History? (default 31)
–PRO Use default value of 31 unless you have been instructed otherwise.
–PRO
–SELECT TO_CHAR(TO_DATE(‘&&edb360_conf_date_to.’, ‘YYYY-MM-DD’) – TO_DATE(‘&&edb360_conf_date_from.’, ‘YYYY-MM-DD’) + 1) history_days FROM DUAL WHERE ‘&&edb360_conf_date_from.’ != ‘YYYY-MM-DD’ AND ‘&&edb360_conf_date_to.’ != ‘YYYY-MM-DD’;
–SELECT ‘0’ history_days FROM DUAL WHERE NVL(TRIM(‘&&diagnostics_pack.’), ‘D’) = ‘D’;
–SET TERM OFF;
VAR hist_work_days NUMBER;
VAR hist_days NUMBER;
BEGIN
:hist_days := TO_DATE(‘&&edb360_date_to.’, ‘YYYY-MM-DD’) – TO_DATE(‘&&edb360_date_from.’, ‘YYYY-MM-DD’) + 1;
:hist_work_days := 0;
FOR i IN 0 .. :hist_days – 1
LOOP
IF TO_CHAR(TO_DATE(‘&&edb360_date_from.’, ‘YYYY-MM-DD’) + i, ‘D’) BETWEEN TO_NUMBER(‘&&edb360_conf_work_day_from.’) AND TO_NUMBER(‘&&edb360_conf_work_day_to.’) THEN
:hist_work_days := :hist_work_days + 1;
dbms_output.put_line((TO_DATE(‘&&edb360_date_from.’, ‘YYYY-MM-DD’) + i)||’ ‘||:hist_work_days);
END IF;
END LOOP;
END;
/
PRINT :hist_work_days;
PRINT :hist_days;
COL hist_work_days NEW_V hist_work_days;
SELECT TO_CHAR(:hist_work_days) hist_work_days FROM DUAL;
—
—
COL diagnostics_pack NEW_V diagnostics_pack FOR A1;
SELECT CASE WHEN ‘&&license_pack.’ IN (‘T’, ‘D’) THEN ‘Y’ ELSE ‘N’ END diagnostics_pack FROM DUAL;
COL skip_diagnostics NEW_V skip_diagnostics FOR A1;
SELECT CASE WHEN ‘&&license_pack.’ IN (‘T’, ‘D’) THEN NULL ELSE ‘Y’ END skip_diagnostics FROM DUAL;
COL tuning_pack NEW_V tuning_pack FOR A1;
SELECT CASE WHEN ‘&&license_pack.’ = ‘T’ THEN ‘Y’ ELSE ‘N’ END tuning_pack FROM DUAL;
COL skip_tuning NEW_V skip_tuning FOR A1;
SELECT CASE WHEN ‘&&license_pack.’ = ‘T’ THEN NULL ELSE ‘Y’ END skip_tuning FROM DUAL;
SET TERM ON;
SELECT ‘Be aware value “N” reduces output content substantially. Avoid “N” if possible.’ warning FROM dual WHERE ‘&&license_pack.’ = ‘N’;
BEGIN
IF ‘&&license_pack.’ = ‘N’ THEN
DBMS_LOCK.SLEEP(10); — sleep few seconds
END IF;
END;
/
–SET TERM OFF;
— snapshot ranges
SELECT ‘0’ history_days FROM DUAL WHERE TRIM(‘&&history_days.’) IS NULL;
COL tool_sysdate NEW_V tool_sysdate;
SELECT TO_CHAR(SYSDATE, ‘YYYYMMDDHH24MISS’) tool_sysdate FROM DUAL;
COL between_times NEW_V between_times;
COL between_dates NEW_V between_dates;
SELECT ‘, between &&edb360_date_from. and &&edb360_date_to.’ between_dates FROM DUAL;
COL minimum_snap_id NEW_V minimum_snap_id;
SELECT NVL(TO_CHAR(MIN(snap_id)), ‘0’) minimum_snap_id FROM dba_hist_snapshot WHERE ‘&&diagnostics_pack.’ = ‘Y’ AND dbid = &&edb360_dbid. AND begin_interval_time > TO_DATE(‘&&edb360_date_from.’, ‘YYYY-MM-DD’);
SELECT ‘-1’ minimum_snap_id FROM DUAL WHERE TRIM(‘&&minimum_snap_id.’) IS NULL;
COL maximum_snap_id NEW_V maximum_snap_id;
SELECT NVL(TO_CHAR(MAX(snap_id)), ‘&&minimum_snap_id.’) maximum_snap_id FROM dba_hist_snapshot WHERE ‘&&diagnostics_pack.’ = ‘Y’ AND dbid = &&edb360_dbid. AND end_interval_time 0
),
ash_awr AS
(
SELECT /*+ &&sq_fact_hints. &&ds_hint. */
DISTINCT current_obj#
FROM dba_hist_active_sess_history
WHERE sql_plan_operation = ‘INDEX’
AND snap_id BETWEEN &&minimum_snap_id. AND &&maximum_snap_id.
AND dbid = &&edb360_dbid.
AND current_obj# > 0
),
sql_mem AS
(
SELECT /*+&&sq_fact_hints. &&ds_hint. */
DISTINCT object_owner, object_name
FROM gv$sql_plan
WHERE operation = ‘INDEX’
),
sql_awr AS
(
SELECT /*+ &&sq_fact_hints. &&ds_hint. */
DISTINCT object_owner, object_name
FROM dba_hist_sql_plan
WHERE operation = ‘INDEX’ AND dbid = &&edb360_dbid.
)SELECT /*+ &&top_level_hints.*/
i.table_owner,
i.table_name,
i.index_name
FROM dba_indexes i
WHERE (index_type LIKE ‘NORMAL%’ OR index_type = ‘BITMAP’ OR index_type LIKE ‘FUNCTION%’)
AND i.table_owner NOT IN (&&exclusion_list)
AND i.table_owner NOT IN (&&exclusion_list2)
AND (i.owner, i.index_name) NOT IN ( SELECT o.owner, o.object_name FROM ash_awr a, objects o WHERE o.object_id = a.current_obj# )
AND (i.owner, i.index_name) NOT IN ( SELECT object_owner, object_name FROM sql_awr)
ORDER BY
i.table_owner,
i.table_name,
i.index_name
;
————————————————————————–
AND (i.owner, i.index_name) NOT IN ( SELECT o.owner, o.object_name FROM ash_awr a, objects o WHERE o.object_id = a.current_obj# )
*
ERROR at line 52:
ORA-00942: table or view does not exist
LikeLike
July 21, 2016 at 8:33 am
Hi Navakanth,
just FYI I don’t maintain eDB360, Carlos Sierra does 🙂
Anyway the error here seems to be you don’t have the grant to read from ASH tables (DBA_HIST_ACTIVE_SESS_HISTORY), did you make sure you have the proper grants?
Anytime you see a ORA-942 the first two things to check are 1. the object exists for real 2. you are the privilege to access it
I hope it helps,
Mauro
LikeLiked by 1 person