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 DECEMBER 08, 2015

Applies to:

Siebel System Software - Version 8.0.0.2 SIA [20412] and later
Information in this document applies to any platform.

Goal

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 


 

Solution

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