RE: 10g - System Statistics - Quick Q

From: Tanel Poder <tanel_at_poderc.com>
Date: Sat, 24 Jan 2009 03:30:37 +0200
Message-ID: <598F31625CFB4C5386B15A77EA461E24_at_porgand>



I see this question has been well answered, but I want to add something as a related "detect when a table/data was last changed" question is a pretty frequent one.

When anyone has a more generic requirement to find out (at low precision) when was some data modified and you don't want to start scanning through loads of logs with logminer yet, you can use my script for detecting when was table data changed. It's low precision, has some limitations, but can save you time in some cases - and it's very easy to use.

http://www.tanelpoder.com/files/scripts/lastchanged.sql

(read instructions and limitations from the script header).

It uses ORA_ROWSCN for getting block last change SCN from matching rows and it maps the SCNs to real time using sys.smon_scn_time and v$log_history views. So it reports two times, first one may be more accurate, second one has longer history on the other hand.

Few examples:

SQL> _at_lastchanged sys.obj$ name='DBMS_STANDARD'

Running this query:

. select MAX(ora_rowscn)
. from sys.obj$
. where name='DBMS_STANDARD';

DATA_SOURCE LAST_CHANGED

----------------- -------------------------------------------------------
sys.smon_scn_time Before  2008-12-31 16:05:25 (earlier than 21 days ago)
v$log_history     Before  2008-10-27 03:58:16 (earlier than 86 days ago)


2 rows analyzed.

SQL> _at_lastchanged scott.emp sal>1000

Running this query:

. select MAX(ora_rowscn)
. from scott.emp
. where sal>1000;

DATA_SOURCE LAST_CHANGED




sys.smon_scn_time Between 2009-01-21 08:56:57 and 2009-01-21 09:02:19 (5 minute range)
v$log_history Between 2009-01-21 08:26:43 and 2009-01-21 08:57:14 (31 minute range)

14 rows analyzed.

SQL> update scott.emp set sal = sal * 2;

14 rows updated.

SQL> commit;

Commit complete.

SQL> _at_lastchanged scott.emp sal>1000

Running this query:

. select MAX(ora_rowscn)
. from scott.emp
. where sal>1000;

DATA_SOURCE LAST_CHANGED




sys.smon_scn_time After 2009-01-21 09:02:19 (between 6 minutes ago and now)
v$log_history After 2009-01-21 08:57:17 (between 11 minutes ago and now)

14 rows analyzed.

--
Tanel Poder
http://blog.tanelpoder.com


> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org 
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Newman, 
> Christopher
> Sent: 23 January 2009 23:55
> To: oracle-l_at_freelists.org
> Subject: 10g - System Statistics - Quick Q
> 
> Anyone know how to determine when system statistics were 
> gathered?  IE, the last time?
> 
> Thanks! - Chris
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 23 2009 - 19:30:37 CST

Original text of this message