-
Titel
Oracle Database monitoring causes high CPU workload on the DB host when querying v$segstat -
Beschreibung
Foglight Oracle Database Monitoring causes high CPU utilization on the monitored Database Host -
Ursache
Two queries used by Foglight are found to be causing high CPU on the DB host
Query 1:
SELECT *FROM (SELECT *FROM (SELECT obj#, statistic_name, valueFROM v$segstatWHERE statistic_name IN('logical reads','physical reads','db block changes','physical writes'))PIVOT(SUM(value) FOR statistic_name IN('logical reads', 'physical reads', 'db block changes', 'physical writes'))ORDER BY 2 DESC)WHERE rownum <= 500Query 2:
SELECT owner || '.' || object_name segment_name, VALUE seg_wait_timeFROM v$segment_statisticsWHERE statistic_name = 'row lock waits'AND VALUE > 0AND rownum <= 500
There is a known issue with Oracle querying V$SEGSTAT View. Please refer to Oracle document 1532624.1 and 21050285.8 on Oracle metalink.
-
Lösung
RESOLUTION 1 - Query 1:
This query comes from Foglight SQL PI monitoring. As a workaround, this query can be disabled by adding a new hidden ASP (Agent Status Property) value called "spiecOlapSegmentStatisticsCollectionDisable" that can be used to disable the collection.
A hotfix can be applied to implement the change.
By disabling this statement the values / metrics under
‘Object I/O’ node (in SQL PI dashboard) and its direct children nodes, ‘DB Block Changes’, ‘Logical Reads’, ‘Physical Reads’ and ‘Physical Writes’ columns will be always 0.
WORKAROUND
Please contact Support for a hotfix with a jar file and groovy script for this issue. A hotfix_readme text file is included with each jar file with instructions to install the hotfix.
STATUS
This issue has been logged as FOM-423 to include the new spiecOlapSegmentStatisticsCollectionDisable ASP value in the 5.9.7.20 and higher releases of the Oracle cartridge.
Once released the collection will be able to disabled by running the following script from Administration | Tooling | Script Console.
/** * This software is confidential. Quest Software Inc., or one of its subsidiaries, has supplied this software to you * under terms of a license agreement, nondisclosure agreement or both. * * You may not copy, disclose, or use this software except in accordance with those terms. * * Copyright 2017 Quest Software Inc. ALL RIGHTS RESERVED. * * QUEST SOFTWARE INC. MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE, EITHER EXPRESS OR * IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, * OR NON-INFRINGEMENT. QUEST SOFTWARE SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, * MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. */ package system._dbwc_mssql_GA_Database_spi.scripts; org.apache.commons.logging.Log log = org.apache.commons.logging.LogFactory.getLog("script." + functionHelper.getFunctionId()); /** * This function return the instance name. * Required inputs: * aInstanceName - String. * Return: * res - String */ def res = null; if(aHostName != null) { res = aHostName; if(!"DEFAULT_INSTANCE".equals(aInstanceName)) { res += "-" + aInstanceName; } } return res;RESOLUTION 2 - Query 2:
This query comes from 'Top Blocked Object' monitoring in Foglight.
WORKAROUND
Please see KB 146220 for details on disabling the Top Blocked Objects collection.
STATUS
This known issue in Oracle can be solved by restarting the monitored database or by applying a Database patch / Database upgrade.
-
Fehler-ID
FOM-423