Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    41

    Unanswered: More SQL Help - Hierarchical Query ( ??? )

    Hi all,

    Someone may have done this already somewhere but I can't find it.

    Am running the SQL query below to check on SQLs that are may or may not be problems. Because they are in v$session_longops, we normally consider such SQLs as "contributors" to performance issues. Anyway, the SQL that am running is as below:

    Does anyone know how I can change it to have the preferred output instead of the sample output? I've looked at examples of hierarchical queries on Google, but it is always based on one table.

    I cannot post the entire output as it is huge. Any tips will be very much appreciated.


    select sl.sid, sl.serial#, sl.sofar, sl.totalwork, t.hash_value, t.piece, t.sql_text
    from v$sqltext t, v$session_longops sl
    where t.address = sl.sql_address
    and t.hash_value = sl.sql_hash_value
    group by sl.sid, sl.serial#, sl.sofar, sl.totalwork,t.hash_value, t.piece, t.sql_text

    Sample output:

    SID SERIAL# SOFAR TOTALWORK HASH_VALUE PIECE SQL_TEXT
    ---- -------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------------
    25 38557 897195 897195 2746682842 0 begin sys.dbms_application_info.set_session_longops
    25 38557 897195 897195 2746682842 1 (rindex => :1, slno => :2,
    25 38557 897195 897195 2746682842 2 op_name => :3, target => :4,
    25 38557 897195 897195 2746682842 3 context => :5, sofar
    25 38557 897195 897195 2746682842 4 => :6, totalwork => :7, ta
    25 38557 897195 897195 2746682842 5 rget_desc => :8, units => 'Blocks'); en
    25 38557 897195 897195 2746682842 6 d;
    25 40648 659809 659809 2746682842 0 begin sys.dbms_application_info.set_session_longops
    25 40648 659809 659809 2746682842 1 (rindex => :1, slno => :2,
    25 40648 659809 659809 2746682842 2 op_name => :3, target => :4,
    25 40648 659809 659809 2746682842 3 context => :5, sofar
    25 40648 659809 659809 2746682842 4 => :6, totalwork => :7, ta
    25 40648 659809 659809 2746682842 5 rget_desc => :8, units => 'Blocks'); en
    25 40648 659809 659809 2746682842 6 d;
    25 64648 36000382 36000382 2746682842 0 begin sys.dbms_application_info.set_session_longops
    25 64648 36000382 36000382 2746682842 1 (rindex => :1, slno => :2,
    25 64648 36000382 36000382 2746682842 2 op_name => :3, target => :4,
    25 64648 36000382 36000382 2746682842 3 context => :5, sofar
    25 64648 36000382 36000382 2746682842 4 => :6, totalwork => :7, ta
    25 64648 36000382 36000382 2746682842 5 rget_desc => :8, units => 'Blocks'); en
    25 64648 36000382 36000382 2746682842 6 d;
    26 63479 6402 6402 1141676395 0 SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),T O_CHAR(
    26 63479 6402 6402 1141676395 1 SYSDATE,'MM','NLS_CALENDAR=Gregorian'),TO_CHAR(SYS DATE,'DD','NLS
    26 63479 6402 6402 1141676395 2 _CALENDAR=Gregorian') FROM X$DUAL
    26 63479 6404 6404 1141676395 0 SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),T O_CHAR(
    26 63479 6404 6404 1141676395 1 SYSDATE,'MM','NLS_CALENDAR=Gregorian'),TO_CHAR(SYS DATE,'DD','NLS
    26 63479 6404 6404 1141676395 2 _CALENDAR=Gregorian') FROM X$DUAL
    34 66 105388 105388 2498401290 0 select * from workorder where ((historyflag like '%' and wonu
    34 66 105388 105388 2498401290 1 m like '%2586281%')) and (siteid = 'GENSITE')
    36 4272 5969 5969 2600991526 0 select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32), 1,120)
    36 4272 5969 5969 2600991526 1 minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
    36 4272 5969 5969 2600991526 2 rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv from
    36 4272 5969 5969 2600991526 3 (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, co
    36 4272 5969 5969 2600991526 4 unt(val)*count(val) repsq from (select /*+ cursor_sharing_exact
    36 4272 5969 5969 2600991526 5 dynamic_sampling(0) no_monitoring*/ "WO5" val, ntile(200) over
    36 4272 5969 5969 2600991526 6 (order by "WO5") bkt from "MAXIMO"."WORKORDER" t where "WO5" i
    36 4272 5969 5969 2600991526 7 s not null) group by val) group by maxbkt order by maxbkt


    Preferred Output:

    SID SERIAL# SOFAR TOTALWORK HASH_VALUE PIECE SQL_TEXT
    ---- -------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------------
    25 38557 897195 897195 2746682842 0 begin sys.dbms_application_info.set_session_longops
    1 (rindex => :1, slno => :2,
    2 op_name => :3, target => :4,
    3 context => :5, sofar
    4 => :6, totalwork => :7, ta
    5 rget_desc => :8, units => 'Blocks'); en
    6 d;
    25 40648 659809 659809 2746682842 0 begin sys.dbms_application_info.set_session_longops
    1 (rindex => :1, slno => :2,
    2 op_name => :3, target => :4,
    3 context => :5, sofar
    4 => :6, totalwork => :7, ta
    5 rget_desc => :8, units => 'Blocks'); en
    6 d;
    25 64648 36000382 36000382 2746682842 0 begin sys.dbms_application_info.set_session_longops
    1 (rindex => :1, slno => :2,
    2 op_name => :3, target => :4,
    3 context => :5, sofar
    4 => :6, totalwork => :7, ta
    5 rget_desc => :8, units => 'Blocks'); en
    6 d;
    26 63479 6402 6402 1141676395 0 SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),T O_CHAR(
    1 SYSDATE,'MM','NLS_CALENDAR=Gregorian'),TO_CHAR(SYS DATE,'DD','NLS
    2 _CALENDAR=Gregorian') FROM X$DUAL
    26 63479 6404 6404 1141676395 0 SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),T O_CHAR(
    1 SYSDATE,'MM','NLS_CALENDAR=Gregorian'),TO_CHAR(SYS DATE,'DD','NLS
    2 _CALENDAR=Gregorian') FROM X$DUAL
    34 66 105388 105388 2498401290 0 select * from workorder where ((historyflag like '%' and wonu
    1 m like '%2586281%')) and (siteid = 'GENSITE')
    36 4272 5969 5969 2600991526 0 select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32), 1,120)
    1 minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
    2 rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv from
    3 (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, co
    4 unt(val)*count(val) repsq from (select /*+ cursor_sharing_exact
    5 dynamic_sampling(0) no_monitoring*/ "WO5" val, ntile(200) over
    6 (order by "WO5") bkt from "MAXIMO"."WORKORDER" t where "WO5" i
    7 s not null) group by val) group by maxbkt order by maxbkt

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Break On...nodup

    Try this:
    Code:
    SET lin 120 trims on
    BREAK on SID nodup on serial# nodup on sofar nodup on totalwork nodup on hash_value nodup on report
    SELECT   sl.SID, sl.serial#, sl.sofar, sl.totalwork, t.hash_value, t.piece,
             t.sql_text
        FROM v$sqltext t, v$session_longops sl
       WHERE t.address = sl.sql_address AND t.hash_value = sl.sql_hash_value
    GROUP BY sl.SID,
             sl.serial#,
             sl.sofar,
             sl.totalwork,
             t.hash_value,
             t.piece,
             t.sql_text
    ORDER BY sl.SID, sl.serial#, sl.sofar, sl.totalwork, t.hash_value, t.piece;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •