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