Suspicious SQL statement

Toad for Oracle - Beta
1

While evaluating a performance report on my DEV server, I came across the following statement:

SELECT 'REF_INTEGRITY' AS TYPE,
       rel.parent_owner,
       rel.parent_table,
       rel.pk_constraint,
       rel.child_owner,
       rel.child_table,
       rel.fk_constraint,
       cc1.column_name pk_column,
       cc2.column_name fk_column,
       cc1.position column_position
  FROM (SELECT c1.owner AS parent_owner,
               c1.table_name AS parent_table,
               c2.r_constraint_name AS pk_constraint,
               c2.owner AS child_owner,
               c2.table_name AS child_table,
               c2.constraint_name AS fk_constraint
          FROM sys.all_constraints c1, sys.all_constraints c2
         WHERE     c1.table_name = :name
               AND c1.owner = :owner
               AND c1.constraint_type IN ('P', 'U')
               AND c2.constraint_type = 'R'
               AND c2.r_constraint_name = c1.constraint_name
               AND c2.r_owner = c1.owner) rel,
       sys.all_cons_columns cc1,
       sys.all_cons_columns cc2
 WHERE     cc1.owner = rel.parent_owner
       AND cc1.constraint_name = rel.pk_constraint
       AND cc2.owner = rel.child_owner
       AND cc2.constraint_name = rel.fk_constraint
       AND cc1.position = cc2.position
UNION ALL
SELECT 'REF_INTEGRITY' AS TYPE,
       rel.parent_owner,
       rel.parent_table,
       rel.pk_constraint,
       rel.child_owner,
       rel.child_table,
       rel.fk_constraint,
       cc1.column_name pk_column,
       cc2.column_name fk_column,
       cc1.position column_position
  FROM (SELECT c1.owner AS parent_owner,
               c1.table_name AS parent_table,
               c2.r_constraint_name AS pk_constraint,
               c2.owner AS child_owner,
               c2.table_name AS child_table,
               c2.constraint_name AS fk_constraint
          FROM sys.all_constraints c1, sys.all_constraints c2
         WHERE     c2.table_name = :name
               AND c2.owner = :owner
               AND c1.constraint_type IN ('P', 'U')
               AND c2.constraint_type = 'R'
               AND c2.r_constraint_name = c1.constraint_name
               AND c2.r_owner = c1.owner) rel,
       sys.all_cons_columns cc1,
       sys.all_cons_columns cc2
 WHERE     cc1.owner = rel.parent_owner
       AND cc1.constraint_name = rel.pk_constraint
       AND cc2.owner = rel.child_owner
       AND cc2.constraint_name = rel.fk_constraint
       AND cc1.position = cc2.position
ORDER BY 4, 10

Is this a statement that TOAD uses?

It stood out because it has an extremely high number of gets. (41,454,722.67 Gets per Exec)
I ran this manually once and it took 3 minutes for one record.

I wouldn't know that the application currently being developed on the DB uses such a statement.

I suspect the code completion of the Toad editor, as it "hung" several times (3-4 times) during the analyzed period.

BR
Dirk

2

Hi Dirk,

Yes, that comes from Code Assist. I believe you can turn off the "Join Suggestions" option to avoid it.

-John

3

Ok, I think I found the option to disable.

Bildschirmfoto 2021-08-05 um 13.56.28
Bildschirmfoto 2021-08-05 um 13.56.28749×553 24.1 KB

Do you have any idea why the query takes over 3 minutes for me to return a result (1 row) for a table? The server is an old 12.1.0.
I would actually like to use the option, but with the response time it's a bit difficult. :wink:

BR
Dirk

4

For me, it's fast. I can try rewriting it today. I've got your email address. If I come up with something that looks faster I'll send you an email and you can test it for me.

One way that you may be able to make it faster is to grant select on DBA_CONS_COLUMNS and DBA_CONSTRAINTS to the user that you're logged in as, then disconnect/reconnect Toad, and Toad should use those views instead. They are usually faster.

5

I tried the DBA_ View, but even with that it takes more than 3 minutes.
With the USER_ Views it is relatively fast (about 900 ms).

But I like to test a customized version.

6

Afternoon Gents,

I'm wondering when you last gathered statistics on the dictionary and on the SYS schema? The following code will do the needful as it gathers stats on SYS, SYSTEM and any other necessary RDBMS schemas.

-- Must have either:
-- SYSDBA  or 
-- (ANALYZE ANY DICTIONARY and ANALYZE ANY) privs.
begin
    dbms_stats.gather_dictionary_stats;
end;

This should be executed fairly regularly since Oracle version 11. Whatever fairly regularly actually means! :grin:

Cheers,
Norm.

7

Sorry, but I'm afraid I've kicked up some dust here for no reason. :crazy_face:

I took a look at the execution plans and noticed that the Optimizer Mode of the instance was set to RULE. I'm sure that is the key factor. I have therefore switched to CHOOSE and tested the queries again.

Here is my result, if the optimizer mode is set to CHOOSE.

Version View Cost Duration [ms]
old ALL_ 338 50
old USER_ 4163 360
new ALL_ 367 150
new USER_ 3138 140

This shows, the current implementation is the fastest.

BUT, after my first set of tests I did a

begin
    dbms_stats.gather_dictionary_stats;
end;

(I've never done this manually on this db :wink: )

And now I get the following results:

Version View Cost Duration [ms]
old ALL_ 338 35000
old USER_ 4163 370
new ALL_ 367 150
new USER_ 3138 140
1 Like