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!