Skip to Main Content
  • Questions
  • How can we query one database and get all databases' cell or IO usage from one database?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Qinsheng.

Asked: April 21, 2021 - 12:12 pm UTC

Last updated: April 28, 2021 - 3:01 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi Ask Tom Team,

We had an issue in all of our production databases on an Exadata cluster. One RAC database's IO issue caused all databases on the cluster slowdown, user transactions took much more time, batch jobs hung. We need to run some scripts to consistently monitor the cluster besides OEM, so that we can identify the issues quicker and take actions sooner. OEM provides good information on our needs. We need your help to have related SQL scripts to generate same or similar information.

In Oracle AWR reports. we can see a portion that is specific for Exadata. It appears in all AWR reports no matter in which database we run the awr report (global data), "Exadata Top Database Consumers". The information includes Cell usage and IO of all databases even though the AWR report is run only in one database (every AWR report has Exadata Top Database Consumers). Can you please help with related SQLs that generate that part of the AWR report? For example, how can we run a query to get "Top Databases by Requests" and so on by running queries only in one of the databases on the cluster. We checked all of the dictionary tables/views but failed to find any of them provide cluster information showing in AWR reports. We need to run the scripts (or stored procedure) periodically and have the system send us an email if any specific database's IO requests surpass a threshold we set. We can query live tables (GV$xxxxx) or DBA_HIST_XXXXX. Please advise.

Thanks!

and Connor said...

The views that the AWR reports typically get their data from are prefixed with AWR. Hence for cell information

SQL> select view_name
  2  from dba_views
  3  where view_name like 'AWR%CELL%'
  4  order by 1;

VIEW_NAME
-------------------------------------------------
-----------------
AWR_CDB_CELL_CONFIG
AWR_CDB_CELL_CONFIG_DETAIL
AWR_CDB_CELL_DB
AWR_CDB_CELL_DISKTYPE
AWR_CDB_CELL_DISK_NAME
AWR_CDB_CELL_DISK_SUMMARY
AWR_CDB_CELL_GLOBAL
AWR_CDB_CELL_GLOBAL_SUMMARY
AWR_CDB_CELL_IOREASON
AWR_CDB_CELL_IOREASON_NAME
AWR_CDB_CELL_METRIC_DESC
AWR_CDB_CELL_NAME
AWR_CDB_CELL_OPEN_ALERTS
AWR_PDB_CELL_CONFIG
AWR_PDB_CELL_CONFIG_DETAIL
AWR_PDB_CELL_DB
AWR_PDB_CELL_DISKTYPE
AWR_PDB_CELL_DISK_NAME
AWR_PDB_CELL_DISK_SUMMARY
AWR_PDB_CELL_GLOBAL
AWR_PDB_CELL_GLOBAL_SUMMARY
AWR_PDB_CELL_IOREASON
AWR_PDB_CELL_IOREASON_NAME
AWR_PDB_CELL_METRIC_DESC
AWR_PDB_CELL_NAME
AWR_PDB_CELL_OPEN_ALERTS
AWR_ROOT_CELL_CONFIG
AWR_ROOT_CELL_CONFIG_DETAIL
AWR_ROOT_CELL_DB
AWR_ROOT_CELL_DISKTYPE
AWR_ROOT_CELL_DISK_NAME
AWR_ROOT_CELL_DISK_SUMMARY
AWR_ROOT_CELL_GLOBAL
AWR_ROOT_CELL_GLOBAL_SUMMARY
AWR_ROOT_CELL_IOREASON
AWR_ROOT_CELL_IOREASON_NAME
AWR_ROOT_CELL_METRIC_DESC
AWR_ROOT_CELL_NAME
AWR_ROOT_CELL_OPEN_ALERTS


If you wanted to dig into the queries that are executed as part of an AWR report, the easiest way would be to trace it. Note that those queries could be platform/version/patch level specific, so you'd need to manage them carefully

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 28, 2021 - 12:04 pm UTC

Those views does Not provide information about other databases. For example (query other other views you mentioned have the same results in the same database):

SQL> select distinct DBID from AWR_ROOT_CELL_DB;

DBID
----------
3297284957

which means we cannot get other DBs' data.

Thanks.

Qinsheng Zhu, April 30, 2021 - 12:47 pm UTC

Now I know what's wrong with my query.

DBID always the ID of current database. What I need is actually in the databases. They should be SRC_DBID or SRC_DBNAME.
For example,
SELECT DISTINCT SRC_DBNAME from AWR_ROOT_CELL_DB will list all the databases on the cluster.

Thank you for your help. Questions answered.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library