If you execute and trace
select tablespace_name from dba_tablespaces where tablespace_name like
'{something returning more than one row}'
You'll find that for each tablespace found Oracle executes the recursive query.
The odd thing is that the query only ever fetches the "first" row from the
table so you have to wonder why it's used at all.
However, the key thing to note in the trace is that you see
PARSE#
EXEC#
FETCH# ....... r=1
CLOSE#
for the cursor each time it runs, except the last time when it doesn't CLOSE#.
This may be why it can appear to run for 1378 seconds - it didn't reach
end-of-fetch, and it didn't close, so it's holding state open at the original
SCN.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
Sent: 04 February 2020 08:49
To: Stefan Koehler; ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: RE: select from ts$ in v$undostat.maxqueryid
Hello Stefan,
Yes, but the query doesn't run longer than a couple of seconds and MAXQUERYLEN
shows 1378s.
Best regards,
Nenad
https://nenadnoveljic.com/blog/
-----Original Message-----
From: Stefan Koehler <contact@xxxxxxxx>
Sent: Dienstag, 4. Februar 2020 09:39
To: Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>; ORACLE-L
(oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
Subject: Re: select from ts$ in v$undostat.maxqueryid
Hello Nenad,
do you possibly have any monitoring tool that runs a query on dba_tablespaces
(e.g. select * from dba_tablespaces where tablespace_name like '%TEST%')?
You should see this query as a recursive one then :)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx> hat am 4. Februar 2020 um 09:17____________________________________________________
geschrieben:
The following query (sql_id is 89w8y2pgn25yd) was recorded in
v$undostat.maxqueryid in a 12.2. database during a period of a high undo
usage:
select ts# from sys.ts$ where ts$.online$ != 3 and bitand(flags,2048) != 2048
;
select
undoblks,txncount,maxquerylen,maxconcurrency,activeblks
from v$undostat u
where maxqueryid='89w8y2pgn25yd'
;
UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXCONCURRENCY,ACTIVEBLKS
39199,4027,1378,5,2531960
…
The query itself has never been captured in v$active_session_history which
might indicate that the cursor remains open during some other activity.
There were two deletes running in the observed time period.
Does anybody know what could trigger the execution of the query above?
Best regards,
Nenad
https://nenadnoveljic.com/blog/