You Asked

Dear Support,
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 19.3.0.0.0 and in a cloud.oracle.com autonomous DB Version 19.5.0.0.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') ;

and Connor said...

First thing I would try is this:

SELECT
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') ;

ie, remove all of the hints. I just tried that on apex.oracle.com on an existing workspace with the HR schema added, and it took 5-10 seconds.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

fail to select from SYS.USER_CONSTRAINTS and SYS.ALL_CONSTRAINTS

Dirk Strack, December 15, 2020 - 12:29 pm UTC

Thanks for the fast response.
Sorry, I can't repeat your solution.
Neither the statement without hints nor the simple

select * from sys.user_constraints

deliver a result. After ca. 10 seconds a blank result region is displayed below the SQL Command.
My workspace name is STRACK_DEV. I try to submit the statements in SQL Workshop / SQL Commands. I have two schemas with installed apex apps.

Dirk Strack, December 15, 2020 - 4:03 pm UTC

Thanks again for directing me to the hints in my queries. I was able to measure the execution time with help of a scheduler job to skill over the 10 seconds time limit that is enforced in the web interface. with the following script, I found out that I had to remove the PARALLEL hint to get good performance,

CREATE MATERIALIZED VIEW MVBASE_REFS
    BUILD DEFERRED
    REFRESH COMPLETE
    ON DEMAND
AS
SELECT /*+ RESULT_CACHE 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') 
;

begin
    dbms_scheduler.create_job(
        job_name => 'INIT_MVIEWS',
        job_type => 'PLSQL_BLOCK',
        job_action => 'begin DBMS_MVIEW.REFRESH(''MVBASE_REFS''); end;',
        enabled => true 
    );
end;
/

Then I looked in the USER_SCHEDULER_JOB_RUN_DETAILS with the following results:
-- no hints -- 49 seconds.
-- with hints RESULT_CACHE PARALLEL USE_MERGE(F FC SC) -- 3 mins 22 seconds.
-- with hints RESULT_CACHE USE_MERGE(F FC SC) hint 12 seconds.

December 17, 2020 - 4:25 am UTC

Nice work

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

fail to select from SYS.USER_CONSTRAINTS and SYS.ALL_CONSTRAINTS

More to Explore

Performance

Performance problem with access to data dictionary views on apex.oracle.com

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

fail to select from SYS.USER_CONSTRAINTS and SYS.ALL_CONSTRAINTS

More to Explore

Performance