Internal queries KILLING my database!

Normally when internal recursive queries are over-running your database server, it means too much parsing. But what if your application does all the right things with bind variables and your database is STILL getting smashed?
Follow me!
blog: connor-mcdonal...
twitter: / connor_mc_d
The Podcast!
podcasts.apple...
open.spotify.c...
Subscribe for new tech videos every week
Other social media channels here: linktr.ee/connor
Music: Night Owl (Broke For Free), Kevin Mcleod (incomptech), Dyalla
#sql #database

[@berndeckenfels]

For the described merge case, there is also the ignore_row_on_dupkey_index hint, I haven’t checked if it avoids the internal query but it surely runs better and avoids the increase of statement error counter in our case.

[@DatabaseDude]

that hint, or not exists...anything that avoids falling through to the exception. Although, the recursive query is often not the killer here, its the full client/server break (check the trace for SQL Net break/reset events)

[@berndeckenfels]

Anecdote, We had on systems problems with internal queries in the blob area which caused a physical block read on each executions (updates’, seemingly not getting cached. Strange enough it did not happen on test systems. We haven’t been able to trace it though.

[@DatabaseDude]

"internal queries in the blob area" - can you elucidate?

[@berndeckenfels]

@@DatabaseDude I need a full text search on my email archive to recall the specifics. I noticed it in physical io stats on a lob segment and it correlated with the statement stats of a short recursive query, but can’t find the details anymore. I hope it’s 12c history

[@DominicGiles]

Great explanation as always.

[@fmsilva11]

how to catch all queries not using bind variables ?

[@DatabaseDude]

select FORCE_MATCHING_SIGNATURE, count(*)
from v$sql
group by FORCE_MATCHING_SIGNATURE
Look for high count(*)