Data Dictionary Query

We have been working through all of the SQL with high buffer-gets in our Banner databases and we came across a data-dictionary query that is performing 300 million per day:

SELECT a.column_name
 FROM all_tab_columns a, all_ind_columns b
 WHERE a.table_name = b.table_name
 AND a.column_name = b.column_name
 AND a.owner = b.index_owner
 AND b.table_name = :1
 AND b.index_owner = :1
 AND b.index_name =
 (SELECT constraint_name
 FROM all_constraints
 WHERE table_name = :1
 AND owner = :1
 AND constraint_type = :1
 AND ROWNUM = 1)

So, we figured out that this query was coming from a forms library file (*.pll) and it’s used on a few forms that support user-defined columns.  This is similar to flex-fields in ERP.  Unfortunately, this query uses rule-based optimization so it can be a challenge to tune.  After some research, it was determined that this query could be rewritten to force cost-based optimization with a slight tweak:

SELECT a.column_name
 FROM all_tab_cols a, all_ind_columns b
 WHERE a.table_name = b.table_name
 AND a.column_name = b.column_name
 AND a.owner = b.index_owner
 AND b.table_name = :1
 AND b.index_owner = :1
 AND b.index_name =
 (SELECT constraint_name
 FROM all_constraints
 WHERE table_name = :1
 AND owner = :1
 AND constraint_type = :1
 AND ROWNUM = 1)

This query now performs 10,000 buffer-gets per run!  At least we can tune it knowing the cost.  Next, we added some hints:

SELECT /*+ NO_CPU_COSTING */ a.column_name
 FROM all_tab_cols a,
 all_ind_columns b
 WHERE a.table_name = b.table_name
 AND a.column_name = b.column_name
 AND a.owner = b.index_owner
 AND b.table_name = :1
 AND b.index_owner = :1
 AND b.index_name = ANY (SELECT constraint_name
 FROM all_constraints
 WHERE table_name = :1
 AND owner = :1
 AND constraint_type = 'P'
 AND ROWNUM = 1)

We finish this off by adding some sys stats:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

At last!  This new, improved query creates 8 buffer-gets per execution.  A pretty good improvement for such a tiny fix.  We updated the ‘.pll’, recompiled all the forms, and we’re ready to move on to the next challenge.

Advertisement