Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Optimization Profiler for Stored procedure

    Hi,

    I have an SQL which when fired from an application retrieves result very faster as this query is added to the optimization profiler. However i tried to make use of that query in a stored procedures where the values to that query in where condition are fetched from the session table created by the procedure. In this case the same query is running for 30 secs where as the standalone query completes in less than a second.

    Somehow i got the below link as reference for adding the stored procedure query in optmization profiler hoping that i should solve by performace issue.
    Use optimization profile in DB2 for Linux, UNIX, and Windows stored procedure

    I captured the query fired from the SPROC and added the same to the optimization profiler but still the SPROC is not mahing use of the opt profiler.

    Can someone tell me how to make use of the optimization profiler for stored procedures or how can i optimize the access plan of these SQL's written in SPROC to retrieve results faster.
    Im using DB2 v 9.5

    Thanks & Regards,
    Naveen

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    I suggest not to use optimization profile to optimize sql if there might me another way to archive that。
    Can you publish the access plan ( db2exfmt ) for the sql which is running for 30 secs here?

  3. #3
    Join Date
    Jun 2012
    Posts
    2

    Optimization Profiler for Stored procedure

    Hi,

    I have around 100's of procedures created and all having the same performance issue. Im just trying to implement this feature in one procedure and check if i can implement the same in all other procedures. If fine tuning is the only way to fix this performance issue then i will have to make this change in all procedures. Hence please let me know if i can apply any other staright forward fix like adding the SPROC to optimization profile which can resolve the performance issue in all procedures.

    Regards,
    Naveen

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    I dont understand exactly what is your meaning of "other staright forward fix"
    As far as i kown there are some ways to influnce db2 to choose a better access plan:
    eg: collect statistics ,statistics view ,selectivity hint, reorg table to improve cluster ratio,
    add some indexes,tuning some config parameter .....
    But they all need to do bad access plan analysis first。
    Would you mind to publish your optimization file and the bad access plan here?

Posting Permissions

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