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




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply