A bigger interval like 30 seconds or ~1minutes is failing the script with
ORA-01489. So not sure this below result from a smaller duration is really
going to make us debug the issue in right direction.
I tried reducing the interval to ~10 seconds duration i.e the start of the
issue period and then got the below output for few seconds duration
https://gist.github.com/databasetech0073/99026ff9530e343f85924795382dca14
In this case i see lot of blocking session as "db writer" i.e. "idle
blocker 1,2395,8486 (oracle@xxxxxxxxxx (DBW0)", so does it mean that we are
actually creating lot of connection that the database can serve so the Db
writer is struggling to catchup?
On Fri, 3 Mar, 2023, 5:18 pm yudhi s, <learnerdatabase99@xxxxxxxxx> wrote:
Hello Listers,
While trying to run Tanels below script for investigating one of the issue
I am seeing error "ORA-01489: result of string concatenation is too long".
The issue was basically the database almost come to freeze for about
~1minute duration. So anybody saw this error while running the
dash_wait_chain script?
https://github.com/tanelpoder/tpt-oracle/blob/master/ash/dash_wait_chains.sql
Is it because we have too many blocking session coming and below part of
the script is throwing that error? How can i handle this error?
,substr(REPLACE(SYS_CONNECT_BY_PATH(/*&1*/ program2||event2, '->'), '->',
' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL
THEN ' -> [idle blocker
'||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT
' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#)
=
((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']'
ELSE NULL END , 1,3999) path
[Error] Execution (64: 296): ORA-01489: result of string concatenation is
too long
