Perform capacity planning using LogMiner

The following is backup and recovery tip #4 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.

As a DBA, you can use LogMiner to do some performance tuning or capacity planning. Say that you've got a table that is busy capturing audit information for your application. Load up LogMiner with an hour's worth (or some other time period) of archive log file. Then run a query against those archive logs to see the insert rate on the table:

SELECT operation, to_char(timestamp,'HH') hour, count(*) total
   FROM v$logmnr_contents
  WHERE seg_name = 'AUDIT_LOG'
    AND seg_owner = 'SOMEUSER'
    AND seg_type_name = 'TABLE'
  GROUP BY operation, to_char(timestamp,'HH');
OPERATION HOUR      TOTAL
--------- ---- ----------
INSERT    10           16
INSERT    11           16
INSERT    12           16
INSERT    13           16

The results of this query show you that the AUDIT_LOG table is adding rows at the rate of 16 per hour.

For more information see Chapter 9 of "Oracle backup and recovery 101."

Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.