Currently browsing

June 2009

Tuning Oracle for Siebel – SQL template

The time has come to write down some of the most relevant discoveries I’ve made so far while being part of a team that is tuning a huge Siebel installation for a leading Italian company (“huge” especially because of the user base dimension and secondarily because of the hardware deployed, a three-node RAC on pretty powerful SMP machines).

This blog entry is about the structure of the Siebel queries and the effect of the settings of some CBO-related parameters – settings made by the Siebel clients by altering the session at connect time, or required as mandatory in the Siebel installation notes.…

Optimizing SQL statements with xplan

Xplan is a utility to simplify and automate the first part of every SQL statement tuning effort, that is, collecting the real plan of the statement, its execution statistics (number of executions, number of buffer gets performed, etc), getting the definition of all the accessed tables (and their indexes), and, last but not least, the CBO-related statistics of the accessed tables (and their indexes and columns) stored in the data dictionary by dbms_stats or ANALYZE.…