The X$KCCCP table contains information about the current redo log file. The X$KCCLE table contains information about all redo log files, as shown here:
?
If you observe in V$LOG_HISTORY or in the “log file space waits” statistic that log switches are occurring more frequently than is appropriate for your database, you may decide to alter the redo log file configuration. You can perform this task while the database is open to users and all tablespaces are online. If you want to minimize the impact on database performance while this or other similar maintenance is performed that involves a DBA-induced log switch with the alter system switch log file command, you can use the query in the preceding listing to measure how much redo log information has to be copied to complete the archive of the current log file. This is particularly relevant in cases of databases with large redo log files (500M or larger).
You can also use this query as a tuning aid to measure how much redo activity is created by a particular transaction or process, if it is possible to isolate a particular database to one session that is guaranteed as the only creator of redo records, other than Oracle itself. Capturing before and after results of this query when testing such a transaction may be useful.
Leave a Reply