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.

Cursors

5 posts in General Discussion Last posting was on 2011-09-21 18:53:04.0Z
dev Posted on 2011-09-21 14:10:22.0Z
Sender: 26af.4e79f000.1804289383@sybase.com
From: Dev
Newsgroups: sybase.public.ase.general
Subject: Cursors
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e79f04e.26c2.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Sep 2011 07:10:22 -0700
X-Trace: forums-1-dub 1316614222 10.22.241.41 (21 Sep 2011 07:10:22 -0700)
X-Original-Trace: 21 Sep 2011 07:10:22 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30546
Article PK: 72721

Hi

Is there a way to find out the number of open cursors
or maximum number of cursors used at server level?

sp_cursorinfo seems to be only at session level.

Thanks


Bret Halford Posted on 2011-09-21 15:28:36.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursors
References: <4e79f04e.26c2.1681692777@sybase.com>
In-Reply-To: <4e79f04e.26c2.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e7a02a4$1@forums-1-dub>
Date: 21 Sep 2011 08:28:36 -0700
X-Trace: forums-1-dub 1316618916 10.22.241.152 (21 Sep 2011 08:28:36 -0700)
X-Original-Trace: 21 Sep 2011 08:28:36 -0700, vip152.sybase.com
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30548
Article PK: 72722


On 9/21/2011 8:10 AM, Dev wrote:
> Hi
>
> Is there a way to find out the number of open cursors
> or maximum number of cursors used at server level?
>
> sp_cursorinfo seems to be only at session level.
>
> Thanks

It isn't documented anywhere that I know of, but a little
experimentation found that every time I declared a cursor,
a new entry was added to monCachedProcedure with a name
with the pattern "*ss%ss*" with an object type of "stored procedure".

select count(*) from monCachedProcedure
where name like "%ss%ss*"

may give the answer you seek.

1> use tempdb
2> go
1> declare bar_cursor cursor for select id from sysindexes
2> go
1> select * from master..monCachedProcedures
2> go
ObjectID InstanceID OwnerUID DBID PlanID MemUsageKB
CompileDate ObjectName
ObjectType OwnerName
DBName RequestCnt TempdbRemapCnt
AvgTempdbRemapTime
----------- ---------- ----------- ----------- ----------- -----------
-------------------------- ------------------------------
-------------------------------- ------------------------------
------------------------------ ----------- --------------
------------------
617050203 0 1 31514 1 720
Sep 19 2011 3:15PM sp_do_poolconfig
stored procedure NULL
sybsystemprocs 2 0
0
1312004674 0 1 1 3 112
Sep 19 2011 3:15PM sp_aux_getsize
stored procedure NULL
master 10 0
0
984948025 0 1 2 5 12
Sep 21 2011 9:05AM *ss0984948025_1045129052ss*
stored procedure NULL
tempdb 2 0
0
1804530431 0 1 31514 7 32
Sep 21 2011 9:06AM sp_role
stored procedure NULL
sybsystemprocs 1 0
0
1296004617 0 1 1 9 100
Sep 21 2011 9:06AM sp_getmessage
stored procedure NULL
master 1 0
0
1000948082 0 1 2 11 12
Sep 21 2011 9:07AM *ss1000948082_1662086991ss*
stored procedure NULL
tempdb 1 0
0

(6 rows affected)


"Mark A. Parsons" <iron_horse Posted on 2011-09-21 18:03:03.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.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: Cursors
References: <4e79f04e.26c2.1681692777@sybase.com> <4e7a02a4$1@forums-1-dub>
In-Reply-To: <4e7a02a4$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e7a26d7$1@forums-1-dub>
Date: 21 Sep 2011 11:03:03 -0700
X-Trace: forums-1-dub 1316628183 10.22.241.152 (21 Sep 2011 11:03:03 -0700)
X-Original-Trace: 21 Sep 2011 11:03:03 -0700, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30549
Article PK: 72723

It's been my experience that the *ss%ss* names are assigned to LWPs (lightweight procedures) that are created as a
result of a) cached statements and b) prepared statements. (there may be others)

I created several cursors and the contents of monCachedProcedures didn't change. I also tried opening and fetching ...
but no new entries in monCachedProcedures. (There was a *ss%ss* entry in monCachedProcedures but that was the cached
statement of my 'select * from monCachedProcedures' query.)

On 09/21/2011 11:28, Bret Halford wrote:
> It isn't documented anywhere that I know of, but a little
> experimentation found that every time I declared a cursor,
> a new entry was added to monCachedProcedure with a name
> with the pattern "*ss%ss*" with an object type of "stored procedure".

... snip ...


Rob V Posted on 2011-09-21 18:26:30.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cursors
References: <4e79f04e.26c2.1681692777@sybase.com> <4e7a02a4$1@forums-1-dub> <4e7a26d7$1@forums-1-dub>
In-Reply-To: <4e7a26d7$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e7a2c56$1@forums-1-dub>
Date: 21 Sep 2011 11:26:30 -0700
X-Trace: forums-1-dub 1316629590 10.22.241.152 (21 Sep 2011 11:26:30 -0700)
X-Original-Trace: 21 Sep 2011 11:26:30 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30550
Article PK: 72725


On 21-Sep-2011 20:03, Mark A. Parsons wrote:
> It's been my experience that the *ss%ss* names are assigned to LWPs
> (lightweight procedures) that are created as a result of a) cached
> statements and b) prepared statements. (there may be others)
>
> I created several cursors and the contents of monCachedProcedures
> didn't change. I also tried opening and fetching ... but no new
> entries in monCachedProcedures. (There was a *ss%ss* entry in
> monCachedProcedures but that was the cached statement of my 'select *
> from monCachedProcedures' query.)
>
>
> On 09/21/2011 11:28, Bret Halford wrote:
>> It isn't documented anywhere that I know of, but a little
>> experimentation found that every time I declared a cursor,
>> a new entry was added to monCachedProcedure with a name
>> with the pattern "*ss%ss*" with an object type of "stored procedure".
>
> ... snip ...

Bret, are you testing on ASE 15.7 by any chance? One of the enhancements
in 15.7 is that the query plan for cursors can also be cached in the
statement cache. That would match the name you are seeing.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2011-09-21 18:53:04.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.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: Cursors
References: <4e79f04e.26c2.1681692777@sybase.com> <4e7a02a4$1@forums-1-dub> <4e7a26d7$1@forums-1-dub> <4e7a2c56$1@forums-1-dub>
In-Reply-To: <4e7a2c56$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e7a3290@forums-1-dub>
Date: 21 Sep 2011 11:53:04 -0700
X-Trace: forums-1-dub 1316631184 10.22.241.152 (21 Sep 2011 11:53:04 -0700)
X-Original-Trace: 21 Sep 2011 11:53:04 -0700, vip152.sybase.com
Lines: 23
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30553
Article PK: 72728

fwiw, I ran my tests on an ASE 15.0.3 instance

On 09/21/2011 14:26, Rob V wrote:
> On 21-Sep-2011 20:03, Mark A. Parsons wrote:
>> It's been my experience that the *ss%ss* names are assigned to LWPs (lightweight procedures) that are created as a
>> result of a) cached statements and b) prepared statements. (there may be others)
>>
>> I created several cursors and the contents of monCachedProcedures didn't change. I also tried opening and fetching ...
>> but no new entries in monCachedProcedures. (There was a *ss%ss* entry in monCachedProcedures but that was the cached
>> statement of my 'select * from monCachedProcedures' query.)
>>
>>
>> On 09/21/2011 11:28, Bret Halford wrote:
>>> It isn't documented anywhere that I know of, but a little
>>> experimentation found that every time I declared a cursor,
>>> a new entry was added to monCachedProcedure with a name
>>> with the pattern "*ss%ss*" with an object type of "stored procedure".
>>
>> ... snip ...
>
> Bret, are you testing on ASE 15.7 by any chance? One of the enhancements in 15.7 is that the query plan for cursors can
> also be cached in the statement cache. That would match the name you are seeing.
>