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.

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 BST Apr 9,2007 |
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 BST Apr 9,2007 |
Mark, Jeremy.
Good follow-up. And thanks for getting the format so clean, Jeremy.
Comment by Jonathan Lewis — April 9, 2007 @ 10:19 pm BST Apr 9,2007 |
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 BST Apr 10,2007 |
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_timeComment by Jeremy — April 10, 2007 @ 1:52 pm BST Apr 10,2007 |
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 BST Apr 14,2007 |
[…] 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 BST Apr 14,2007 |
[…] 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 |