Sorry for the length of this. As you'll see in the log, the process hangs at the first sql statement that is gathering table names from sys.all_tab_privs. I tried running this query against the same schema and killed it after 20 minutes. I then tried a simple select against sys.all_tab_privs and got back all results in 1 second, so this pointed to an issue in the query. Was 99.9% sure it was the huge OR list of table_names, generally a list this long kills performance. To fix it, I altered the query to create a temp table of all of the table names and joined sys.all_tab_privs to this table, along with the other where clause criteria found in the original query. It is shown below, and it returned 46 rows in about 1 second. Can this query be updated without a product update?
SELECT privs.TABLE_NAME FROM SYS.ALL_TAB_PRIVS privs join
(select 'ALL_COL_COMMENTS' as table_name from dual
union
select 'ALL_CONS_COLUMNS' as table_name from dual
union
select 'ALL_CONSTRAINTS' as table_name from dual
union
select 'ALL_DIRECTORIES' as table_name from dual
union
select 'ALL_ENCRYPTED_COLUMNS' as table_name from dual
union
select 'ALL_EXTERNAL_LOCATIONS' as table_name from dual
union
select 'ALL_EXTERNAL_TABLES' as table_name from dual
union
select 'ALL_IND_COLUMNS' as table_name from dual
union
select 'ALL_IND_EXPRESSIONS' as table_name from dual
union
select 'ALL_INDEXES' as table_name from dual
union
select 'ALL_JOIN_IND_COLUMNS' as table_name from dual
union
select 'ALL_LOBS' as table_name from dual
union
select 'ALL_MVIEW_LOGS' as table_name from dual
union
select 'ALL_MVIEWS' as table_name from dual
union
select 'ALL_NESTED_TABLES' as table_name from dual
union
select 'ALL_REFS' as table_name from dual
union
select 'ALL_REGISTERED_MVIEWS' as table_name from dual
union
select 'ALL_SEQUENCES' as table_name from dual
union
select 'ALL_SNAPSHOTS' as table_name from dual
union
select 'ALL_SOURCE' as table_name from dual
union
select 'ALL_SYNONYMS' as table_name from dual
union
select 'ALL_TAB_COLUMNS' as table_name from dual
union
select 'ALL_TAB_COLS' as table_name from dual
union
select 'ALL_TAB_COMMENTS' as table_name from dual
union
select 'ALL_TAB_PRIVS' as table_name from dual
union
select 'ALL_TABLES' as table_name from dual
union
select 'ALL_TRIGGER_COLS' as table_name from dual
union
select 'ALL_TRIGGER_ORDERING' as table_name from dual
union
select 'ALL_TRIGGERS' as table_name from dual
union
select 'ALL_USERS' as table_name from dual
union
select 'ALL_VARRAYS' as table_name from dual
union
select 'ALL_VIEWS' as table_name from dual
union
select 'ALL_XML_TAB_COLS' as table_name from dual
union
select 'ALL_IND_PARTITIONS' as table_name from dual
union
select 'ALL_IND_SUBPARTITIONS' as table_name from dual
union
select 'ALL_LOB_PARTITIONS' as table_name from dual
union
select 'ALL_LOB_SUBPARTITIONS' as table_name from dual
union
select 'ALL_PART_INDEXES' as table_name from dual
union
select 'ALL_PART_KEY_COLUMNS' as table_name from dual
union
select 'ALL_PART_TABLES' as table_name from dual
union
select 'ALL_SUBPART_KEY_COLUMNS' as table_name from dual
union
select 'ALL_SUBPARTITION_TEMPLATES' as table_name from dual
union
select 'ALL_TAB_PARTITIONS' as table_name from dual
union
select 'ALL_TAB_SUBPARTITIONS' as table_name from dual
union
select 'ALL_EDITIONS' as table_name from dual
union
select 'ALL_ZONEMAPS' as table_name from dual
union
select 'V_$IM_COLUMN_LEVEL' as table_name from dual) tlist
on privs.table_name = tlist.table_name
WHERE privs.TABLE_SCHEMA='SYS' AND (privs.PRIVILEGE='SELECT' OR privs.PRIVILEGE='READ');
MessageExplorer.log (6 KB)