View is slow but SQL is fast

(Doc ID 481522.1)

Last updated on JULY 11, 2017

Applies to:

Siebel Tools - Version SIA [18372] and later
Generic Windows



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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms