1

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.

Improve this question

1 Answer 1

Reset to default
2

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
Improve this answer
3
  • This looks good, thanks. Out of interests what are delta read/writes and what is the difference between the direct and non direct metrics?
    – Molenpad
    Jan 10, 2019 at 10:57
  • 1
    @Molenpad The database creates snapshots of performance metrics at regular intervals. The snapshots store the deltas of the metrics. Summing the deltas gives the total value. Direct reads bypass the buffer cache so they are not cached. Direct writes are used for direct path load, temp operation, parallel DML.
    – Balazs Papp
    Jan 10, 2019 at 11:05
  • I am accepting this as the answer as it gave me a really solid foundation on which to build my queries, and I learned a lot. Thanks.
    – Molenpad
    Jan 10, 2019 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.