Performance Analysis | Set Siebel Session

Let us see how to find the correct cost (performance) of SQL while running in SQL Developer.

First thing needs to be done while working on performance is to alter the session of SQL developer. With this, SQL will run as it is running from Siebel client.

Following parameters are set by Siebel for better performance of SQL in Siebel server. We should set the same parameters in SQL developer to find the correct cost of the SQL.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

ALTER SESSION SET HASH_JOIN_ENABLED = FALSE;

ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = TRUE;

ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE;

ALTER SESSION SET "_always_semi_join"=OFF;

ALTER SESSION SET "_sort_elimination_cost_ratio"=5;

To find the difference in execution time, run the SQL without setting above parameters and after setting these parameters.