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

    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
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    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
    Code:
    set plan dump on
    to store the plans on table sysqueryplans, also
    Code:
    use set plan replace on
    to have only one entry for the same query.

    2. - Use for each stored proc the commands
    Code:
    set noexec on
    go
    set showplan on
    go
    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.
    Regards.

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    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
  •