Performance of SQL Queries

To check the performance of the application we get the spool and run the queries against the database to check the time it is taking. This check does not return the correct value of the time taken by query when executed through Siebel application. The reason being  when the query is executed Siebel application sets certain parameters at each session level.  Until you set the parameters in the database before execution of the query, correct performance cannot be evaluated.

 Solution :

•  Set the parameters at the logged in Database session with the following commands
  • alter session set optimizer_mode = first_rows_10;
  • alter session set "_hash_join_enabled" = FALSE;
  • alter session set "_optimizer_sortmerge_join_enabled" = FALSE;
  • alter session set "_optimizer_join_sel_sanity_check" = TRUE;
•  Run the SQL query (Spool file -  SQL query set)
•  Capture the performance time related to the specific query with different data set.
• Analyze the bottleneck areas and use Performance optimizer tips to improve the Turn Around time