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'
(Doc ID 2084568.1)
Last updated on APRIL 30, 2018
Applies to:Siebel System Software - Version 220.127.116.11 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
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document