Oracle Scratchpad

April 9, 2007

Log Switching

Filed under: Performance,redo,Tuning — Jonathan Lewis @ 7:04 pm GMT Apr 9,2007

Analytic function have all sorts of uses, and the lag() and lead() functions are among my favourites. I always like to remind people of the sorting overheads involved with analytic functions – but if you are prepared to accept the overhead the simplicity of comparing ‘current’ rows with ‘earlier’ or ‘later’ rows using lag() or lead() is a tremendous coding benefit.

Here’s just one example of the lead() function – answering the simple question: “how much time do I spend between log file switches?”

select
	to_char(first_time,'dd hh24:mi:ss') 	first_time,
	round(24 * 60 * (
		lead(first_time,1) over (
			order by first_time
		) -
		first_time
           ),2) 				minutes
from
	v$log_history v
where
	recid >= 3654
order by
	recid
;

FIRST_TIME   MINUTES
------------ -------
07 13:21:13     8.63
07 13:29:51     9.98
07 13:39:50     9.88
07 13:49:43    10.00
07 13:59:43    10.00
07 14:09:43     9.90
07 14:19:37   129.50
07 16:29:07     9.90
07 16:39:01    10.00
07 16:49:01

I happen to have used a constant as the starting point in v$log_history for this example; for a slightly more sophisticated approach you could pick up the highest log file sequence number from v$log and report (say) the last couple of dozen log switches with code something like the following:

column sequence# new_value m_sequence

select	max(sequence#) sequence#
from	v$log
;

select ...
where
	recid >= &m_sequence - 30
...

Footnote: See comment #2 for a RAC variant on this script, and comment #6 for suggestions on improving efficiency.

Other simple utility scripts

8 Comments »

  1. Nice little utility script, Jonathan.

    One word of warning, though:
    If you run RAC, you’ll get some bizarre results. The solution is to add a predicate to the WHERE clause to filter on THREAD#. Otherwise, it sees all threads (across all RAC instances) and the calculations break.

    -Mark

    Comment by Mark J. Bobak — April 9, 2007 @ 7:42 pm GMT Apr 9,2007 | Reply

  2. Alternatively you could use PARTITION BY in the analytical clause to sort out threads on a RAC database.


    select
            thread#,
            to_char(first_time,'dd hh24:mi:ss')         first_time,
            round(24 * 60 * (
                    lead(first_time,1) over (
                            partition by thread#
                            order by first_time
                    ) -
                    first_time
               ),2)                                 minutes
    from
            v$log_history v
    where
            recid >= 117
    order by
            thread#, recid
    ;    

       THREAD# FIRST_TIME     MINUTES
    ---------- ----------- ----------
             1 03 21:06:25     473.83
             1 04 05:00:15     962.68
             1 04 21:02:56     380.22
             1 05 03:23:09     937.87
             1 05 19:01:01     477.12
             1 06 02:58:08    1083.63
             1 06 21:01:46     384.72
             1 07 03:26:29     214.55
             1 07 07:01:02    1186.35
             1 08 02:47:23     571.88
             1 08 12:19:16
             2 02 18:25:20    1954.57
             2 04 02:59:54     1968.3
             2 05 11:48:12    1570.95
             2 06 13:59:09     1799.1
             2 07 19:58:15

    Comment by Jeremy — April 9, 2007 @ 10:03 pm GMT Apr 9,2007 | Reply

  3. Mark, Jeremy.
    Good follow-up. And thanks for getting the format so clean, Jeremy.

    Comment by Jonathan Lewis — April 9, 2007 @ 10:19 pm GMT Apr 9,2007 | Reply

  4. Analytic function rocks! :)

    Some time ago I write a single query that make “diff” between 2 disjointed set!

    The normal approach would be a double execution and a “unix-diff” between files, but with lag I was able to write a single query that do the work!

    Nice, no? :)

    Comment by Antonio — April 10, 2007 @ 6:48 am GMT Apr 10,2007 | Reply

  5. One other thought… you could probably sort by first_time rather than recid and get the same results with one less sort… probably splitting hairs since this is not really a slow query but it does give a simple illustration of the “sorting overheads involved with analytic functions” that you mentioned.

    order by thread#, first_time

    Comment by Jeremy — April 10, 2007 @ 1:52 pm GMT Apr 10,2007 | Reply

  6. Jeremy, that’s an interesting example of finding ways to avoid work. Of course, we must include an order by clause to guarantee the order of output (see, for example, this blog entry)- but Oracle is allowed to avoid a sort operation if the optimizer considers it redundant.

    In fact, when I tried your suggestion (in 10.2.0.1) I still got a sort order by operation. I think this was because the name first_time is both a column in the underlying view and a column alias I’ve used in the query.

    When I also changed the column alias to start_time the sort operation disappeared. Alternatively, when I used the recid in the over() and the order by clause this also eliminated the sort operation.

    Comment by Jonathan Lewis — April 14, 2007 @ 5:10 pm GMT Apr 14,2007 | Reply

  7. [...] Infrastructure — Jonathan Lewis @ 9:55 pm UTC Apr 12,2007 A few days ago, I published a simple query you could run against v$log_history to show the time between log file switches.  So if you use this [...]

    Pingback by Log File Switch « Oracle Scratchpad — April 14, 2007 @ 5:20 pm GMT Apr 14,2007 | Reply

  8. [...] simple script for finding objects subject to a large number of tablescans or index fast full scans Log file switches: a script (for single instance) to report the time of, and time between, log file [...]

    Pingback by Simple scripts « Oracle Scratchpad — February 13, 2010 @ 8:59 am GMT Feb 13,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers