String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004

In the last days, I touched hierarchical queries – it’s a strange field for a DBA, but I did need it for DBA desires: A sophisticated query on statspack. Why? Since oddly, the stats$sql_text table of statspack / perfstat DOES contain the full SQL for a statement, but fragmented over various lines. But I had to display all queries in one line, as one string. The hierachical function sys_connect_by_path() looked sexy for it, so I dived into for good. 🙂

But soon a problem occured: sys_connect_by_path() needs a seperator string, that does not already occur within the to-be-concatenated strings. Okay, this is understandable and rather easy to cope with: Just replace the known string out of the result, done. But most annoying, this seperator has to be LITERAL – no function is allowed there! So in my case, a cause-loop rised its ugly head: As soon as this literal string is stated, it will occur within the stats$sql_text table, because this table contains (nearly) all SQL ran against the DB. Whoop – I’m out of business?

Not really: the oracle-l mailing list saved me (again). This thread resolved the problem – if you ever should need a similar query, just have a look at the solution of this nice little puzzle:

-----------------------
select
   ss.sql_id,
   (max(ss.executions)-min(ss.executions)) execs,
   st.sql_fulltext
     from stats$sql_summary ss,
/****** here the hierarchical part starts *****/
      (with data
         as
         (
          select
            sql_id,
            replace(SQL_TEXT, '¬', ' ') SQL_TEXT,
            row_number() over (partition by SQL_ID
                 order by PIECE) rn,
            count(*) over (partition by SQL_ID) cnt
          from stats$sqltext
        )
        select
          sql_id,
          replace(SQL_FULLTEXT, '¬', '') SQL_FULLTEXT
        from
         (select
             SQL_ID,
             sys_connect_by_path(SQL_TEXT, '¬') SQL_FULLTEXT
          from data
          where rn = cnt
          start with rn = 1
          connect by prior SQL_ID = SQL_ID
              and prior rn<10
              and prior rn = rn-1)
/****** here the hierarchical part ends *****/
    ) st
      where ss.snap_id>=(
         select min(sns.snap_id)
         from stats$snapshot sns
         where sns.snap_time >= sysdate-1
      )
       and ss.sql_id=st.sql_id
     group by ss.sql_id, st.sql_fulltext
     order by execs desc
;
/
-----------------------

Thanks a lot to Kenneth Naim, Stephane Faroult and Dietmar for insights, code and their time!

Regards
Martin