Query to get list of busiest schemas in Oracle 12c database

oracleoracle-12c

I have an Oracle 12c database with many schemas, each schema belongs to a specific application.

I want to produce a query that shows me which schemas are the busiest in terms of read/write activity / IO etc.

I know that I can use the DBA_TAB_MODIFICATIONS table to get modifications since the last stats update, per table.

I've been told that the DBA_HIST objects will be able to give me more information but there's a lot of data there and I don't really know how to get the best out of it for this purpose.

Any help would be appreciated.

Best Answer

DBA_HIST_SEG_STAT stores segment level statistics about reads, writes and others.

Example output from one of my sandbox environments:

select
  o.owner,
  sum(physical_reads_delta) as physical_reads,
  sum(physical_reads_direct_delta) as physical_reads_direct,
  sum(physical_writes_delta) as physical_writes,
  sum(physical_writes_direct_delta) as physical_writes_direct,
  sum(db_block_changes_delta) as db_block_changes
from
  dba_hist_seg_stat ss
  join dba_objects o on (o.object_id = ss.obj#)
group by
  o.owner
order by
  o.owner
;

OWNER  PHYSICAL_READS PHYSICAL_READS_DIRECT PHYSICAL_WRITES PHYSICAL_WRITES_DIRECT DB_BLOCK_CHANGES
------ -------------- --------------------- --------------- ---------------------- ----------------
AUDSYS              0                     0              42                      0              112
BP            7917707               7917685          387803                 386932             4224
DBSNMP              0                     0               0                      0             1264
SYS            192271                 13873          290452                   5753          8182176
Related Question