Query for "Granting SELECT ANY TABLE privilege" Database Policy Consumes Very High CPU (Doc ID 888031.1)

Last updated on NOVEMBER 18, 2014

Applies to:

Enterprise Manager for Oracle Database - Version 10.2.0.5 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 20-Oct-2011***


Symptoms

Using 10.2.0.5 Agent to monitor many database targets on an AIX 5.3 machine.

Immediately after the agent is started, the cpu usage in the machine goes up. The host performance page in the Grid console shows that the cpu is almost 100%.
The CPU usage remains high as long as the Agent is started and is normal once the agent is stopped.
Specifically for the AIX host, all the suggestions <Note 552404.1> have been followed.

-  The output from topas shows :

Name PID CPU% PgSp Owner

oracle 4415628 11.8 50.7 oracle
oracle 1564696 11.5 34.1 oracle
oracle 5038170 11.1 51.0 oracle
oracle 4436096 11.0 50.8 oracle
oracle 4051198 3.4 18.8 oracle
oracle 3834086 3.3 6.2 oracle
.........

-  Checking the process details :

ps -ef | grep 4415628

oracle 4415628 1 120 10:32:51 - 16:33 oracleORCL (LOCAL=NO)

-  Checking the details of this OS pid in the ORCL database:

SQL> SELECT s.sid, p.spid, s.osuser, s.program, s.SQL_ID
           FROM v$process p, v$session s
           WHERE p.addr = s.paddr and p.spid=4415628;

SID SPID         OSUSER           PROGRAM                                           SQL_ID
----------           ---------             ----------------------------------------   -------------
99 4415628    oracle                 emagent@orcl (TNS V1-V3)         51bbkcd9zwsjw

-  The SQL code which is being run by the agent session is :

/* OracleOEM */ with max1 as
(select :1 as select_tab,:2 as select_priv
from dual)
select 'select_any_table', substr(SYS_CONNECT_BY_PATH(c, '->'),3,512) path, c
from ( select null p, name c
from system_privilege_map
where name = 'SELECT ANY TABLE'
union
select granted_role p, grantee c
from dba_role_privs
union
select privilege p, grantee c
from dba_sys_privs )
where ((c = 'PUBLIC') or (exists (select 'w' from dba_users
where username=c)) )
and rownum <= decode((select select_tab from max1),'-1',2147483647,(select
select_tab from max1))
start with p is null
connect by p = prior c
union
select 'select_privilege', substr(SYS_CONNECT_BY_PATH(c, '->'),3,512) path, c
from ( select null p, view_name c
from dba_views
where view_name like 'DBA_%'
union
select granted_role p, grantee c
from dba_role_privs
union
select table_name p, grantee c
from dba_tab_privs
where privilege = 'SELECT' )
where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c)))
and rownum <= decode((select select_priv from max1),'-1',2147483647,(select select_priv from
max1))
start with p is null
connect by p = prior c

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms