High Version Counts In Shared_Pool of Text Queries With CONTAINS Operator

(Doc ID 956698.1)

Last updated on APRIL 26, 2011

Applies to:

Oracle Text - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
Checked for relevance on 27-Apr-2011

Symptoms

A high version count 2,159 for Text contains() queries is observed from below query or in AWR (SQL Id 079z7y04jyswn in "SQL ordered by Version Count"):

SQL> select inst_id, sql_id, sql_text, version_count,
    loaded_versions, loads, invalidations from gv$sqlarea
    where parsing_schema_name like upper('gs')
    and version_count > 50
    order by sql_id, inst_id ;

 INST_ID  SQL_ID
 -------- -------------
 SQL_TEXT
 ------------------------------------------------------------------------------
 VERSION_COUNT LOADED_VERSIONS      LOADS INVALIDATIONS
 ------------- --------------- ---------- -------------
       1 079z7y04jyswn
select *
from gallery
where domain_id = :"SYS_B_0" and launched=:"SYS_B_1"
and ((contains (gallery_name,:"SYS_B_2", :"SYS_B_3") > :"SYS_B_4")
OR (contains (gallery_description,:"SYS_B_5", :"SYS_B_6") > :"SYS_B_7"))
order by published DESC
         2159            2159        2162            27

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