Monday, November 07, 2011

Sql statements executed by a Concurrent Request

If some concurrent program is running slow and trace in not enabled, this sql statement can give you list of sql statements and their plan

SELECT to_char(sql_out.last_active_time,'dd hh24:mi:ss') Last_active
     , sql_out.sql_fulltext
     , sql_out.module
     , sql_plan.id
     , rpad(' ',depth      *2,' ')
       || rpad(operation,50- depth*2,' ')
       || rpad(OPTIONS,30,' ')
       || rpad(object_name,30,' ')
     , access_predicates
     , filter_predicates
  FROM gv$sql_plan sql_plan
     , gv$sql sql_out
 WHERE sql_plan.sql_id       = sql_out.sql_id
   AND sql_plan.inst_id      = sql_out.inst_id
   AND sql_plan.child_number = sql_out.child_number
   AND (sql_plan.sql_id, sql_plan.inst_id, sql_plan.child_number) IN
            (SELECT sql_id
                  , sql_in.inst_id
                  , MAX(child_number)
               FROM gv$sql sql_in
                  , fnd_concurrent_requests req
                  , fnd_concurrent_programs prog             
              WHERE req.request_id               = :request_id
                AND req.concurrent_program_id    = prog.concurrent_program_id
                AND req.program_application_id   = prog.application_id
                AND prog.concurrent_program_name = sql_in.module
                AND sql_in.last_active_time     >= req.actual_start_date
              GROUP BY sql_in.sql_id
                     , sql_in.inst_id
            )
 ORDER BY 1 desc
        , sql_plan.sql_id
        , sql_plan.child_number
        , sql_plan.id ;

No comments:

Post a Comment