RE: Question about V$SYSMETRIC *FUTURE* dated rows

From: Taylor, Chris David <Chris.Taylor_at_ingrambarge.com>
Date: Wed, 7 Jan 2009 15:29:08 -0600
Message-ID: <17E4CDE8F84DC44A992E8C00767402E0019D7B82_at_spobmexc02.adprod.directory>

Well, I came up with a band-aid (i.e. duct-tape) to get me around this issue until I can bounce the databases.  

So I'll pass it along to those who are interested.  

I noticed that there are 5 v$ views that are used by the DBSNMP connection when using the performance tab of Grid Control for each monitored database. (We use DBSNMP as the login for the grid control software - yours may be something different).  

The 5 views are:

V$SYSMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY
V$METRIC_HISTORY
V$FILEMETRIC_HISTORY
V$WAITCLASSMETRIC_HISTORY
 

So, here is what I did: I created a copy of these views owned by DBSNMP that include a case statement for the BEGIN_TIME and END_TIME. (IF date > sysdate +10, then sysdate, or sysdate +1/(24*60) for END_TIME). The END TIME must be greater (not equal) than the BEGIN_TIME.  

Also I created a trigger on the SYSMAN.MGMT_METRICS_RAW table:  

<pseudo>
IF COLLECTION_TIMESTAMP > SYSDATE, THEN COLLECTION_TIMESTAMP := sysdate+5/(24*60*60); /* 5 seconds added to sysdate */ (may not be necessary to add any seconds here)
So far, this gets me around the XML documents piling up in my OMS home ($OMS_HOME/sysman/recv/errors) directory.

And it allows me to use the performance tab of grid control for the affected database without issue.

Here's a script for one of the DBSNMP views if you're curious:

CREATE OR REPLACE VIEW DBSNMP.V$SYSMETRIC_HISTORY (

     BEGIN_TIME, 
     END_TIME, 
     INTSIZE_CSEC, 
     GROUP_ID, 
     METRIC_ID, 
     METRIC_NAME, 
     VALUE, 
     METRIC_UNIT

)
AS
SELECT
(CASE

         WHEN "BEGIN_TIME" > SYSDATE+10 
         THEN SYSDATE 
         END) AS "BEGIN_TIME", 
(CASE
         WHEN "END_TIME" > SYSDATE+10 
         THEN SYSDATE+1/(24*60) 
         END) AS "END_TIME", 

"INTSIZE_CSEC",
"GROUP_ID",
"METRIC_ID",
"METRIC_NAME",
"VALUE",
"METRIC_UNIT"

FROM SYS.v_$sysmetric_history
/

(I used +10 days to test in case there were any metrics that would be future dated correctly - I don't think there is, so the +10 could probably be dropped from here) HOWEVER< the END_TIME must include an addition to the sysdate to remain larger than the BEGIN_TIME by 1 minute.  

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David Sent: Wednesday, January 07, 2009 1:40 PM To: ORACLE-L
Subject: Question about V$SYSMETRIC *FUTURE* dated rows

Any of you guys ever run into the problem with 10.2.0.x databases having the V$SYSMETRIC tables future dated?? This causes Grid Control no problem of headaches. The future dated "data" gets sent to the grid control server in xml files. The grid control repository is unable to insert the data into the MGMT_METRICS_RAW table. Also, if you click on the performance graph for the affected target, it causes the Grid Control components to crash and restart (OC4J etc).  

Just curious if any of you have found a way to clear the V$SYSMETRIC tables without restarting the DB.  

I've been "playing" around with these parameters, but so far no luck.  

NAME VALUE DESCRIPTION UPDATE_COMMENT

_swrf_test_action         0 test action parameter for SWRF 
_swrf_mmon_flush     FALSE Enable/disable SWRF MMON FLushing 
_awr_corrupt_mode     FALSE AWR Corrupt Mode 
_awr_restrict_mode     FALSE AWR Restrict Mode 
_swrf_mmon_metrics     FALSE Enable/disable SWRF MMON Metrics Collection

_swrf_metric_frequent_mode     TRUE Enable/disable SWRF Metric Frequent
Mode Collection 
_awr_flush_threshold_metrics     TRUE Enable/Disable Flushing AWR
Workload Metrics 
_awr_flush_workload_metrics     TRUE Enable/Disable Flushing AWR

Workload Metrics

_awr_disabled_flush_tables  Disable flushing of specified AWR tables 
_swrf_on_disk_enabled     TRUE Parameter to enable/disable SWRF 
_swrf_mmon_dbfus     TRUE Enable/disable SWRF MMON DB Feature Usage 
_awr_mmon_cpuusage     TRUE Enable/disable AWR MMON CPU Usage Tracking 
_swrf_test_dbfus     FALSE Enable/disable DB Feature Usage Testing 
_awr_sql_child_limit 200 Setting for AWR SQL Child Limit 

 

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com  

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 07 2009 - 15:29:08 CST