I have a performance problem specific to apex.oracle.com where I have installed the 'HR data' sample schema
and then try to execute a simple select on a data dictionary view in SQL Commands:
select * from SYS.USER_CONSTRAINTS;
returns no rows. (because of timeout!?)
Explain returns ORA-01039: insufficient privileges on underlying objects of the view
What works is creating materialized views on queries that access the data dictionary views. Those views were tuned to run fast in my local Oracle Virtual Box with DB Version 184.108.40.206.0 and in a cloud.oracle.com autonomous DB Version 220.127.116.11.0 environment.
But on apex.oracle.com the refresh in a scheduler job takes ca. 10 minutes for the following query.
What can I do to make them run fast again?
SELECT /*+ RESULT_CACHE PARALLEL USE_MERGE(F FC SC) */
F.TABLE_NAME, F.OWNER, F.CONSTRAINT_NAME, FC.COLUMN_NAME, FC.POSITION,
SC.COLUMN_ID, SC.NULLABLE, F.DELETE_RULE, F.DEFERRABLE, F.DEFERRED,
F.STATUS, F.VALIDATED, F.R_CONSTRAINT_NAME, F.R_OWNER
FROM SYS.USER_CONSTRAINTS F
JOIN SYS.USER_CONS_COLUMNS FC ON F.OWNER = FC.OWNER AND F.CONSTRAINT_NAME = FC.CONSTRAINT_NAME AND F.TABLE_NAME = FC.TABLE_NAME
JOIN SYS.USER_TAB_COLS SC ON SC.TABLE_NAME = F.TABLE_NAME AND SC.COLUMN_NAME = FC.COLUMN_NAME AND SC.HIDDEN_COLUMN = 'NO'
AND F.CONSTRAINT_TYPE = 'R'
AND F.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') ;