1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to diagnose a oracle procdure which is slow in batch

Discussion in 'SQL PL/SQL' started by ashish kumar, Jan 29, 2014.

  1. ashish kumar

    ashish kumar Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    A Java batch program calls many oracle procedures and it takes 5 min to complete.
    Not it is taking 3 hrs.
    how to identify a which procedure is taking more time and tuned it ????
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. ashish kumar

    ashish kumar Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi
    we use dbms_profiler to individual procedure.
    java batch program like a batch file which calls many oracle procedures.
    we need to identify procedure name which is taking more time.
    is there any approach or method???

    thanks
    ashish
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    there are different approaches and methods ....


    one of variants:

    1) if there is an opportunity, then add in application the code for setting id client for identification of executed requests, unit Pl/sql and etc.
    p.s. see of describing dbms_application_info


    2) after completion to look at the ASH-report or to use real-time monitoringn

    http://docs.oracle.com/cd/B28359_01/server.111/b28274/autostat.htm#i35568 - ASH
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm - AWR




    p.s.
    http://docs.oracle.com/javase/7/docs/technotes/samples/hprof.html
    http://docs.oracle.com/javase/7/docs/technotes/tools/share/jvisualvm.html
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That can depend on your version of Oracle. If you're running 11.2.0.x you have access to V$SQL_MONITOR that can help you find the problem PL/SQL. It will report the actual PL/SQL code that executed, and you can restrict output based on the elapsed time.
     
  6. ashish kumar

    ashish kumar Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    I am using Oracle 10g.
    I can get detail to individual Sql statement through AWR & ADDM Reports.
    But issue is my batch file calling 15 Oracle procedures and each procedure have 2000 to 3000 lines of code.
    Is there any way to diagnose the procedure name which is taking more time.
    If can find procedure name which is slow then we can use dbms_profiler package for that procedure.
    Main concern for me how to diagnose a procedure name which need s to be tuned.

    Many Thanks,
    Ashish
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    the simple solution is absent...
    in oracle there are full tools for diagnostics of problems with a productivity...

    http://docs.oracle.com/cd/B19306_01/server.102/b14211/toc.htm
    http://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm

    one of variants:

    1) to receive from AWR of the report of sql-id of "expensive" sql-query and to find procedures in which they is used.
    the found procedures to profiling

    2) see of the describe v$ and create necessary scenarios for itself...

    http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1.htm

    3) example scripts :
    http://www.think-forward.com/oracle_monitor_scripts.htm
     
  8. ashish kumar

    ashish kumar Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Sergey Krasnoslobodtsev
    I believe that it will be helpful for me.



    Thanks,
    Ashish
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can use this script to generate a report of long running queries and report the offending text:


    Code (SQL):

    SET linesize 200 trimspool ON pagesize 60
    COLUMN begin_interval_time format a35
    COLUMN end_interval_time format a35
    break ON sql_id skip 1 ON instance_number
    COLUMN sdate new_value sdt noprint
    SELECT to_char(sysdate, 'YYYYMMDDHHMI') sdate FROM dual;
    spool &sdt._elapsed_time_report.log
    prompt
    prompt  Historic
    prompt
    prompt  Elapsed BY EXEC
    prompt
    SELECT DISTINCT x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time
    FROM
    (SELECT instance_number, sql_id, snap_id,
           round((elapsed_time_total/1000000)/(CASE WHEN executions_total = 0 THEN 1 ELSE executions_total END),4) time_per_exec,
           round(elapsed_time_total/1000000, 4) elapsed_time_total
    FROM dba_hist_sqlstat) x, (SELECT snap_id,
                                      MAX(begin_interval_time) begin_interval_time,
                                      MAX(end_interval_time) end_interval_time
                               FROM dba_hist_snapshot
                               GROUP BY snap_id) s
    WHERE s.snap_id = x.snap_id
    AND x.time_per_exec > &&1
    AND x.time_per_exec <> x.elapsed_time_total
    ORDER BY 2 ASC, 3 DESC, 6 DESC
    /
    clear breaks
    prompt
    prompt  Elapsed TIME total
    prompt
    SELECT inst_id,
           sql_id,
           executions,
           round(elapsed_time/1000000, 6) elapsed_sec,
           round((elapsed_time/1000000)/(CASE WHEN executions = 0 THEN 1 ELSE executions END), 6) elapsed_per_exec,
           last_active_time
    FROM gv$sqlstats
    WHERE elapsed_time/1000000 > &&1
    ORDER BY 4 DESC
    /
    prompt
    prompt  Elapsed per EXEC
    prompt
    SELECT inst_id,
           sql_id,
           executions,
           round(elapsed_time/1000000, 6) elapsed_sec,
           round((elapsed_time/1000000)/(CASE WHEN executions = 0 THEN 1 ELSE executions END), 6) elapsed_per_exec,
           last_active_time
    FROM gv$sqlstats
    WHERE elapsed_time/1000000 > &&1
    ORDER BY 5 DESC
    /
    prompt
    prompt SQL text
    prompt
    SET long 1000000
    SELECT sql_id, sql_text
    FROM dba_hist_sqltext
    WHERE sql_id IN
    (
    SELECT sql_id
    FROM gv$sqlstats
    WHERE elapsed_time/1000000 > &&1
    );
    spool off


     

    Supply the minimum execution time you want to look for and the script will report all queries and statements that take that long or longer to run.