- 3,708,780 Users
- 2,241,126 Discussions
- 7,840,613 Comments
OEM 13.2 other collected metrics -GRANT ANY/SELECT PRIVILEGES
We are using OEM 13.2 and have deployed the provided database instance template to our production servers.
That includes a monitor for SQL Application Wait Time.
The warning and critical thresholds on that metric were very low so we increased them.
Now one of the SQLs being reported on all DB instances is a sql that starts
WITH max1AS (SELECT :1 AS select_tab,:2 AS select_privFROM dual),users_with_dba_privsAS (SELECT DISTINCT child user_name, con_idFROM (SELECT granted_role parent,grantee child,con_idFROM cdb_role_privs) rWHERE r.child IN (SELECT usernameFROM cdb_users uWHERE u.con_id = r.con_id)START WITH parent = 'DBA'CONNECT BY parent = PRIOR child)SELECT distinct v.name,res.priv,res.path,res.cFROM (SELECT *FROM (SELECT 'select_any_table' priv,Substr(Sys_connect_by_path(c, '->'), 3, 512) path,...
I was able to track down this code which is present in /u01/oemagent/agent_18.104.22.168.0/plugins/oracle.sysman.db.agent.plugin_22.214.171.124.0/metadata/databases.xmlp
It starts on line 13872 under the comment <!-- privileges -->
Also found a MOS document related to this SQL statement Query for "Granting SELECT ANY TABLE privilege" Database Policy Consumes Very High CPU (Doc ID 888031.1) for OEM 10G and 11G
So we are seeing the same problem in OEM 13.2 for the same statement and dont see that the bug was resolved on the old ones.
In the OEM UI, this query appears with the label GRANT ANY/SELECT PRIVILEGES and as per the template , it is disabled.
However, in our environments, it is still running.
I have removed it from the db instance template, then redeployed the template with replace all metrics.
It is still running and other metrics report high CPU at the same time every 24 hours and the SQL Execution Applicaiton Wait time metric specifically reports the SQL id for this SQL.
In the MOS document, it mentioned a workaround to add DISABLED="TRUE" on the line for that statement.
Did that change on Monday afternoon, but yesterday it still reported high CPU and high SQL Execution Application wait times for that same SQL
I create a new db instance template in OEM with NO metrics and NO other collected items.
Deployed that template to the databases with replace all metrics with template
Then redeployed the db instance template which no longer contains the GRANT ANY/SELECT PRIVILEGES metric
When viewing all metrics on the target db instances, it is still listed in the other collected metrics.
My conclusions at the moment are ...
1) "Other collected metrics" in the DB Instance which indicate DISABLED in the UI are not, they are still running
2) There seems no way to add/remove other collected metrics from an agent, even if removed from the template and using replace all metrics in the template options
3) The workaround suggested for 10G/11G no longer works
4) setting DISABLED="TRUE" in the database.xmlp on the agent itself doesnt seem to make any difference either
We are also seeing other SQLs (DB_FEATURE_USAGE related) taking 40 minutes every 24 hours. These are also listed in the same db instance template so possible we will have same issues with those.
Can anyone confirm that the "other collected items" UI just does not work on 13.2 OEMCC and the metric are collected regardless of disabled status or whether the agent xml is changed directly ?