Customer Login

Click Here

Free Trial

Click Here

Request a Demo

Click Here

Join Our Tip Email

Click Here

Tuning Tips - Holiday Prep

Holiday Prep

Holiday Prep and even new releases of an application the steps below should be done.

Each check below is important because they help reduce unnecessary waits in the Oracle and other databases. At Tuning Ace we have a portal tool kit that runs all of the checks below plus 100s of other checks. Please consider a trial of our tool kit and see what it will find all geared to giving you the solution to better Oracle and now Postgres (Aurora) databases.

The first check for missing foreign keys indexes is very important because of how Oracle validated the foreign keys.

Check 1A - Foreign Key data types should match

Believe it or not, Oracle will perform the join across data types. We have found several customers that were joining tables across different data types. Once fixed the SQL ran 1000s of times faster

SQL to that returns columns that do not match in data type. 

 
REM FOR SQL*Plus
REM Returns columns that do not match in data type

col OWNER Format a30
col TABLE_NAME Format a30
col REF_COLUMN_NAME Format a30
col REF_DATA_TYPE Format A20
col REF_OWNER Format a30
col PK_TABLE_NAME Format a30
col PK_COLUMN_NAME Format a30
col PK_DATA_TYPE Format a30

SELECT CON.OWNER
     , CON.TABLE_NAME
     , RefCols.COLUMN_NAME REF_COLUMN_NAME
     , RefColsType.DATA_TYPE REF_DATA_TYPE
     , PkCols.OWNER REF_OWNER
     , PkCols.TABLE_NAME PK_TABLE_NAME
     , PkColsType.COLUMN_NAME PK_COLUMN_NAME
     , PkColsType.DATA_TYPE PK_DATA_TYPE
from DBA_CONSTRAINTS CON
, DBA_CONS_COLUMNS RefCols , DBA_TAB_COLUMNS RefColsType
, DBA_CONS_COLUMNS PkCols  , DBA_TAB_COLUMNS PkColsType
where CON.CONSTRAINT_TYPE = 'R'
and   CON.OWNER = 'ATG_PUB'
and   CON.OWNER = RefCols.OWNER
and   CON.CONSTRAINT_NAME     = RefCols.CONSTRAINT_NAME
and   RefColsType.OWNER      = RefCols.OWNER
and   RefColsType.TABLE_NAME  = RefCols.TABLE_NAME
and   RefColsType.COLUMN_NAME = RefCols.COLUMN_NAME
and   Con.R_OWNER             = PkCols.OWNER
and   Con.R_CONSTRAINT_NAME   = PkCols.CONSTRAINT_NAME
and   PkCols.OWNER            = PkColsType.OWNER
and   PkCols.TABLE_NAME       = PkColsType.TABLE_NAME
and   PkCols.COLUMN_NAME      = PkColsType.COLUMN_NAME
and   RefColsType.DATA_TYPE   != PkColsType.DATA_TYPE
Check 1B - Foreign Key column length should match

This is very important that the child table not be smaller than the parent table. This makes sense, but happens more often than many realize. For example, if the max length of the Order Table Primary Key is 20 characters and the child Items table is 10 characters.  The application will run just fine until the Order Numbers grows to 11 characters.  The last thing anyone wants is for the application to stop taking orders during the holiday. 

 
REM FOR SQL*Plus
REM Returns columns that do not match in data type

col OWNER Format a30
col TABLE_NAME Format a30
col REF_COLUMN_NAME Format a30
col REF_DATA_TYPE Format A20
col REF_OWNER Format a30
col PK_TABLE_NAME Format a30
col PK_COLUMN_NAME Format a30
col PK_DATA_TYPE Format a30

SELECT CON.OWNER
     , CON.TABLE_NAME
     , RefCols.COLUMN_NAME REF_COLUMN_NAME
     , RefColsType.DATA_TYPE REF_DATA_TYPE
     , PkCols.OWNER REF_OWNER
     , PkCols.TABLE_NAME PK_TABLE_NAME
     , PkColsType.COLUMN_NAME PK_COLUMN_NAME
     , PkColsType.DATA_TYPE PK_DATA_TYPE
from DBA_CONSTRAINTS CON
, DBA_CONS_COLUMNS RefCols , DBA_TAB_COLUMNS RefColsType
, DBA_CONS_COLUMNS PkCols  , DBA_TAB_COLUMNS PkColsType
where CON.CONSTRAINT_TYPE = 'R'
and   CON.OWNER = 'ATG_PUB'
and   CON.OWNER = RefCols.OWNER
and   CON.CONSTRAINT_NAME     = RefCols.CONSTRAINT_NAME
and   RefColsType.OWNER      = RefCols.OWNER
and   RefColsType.TABLE_NAME  = RefCols.TABLE_NAME
and   RefColsType.COLUMN_NAME = RefCols.COLUMN_NAME
and   Con.R_OWNER             = PkCols.OWNER
and   Con.R_CONSTRAINT_NAME   = PkCols.CONSTRAINT_NAME
and   PkCols.OWNER            = PkColsType.OWNER
and   PkCols.TABLE_NAME       = PkColsType.TABLE_NAME
and   PkCols.COLUMN_NAME      = PkColsType.COLUMN_NAME
and (
       RefColsType.DATA_LENGTH            != PkColsType.DATA_LENGTH
    or nvl(RefColsType.DATA_PRECISION,-1) != nvl(PkColsType.DATA_PRECISION,-1)
    or nvl(RefColsType.DATA_SCALE,-1)     != nvl(PkColsType.DATA_SCALE,-1)
)
Check 1C - Foreign Key columns should be indexed

The Oracle Documentation explains how Foreign Keys are validated when the Foreign Key columns are indexed or not. When the index is missing, Oracle has to put an exclusive lock on the table while the foreign key is validated. This basically means that your transactions becomes serialized and Oracle will not allow multiple inserts/updates to happen at the same time. In all versions of Oracle documentation states:

Concurrency Control, Indexes, and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

For more information on how Oracle validates Foreign Keys when they are indexed and not click here: Oracle Doc Reference

This is just one of the over 500 of checks our Performance Suite does for you. The missing Foreign Key index check will generate you the SQL to create the missing Foreign Key index. Consider Trial

Verify the install Using update-alternatives --display java

Use the following command to see what is install for java

update-alternatives --display java

This will display something like

update-alternatives --display java
java - status is manual.
link currently points to /usr/lib/jdk/jdk1.6.0_45/bin/java
/usr/lib/jvm/jre-1.4.2-gcj/bin/java - priority 1420
slave keytool: /usr/lib/jvm/jre-1.4.2-gcj/bin/keytool
slave rmiregistry: /usr/lib/jvm/jre-1.4.2-gcj/bin/rmiregistry
slave jre_exports: /usr/lib/jvm-exports/jre-1.4.2-gcj
slave jre: /usr/lib/jvm/jre-1.4.2-gcj
/usr/lib/jdk/jdk1.6.0_38/bin/java - priority 10
slave keytool: (null)
slave rmiregistry: (null)
slave jre_exports: (null)
slave jre: (null)
/usr/lib/jdk/jdk1.6.0_45/bin/java - priority 11
slave keytool: (null)
slave rmiregistry: (null)
slave jre_exports: (null)
slave jre: (null)

Verify the install Using update-alternatives --display java

Use the following command to see what is install for java

update-alternatives --display java

This will display something like

update-alternatives --display java
java - status is manual.
link currently points to /usr/lib/jdk/jdk1.6.0_45/bin/java
/usr/lib/jvm/jre-1.4.2-gcj/bin/java - priority 1420
slave ControlPanel: (null)
slave HtmlConverter: (null)
slave appletviewer: (null)
slave apt: (null)
slave extcheck: (null)
slave idlj: (null)
slave jar: (null)
slave jarsigner: (null)
slave javac: (null)
slave javadoc: (null)
slave javah: (null)
slave javap: (null)
slave javaws: (null)
slave jconsole: (null)
slave jcontrol: (null)
slave jdb: (null)
slave jhat: (null)
slave jinfo: (null)
slave jmap: (null)
slave jps: (null)
slave jrunscript: (null)
slave jsadebugd: (null)
slave jstack: (null)
slave jstat: (null)
slave jstatd: (null)
slave jvisualvm: (null)
slave keytool: /usr/lib/jvm/jre-1.4.2-gcj/bin/keytool
slave native2ascii: (null)
slave orbd: (null)
slave pack200: (null)
slave policytool: (null)
slave rmic: (null)
slave rmid: (null)
slave rmiregistry: /usr/lib/jvm/jre-1.4.2-gcj/bin/rmiregistry
slave schemagen: (null)
slave serialver: (null)
slave servertool: (null)
slave tnameserv: (null)
slave unpack200: (null)
slave wsgen: (null)
slave wsimport: (null)
slave xjc: (null)
slave jre_exports: /usr/lib/jvm-exports/jre-1.4.2-gcj
slave jre: /usr/lib/jvm/jre-1.4.2-gcj
/usr/lib/jdk/jdk1.6.0_45/bin/java - priority 200
slave ControlPanel: /usr/lib/jdk/jdk1.6.0_45/bin/ControlPanel
slave HtmlConverter: /usr/lib/jdk/jdk1.6.0_45/bin/HtmlConverter
slave appletviewer: /usr/lib/jdk/jdk1.6.0_45/bin/appletviewer
slave apt: /usr/lib/jdk/jdk1.6.0_45/bin/apt
slave extcheck: /usr/lib/jdk/jdk1.6.0_45/bin/extcheck
slave idlj: /usr/lib/jdk/jdk1.6.0_45/bin/idlj
slave jar: /usr/lib/jdk/jdk1.6.0_45/bin/jar
slave jarsigner: /usr/lib/jdk/jdk1.6.0_45/bin/jarsigner
slave javac: /usr/lib/jdk/jdk1.6.0_45/bin/javac
slave javadoc: /usr/lib/jdk/jdk1.6.0_45/bin/javadoc
slave javah: /usr/lib/jdk/jdk1.6.0_45/bin/javah
slave javap: /usr/lib/jdk/jdk1.6.0_45/bin/javap
slave javaws: /usr/lib/jdk/jdk1.6.0_45/bin/javaws
slave jconsole: /usr/lib/jdk/jdk1.6.0_45/bin/jconsole
slave jcontrol: /usr/lib/jdk/jdk1.6.0_45/bin/jcontrol
slave jdb: /usr/lib/jdk/jdk1.6.0_45/bin/jdb
slave jhat: /usr/lib/jdk/jdk1.6.0_45/bin/jhat
slave jinfo: /usr/lib/jdk/jdk1.6.0_45/bin/jinfo
slave jmap: /usr/lib/jdk/jdk1.6.0_45/bin/jmap
slave jps: /usr/lib/jdk/jdk1.6.0_45/bin/jps
slave jrunscript: /usr/lib/jdk/jdk1.6.0_45/bin/jrunscript
slave jsadebugd: /usr/lib/jdk/jdk1.6.0_45/bin/jsadebugd
slave jstack: /usr/lib/jdk/jdk1.6.0_45/bin/jstack
slave jstat: /usr/lib/jdk/jdk1.6.0_45/bin/jstat
slave jstatd: /usr/lib/jdk/jdk1.6.0_45/bin/jstatd
slave jvisualvm: /usr/lib/jdk/jdk1.6.0_45/bin/jvisualvm
slave keytool: /usr/lib/jdk/jdk1.6.0_45/bin/keytool
slave native2ascii: /usr/lib/jdk/jdk1.6.0_45/bin/native2ascii
slave orbd: /usr/lib/jdk/jdk1.6.0_45/bin/orbd
slave pack200: /usr/lib/jdk/jdk1.6.0_45/bin/pack200
slave policytool: /usr/lib/jdk/jdk1.6.0_45/bin/policytool
slave rmic: /usr/lib/jdk/jdk1.6.0_45/bin/rmic
slave rmid: /usr/lib/jdk/jdk1.6.0_45/bin/rmid
slave rmiregistry: /usr/lib/jdk/jdk1.6.0_45/bin/rmiregistry
slave schemagen: /usr/lib/jdk/jdk1.6.0_45/bin/schemagen
slave serialver: /usr/lib/jdk/jdk1.6.0_45/bin/serialver
slave servertool: /usr/lib/jdk/jdk1.6.0_45/bin/servertool
slave tnameserv: /usr/lib/jdk/jdk1.6.0_45/bin/tnameserv
slave unpack200: /usr/lib/jdk/jdk1.6.0_45/bin/unpack200
slave wsgen: /usr/lib/jdk/jdk1.6.0_45/bin/wsgen
slave wsimport: /usr/lib/jdk/jdk1.6.0_45/bin/wsimport
slave xjc: /usr/lib/jdk/jdk1.6.0_45/bin/xjc
slave jre_exports: (null)
slave jre: (null)
/usr/lib/jdk/jdk1.6.0_38/bin/java - priority 200
slave ControlPanel: /usr/lib/jdk/jdk1.6.0_38/bin/ControlPanel
slave HtmlConverter: /usr/lib/jdk/jdk1.6.0_38/bin/HtmlConverter
slave appletviewer: /usr/lib/jdk/jdk1.6.0_38/bin/appletviewer
slave apt: /usr/lib/jdk/jdk1.6.0_38/bin/apt
slave extcheck: /usr/lib/jdk/jdk1.6.0_38/bin/extcheck
slave idlj: /usr/lib/jdk/jdk1.6.0_38/bin/idlj
slave jar: /usr/lib/jdk/jdk1.6.0_38/bin/jar
slave jarsigner: /usr/lib/jdk/jdk1.6.0_38/bin/jarsigner
slave javac: /usr/lib/jdk/jdk1.6.0_38/bin/javac
slave javadoc: /usr/lib/jdk/jdk1.6.0_38/bin/javadoc
slave javah: /usr/lib/jdk/jdk1.6.0_38/bin/javah
slave javap: /usr/lib/jdk/jdk1.6.0_38/bin/javap
slave javaws: /usr/lib/jdk/jdk1.6.0_38/bin/javaws
slave jconsole: /usr/lib/jdk/jdk1.6.0_38/bin/jconsole
slave jcontrol: /usr/lib/jdk/jdk1.6.0_38/bin/jcontrol
slave jdb: /usr/lib/jdk/jdk1.6.0_38/bin/jdb
slave jhat: /usr/lib/jdk/jdk1.6.0_38/bin/jhat
slave jinfo: /usr/lib/jdk/jdk1.6.0_38/bin/jinfo
slave jmap: /usr/lib/jdk/jdk1.6.0_38/bin/jmap
slave jps: /usr/lib/jdk/jdk1.6.0_38/bin/jps
slave jrunscript: /usr/lib/jdk/jdk1.6.0_38/bin/jrunscript
slave jsadebugd: /usr/lib/jdk/jdk1.6.0_38/bin/jsadebugd
slave jstack: /usr/lib/jdk/jdk1.6.0_38/bin/jstack
slave jstat: /usr/lib/jdk/jdk1.6.0_38/bin/jstat
slave jstatd: /usr/lib/jdk/jdk1.6.0_38/bin/jstatd
slave jvisualvm: /usr/lib/jdk/jdk1.6.0_38/bin/jvisualvm
slave keytool: /usr/lib/jdk/jdk1.6.0_38/bin/keytool
slave native2ascii: /usr/lib/jdk/jdk1.6.0_38/bin/native2ascii
slave orbd: /usr/lib/jdk/jdk1.6.0_38/bin/orbd
slave pack200: /usr/lib/jdk/jdk1.6.0_38/bin/pack200
slave policytool: /usr/lib/jdk/jdk1.6.0_38/bin/policytool
slave rmic: /usr/lib/jdk/jdk1.6.0_38/bin/rmic
slave rmid: /usr/lib/jdk/jdk1.6.0_38/bin/rmid
slave rmiregistry: /usr/lib/jdk/jdk1.6.0_38/bin/rmiregistry
slave schemagen: /usr/lib/jdk/jdk1.6.0_38/bin/schemagen
slave serialver: /usr/lib/jdk/jdk1.6.0_38/bin/serialver
slave servertool: /usr/lib/jdk/jdk1.6.0_38/bin/servertool
slave tnameserv: /usr/lib/jdk/jdk1.6.0_38/bin/tnameserv
slave unpack200: /usr/lib/jdk/jdk1.6.0_38/bin/unpack200
slave wsgen: /usr/lib/jdk/jdk1.6.0_38/bin/wsgen
slave wsimport: /usr/lib/jdk/jdk1.6.0_38/bin/wsimport
slave xjc: /usr/lib/jdk/jdk1.6.0_38/bin/xjc
slave jre_exports: (null)
slave jre: (null)
Current `best' version is /usr/lib/jvm/jre-1.4.2-gcj/bin/java.

We offer the Tuning Ace Nucleus part of the Tuning Ace Performance Tuning Suite. For more information please contact us here.

Please send us comments here TuningTipComments@TuningAce.com

This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document.