eDB360
An Oracle Database 360-degree View
eDB360 is a free tool that executes on an Oracle database and produces a compressed file which includes a large set of small Reports. This set of Reports provides a 360-degree view of an Oracle Database. eDB360 is mostly used for one of the following 3 reasons, listed here in order of frequency of use: 1) Keystone of an Oracle database Health-Check. 2) Kick-off for an Oracle database Performance Evaluation. 3) High-level view of System Resources demand and utilization for an Oracle database Sizing and Provisioning project.
Usually, Developers, Sys Admins and Consultants are not given open access to a database in a Production environment. This eDB360 free tool helps approved users to become familiar with an Oracle database in a non-intrusive way. Without installing anything on the database, the eDB360 tool connects to an Oracle database and produces a large set of flat files that can be reviewed offline while using an HTML browser or a Text editor.
eDB360 can be executed by someone with very limited access to an Oracle database (i.e. a Developer, Sys Admin or Consultant with just query access to the Data Dictionary views); or if executed by an authorized DBA, there is no actual need to provide any additional access to the Oracle database to the party requesting eDB360.
eDB360 works on 10gR2, 11gR2, and on higher releases of Oracle; and it can be used on Linux or UNIX Platforms. It has not been tested on Windows.
Instructions
Download the eDB360 tool and review the readme.md file included. Uncompress the master ZIP file on the Database Server of interest. Navigate to the main (master) directory and execute script edb360.sql connected as SYS or any other account with access to the Data Dictionary views (a DBA account is not required but it is preferred).
Execution time for eDB360 may exceed 1 hour, depending on the size of the Data Dictionary. And the size of the output may reach 1 GB, so be sure you execute this tool from a file system directory with at least 1 GB or free space. Common sizes of the output range between 10 and 100 MB.
eDB360 has only one required execution parameter:
- Oracle Pack License: A big portion of the information presented by eDB360 comes from Oracle’s Automatic Workload Repository (AWR), and AWR is licensed by Oracle under the Diagnostics Pack. A small part of the output of eDB360 comes from the SQL Monitoring repository, which is part of the Oracle Tuning Pack. This parameter accepts one of 3 values: “T”, “D” or “N”. If you database is licensed under the Oracle Tuning Pack, enter then the value of “T”. If your database is not licensed to use the Oracle Tuning Pack but it is licensed to use the Oracle Diagnostics Pack, enter “D” then. If your site is not licensed on any of these two Oracle Packs, enter “N” then. Be aware that a value of “N” reduces substantially the content and value of the output. Thus the preferred parameter value is “T” (Oracle Tuning Pack).
Sample
# unzip edb360-master.zip # cd edb360-master # sqlplus / as sysdba SQL> @edb360.sql T
Download
eDB360, now part of SQLdb360, is available as free-to-use software. You can see its readme.md, license.txt or any other piece of the tool before downloading it.
Feedback
Please post your feedback about this eDB360 tool at this blog, or send and email directly to the tool author: Carlos Sierra.
[…] a point, eAdam is similar to eDB30; both access the Data Dictionary in order to produce some reports. The key difference is that […]
eAdam | Oracle SQL Tuning Tools and Tips
July 27, 2014 at 6:25 pm
[…] Metrics are easier to digest if visualized trough some Line Charts. OEM, eDB360, eAdam and other tools use them. If you already have a SQL Statement that provides the Performance […]
Free script to generate a Line Chart on HTML | Oracle SQL Tuning Tools and Tips
July 28, 2014 at 2:34 pm
[…] Metrics are easier to digest if visualized trough some Line Charts. OEM, eDB360, eAdam and other tools use them. If you already have a SQL Statement that provides the […]
script to generate a Line Chart on HTML « Oracle-dba
August 5, 2014 at 12:49 pm
Love the tool! Is there anything special needed to run edb360 on a exadata box?
Mark Davidson
November 13, 2014 at 11:46 am
nothing special. you can run edb360 on exadata
Carlos Sierra
November 13, 2014 at 3:01 pm
I am working with several 12c container databases with pluggables within. I can get edb360 (I did download the latest version) to run at the container but I am not seeing anything at the pluggable level. I have tried connecting to the container, changing to the PDB and then running edb360 but it still seems to be trying to run at the container level.
Am I correct that currently it will only work at the container level? If so – any plans on getting it to recognize pluggables? Or am I missing something.
Kevin
November 14, 2014 at 3:26 pm
Kevin,
It is not 12c ready yet. It will, but not yet. My ETA is end of December 2014.
Carlos Sierra
November 14, 2014 at 3:56 pm
Thanks – I will watch for it.
Kevin
November 17, 2014 at 11:44 am
[…] eDB360 […]
Some eDB360 Frequently Asked Questions | Carlos Sierra's Tools and Tips
January 7, 2015 at 2:56 pm
[…] so far sounds familiar then you are probably right. SQLd360 has been created to complement eDB360 tool created by Carlos Sierra. The design is similar but at the same time different having to deal […]
SQLd360, SQL diagnostics collection made faster | Mauro Pagano's Blog
February 15, 2015 at 7:00 pm
[…] Always happy to share their knowledge, to help and to share free tools (recent examples are: edb360 and […]
Joining the Accenture Enkitec Group | bdt's oracle blog
February 18, 2015 at 1:53 pm
[…] Sierra. EDB360 provides a number of reports giving a 360-degree view of an Oracle […]
Oracle Standard Edition Expert Interview with Ann Sjokvist of SEJustLoveit.com | Madora Consulting
May 8, 2015 at 2:41 pm
I love the new checks for stale stats before edb360 begins. Can those be bypassed in batch?
Michael Fontana
September 3, 2015 at 5:11 pm
i will write you something to bypass those
Carlos Sierra
September 4, 2015 at 6:52 pm
on next upload (v1528) you simply remove file edb360_0h_ash_validation.sql from the sql directory and you then bypass such validation.
Carlos Sierra
September 8, 2015 at 10:49 pm
[…] Source: eDB360 […]
eDB360 |
November 2, 2015 at 3:14 am
[…] are many queries out there that can help you do this analyses on AWR, my favorite is to use EDB360, which you can also extract the query and adjust for your own […]
Monitor your indexes!!! Everything in excess is bad. Except Ice Cream :) | Rodrigo Righetti DBA
April 19, 2016 at 12:37 pm
[…] The concept behind this tool is just like other tools that have been released with the 360 concept (edb360 and sqld360); to provide a quick and easy approach to checking an environment. As with edb360 and […]
#EMd360 … OEM health checks made easy | DBASolved
April 25, 2016 at 4:11 pm
Fantastic tool. However, many sites take AWR snapshots more frequently than every hour. For example it’s not that uncommon to see snapshots every 15 minutes. Can you comment on edb360’s compatibility with non-hourly AWR snapshot frequencies as it seems like some queries (for example in section 5a.389 just to name one) do a lot of math based on things like HH+1/24 or in other places dividing by 3600? Thanks!
Simon
May 10, 2016 at 10:15 am
Simon,
eDB360 expects snaps every hour, or every 30 mins, or 20 or 15, etc. Since it is common practice to change default interval from 1hr to something else, eDB360 normalizes all its report to 1hr intervals. In other words, it works just fine.
Carlos Sierra
May 11, 2016 at 5:51 pm
Great – thanks for the reassurance!
Simon
May 11, 2016 at 6:56 pm
Hi Carlos, how are you doing ! We have met during your two weeks training in Egypt with Hector, I believe this was in 2009, time is really flying. by this time we learnt a lot about your stunning tool SQLT and now I am working as a technical delivery manager for HPE and encourage our Database team to look at your new fascinating tool ! the quest now is .. is this tool authorised by Oracle, also we are looking for a Demo?
Mohamed Badr
November 8, 2016 at 2:39 am
Hey Mohamed,
We use this eDB360 tool in many Oracle sites. It does not require authorization from Oracle. It is free software. You ma also want to check SQLd360. On eDB360 page (see on the right) there is a link for a sample report. Better yet, just execute it on a lower environment so you can see the output.
Carlos Sierra
November 8, 2016 at 7:57 am
[…] Carlos Sierra and EDB360 […]
#81 – Database Statistics w/ David Kurtz
May 19, 2017 at 7:00 am
Hi Carlos,
you mentioned that the tool might take several hours possibly due to a large unpurged AWR data.
I’m just running it on an Oracle EBS vision environment right now for about 9 hours already and the reason for slow performance is not large AWR data, but one single problematic SQL (id:1dbc5krbyvt9h) trying to scan through the ~60 million codelines in dba_source to generate this:
00155_edb360_119691_2a_136_libraries_calling_dbmsstats.html
This SQL has read about 1.5TB of data already while causing 99.3% of the overall execution time, so maybe you could review it in order to make edb360 usable for environments with a large codebase as well (or alternatively provide an option to remove this check from the script).
Thanks and regards from Switzerland,
Andy
Andy Haack
May 30, 2017 at 10:47 pm
Andy. I will add a flag to set/unset that query. In the meantime, you can simply remove it from the code, or comment out the one line that executes it. Thx.
Carlos Sierra
May 31, 2017 at 4:44 am
[…] we are very happy to release SQLdb360, a new tool that merges together eDB360 and SQLd360, under a single package. Tools eDB360 and SQLd360 can still be used independently, but […]
Introducing SQLdb360: merging eDB360 and SQLd360, while rising the bar to community engagement | Mauro Pagano's Blog
June 11, 2018 at 7:01 am
[…] will benefit from this session on using Google Charts. I first got a taste of Google Charts from EBD360 (Carlos Sierra) and SQLD360 (Mauro Pagano) tools. [Note: If you are DBA and not familiar with these […]
#OOW19 Session: It’s all in the presentation of data! – OracleNotes
September 10, 2019 at 8:11 pm
Hi Carlos,
I have tried to add a new field under ‘Database Administration’ field for ‘in-flight transactions’ and have something like below to the script ‘2a_admin.sql’
DEF title = ‘Inflight Transactions’;
DEF main_table = ‘GV$SESSION’;
BEGIN
:sql_text := q'[
select /*+ &&top_level_hints. */ /* &§ion_id..&&report_sequence. */
x.inst_id,
x.sid
,x.serial#
,x.username
,x.sql_id
,plan_hash_value as PHV
,sqlarea.DISK_READS
,sqlarea.BUFFER_GETS
,sqlarea.ROWS_PROCESSED
,x.event
,x.osuser
,x.status
,x.BLOCKING_SESSION_STATUS
,x.BLOCKING_INSTANCE
,x.BLOCKING_SESSION
,x.process
,x.machine
,x.OSUSER
,x.program
,x.module
,x.action
,TO_CHAR(x.LOGON_TIME, ‘MM-DD-YYYY HH24:MI:SS’) logontime
,x.LAST_CALL_ET
,x.SECONDS_IN_WAIT
,x.state
,sql_text,
ltrim(to_char(floor(x.LAST_CALL_ET/3600), ’09’)) || ‘:’
|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), ’09’)) || ‘:’
|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), ’09’)) RUNNING_SINCE
from gv$sqlarea sqlarea
,gv$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and sql_text not like ‘%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value%’
and x.status=’ACTIVE’
and x.USERNAME is not null
and x.SQL_ADDRESS = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc
]’;
END;
/
@@edb360_9a_pre_one.sql
This is what I am getting.
SELECT TO_CHAR(ROWNUM) row_num, v0.* FROM /* 2a.68 */ (
*
ERROR at line 1:
ORA-00918: column ambiguously defined
This is what it is there in ‘9a_pre_1.sql’
— spools query
SPO &&common_edb3601_prefix._query.sql;
SELECT ‘SELECT TO_CHAR(ROWNUM) row_num, v0.* FROM /* &§ion_id..&&report_sequence. */ (‘||CHR(10)||TRIM(CHR(10) FROM :sql_text)||CHR(10)||’) v0 WHERE ROWNUM <= &&max_rows.' FROM DUAL;
SPO OFF;
SET HEA ON;
GET &&common_edb3601_prefix._query.sql
Not sure where I am doing mistake. Could you please help!
FatDBA
July 9, 2020 at 3:59 am
try your sql stand-alone from sql plus, and fix any errors
Carlos Sierra
July 9, 2020 at 6:24 am