My Oracle Support Banner

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 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

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
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.