Intellisense stuck at "Loading Constraints" v 15.1

Toad for Oracle
1

When using Intellisense CTRL+. for something like a table / partial proc name in Toad 15.1.113.1379, it gets stuck on "Loading Constraints", this is particularly bad on instances with lots of schemas.

If you spool the SQL, the problem is obvious, the data dictionary query for FK constraints is NOT filtering for the current schema. So this can be 100000s of FKs on large instances.

-- Currently
WITH
    ref_cons
    AS
        (Select c.owner,
                c.constraint_name,
                c.table_name,
                c.r_owner,
                c.r_constraint_name,
                cc.position,
                cc.column_name
           FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc
          WHERE     c.constraint_type = 'R'
                AND c.owner = cc.owner
                AND c.constraint_name = cc.constraint_name),
    pk_cons
    AS
        (Select c.owner,
                c.constraint_name,
                c.table_name,
                cc.position,
                cc.column_name
           FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc
          WHERE     c.constraint_type IN ('P', 'U')
                AND c.owner = cc.owner
                AND c.constraint_name = cc.constraint_name)
  SELECT 'REF_INTEGRITY'             AS type,
         ref_cons.owner,
         ref_cons.table_name,
         ref_cons.column_name,
         ref_cons.constraint_name,
         ref_cons.r_owner,
         pk_cons.table_name          AS r_table_name,
         pk_cons.column_name         AS r_column_name,
         pk_cons.constraint_name     AS r_constraint_name
    FROM pk_cons, ref_cons
   WHERE     ref_cons.r_owner = pk_cons.owner
         AND ref_cons.r_constraint_name = pk_cons.constraint_name
         AND ref_cons.position = pk_cons.position
ORDER BY ref_cons.owner,
         ref_cons.table_name,
         ref_cons.constraint_name,
         ref_cons.position;

Suggested fix :

/* 
 Query filters below don't use "USER" function for filtering, as it is slow to
 evaluate for each row in where clause.  It could be used if wrapped in subquery 
 to leverage cursor caching. E.g. AND c.owner = (select USER from dual) 
*/

WITH
    ref_cons
    AS
        (Select c.owner,
                c.constraint_name,
                c.table_name,
                c.r_owner,
                c.r_constraint_name,
                cc.position,
                cc.column_name
           FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc
          WHERE     c.constraint_type = 'R'
                AND c.owner = cc.owner
                AND c.constraint_name = cc.constraint_name
                AND sys_context('USERENV', 'SESSION_USER') in (c.r_owner, c.owner) -- Constraint owner is USER or referenced owner is USER  
                ),
    pk_cons
    AS
        (Select c.owner,
                c.constraint_name,
                c.table_name,
                cc.position,
                cc.column_name
           FROM sys.DBA_CONSTRAINTS c, sys.DBA_CONS_COLUMNS cc
          WHERE     c.constraint_type IN ('P', 'U')
                AND c.owner = cc.owner
                AND c.constraint_name = cc.constraint_name
                AND c.owner = sys_context('USERENV', 'SESSION_USER')
                )
  SELECT 'REF_INTEGRITY'             AS type,
         ref_cons.owner,
         ref_cons.table_name,
         ref_cons.column_name,
         ref_cons.constraint_name,
         ref_cons.r_owner,
         pk_cons.table_name          AS r_table_name,
         pk_cons.column_name         AS r_column_name,
         pk_cons.constraint_name     AS r_constraint_name
    FROM pk_cons, ref_cons
   WHERE     ref_cons.r_owner = pk_cons.owner
         AND ref_cons.r_constraint_name = pk_cons.constraint_name
         AND ref_cons.position = pk_cons.position
ORDER BY ref_cons.owner,
         ref_cons.table_name,
         ref_cons.constraint_name,
         ref_cons.position;
2

Hey Paul,

How long is the query taking to run in your instance? We intentionally made this change so that the constraints would be loaded only once for the duration of the session, as opposed to once each time the popup appears. Maybe there is a middle ground approach we could take.

-John

3

Hi John

One of our instances hosts 118 schemas (it's a VERY powerful box), some others maybe 30-50 schemas. Even so, on the behemoth it can take > 30 seconds as schemas will have 1000s of FKs each, so I cancel. It seems overkill to me to cache all the FKs from all schemas (both memory and time overhead). Fine if you have a handful of schemas, but modern hardware can happily host scores of schemas.

4

ok, thanks for the info.

5

Would loading from ALL_CONSTRAINTS/ALL_CONS_COLUMNS be a helpful option?

if we limit it as you suggested above, you wouldn't be able to reference join info in other schemas.

6

Hi John

ALL_* is considerably slower. I still think you'd be better off only loading constraints for the schema in question, so either where the schema is the owner of the FK or schema is the owner of the table whose PK is referenced. I don't see why you'd want to load all of an instance's references. There could be hundreds of thousands and you may only be logging into one of many schemas all day - so you are caching a huge amount of data you may never need.

In my eyes, and it may be simplistic, you keep a record of schemas you've loaded refs for, and the data is indexed by the combination of the schemas involved (FK_SCHEMA, PK_SCHEMA). As users log into another, you update the refs for that combination, as there may be overlaps. For example, if there's a ref across schemas, say SCHEMA_A to SCHEMA_B, because you had a session for SCHEMA_A open, and later the user opened SCHEMA_B, you update data for any record on that indexed record where (FK_SCHEMA = SCHEMA_B, PK_SCHEMA => *) or (FK_SCHEMA = *, PK_SCHEMA => SCHEMA_B). So your query predicate would always be....

WHERE ref_cons.r_owner = pk_cons.owner
AND ref_cons.r_constraint_name = pk_cons.constraint_name
AND ref_cons.position = pk_cons.position
AND (sys_context('USERENV', 'SESSION_USER') in (ref_cons.r_owner, pk_cons.owner)) -- Add this line

Paul

7

Hi Paul,

I can add an option to limit to the current user. The reason that I don't want to just do that by default is that I know that a lot of users will log in as a user with no objects and then work on objects in other schemas.

-John

8

Hi John

In which case they'd have to type the owner name DOT object, so why not load the corresponding owner data when they first type the owner name? That to me seems more sensible than loading every schema's objects / refs just because someone MAY need it.

Anyway, perhaps my viewpoint is simplistic here.

Paul

9

We used to run queries for contstraint info (for just the tables in the SQL in the editor) every time the dialog popped up. This was causing some other problems which we were trying to fix by loading all FK constraints one time.

Anyway, next beta will have this option that will do what you are asking for: loads the constraints for the current schema instead of the entire DB.

image
image790×728 59 KB

1 Like