Databases Reference
In-Depth Information
print sbttime_in_secs;
-- CMDTIME_IN_SECS is total command execution time
print cmdtime_in_secs;
-- NON_SBTTIME_IN_SECS is time spent in non-SBT code
print non_sbttime_in_secs;
-- TOTAL INPUT BYTES read
print total_input_bytes;
-- TOTAL OUTPUT BYTES written
print total_output_bytes;
-- Effective output bytes per second
select :total_output_bytes/:cmdtime_in_secs effective_output_bytes_per_sec
from dual;
-- Effective input bytes per second
select :total_input_bytes/:cmdtime_in_secs effective_input_bytes_per_sec
from dual;
-- Input file that is bottleneck for the backup operation
select long_waits, io_count, filename input_bottleneck_filename
from backup_async_io
where type='INPUT'
and long_waits/io_count = (select max(long_waits/io_count)
from backup_async_io where type='INPUT' );
-- Critical I/O event information
select
pre.event, (post.total_waits-pre.total_waits) total_waits,
(post.total_timeouts-pre.total_timeouts) total_timeouts,
(post.time_waited-pre.time_waited) time_waited,
(post.time_waited_micro - pre.time_waited_micro) time_waited_micro,
(post.time_waited_micro-pre.time_waited_micro)/
(post.total_waits-pre.total_waits) average_wait_micro
from pre_cmd_ksfq_events pre, v$system_event post
where pre.event=post.event;
-- IO_SLAVES parameter settings for the instance
show parameter io_slaves;
set line 2000
-- Contents of V$BACKUP_ASYNC_IO generated for the RMAN command executed
select * from backup_async_io;
-- Contents of V$BACKUP_SYNC_IO generated for the RMAN command executed
select * from backup_sync_io;
-- Pre and Post command statistics for important KSFQ events
select * from v$system_event where event in ('i/o slave wait', 'io done');
exit;
Again, this level of debugging and tracing often is required only when requested by Oracle Support. The output is
helpful in diagnosing difficult performance and troubleshooting issues.
Enabling Granular Time Reporting
Problem
You're troubleshooting an issue, and you notice that the output from the
list
command specifies the date but
without an hours:minutes:time component. You want to capture the exact second when your RMAN operations
started and finished.
