View is slow but SQL is fast
Last updated on JULY 11, 2017
Applies to:Siebel Tools - Version 184.108.40.206 SIA  and later
I am trying to resolve a performance issue, which we had in one of our asset screen. To reproduce the error, logged into application and went to our "Chassis" screen (which uses S_ASSET) and then clicked query go. Then it took couple of minutes before it returned the error Ã¢ÂÂSBL-DAT-00500 too many recordsÃ¢ÂÂ.
We have taken SQL Spool and ran the SQL statement in TOAD directly against database and the response was very fast. I know that Siebel sets the following parameters in each session before executing the SQL:
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;
But even if I run these before executing the SQL in TOAD it goes fast. I wonder what Siebel is doing after receiving these record, why does it keep trying to get the next records all the time? And how can I prevent Siebel to get the next records and just take the first 10?
In our other S_ASSET screen everything goes fast, the only difference is that this new "Chassis" is using a new custom 1:1 table that stores a lot of new columns. My guess is that since there are so many old records that only have data in the master table, it tries to iterate until it finds a match in the new extension table? Could it be so? If so, what can I do to prevent it? I have outer join set in.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms