Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012

    Exclamation Unanswered: Get Execution Plan

    Good Morning in my timezone.

    We are optimizing each Store Procedure that a Web Application use, but there are more than one hundred to analyse.So i want to make a little web app that reads all the objects from sysobjects and for each one execute the "execution plan" , that way i am able to present the most heavy store procedures by order , my question is : What commands should i use to retrieve the "execution plan"?
    In Oracle i think there is a PLAN_TABLE where all the results are temporarily store , is there any similar table in Sybase ?
    Thanks in advance.
    With the best regards

    Pedro Costa

  2. #2
    Join Date
    Jan 2012
    Ola Pedro,
    Tudo bem?

    Let's see if i can help!

    Check ou this link

    Performance and Tuning Guide

    To summit up, you have 2 options:
    1. - Use
    set plan dump on
    to store the plans on table sysqueryplans, also
    use set plan replace on
    to have only one entry for the same query.

    2. - Use for each stored proc the commands
    set noexec on
    set showplan on
    exec <sp>
    This way you get the output of the query plan for the sp. Only issue is that you have to run this code for each sp, if you have more than one hundred, its best to automatize this.

    Hope it helps.

  3. #3
    Join Date
    Sep 2012
    Please do the "set noexec" at last, otherwise the "set showplan" doesnt work.

Tags for this Thread

Posting Permissions

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