Custom queries with EXISTS / SELECT ... IN subqueries and an ORDER BY clause have DB execution plans that read millions of blocks - when running the SQL with NO_UNNEST hint, only 20000 blocks are read. Worked around with "_fix_control" = '12555499:0'
Last updated on MARCH 01, 2018
Applies to:Siebel System Software - Version 126.96.36.199 SIA  and later
Information in this document applies to any platform.
Custom queries with EXISTS / SELECT ... IN subqueries and an ORDER BY clause have DB execution plans that read millions of blocks - when running the statement with with an NO_UNNEST hint, "only" 20000 blocks are read and performance is acceptable.
Customizing the Siebel application has resulted in a number of queries that perform an exessive amount of read operations to bring back a few rows.
The queries in question all have subqueries (resulting from an EXISTS query that the Siebel application uses for multi value fields / links)
The sample query provided in additon to the SELECT ... IN subquery
had no selective search spec that could be index supportable,
and to make things worse, had an ORDER BY clause on yet another custom column from another table that was not part of the search spec
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