Query Using Huge INLIST Hangs

(Doc ID 1129423.1)

Last updated on JUNE 21, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 10.2 to 11.1
Information in this document applies to any platform.


SQL involving heavy inlist and order by clause hangs.

The more amount of time is spent on parsing.

The heavy and order by clause put overhead into the parsing phase of the sql execution.

The trace information shows the following:- 

call count cpu elapsed rows
------- ------ -------- ---------- ----------
Parse 1 476.68 529.40 0  <=
Execute 1 0.00 0.01 0
Fetch 0 0.00 0.00 0
------- ------ -------- ---------- ----------
total 2 476.68 529.41 0

Here, the more amount of time is spent on CPU while parsing. Parsing takes huge CPU.


This does not happen in where the parsing takes less time.

If the ORDER BY is removed, the parsing is reduced significantly on


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