Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Set noexec on in showplan

5 posts in General Discussion Last posting was on 2012-07-31 10:14:48.0Z
Mike Posted on 2012-07-18 04:57:15.0Z
Sender: 4ae6.500521b0.1804289383@sybase.com
From: Mike
Newsgroups: sybase.public.ase.general
Subject: Set noexec on in showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5006422b.6f58.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Jul 2012 21:57:15 -0700
X-Trace: forums-1-dub 1342587435 172.20.134.41 (17 Jul 2012 21:57:15 -0700)
X-Original-Trace: 17 Jul 2012 21:57:15 -0700, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31256
Article PK: 74144

Dear all,
I am using below option to generate the desired output
set showplan on
set statistics io on
set statistics time on

to get the following information
- table scan
- index used
- log scan
- page scan

- elapsed time
etc..

using these options it executes the procedure and also the
sub procedure.
My requirement is that i want the above output but do not
want the SP to be executed.
I tried option
set noexec on
but it does not produce the required output in the showplan

any idea how can i achieve it.

- Mike


"Mark A. Parsons" <iron_horse Posted on 2012-07-18 13:13:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Set noexec on in showplan
References: <5006422b.6f58.1681692777@sybase.com>
In-Reply-To: <5006422b.6f58.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5006b688$1@forums-1-dub>
Date: 18 Jul 2012 06:13:44 -0700
X-Trace: forums-1-dub 1342617224 172.20.134.152 (18 Jul 2012 06:13:44 -0700)
X-Original-Trace: 18 Jul 2012 06:13:44 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31258
Article PK: 74145

What do you mean 'do not want the SP to be executed'?

If the proc does not *run* then there's no way of getting the performance stats.

If instead you mean that you want the proc to run but not affect any data:

1 - with ASE 15.x you can issue 'set nodata on' to suppress some result sets ... but this may lead to inaccurate
performance metrics for some follow-on queries

2 - you could wrap the entire test in a 'begin/rollback tran'; this will allow you to capture performance stats, but at
the cost of having to incur some additional overhead to perform a rollback of any data changes; of course, this may not
be an option if your code performs some operations which cannot be performed from within a transaction

3 - find a database where there are no other users, perform the necessary operations to get your database into the
desired state (load tables, update stats, etc), make a backup of the database (ie, obtain a baseline from which you can
run multiple tests); run your tests; if you want to re-run your tests then reload the database from your baseline backup
and run your tests again; obviously this could be a bit time consuming if your database is 100s of GBs in size

Alternatively, provide more details on exactly what you're trying to accomplish ... including why you don't want the
proc to run.

On 07/17/2012 22:57, Mike wrote:
> Dear all,
> I am using below option to generate the desired output
> set showplan on
> set statistics io on
> set statistics time on
>
> to get the following information
> - table scan
> - index used
> - log scan
> - page scan
>
> - elapsed time
> etc..
>
> using these options it executes the procedure and also the
> sub procedure.
> My requirement is that i want the above output but do not
> want the SP to be executed.
> I tried option
> set noexec on
> but it does not produce the required output in the showplan
>
> any idea how can i achieve it.
>
> - Mike


Mike Posted on 2012-07-19 05:16:57.0Z
Sender: 1562.500797a6.1804289383@sybase.com
From: Mike
Newsgroups: sybase.public.ase.general
Subject: Re: Set noexec on in showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50079849.1570.1681692777@sybase.com>
References: <5006b688$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Jul 2012 22:16:57 -0700
X-Trace: forums-1-dub 1342675017 172.20.134.41 (18 Jul 2012 22:16:57 -0700)
X-Original-Trace: 18 Jul 2012 22:16:57 -0700, 172.20.134.41
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31263
Article PK: 74151

thanks for the reply.
I want to get the mentioned below performance stats for a
procedure. but it also gives the stats for sub procedure
within the procedures which i do not want.
Also, it gives the detail for any trigger associated with
any table used in the procedure.
htnak
-Mike

> What do you mean 'do not want the SP to be executed'?
>
> If the proc does not *run* then there's no way of getting
> the performance stats.
>
> If instead you mean that you want the proc to run but not
> affect any data:
>
> 1 - with ASE 15.x you can issue 'set nodata on' to
> suppress some result sets ... but this may lead to
> inaccurate performance metrics for some follow-on queries
>
> 2 - you could wrap the entire test in a 'begin/rollback
> tran'; this will allow you to capture performance stats,
> but at the cost of having to incur some additional
> overhead to perform a rollback of any data changes; of
> course, this may not be an option if your code performs
> some operations which cannot be performed from within a
> transaction
>
> 3 - find a database where there are no other users,
> perform the necessary operations to get your database into
> the desired state (load tables, update stats, etc), make
> a backup of the database (ie, obtain a baseline from which
> you can run multiple tests); run your tests; if you want
> to re-run your tests then reload the database from your
> baseline backup and run your tests again; obviously this
> could be a bit time consuming if your database is 100s of
> GBs in size
>
> Alternatively, provide more details on exactly what you're
> trying to accomplish ... including why you don't want the
> proc to run.
>
> On 07/17/2012 22:57, Mike wrote:
> > Dear all,
> > I am using below option to generate the desired output
> > set showplan on
> > set statistics io on
> > set statistics time on
> >
> > to get the following information
> > - table scan
> > - index used
> > - log scan
> > - page scan
> >
> > - elapsed time
> > etc..
> >
> > using these options it executes the procedure and also
> > the sub procedure.
> > My requirement is that i want the above output but do
> > not want the SP to be executed.
> > I tried option
> > set noexec on
> > but it does not produce the required output in the
> showplan >
> > any idea how can i achieve it.
> >
> > - Mike


"Mark A. Parsons" <iron_horse Posted on 2012-07-19 12:38:42.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Set noexec on in showplan
References: <5006b688$1@forums-1-dub> <50079849.1570.1681692777@sybase.com>
In-Reply-To: <50079849.1570.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5007ffd2@forums-1-dub>
Date: 19 Jul 2012 05:38:42 -0700
X-Trace: forums-1-dub 1342701522 172.20.134.152 (19 Jul 2012 05:38:42 -0700)
X-Original-Trace: 19 Jul 2012 05:38:42 -0700, vip152.sybase.com
Lines: 75
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31265
Article PK: 74154

No easy way that I know of.

I just capture all the output (to a file) and then search for the parts of interest to me.

On 07/18/2012 23:16, Mike wrote:
> thanks for the reply.
> I want to get the mentioned below performance stats for a
> procedure. but it also gives the stats for sub procedure
> within the procedures which i do not want.
> Also, it gives the detail for any trigger associated with
> any table used in the procedure.
> htnak
> -Mike
>
>> What do you mean 'do not want the SP to be executed'?
>>
>> If the proc does not *run* then there's no way of getting
>> the performance stats.
>>
>> If instead you mean that you want the proc to run but not
>> affect any data:
>>
>> 1 - with ASE 15.x you can issue 'set nodata on' to
>> suppress some result sets ... but this may lead to
>> inaccurate performance metrics for some follow-on queries
>>
>> 2 - you could wrap the entire test in a 'begin/rollback
>> tran'; this will allow you to capture performance stats,
>> but at the cost of having to incur some additional
>> overhead to perform a rollback of any data changes; of
>> course, this may not be an option if your code performs
>> some operations which cannot be performed from within a
>> transaction
>>
>> 3 - find a database where there are no other users,
>> perform the necessary operations to get your database into
>> the desired state (load tables, update stats, etc), make
>> a backup of the database (ie, obtain a baseline from which
>> you can run multiple tests); run your tests; if you want
>> to re-run your tests then reload the database from your
>> baseline backup and run your tests again; obviously this
>> could be a bit time consuming if your database is 100s of
>> GBs in size
>>
>> Alternatively, provide more details on exactly what you're
>> trying to accomplish ... including why you don't want the
>> proc to run.
>>
>> On 07/17/2012 22:57, Mike wrote:
>>> Dear all,
>>> I am using below option to generate the desired output
>>> set showplan on
>>> set statistics io on
>>> set statistics time on
>>>
>>> to get the following information
>>> - table scan
>>> - index used
>>> - log scan
>>> - page scan
>>>
>>> - elapsed time
>>> etc..
>>>
>>> using these options it executes the procedure and also
>>> the sub procedure.
>>> My requirement is that i want the above output but do
>>> not want the SP to be executed.
>>> I tried option
>>> set noexec on
>>> but it does not produce the required output in the
>> showplan>
>>> any idea how can i achieve it.
>>>
>>> - Mike


vtpcnk Posted on 2012-07-31 10:14:48.0Z
Sender: 4fc.5017ad83.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: Set noexec on in showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5017b018.545.1681692777@sybase.com>
References: <5006422b.6f58.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Jul 2012 03:14:48 -0700
X-Trace: forums-1-dub 1343729688 172.20.134.41 (31 Jul 2012 03:14:48 -0700)
X-Original-Trace: 31 Jul 2012 03:14:48 -0700, 172.20.134.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31283
Article PK: 74172


> Dear all,
> I am using below option to generate the desired output
> set showplan on
> set statistics io on
> set statistics time on
>
> to get the following information
> - table scan
> - index used
> - log scan
> - page scan
>
> - elapsed time
> etc..
>
> using these options it executes the procedure and also the
> sub procedure.
> My requirement is that i want the above output but do not
> want the SP to be executed.
> I tried option
> set noexec on
> but it does not produce the required output in the
> showplan
>
> any idea how can i achieve it.
>
> - Mike

not sure if you are looking for :

set showplan, fmtonly on