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

  • From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
  • To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 7 Jan 2009 15:29:08 -0600

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@xxxxxxxxxxxxxxx
 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxxxx
 

Other related posts: