Unusually High Value In V$UNDOSTAT.MAXQUERYLEN For Short Recursive SQL Statements (Doc ID 2776001.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

Recursive SQL statements which should have completed in very short time appears as having high MAXQUERYLEN in V$UNDOSTAT. 

Few examples of such recursive statements may include:

sql_id c8h20n1d0k95m
select /*+ no_parallel */ spare4 from sys.optstat_hist_control$ where sname=:1

sqlid 89w8y2pgn25yd
select ts# from ts$ where ts$.online$ != 3 and bitand(flags,2048) != 2048

sqlid 7vukkk6k166q2
select 1 from sys.aq$_subscriber_table   where rownum < 2 and subscriber_id <> 0 and table_objno <> 0

Another symptom may be that these statements show up in V$OPEN_CURSOR for a long time.

Changes

Cause

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