Loading constraints

I was trying out your new constraint item
image

but toad still locked up and I needed to kill it . The pop up was frozen for a few minutes.
image

Hi Marc,

If you turn on spool SQL, you should be seeing it running this:

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 sys_context('USERENV', 'CURRENT_SCHEMA') in (c.r_owner, c.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.owner = sys_context('USERENV', 'CURRENT_SCHEMA')
                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     pk_cons.owner in (ref_cons.owner, ref_cons.r_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;

Can you try that SQL in the editor and let me know how long it takes to run?

Does it help to add this in each of the "WITH" subqueries? Seems like this ought to be there but for me it did not make a noticeable difference.

        and c.table_name = cc.table_name

Is this faster? It seems to have a slightly better explain plan for me.

with cons as
  (select c.owner, c.constraint_name, c.constraint_type, c.table_name, c.r_owner, c.r_constraint_name, cc.column_name, cc.position
       from   dba_constraints c, dba_cons_columns cc
       where  (Not ((c.constraint_name like 'BIN$%==$0') and length(c.constraint_name) = 30))
       and
               (    c.constraint_type = 'R' 
               and ((c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')) or (c.r_owner = sys_context('USERENV', 'CURRENT_SCHEMA'))) 
               and c.owner = cc.owner 
               and c.constraint_name = cc.constraint_name
               )
       or     (    c.constraint_type in ('P', 'U') 
               and c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')
               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,
         pk_cons.owner              as 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 cons ref_cons,
         cons pk_cons
   WHERE  ref_cons.constraint_type = 'R' 
    and   pk_cons.constraint_type in ('P', 'U') 
    and   pk_cons.owner in (ref_cons.r_owner, ref_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;

I attached both explains, 1st qry "with_ref_const", 2nd "with_cons",

with_ref_const took 7 seconds
with_cons took 1 second

Is this the same qry that is used in the popup. The popup freeze was a non dba account. I changed both qrys to use the non dba schema(sys_context('USERENV', 'CURRENT_SCHEMA')) and the timing was the same, around 7 and 1 seconds
with_cons.txt (14.8 KB)
WITH_ ref_cons.txt (52.1 KB)

Thanks Marc.

the ref_cons version is what's in the popup now. The cons version is a rewrite that I did yesterday and may change the query to if it continues to test well.

-John

Happy to run more queries. Does this fix the freeze? The query finished, the popup some times freeze toad.

Also, not sure if it really is a freeze or not. Yesterday the popup was really frozen. Could not get it off any screen. Even showed on the desktop. But sometimes, the hour glass is going and I can eventually close it.

Some users have reported a delay while the constraints query runs. If they hit ESC, it cancels just fine. Or if they let it finish, all is well (and then the constraint query is not run again since the data is cached)

I haven't heard of the popup not going away before though, or Toad freezing completely even after the query is done. Maybe there is still something else going on in there.

In another thread you mentioned using the 19c client with an 11g database. Is this still the case? If so that is a known issue and will lead to freezing.

i stopped using my 19c client, only 11g