Store procedure tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Store procedure tuning

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    We have a package stored procedure which takes close to 9 hours to complete execution for 30000 rows. We have the auto trace on but cannot use it to
    determine where the bottleneck happens because autotrace is useful to analyze select statements and not procedure calls. Could some one advice any methods to
    determine where the bottle neck happens? I am aware of oracle Top sessions but do not have it with me. Any alternatives? Please advice....
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    use dbms_system.set_sql_trace_in_session
    to turn on tracing for the session that executes the stored
    proc. you can also look at DBMS_PROFILER if you are on 8i
    to help you keep trace of performace.

    -John

  3. #3
    Join Date
    Jun 2001
    Posts
    15
    Maybe taking out every select/insert/delete statement and running an explain plan on it will help...Did you check if you have and use indexes?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I too have a feeling you probably have SQL problems, not PL/SQL. Check this by running TKPROF on the collected trace.

    Or you might have some database configuration related problems - let your DBA do some monitoring while your program is running.

    If you realy think PL/SQL code is the major source of your problems then take a look at DBMS_PROFILER package - you will be able to find the bottleneck in your code with it...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width