10053 trace event in Oracle

This event trace is useful to troubleshoot issues when one cannot understand why the CBO (Cost Based Optimizer) is making wrong decisions. Most of the time, the cost/cardinality and the execution plan analysis for a particular SQL statement is enough to see where the bottleneck is but when everything else fails, this event trace helps since it shows the different options that the CBO considered prior to generating a plan that it chose.  I have found this to be very useful in troubleshooting bizarre performance issues and got introduced to this concept by reading up a post at Tom Kyte’s site and then in his book.  Below are two links that you can use to further read upon the 10053 trace output and how to interpret it.  I hope that Oracle will provide a utility in the future to make this interpretation easier.  Also, a point of caution, this should be used only when you have tried everything else.  Depending upon the type of query, it can dump a lot of information out.

Here are two very good URLs to learn about this trace event:

http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.ppt.pdf

http://asktom.oracle.com/pls/ask/f?p=4950:8:18270622340558063723::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:63445044804318

Disable/Enable constraints in DB2 LUW

Here are small procedures that can be used for disabling/enabling constraints in DB2 LUW (tested against 8.1 and 8.2 not against Viper (version 9)).

/*Replace XYZ with your schema name*/

SET SCHEMA XYZ;
SET CURRENT PATH = “XYZ”,”SYSIBM”,”SYSFUN”,”SYSPROC”;

create procedure USP_DISABLE_ALL
LANGUAGE SQL
BEGIN
declare v_tabname varchar(30);
declare v_constname varchar(30);
declare v_rows integer;
declare v_sql varchar(200);

declare temp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = CURRENT SERVER ORDER BY CONSTNAME,TABNAME, REFTABNAME;
select count(*) into v_rows from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

open temp_cursor;

while (v_rows > 0) DO
FETCH temp_cursor INTO v_tabname, v_constname;
SET v_sql = ‘alter table ‘ || current server || ‘.’ || v_tabname ||  ‘ alter foreign key ‘ || v_constname || ‘ NOT ENFORCED’;
execute immediate v_sql;

SET v_rows = v_rows – 1;
end while;
close temp_cursor;
END;

create procedure USP_ENABLE_ALL
LANGUAGE SQL
BEGIN
declare v_tabname varchar(30);
declare v_constname varchar(30);
declare v_rows integer;
declare v_sql varchar(200);

declare temp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = CURRENT SERVER ORDER BY CONSTNAME,TABNAME, REFTABNAME;
select count(*) into v_rows from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

open temp_cursor;

while (v_rows > 0) DO
FETCH temp_cursor INTO v_tabname, v_constname;
SET v_sql = ‘alter table ‘ || current server || ‘.’ || v_tabname ||  ‘ alter foreign key ‘ || v_constname || ‘ ENFORCED’;
execute immediate v_sql;

SET v_rows = v_rows – 1;
end while;
close temp_cursor;
END;