Wednesday, April 28, 2010

Detect Unindexed Foreign Keys

Unindexed foreign keys continue to be a prime performance bottleneck.

Here is a script that will run through the foreign keys in a schema and display them all and the index that is associated with them (or not) and indicate whether the foreign key requires an index.

set lines 140
set pages 1000

col status for a6
col child_table for a24
col fk_columns for a27
col indexed_columns for a27
col parent_table for a24
col pk_columns for a27

select decode( b.table_name, NULL, '****', 'ok' ) Status
     , a.table_name                               Child_Table
     , c.table_name                               Parent_Table
     , c.columns                                  PK_Columns
     , a.columns                                  FK_Columns
     , b.columns                                  Indexed_Columns
from
( select a.table_name
       , a.constraint_name
       , b.r_constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name
          , a.constraint_name
          , b.r_constraint_name ) a,
( select table_name
       , index_name
       , max(decode(column_position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(column_position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_ind_columns
   group by table_name
          , index_name ) b,
( select a.table_name
       , a.constraint_name
       , max(decode(position, 1,'  '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 2,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 3,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 4,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 5,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 6,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 7,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 8,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position, 9,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,10,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,11,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,12,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,13,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,14,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,15,', '||rpad(substr(column_name,1,25),25),NULL)) ||
         max(decode(position,16,', '||rpad(substr(column_name,1,25),25),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P' , 'U' )
   group by a.table_name
          , a.constraint_name ) c
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  and a.r_constraint_name = c.constraint_name
order by a.table_name
/

You will need quite a wide screen to incorporate all the data. I use 140 character width.

How to disable the DBMS_SCHEDULER

I discovered this after searching all over the web and am putting it here so that I do not have to do that again.

To turn the scheduler off use

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE')

Note that the TRUE is not a BOOLEAN but text.

To check the status

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

To turn the scheduler back on again

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE')

Creating Object DDL

Here is some handy code to create object DDL using DBMS_METADATA.

There are 2 scripts, I shall call them the calling script and the running script.

The calling script (which I called get_obj.sql) is as follows

doc
 Possible objects to get DDL for are :-
  CONSTRAINT
  DB_LINK
  DIRECTORY
  FUNCTION
  INDEX
  MATERIALIZED_VIEW
  MATERIALIZED_VIEW_LOG
  PACKAGE_SPEC
  PACKAGE_BODY
  PROCEDURE
  SEQUENCE
  SYNONYM
  TABLE
  TRIGGER
  TYPE_SPEC
  TYPE_BODY
  VIEW
#

accept Object prompt  'Enter Object type -> '
accept OwnerIN prompt   "Enter Schema Owner (default %) -> "
accept ObjNameIN prompt "Enter Object Name (default %) -> "

set verify off 
set head off
set feed off
set pages 0
set trims on
set term off
set lines 200

col spool_on      for a200
col select_clause for a200
col from_clause   for a200
col where_clause  for a200
col and_clause    for a200
col object_clause for a200
col spool_off     for a200

col object_type_dir  new_value dir_and_type  noprint
col object_type_dirs new_value dir_and_types noprint

col OwnerOUT new_value Owner noprint
col ObjNameOUT new_value ObjName noprint

select nvl('&OwnerIN','%') OwnerOUT , nvl('&ObjNameIN','%') ObjNameOUT from dual;

SELECT 
   CASE upper('&Object')
  WHEN 'MATERIALIZED_VIEW'     THEN 'mview'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'mview_log'
  WHEN 'PACKAGE_SPEC'          THEN 'package'
  WHEN 'TYPE_SPEC'             THEN 'type'
  ELSE                              lower('&Object')
   END        object_type_dir 
FROM      dual;

SELECT 
  CASE '&dir_and_type'
  WHEN 'index'                 THEN 'indexes'
  WHEN 'package_body'          THEN 'package_bodies'
  WHEN 'type_body'             THEN 'type_bodies'
  ELSE                              '&dir_and_type'||'s'
  END         object_type_dirs
FROM     dual;

spool get_sp_obj.sql

SELECT     'spool do_get_sp_obj.sql'                                                spool_on
 ,  q'[SELECT '@@get_ddl '||upper('&Object')||' '||]'
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[log_owner||' '||log_table||' '||log_owner||'/]'||'&dir_and_types'||q'[ '||master]'
  WHEN 'DB_LINK'               THEN q'[owner||' '||db_link||' '||owner||'/]'||'&dir_and_types'||q'[/'||db_link]'
  WHEN 'FUNCTION'              THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PROCEDURE'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_SPEC'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'PACKAGE_BODY'          THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  WHEN 'SEQUENCE'              THEN q'[sequence_owner||' '||sequence_name||' '||sequence_owner||'/]'||'&dir_and_types'||q'[ '||sequence_name]'
  WHEN 'TYPE_SPEC'             THEN q'[owner||' '||type_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||type_name]'
  WHEN 'TYPE_BODY'             THEN q'[owner||' '||object_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||object_name]'
  ELSE                              q'[owner||' '||]'||'&dir_and_type'||q'[_name||' '||owner||'/]'||'&dir_and_types'||q'[ '||]'||'&dir_and_type'||'_name'
    END                                                                      select_clause
 ,  'from '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN 'dba_objects'
  WHEN 'PROCEDURE'             THEN 'dba_objects'
  WHEN 'PACKAGE_SPEC'          THEN 'dba_objects'
  WHEN 'PACKAGE_BODY'          THEN 'dba_objects'
  WHEN 'TYPE_SPEC'             THEN 'dba_types'
  WHEN 'TYPE_BODY'             THEN 'dba_objects'
  ELSE                              'dba_'||'&dir_and_types'
    END                                                                      from_clause
 , 'where '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN 'log_owner'
  WHEN 'SEQUENCE'              THEN 'sequence_owner'
  ELSE                              'owner'
    END
 || q'[ like upper('&Owner')]'                                                    where_clause
 , 'and '
 || CASE '&Object'
  WHEN 'FUNCTION'              THEN q'[object_type = 'FUNCTION']'
  WHEN 'PROCEDURE'             THEN q'[object_type = 'PROCEDURE']'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_type = 'PACKAGE']'
  WHEN 'PACKAGE_BODY'          THEN q'[object_type = 'PACKAGE BODY']'
  WHEN 'TYPE_BODY'             THEN q'[object_type = 'TYPE BODY']'
  ELSE                              '1 = 1'
    END                                                                     and_clause
 , 'and '
 || CASE '&Object'
  WHEN 'MATERIALIZED_VIEW_LOG' THEN q'[master like upper('&ObjName')]'
  WHEN 'DB_LINK'               THEN q'[db_link like upper('&ObjName')]'
  WHEN 'FUNCTION'              THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PROCEDURE'             THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_SPEC'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'PACKAGE_BODY'          THEN q'[object_name like upper('&ObjName')]'
  WHEN 'TYPE_SPEC'             THEN q'[type_name like upper('&ObjName')]'
  WHEN 'TYPE_BODY'             THEN q'[object_name like upper('&ObjName')]'
  ELSE                              '&dir_and_type'||q'[_name like upper('&ObjName')]'
    END
 || ';'                                                                     object_clause
 ,  'spool off'                                                             spool_off
FROM    dual
/
spool off

@@get_sp_obj.sql

@@do_get_sp_obj.sql

set verify on
set head on
set pages 60
set lines 140
set term on
set feed 5

This script as you can see on line 72 calls the running script called get_ddl.sql

Here is get_ddl.sql

set lines 32767
set pages 0
set head off
set feed off
set trims on
set verify off
set term off
set long 5000000

col text for a32767

!if [ ! -d &4 ]; then mkdir -p &4;fi

spool &4/&5..sql

select dbms_metadata.get_ddl('&1','&3','&2') text from dual
/

prompt /

spool off

Load both of these scripts into the same directory and call the calling script from SQL*Plus.

For example

@get_obj

You will be prompted to input some object_types and possibly names and each object creation script will be spooled to its individual file in a directory structure that matches the object type.
For example all the table definitions will be in a directory named 'tables'.

These scripts can be handy if you need to do a lot of reconfiguring or simply wish to have a record of schema objects.