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.

qp metrics not being captured - security? - help

11 posts in General Discussion Last posting was on 2009-07-30 21:35:57.0Z
Alberto Posted on 2009-07-08 10:24:25.0Z
Sender: 2a95.4a5361f3.1804289383@sybase.com
From: Alberto
Newsgroups: sybase.public.ase.general
Subject: qp metrics not being captured - security? - help
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a5473d9.a4a.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 8 Jul 2009 03:24:25 -0700
X-Trace: forums-3-dub.sybase.com 1247048665 10.22.241.188 (8 Jul 2009 03:24:25 -0700)
X-Original-Trace: 8 Jul 2009 03:24:25 -0700, forums-3-dub.sybase.com
Lines: 17
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27971
Article PK: 77217

Greetings Gurus,

I'm running ASE15.0.3ESD#1 x64 on Solaris 10 x64, and am
trying to collect sysquerymetrics.

I've sp_configure 'enable metrics capture', 1
I've used sp_metrics 'flush' (done as SA)

When I select * from sysquerymetrics I'm only seeing qp
related to SA/DBO and none of the unprivileged users.

Is there a grant / role to allow regular users to collect
into sysqueryplans/sysquerymetrics?

Thanks in advance.

(I did post this in the performance forum, but no answers).


"Mark A. Parsons" <iron_horse Posted on 2009-07-08 11:51:36.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a5473d9.a4a.1681692777@sybase.com>
In-Reply-To: <4a5473d9.a4a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090706-0, 07/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a548848$1@forums-3-dub.sybase.com>
Date: 8 Jul 2009 04:51:36 -0700
X-Trace: forums-3-dub.sybase.com 1247053896 10.22.241.152 (8 Jul 2009 04:51:36 -0700)
X-Original-Trace: 8 Jul 2009 04:51:36 -0700, vip152.sybase.com
Lines: 72
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27974
Article PK: 77221

The uid column in sysquerymetrics/sysqueryplans is suppose to be the uid of the process that captured the stats.

In my 15.0.2 and 15.0.3 dataservers I see 'uid = 0' for all queries issued by sa/dbo *AND* any non-sa/dbo login. [uid=0
typically refers to an internal, user-less dataserver process]

I don't see anything in sysquerymetrics/sysqueryplans that tells me what login/user ran what query.

In fact, if I execute 'select count(*) from sysobjects' in the tempdb database (as both 'sa' and 'markp') I see one
record for said query in sysquerymetrics with sysquerymetrics.cnt=2; ie, sysquerymetrics keeps track of performance
stats aggregated by the actual query and not the login.

Sooooo, how are you determining that only SA/DBO queries are being captured, ie, how do you know a particular query was
executed by the SA/DBO?

----------------

Running some tests against 15.0.2 and 15.0.3 dataservers ... with 'enable metrics capture' = 1 ... I'm seeing the following:

15.0.2 - metrics are captured for sa/dbo and non-sa/dbo logins

15.0.3 - metrics are captured for sa/dbo and non-sa/dbo logins *ONLY IF* the login has first issued 'set metrics_capture
on', otherwise the metrics are *NOT* being captured

From this test it would appear that Sybase made a change in the dataserver code line that now requires a process (aka
spid) to first enable metrics_capture at the session level. ['enable metrics capture' must be set to 1 at the
dataserver level]

As a DBA this change (in 15.0.3) looks good since it provides better control as to when metrics are captured; thus
reducing a lot of the overhead (dataserver performance degradation; sysqueryplans space usage) that occurred in earlier
releases of ASE.

As a DBA this change (in 15.0.3) also means that if I want to capture metrics for some other process I'll either have to
a) get the process owner to modify his/her code to issue 'set metrics_capture on/off', b) attach a (global) login
trigger [to issue 'set metrics_capture on'] to said process's login, or c) see if I can use ASE 15's 'application
tracing' facility to dynamically 'set metrics_capture on' for the other process.

And unless I'm not looking in the right place ... it would appear that someone at Sybase forgot to document this new
'feature' of ASE 15.0.3.

What I'm puzzled about is that you mention being able to capture sa/dbo metrics ... did the parent session(s) have to
issue 'set metrics_capture on' (either manually, via login trigger, or through application tracing) in order to allow
the capture of stats?

----------------

FWIW, my test environments:

Adaptive Server Enterprise/15.0.2/EBF 15682 ESD#5/P/Linux Intel/Linux 2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Tue
Jun 17 13:35:53 2008

Adaptive Server Enterprise/15.0.3/EBF 16550 ESD#1/P/NT (IX86)/Windows 2003/ase1503/2680/32-bit/OPT/Thu Mar 05 00:21:40 2009

Alberto wrote:
> Greetings Gurus,
>
> I'm running ASE15.0.3ESD#1 x64 on Solaris 10 x64, and am
> trying to collect sysquerymetrics.
>
> I've sp_configure 'enable metrics capture', 1
> I've used sp_metrics 'flush' (done as SA)
>
> When I select * from sysquerymetrics I'm only seeing qp
> related to SA/DBO and none of the unprivileged users.
>
> Is there a grant / role to allow regular users to collect
> into sysqueryplans/sysquerymetrics?
>
> Thanks in advance.
>
> (I did post this in the performance forum, but no answers).


Alberto Posted on 2009-07-08 15:36:10.0Z
Sender: 2a95.4a5361f3.1804289383@sybase.com
From: Alberto
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a54bcea.3f79.1681692777@sybase.com>
References: <4a548848$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 8 Jul 2009 08:36:10 -0700
X-Trace: forums-3-dub.sybase.com 1247067370 10.22.241.188 (8 Jul 2009 08:36:10 -0700)
X-Original-Trace: 8 Jul 2009 08:36:10 -0700, forums-3-dub.sybase.com
Lines: 152
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27975
Article PK: 77222

Thanks for the response.

> The uid column in sysquerymetrics/sysqueryplans is suppose
> to be the uid of the process that captured the stats.
>
> In my 15.0.2 and 15.0.3 dataservers I see 'uid = 0' for
> all queries issued by sa/dbo *AND* any non-sa/dbo login.
> [uid=0 typically refers to an internal, user-less
> dataserver process]
>
> I don't see anything in sysquerymetrics/sysqueryplans that
> tells me what login/user ran what query.
>
> In fact, if I execute 'select count(*) from sysobjects' in
> the tempdb database (as both 'sa' and 'markp') I see one
> record for said query in sysquerymetrics with
> sysquerymetrics.cnt=2; ie, sysquerymetrics keeps track of
> performance stats aggregated by the actual query and not
> the login.
>
> Sooooo, how are you determining that only SA/DBO queries
> are being captured, ie, how do you know a particular query
> was executed by the SA/DBO?

The server has about 50 sessions (developers/testers)

I've enable "app tracing"
set tracefile '$FILE' for $SPID
and can see the SQL comming in from the 50 sessions

The queries are NOT showing up in sysquerymetrics.

If I use sa or a DBO, the the qp gets recorded.


> ----------------
>
> Running some tests against 15.0.2 and 15.0.3 dataservers
> ... with 'enable metrics capture' = 1 ... I'm seeing the
> following:
>
> 15.0.2 - metrics are captured for sa/dbo and non-sa/dbo
> logins
>
> 15.0.3 - metrics are captured for sa/dbo and non-sa/dbo
> logins *ONLY IF* the login has first issued 'set
> metrics_capture on', otherwise the metrics are *NOT*
> being captured
>
> From this test it would appear that Sybase made a change
> in the dataserver code line that now requires a process
> (aka spid) to first enable metrics_capture at the session
> level. ['enable metrics capture' must be set to 1 at the
> dataserver level]
>
> As a DBA this change (in 15.0.3) looks good since it
> provides better control as to when metrics are captured;
> thus reducing a lot of the overhead (dataserver
> performance degradation; sysqueryplans space usage) that
> occurred in earlier releases of ASE.
>
> As a DBA this change (in 15.0.3) also means that if I want
> to capture metrics for some other process I'll either have
> to
> a) get the process owner to modify his/her code to issue
> 'set metrics_capture on/off', b) attach a (global) login
> trigger [to issue 'set metrics_capture on'] to said
> process's login, or c) see if I can use ASE 15's
> 'application tracing' facility to dynamically 'set
> metrics_capture on' for the other process.
>
> And unless I'm not looking in the right place ... it would
> appear that someone at Sybase forgot to document this new
> 'feature' of ASE 15.0.3.
>
> What I'm puzzled about is that you mention being able to
> capture sa/dbo metrics ... did the parent session(s) have
> to issue 'set metrics_capture on' (either manually, via
> login trigger, or through application tracing) in order to
> allow the capture of stats?

I have not used 'set metrics_capture on/off'

As a test, I 2 opened sessions:
In session 1, as SA I do a

while:
use mydb
go
sp_metrics 'flush'
go
select * from sysquerymetrics
go
sleep 1
done

In session 2
I use a non-sa account and I execute some sql
No info is recorded in sysquerymetrics.

I grant sa_role to the user, and execute more sql

The second batch gets recordered, but not the first.

-------
IMHO, sp_configure 'enable metrics capture', 1
should be a global parameter and should capture all metrics
in all databases.

In my experience, there is minimal overhead enabling this.
I've used it on a busy ASE15.0.2ESD#5 server with minimal
impact.

If you need fine grained qp, then use 'set metrics_capture
on/off' in a login trigger.


> ----------------
>
> FWIW, my test environments:
>
> Adaptive Server Enterprise/15.0.2/EBF 15682 ESD#5/P/Linux
> Intel/Linux 2.4.21-47.ELsmp
> i686/ase1502/2528/32-bit/FBO/Tue Jun 17 13:35:53 2008
>
> Adaptive Server Enterprise/15.0.3/EBF 16550 ESD#1/P/NT
> (IX86)/Windows 2003/ase1503/2680/32-bit/OPT/Thu Mar 05
> 00:21:40 2009


I'll do some testing on ASE15.0.2ESD#5.


> Alberto wrote:
> > Greetings Gurus,
> >
> > I'm running ASE15.0.3ESD#1 x64 on Solaris 10 x64, and am
> > trying to collect sysquerymetrics.
> >
> > I've sp_configure 'enable metrics capture', 1
> > I've used sp_metrics 'flush' (done as SA)
> >
> > When I select * from sysquerymetrics I'm only seeing qp
> > related to SA/DBO and none of the unprivileged users.
> >
> > Is there a grant / role to allow regular users to
> > collect into sysqueryplans/sysquerymetrics?
> >
> > Thanks in advance.
> >
> > (I did post this in the performance forum, but no
> answers).


"Mark A. Parsons" <iron_horse Posted on 2009-07-08 16:48:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a548848$1@forums-3-dub.sybase.com> <4a54bcea.3f79.1681692777@sybase.com>
In-Reply-To: <4a54bcea.3f79.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090707-0, 07/07/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a54cdfb$3@forums-3-dub.sybase.com>
Date: 8 Jul 2009 09:48:59 -0700
X-Trace: forums-3-dub.sybase.com 1247071739 10.22.241.152 (8 Jul 2009 09:48:59 -0700)
X-Original-Trace: 8 Jul 2009 09:48:59 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27976
Article PK: 77223


>> Running some tests against 15.0.2 and 15.0.3 dataservers
>> ... with 'enable metrics capture' = 1 ... I'm seeing the
>> following:
>>
>> 15.0.2 - metrics are captured for sa/dbo and non-sa/dbo
>> logins
>>
>> 15.0.3 - metrics are captured for sa/dbo and non-sa/dbo
>> logins *ONLY IF* the login has first issued 'set
>> metrics_capture on', otherwise the metrics are *NOT*
>> being captured

My 15.0.3 tests were on a ... ummm ... errrrr ... 'modified' dataserver install. (long story)

After creating a new 15.0.3/ESD#1 dataserver I ran my tests again and found that I was capturing all metrics for all
users (sa/dbo and non-sa/dbo) without the need to issue any 'set metrics_capture' commands.

Do you get the same results (ie, missing metrics for non-sa-role users) on other ASE 15.0.3 dataservers?

... now I get to figure out what I did with that other ASE 15.0.3/ESD#1 dataserver that caused the earlier tests to
require the use of the 'set metrics_capture on' command ...

... snip ...

> In session 2
> I use a non-sa account and I execute some sql
> No info is recorded in sysquerymetrics.
>
> I grant sa_role to the user, and execute more sql
>
> The second batch gets recordered, but not the first.

Hmmmm ... interesting.

In my new ASE 15.0.3/ESD#1 tests my non-sa account did not have any roles granted to it, yet all of its queries were
captured in sysquerymetrics.

> -------
> IMHO, sp_configure 'enable metrics capture', 1
> should be a global parameter and should capture all metrics
> in all databases.
>
> In my experience, there is minimal overhead enabling this.
> I've used it on a busy ASE15.0.2ESD#5 server with minimal
> impact.

On dataservers with heavy, non-stored-proc SQL query activity I've seen noticeable performance degradations due to:

- large volumes of inserts into sysqueryplans (also uses up data and log space)

- heavy cpu usage for sysquerymetrics-based queries, especially as sysqueryplans grows in size (all of those function
calls, applied to large volumes of records, can eat up a good bit of cpu cycles)


Alberto Posted on 2009-07-08 22:47:29.0Z
Sender: 2a95.4a5361f3.1804289383@sybase.com
From: Alberto
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a552201.d93.1681692777@sybase.com>
References: <4a54cdfb$3@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 8 Jul 2009 15:47:29 -0700
X-Trace: forums-3-dub.sybase.com 1247093249 10.22.241.188 (8 Jul 2009 15:47:29 -0700)
X-Original-Trace: 8 Jul 2009 15:47:29 -0700, forums-3-dub.sybase.com
Lines: 56
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27979
Article PK: 77226


> >> Running some tests against 15.0.2 and 15.0.3
> dataservers >> ... with 'enable metrics capture' = 1 ...
> I'm seeing the >> following:

SNIP

> > In my experience, there is minimal overhead enabling
> > this. I've used it on a busy ASE15.0.2ESD#5 server with
> > minimal impact.
>
> On dataservers with heavy, non-stored-proc SQL query
> activity I've seen noticeable performance degradations due
> to:
>
> - large volumes of inserts into sysqueryplans (also uses
> up data and log space)
>
> - heavy cpu usage for sysquerymetrics-based queries,
> especially as sysqueryplans grows in size (all of those
> function calls, applied to large volumes of records, can
> eat up a good bit of cpu cycles)

I've been using qp_metrics for a few months - and must say
it's one of the most useful features in ASE15.
Makes it really easy to find top 10 most io / most executed
/ longest running, etc.

The server that I profiled was using a DBO account.

What I usually do is
while :
for alldb
use $DB
sp_metrics 'flush'
insert into tempdb..my_sysquerymetrics select
*,'$DB',getdate() from $db..sysquerymetrics
sp_metrics 'drop','1'
done
sleep 600
done

This flushes/backups/drops qp for the 10minute period.
Since we only kept 10minutes of qp data, performance was not
noticeably impacted.

With ASE15.0.2ESD#5, after a few hours, of flush/drop/sleep
600,
we started getting stack traces when issuing sp_metrics
'flush'
This was fixed in ESD#6.

I'll check on ASE15.0.2#ESD5 to see if the issue that I'm
seeing also existed there.

Thanks

Alberto


Rob V [Sybase] Posted on 2009-07-08 20:41:10.0Z
Reply-To: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a548848$1@forums-3-dub.sybase.com> <4a54bcea.3f79.1681692777@sybase.com>
Subject: Re: qp metrics not being captured - security? - help
Lines: 180
Organization: Sypron BV / TeamSybase / Sybase Inc
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a550466$1@forums-3-dub.sybase.com>
Date: 8 Jul 2009 13:41:10 -0700
X-Trace: forums-3-dub.sybase.com 1247085670 10.22.241.152 (8 Jul 2009 13:41:10 -0700)
X-Original-Trace: 8 Jul 2009 13:41:10 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27977
Article PK: 77224

Are you looking at the right database? Metrics are captured into
sysquerymetrics/sysqueryplans in the database where the query is executed,
not the database where the tables reside. I have seen cases where this
resulted in all captured metrics getting written into master or tempdb.

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"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<Alberto> wrote in message news:4a54bcea.3f79.1681692777@sybase.com...
> Thanks for the response.
>
>> The uid column in sysquerymetrics/sysqueryplans is suppose
>> to be the uid of the process that captured the stats.
>>
>> In my 15.0.2 and 15.0.3 dataservers I see 'uid = 0' for
>> all queries issued by sa/dbo *AND* any non-sa/dbo login.
>> [uid=0 typically refers to an internal, user-less
>> dataserver process]
>>
>> I don't see anything in sysquerymetrics/sysqueryplans that
>> tells me what login/user ran what query.
>>
>> In fact, if I execute 'select count(*) from sysobjects' in
>> the tempdb database (as both 'sa' and 'markp') I see one
>> record for said query in sysquerymetrics with
>> sysquerymetrics.cnt=2; ie, sysquerymetrics keeps track of
>> performance stats aggregated by the actual query and not
>> the login.
>>
>> Sooooo, how are you determining that only SA/DBO queries
>> are being captured, ie, how do you know a particular query
>> was executed by the SA/DBO?
>
> The server has about 50 sessions (developers/testers)
>
> I've enable "app tracing"
> set tracefile '$FILE' for $SPID
> and can see the SQL comming in from the 50 sessions
>
> The queries are NOT showing up in sysquerymetrics.
>
> If I use sa or a DBO, the the qp gets recorded.
>
>
>> ----------------
>>
>> Running some tests against 15.0.2 and 15.0.3 dataservers
>> ... with 'enable metrics capture' = 1 ... I'm seeing the
>> following:
>>
>> 15.0.2 - metrics are captured for sa/dbo and non-sa/dbo
>> logins
>>
>> 15.0.3 - metrics are captured for sa/dbo and non-sa/dbo
>> logins *ONLY IF* the login has first issued 'set
>> metrics_capture on', otherwise the metrics are *NOT*
>> being captured
>>
>> From this test it would appear that Sybase made a change
>> in the dataserver code line that now requires a process
>> (aka spid) to first enable metrics_capture at the session
>> level. ['enable metrics capture' must be set to 1 at the
>> dataserver level]
>>
>> As a DBA this change (in 15.0.3) looks good since it
>> provides better control as to when metrics are captured;
>> thus reducing a lot of the overhead (dataserver
>> performance degradation; sysqueryplans space usage) that
>> occurred in earlier releases of ASE.
>>
>> As a DBA this change (in 15.0.3) also means that if I want
>> to capture metrics for some other process I'll either have
>> to
>> a) get the process owner to modify his/her code to issue
>> 'set metrics_capture on/off', b) attach a (global) login
>> trigger [to issue 'set metrics_capture on'] to said
>> process's login, or c) see if I can use ASE 15's
>> 'application tracing' facility to dynamically 'set
>> metrics_capture on' for the other process.
>>
>> And unless I'm not looking in the right place ... it would
>> appear that someone at Sybase forgot to document this new
>> 'feature' of ASE 15.0.3.
>>
>> What I'm puzzled about is that you mention being able to
>> capture sa/dbo metrics ... did the parent session(s) have
>> to issue 'set metrics_capture on' (either manually, via
>> login trigger, or through application tracing) in order to
>> allow the capture of stats?
>
> I have not used 'set metrics_capture on/off'
>
> As a test, I 2 opened sessions:
> In session 1, as SA I do a
>
> while:
> use mydb
> go
> sp_metrics 'flush'
> go
> select * from sysquerymetrics
> go
> sleep 1
> done
>
> In session 2
> I use a non-sa account and I execute some sql
> No info is recorded in sysquerymetrics.
>
> I grant sa_role to the user, and execute more sql
>
> The second batch gets recordered, but not the first.
>
> -------
> IMHO, sp_configure 'enable metrics capture', 1
> should be a global parameter and should capture all metrics
> in all databases.
>
> In my experience, there is minimal overhead enabling this.
> I've used it on a busy ASE15.0.2ESD#5 server with minimal
> impact.
>
> If you need fine grained qp, then use 'set metrics_capture
> on/off' in a login trigger.
>
>
>> ----------------
>>
>> FWIW, my test environments:
>>
>> Adaptive Server Enterprise/15.0.2/EBF 15682 ESD#5/P/Linux
>> Intel/Linux 2.4.21-47.ELsmp
>> i686/ase1502/2528/32-bit/FBO/Tue Jun 17 13:35:53 2008
>>
>> Adaptive Server Enterprise/15.0.3/EBF 16550 ESD#1/P/NT
>> (IX86)/Windows 2003/ase1503/2680/32-bit/OPT/Thu Mar 05
>> 00:21:40 2009
>
>
> I'll do some testing on ASE15.0.2ESD#5.
>
>
>> Alberto wrote:
>> > Greetings Gurus,
>> >
>> > I'm running ASE15.0.3ESD#1 x64 on Solaris 10 x64, and am
>> > trying to collect sysquerymetrics.
>> >
>> > I've sp_configure 'enable metrics capture', 1
>> > I've used sp_metrics 'flush' (done as SA)
>> >
>> > When I select * from sysquerymetrics I'm only seeing qp
>> > related to SA/DBO and none of the unprivileged users.
>> >
>> > Is there a grant / role to allow regular users to
>> > collect into sysqueryplans/sysquerymetrics?
>> >
>> > Thanks in advance.
>> >
>> > (I did post this in the performance forum, but no
>> answers).


Alberto Posted on 2009-07-08 22:15:00.0Z
Sender: 2a95.4a5361f3.1804289383@sybase.com
From: Alberto
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a551a64.748.1681692777@sybase.com>
References: <4a550466$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 8 Jul 2009 15:15:00 -0700
X-Trace: forums-3-dub.sybase.com 1247091300 10.22.241.188 (8 Jul 2009 15:15:00 -0700)
X-Original-Trace: 8 Jul 2009 15:15:00 -0700, forums-3-dub.sybase.com
Lines: 28
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27978
Article PK: 77225

Thanks for the sugestion

I've looked in all the databases..sysquerymetrics.

The QP for non-sa users does not seem to be recordered.

As soon as I grant sa_role to ads2 (my test user),
any SQL issued by this account is logged.

Any further suggestions?

> Are you looking at the right database? Metrics are
> captured into sysquerymetrics/sysqueryplans in the
> database where the query is executed, not the database
> where the tables reside. I have seen cases where this
> resulted in all captured metrics getting written into
> master or tempdb.
>
> 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

SNIP


Jeff Tallman [Sybase] Posted on 2009-07-09 01:45:04.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a550466$1@forums-3-dub.sybase.com> <4a551a64.748.1681692777@sybase.com>
In-Reply-To: <4a551a64.748.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: <4a554ba0@forums-3-dub.sybase.com>
Date: 8 Jul 2009 18:45:04 -0700
X-Trace: forums-3-dub.sybase.com 1247103904 10.22.241.152 (8 Jul 2009 18:45:04 -0700)
X-Original-Trace: 8 Jul 2009 18:45:04 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27980
Article PK: 77228

Are the users running procs or cached queries from statement cache??

Even further out there - is there a login trigger that sets
metrics_capture off for the session??

Are the users using fully prepared statements???

Is compatibility mode on (shouldn't be a problem, but....)????

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Alberto wrote:
> Thanks for the sugestion
>
> I've looked in all the databases..sysquerymetrics.
>
> The QP for non-sa users does not seem to be recordered.
>
> As soon as I grant sa_role to ads2 (my test user),
> any SQL issued by this account is logged.
>
> Any further suggestions?
>
>> Are you looking at the right database? Metrics are
>> captured into sysquerymetrics/sysqueryplans in the
>> database where the query is executed, not the database
>> where the tables reside. I have seen cases where this
>> resulted in all captured metrics getting written into
>> master or tempdb.
>>
>> 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
>
> SNIP


Alberto Posted on 2009-07-09 07:08:12.0Z
From: Alberto <Alberto.daSilva@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a550466$1@forums-3-dub.sybase.com> <4a551a64.748.1681692777@sybase.com> <4a554ba0@forums-3-dub.sybase.com>
In-Reply-To: <4a554ba0@forums-3-dub.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: <4a55975c@forums-3-dub.sybase.com>
Date: 9 Jul 2009 00:08:12 -0700
X-Trace: forums-3-dub.sybase.com 1247123292 10.22.241.152 (9 Jul 2009 00:08:12 -0700)
X-Original-Trace: 9 Jul 2009 00:08:12 -0700, vip152.sybase.com
Lines: 81
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27983
Article PK: 77230


Jeff Tallman [Sybase] wrote:
>
> Are the users running procs or cached queries from statement cache??

Some are running stored procs.
We have the statement cache enabled.
We have literal autoparam enabled.

> Even further out there - is there a login trigger that sets
> metrics_capture off for the session??

Some of the users do have a login trigger to enforce maximum
allowed concurrent sessions.
The trigger does not do any "set" commands.

I will try with the login trigger dropped.

> Are the users using fully prepared statements???

No. The app is written in Smalltalk.

> Is compatibility mode on (shouldn't be a problem, but....)????

Compatibility mode not on.

In the tests I did granting sa_role resulted in qp being collected.
I used 2 sessions (isql)
In session 1 - as SA,
while :
"use tempdb
sp_metrics 'flush'
select * from sysquerymetrics" | isql -Usa -Ppass -Ssrv
sleep 1
done

In session 2 - as non-sa
while :
"use tempdb
select name from sysobjects where name like 'a%' |
isql -Uads2 -Ppass -Ssrv
sleep 1
done

doing a sp_role 'grant','sa_role','ads2' resulted in the qp being
collected in session 1


> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> Alberto wrote:
>> Thanks for the sugestion
>>
>> I've looked in all the databases..sysquerymetrics.
>>
>> The QP for non-sa users does not seem to be recordered.
>>
>> As soon as I grant sa_role to ads2 (my test user),
>> any SQL issued by this account is logged.
>>
>> Any further suggestions?
>>
>>> Are you looking at the right database? Metrics are
>>> captured into sysquerymetrics/sysqueryplans in the
>>> database where the query is executed, not the database
>>> where the tables reside. I have seen cases where this
>>> resulted in all captured metrics getting written into
>>> master or tempdb.
>>>
>>> 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
>>
>> SNIP


Alberto Posted on 2009-07-25 23:55:35.0Z
From: Alberto <Alberto.daSilva@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a550466$1@forums-3-dub.sybase.com> <4a551a64.748.1681692777@sybase.com> <4a554ba0@forums-3-dub.sybase.com> <4a55975c@forums-3-dub.sybase.com>
In-Reply-To: <4a55975c@forums-3-dub.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: <4a6b9b77$1@forums-3-dub.sybase.com>
Date: 25 Jul 2009 16:55:35 -0700
X-Trace: forums-3-dub.sybase.com 1248566135 10.22.241.152 (25 Jul 2009 16:55:35 -0700)
X-Original-Trace: 25 Jul 2009 16:55:35 -0700, vip152.sybase.com
Lines: 92
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28049
Article PK: 77296


Alberto wrote:
> Jeff Tallman [Sybase] wrote:
>>
>> Are the users running procs or cached queries from statement cache??
>
> Some are running stored procs.
> We have the statement cache enabled.
> We have literal autoparam enabled.
>
>> Even further out there - is there a login trigger that sets
>> metrics_capture off for the session??
>
> Some of the users do have a login trigger to enforce maximum
> allowed concurrent sessions.
> The trigger does not do any "set" commands.
>
> I will try with the login trigger dropped.
>
>> Are the users using fully prepared statements???
>
> No. The app is written in Smalltalk.
>
>> Is compatibility mode on (shouldn't be a problem, but....)????
>
> Compatibility mode not on.
>
> In the tests I did granting sa_role resulted in qp being collected.
> I used 2 sessions (isql)
> In session 1 - as SA,
> while :
> "use tempdb
> sp_metrics 'flush'
> select * from sysquerymetrics" | isql -Usa -Ppass -Ssrv
> sleep 1
> done
>
> In session 2 - as non-sa
> while :
> "use tempdb
> select name from sysobjects where name like 'a%' |
> isql -Uads2 -Ppass -Ssrv
> sleep 1
> done
>
> doing a sp_role 'grant','sa_role','ads2' resulted in the qp being
> collected in session 1
>
>
>> Jeff Tallman
>> Enterprise Data Management Products Technical Evangelism
>> jeff.tallman@sybase.com
>> http://blogs.sybase.com/database
>>
>> Alberto wrote:
>>> Thanks for the sugestion
>>>
>>> I've looked in all the databases..sysquerymetrics.
>>>
>>> The QP for non-sa users does not seem to be recordered.
>>>
>>> As soon as I grant sa_role to ads2 (my test user),
>>> any SQL issued by this account is logged.
>>>
>>> Any further suggestions?
>>>
>>>> Are you looking at the right database? Metrics are
>>>> captured into sysquerymetrics/sysqueryplans in the
>>>> database where the query is executed, not the database
>>>> where the tables reside. I have seen cases where this
>>>> resulted in all captured metrics getting written into
>>>> master or tempdb.
>>>>
>>>> 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
>>>
>>> SNIP

More testing on ASE15.0.3ESD#1,

Disabling the statement cache OR using dbo/sa for all users
causes the qp metrics to be captured.

Neither option is ideal.

Alberto


Jeff Tallman [Sybase] Posted on 2009-07-30 21:35:57.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: qp metrics not being captured - security? - help
References: <4a550466$1@forums-3-dub.sybase.com> <4a551a64.748.1681692777@sybase.com> <4a554ba0@forums-3-dub.sybase.com> <4a55975c@forums-3-dub.sybase.com> <4a6b9b77$1@forums-3-dub.sybase.com>
In-Reply-To: <4a6b9b77$1@forums-3-dub.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: <4a72123d$3@forums-3-dub.sybase.com>
Date: 30 Jul 2009 14:35:57 -0700
X-Trace: forums-3-dub.sybase.com 1248989757 10.22.241.152 (30 Jul 2009 14:35:57 -0700)
X-Original-Trace: 30 Jul 2009 14:35:57 -0700, vip152.sybase.com
Lines: 102
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28075
Article PK: 77320

Stats on cached statements is captured in monCachedStatement....


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Alberto wrote:
> Alberto wrote:
>> Jeff Tallman [Sybase] wrote:
>>>
>>> Are the users running procs or cached queries from statement cache??
>>
>> Some are running stored procs.
>> We have the statement cache enabled.
>> We have literal autoparam enabled.
>>
>>> Even further out there - is there a login trigger that sets
>>> metrics_capture off for the session??
>>
>> Some of the users do have a login trigger to enforce maximum
>> allowed concurrent sessions.
>> The trigger does not do any "set" commands.
>>
>> I will try with the login trigger dropped.
>>
>>> Are the users using fully prepared statements???
>>
>> No. The app is written in Smalltalk.
>>
>>> Is compatibility mode on (shouldn't be a problem, but....)????
>>
>> Compatibility mode not on.
>>
>> In the tests I did granting sa_role resulted in qp being collected.
>> I used 2 sessions (isql)
>> In session 1 - as SA,
>> while :
>> "use tempdb
>> sp_metrics 'flush'
>> select * from sysquerymetrics" | isql -Usa -Ppass -Ssrv
>> sleep 1
>> done
>>
>> In session 2 - as non-sa
>> while :
>> "use tempdb
>> select name from sysobjects where name like 'a%' |
>> isql -Uads2 -Ppass -Ssrv
>> sleep 1
>> done
>>
>> doing a sp_role 'grant','sa_role','ads2' resulted in the qp being
>> collected in session 1
>>
>>
>>> Jeff Tallman
>>> Enterprise Data Management Products Technical Evangelism
>>> jeff.tallman@sybase.com
>>> http://blogs.sybase.com/database
>>>
>>> Alberto wrote:
>>>> Thanks for the sugestion
>>>>
>>>> I've looked in all the databases..sysquerymetrics.
>>>>
>>>> The QP for non-sa users does not seem to be recordered.
>>>>
>>>> As soon as I grant sa_role to ads2 (my test user),
>>>> any SQL issued by this account is logged.
>>>>
>>>> Any further suggestions?
>>>>
>>>>> Are you looking at the right database? Metrics are
>>>>> captured into sysquerymetrics/sysqueryplans in the
>>>>> database where the query is executed, not the database
>>>>> where the tables reside. I have seen cases where this
>>>>> resulted in all captured metrics getting written into
>>>>> master or tempdb.
>>>>>
>>>>> 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
>>>>
>>>> SNIP
>
>
> More testing on ASE15.0.3ESD#1,
>
> Disabling the statement cache OR using dbo/sa for all users
> causes the qp metrics to be captured.
>
> Neither option is ideal.
>
> Alberto