Query Using Huge INLIST Hangs (Doc ID 1129423.1)

Last updated on JUNE 21, 2010

Applies to:

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

Symptoms

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.

Changes

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

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

Cause

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