Ola Hallengren Command Log History Scripts

If you are like me and use the Ola Hallengren set of free scripts to help manage backups, checkdbs, and index maintenance operations then you may find yourself needing to dive into the Command Log history to calculate the duration of some of your common operations. See below for some scripts I wrote to query the data in the CommandLog.

Command Type Duration by Days of the week

I was trying to track down which indexes were being rebuilt on what days. The Index job was taking longer on some days then others and I was looking for a trend. This stored procedure will show you all the ALTER_INDEX commands (or whatever other commands you want to see) by day of week. This will help you get an idea of what indexes are reorganized or rebuilt on what days.:


This will show you the duration for each day of the week. If you pass the optional DaysBack parameter as 7 it will show you the history for the past 7 days broken down by day of week.

ALTER INDEX Command Type SELECT

Select statement to show all the indexes that were included in maintenance on a specific date. I wanted to see what indexes were modified today so I used the script below.

CommandType Count Script

I needed a quick SELECT to show the number of time an index had been reorganized or rebuilt.

Setting optimal UNDO retention

Run this query to find the optimal

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.STATUS = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

Then run this to set the optimal

ALTER SYSTEM SET UNDO_RETENTION=5;

Current Running Queries Oracle

Several different ways to get this data. The first one is a query to get the current running quries.

select sesion.sid,
      sess.username,
      optimizer_mode,
      hash_value,
      address,
      cpu_time,
      elapsed_time,
      sql_text
 from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
  and sess.sql_address    = sqlarea.address
  and sess.username is not null

The next one will display the SQL of the PIDs using the Most CPU. You get the PID number by running the top command

select proc.spid, sess.username, s.sql_text
  from v$process proc, v$session sess, v$sqlarea s
 where proc.addr = sess.paddr
   and sess.sql_hash_value = s.hash_value
   and proc.spid in (22725,553)