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.

HELP: No useful sqltext available with tracefile

9 posts in General Discussion Last posting was on 2009-07-27 19:51:44.0Z
Alberto Posted on 2009-07-22 14:36:02.0Z
Sender: 77a1.4a672107.1804289383@sybase.com
From: Alberto
Newsgroups: sybase.public.ase.general
Subject: HELP: No useful sqltext available with tracefile
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a6723d2.79c5.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 22 Jul 2009 07:36:02 -0700
X-Trace: forums-3-dub.sybase.com 1248273362 10.22.241.188 (22 Jul 2009 07:36:02 -0700)
X-Original-Trace: 22 Jul 2009 07:36:02 -0700, forums-3-dub.sybase.com
Lines: 34
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28022
Article PK: 77269

Greeting Experts,

On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
set tracefile '/tmp/apptrace.123' for 123
go
set show_sqltext on
set statistics io on
set statistics time off
set statistics plancost off
set showplan off
go

I'm getting
2009/07/22 14:31:17.21
**No useful sqltext available.**
Table: pricing_param_items scan count 1, logical reads:
(regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
total=0), apf IOs used=0
Total actual I/O cost for this command: 12.
Total writes for this command: 0
End of Batch 72549


Well that kind of defeats the purpose of
tracefile/show_sqltext.

If I do dbcc traceon(11202), I can see the sql coming in,
but not in tracefile.

How do I get a trace of incoming SQL?

Thanks,

Alberto


J Posted on 2009-07-22 15:15:51.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: HELP: No useful sqltext available with tracefile
Reply-To: J@bogusemailAddress.com
Message-ID: <4a672c34.1097327@forums.sybase.com>
References: <4a6723d2.79c5.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Jul 2009 08:15:51 -0700
X-Trace: forums-3-dub.sybase.com 1248275751 10.22.241.152 (22 Jul 2009 08:15:51 -0700)
X-Original-Trace: 22 Jul 2009 08:15:51 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28023
Article PK: 77270

On 22 Jul 2009 07:36:02 -0700, Alberto wrote:

I think you probably have "statement caching" enabled so the sqltext
is not captured after the first appearance of your statement from
previous caching.

Jay

>Greeting Experts,
>
>On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>set tracefile '/tmp/apptrace.123' for 123
>go
>set show_sqltext on
>set statistics io on
>set statistics time off
>set statistics plancost off
>set showplan off
>go
>
>I'm getting
>2009/07/22 14:31:17.21
>**No useful sqltext available.**
>Table: pricing_param_items scan count 1, logical reads:
>(regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>total=0), apf IOs used=0
>Total actual I/O cost for this command: 12.
>Total writes for this command: 0
>End of Batch 72549
>
>
>Well that kind of defeats the purpose of
>tracefile/show_sqltext.
>
>If I do dbcc traceon(11202), I can see the sql coming in,
>but not in tracefile.
>
>How do I get a trace of incoming SQL?
>
>Thanks,
>
>Alberto


J Posted on 2009-07-22 15:20:28.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: HELP: No useful sqltext available with tracefile
Reply-To: J@bogusemailAddress.com
Message-ID: <4a672e0a.1567984@forums.sybase.com>
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Jul 2009 08:20:28 -0700
X-Trace: forums-3-dub.sybase.com 1248276028 10.22.241.152 (22 Jul 2009 08:20:28 -0700)
X-Original-Trace: 22 Jul 2009 08:20:28 -0700, vip152.sybase.com
Lines: 54
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28024
Article PK: 77273

On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:

To turn off statement caching for your client session add:
set statement_cache off

to you list of "set"'s ....


Jay

>On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>
>I think you probably have "statement caching" enabled so the sqltext
>is not captured after the first appearance of your statement from
>previous caching.
>
>Jay
>
>>Greeting Experts,
>>
>>On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>set tracefile '/tmp/apptrace.123' for 123
>>go
>>set show_sqltext on
>>set statistics io on
>>set statistics time off
>>set statistics plancost off
>>set showplan off
>>go
>>
>>I'm getting
>>2009/07/22 14:31:17.21
>>**No useful sqltext available.**
>>Table: pricing_param_items scan count 1, logical reads:
>>(regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>total=0), apf IOs used=0
>>Total actual I/O cost for this command: 12.
>>Total writes for this command: 0
>>End of Batch 72549
>>
>>
>>Well that kind of defeats the purpose of
>>tracefile/show_sqltext.
>>
>>If I do dbcc traceon(11202), I can see the sql coming in,
>>but not in tracefile.
>>
>>How do I get a trace of incoming SQL?
>>
>>Thanks,
>>
>>Alberto
>


Alberto Posted on 2009-07-22 15:51:20.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: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com>
In-Reply-To: <4a672e0a.1567984@forums.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: <4a673578@forums-3-dub.sybase.com>
Date: 22 Jul 2009 08:51:20 -0700
X-Trace: forums-3-dub.sybase.com 1248277880 10.22.241.152 (22 Jul 2009 08:51:20 -0700)
X-Original-Trace: 22 Jul 2009 08:51:20 -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:28025
Article PK: 77271

Thanks J,

Tried sp_configure "statement cache",0
Still "No useful sqltext available."

The purpose of tracefile is to save all sql.
On ASE15.0.2 it works as documented - all SQL is saved.
Event with statement cache enabled.
All our servers have statement cache enabled.

According ASE15/Reference Manual/Commands/set tracefile:
"Once enabled, set tracefile saves *all* SQL text for the
current session to the specified file, each SQL text batch
appending to the previous batch. "

On ASE15.0.3ESD#1, it outputs "No useful sqltext available."

Seems to be a "feature" of ASE15.0.3ESD#1.
Two steps forward, one step back.

J wrote:
> On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:
>
> To turn off statement caching for your client session add:
> set statement_cache off
>
> to you list of "set"'s ....
>
> Jay
>
>> On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>>
>> I think you probably have "statement caching" enabled so the sqltext
>> is not captured after the first appearance of your statement from
>> previous caching.
>>
>> Jay
>>
>>> Greeting Experts,
>>>
>>> On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>> set tracefile '/tmp/apptrace.123' for 123
>>> go
>>> set show_sqltext on
>>> set statistics io on
>>> set statistics time off
>>> set statistics plancost off
>>> set showplan off
>>> go
>>>
>>> I'm getting
>>> 2009/07/22 14:31:17.21
>>> **No useful sqltext available.**
>>> Table: pricing_param_items scan count 1, logical reads:
>>> (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>> total=0), apf IOs used=0
>>> Total actual I/O cost for this command: 12.
>>> Total writes for this command: 0
>>> End of Batch 72549
>>>
>>>
>>> Well that kind of defeats the purpose of
>>> tracefile/show_sqltext.
>>>
>>> If I do dbcc traceon(11202), I can see the sql coming in,
>>> but not in tracefile.
>>>
>>> How do I get a trace of incoming SQL?
>>>
>>> Thanks,
>>>
>>> Alberto


Alberto Posted on 2009-07-22 16:47:54.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: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com> <4a673578@forums-3-dub.sybase.com>
In-Reply-To: <4a673578@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: <4a6742ba@forums-3-dub.sybase.com>
Date: 22 Jul 2009 09:47:54 -0700
X-Trace: forums-3-dub.sybase.com 1248281274 10.22.241.152 (22 Jul 2009 09:47:54 -0700)
X-Original-Trace: 22 Jul 2009 09:47:54 -0700, vip152.sybase.com
Lines: 157
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28026
Article PK: 77272

I've done more testing on ASE15.0.3ESD#1.

Disabling the statement cache causes about 90%
of the SQL goto the tracefile.
About 10% of SQL still shows "No useful sqltext available."

The purpose of "set tracfile" is to capture *all* SQL supplied by the
session regardless of statement cache settings.

What do DBA's out there use to capture all the SQL?

Alberto

Alberto wrote:
> Thanks J,
>
> Tried sp_configure "statement cache",0
> Still "No useful sqltext available."
>
> The purpose of tracefile is to save all sql.
> On ASE15.0.2 it works as documented - all SQL is saved.
> Event with statement cache enabled.
> All our servers have statement cache enabled.
>
> According ASE15/Reference Manual/Commands/set tracefile:
> "Once enabled, set tracefile saves *all* SQL text for the
> current session to the specified file, each SQL text batch
> appending to the previous batch. "
>
> On ASE15.0.3ESD#1, it outputs "No useful sqltext available."
>
> Seems to be a "feature" of ASE15.0.3ESD#1.
> Two steps forward, one step back.
>
> J wrote:
>> On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:
>>
>> To turn off statement caching for your client session add: set
>> statement_cache off
>>
>> to you list of "set"'s ....
>>
>> Jay
>>
>>> On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>>>
>>> I think you probably have "statement caching" enabled so the sqltext
>>> is not captured after the first appearance of your statement from
>>> previous caching.
>>> Jay
>>>
>>>> Greeting Experts,
>>>>
>>>> On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>>> set tracefile '/tmp/apptrace.123' for 123
>>>> go
>>>> set show_sqltext on
>>>> set statistics io on
>>>> set statistics time off
>>>> set statistics plancost off
>>>> set showplan off
>>>> go
>>>>
>>>> I'm getting
>>>> 2009/07/22 14:31:17.21
>>>> **No useful sqltext available.**
>>>> Table: pricing_param_items scan count 1, logical reads:
>>>> (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>>> total=0), apf IOs used=0
>>>> Total actual I/O cost for this command: 12.
>>>> Total writes for this command: 0
>>>> End of Batch 72549
>>>>
>>>>
>>>> Well that kind of defeats the purpose of
>>>> tracefile/show_sqltext.
>>>>
>>>> If I do dbcc traceon(11202), I can see the sql coming in,
>>>> but not in tracefile.
>>>>
>>>> How do I get a trace of incoming SQL?
>>>>
>>>> Thanks,
>>>>
>>>> Alberto

> Thanks J,
>
> Tried sp_configure "statement cache",0
> Still "No useful sqltext available."
>
> The purpose of tracefile is to save all sql.
> On ASE15.0.2 it works as documented - all SQL is saved.
> Event with statement cache enabled.
> All our servers have statement cache enabled.
>
> According ASE15/Reference Manual/Commands/set tracefile:
> "Once enabled, set tracefile saves *all* SQL text for the
> current session to the specified file, each SQL text batch
> appending to the previous batch. "
>
> On ASE15.0.3ESD#1, it outputs "No useful sqltext available."
>
> Seems to be a "feature" of ASE15.0.3ESD#1.
> Two steps forward, one step back.
>
> J wrote:
>> On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:
>>
>> To turn off statement caching for your client session add: set
>> statement_cache off
>>
>> to you list of "set"'s ....
>>
>> Jay
>>
>>> On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>>>
>>> I think you probably have "statement caching" enabled so the sqltext
>>> is not captured after the first appearance of your statement from
>>> previous caching.
>>> Jay
>>>
>>>> Greeting Experts,
>>>>
>>>> On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>>> set tracefile '/tmp/apptrace.123' for 123
>>>> go
>>>> set show_sqltext on
>>>> set statistics io on
>>>> set statistics time off
>>>> set statistics plancost off
>>>> set showplan off
>>>> go
>>>>
>>>> I'm getting
>>>> 2009/07/22 14:31:17.21
>>>> **No useful sqltext available.**
>>>> Table: pricing_param_items scan count 1, logical reads:
>>>> (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>>> total=0), apf IOs used=0
>>>> Total actual I/O cost for this command: 12.
>>>> Total writes for this command: 0
>>>> End of Batch 72549
>>>>
>>>>
>>>> Well that kind of defeats the purpose of
>>>> tracefile/show_sqltext.
>>>>
>>>> If I do dbcc traceon(11202), I can see the sql coming in,
>>>> but not in tracefile.
>>>>
>>>> How do I get a trace of incoming SQL?
>>>>
>>>> Thanks,
>>>>
>>>> Alberto


Jeff Tallman [Sybase] Posted on 2009-07-23 22:29:43.0Z
Message-ID: <4A68E45A.1060408@sybase.com>
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
To: Alberto <Alberto.daSilva@gmail.com>
Subject: Re: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com> <4a673578@forums-3-dub.sybase.com> <4a6742ba@forums-3-dub.sybase.com>
In-Reply-To: <4a6742ba@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
Date: 23 Jul 2009 15:29:43 -0700
X-Trace: forums-3-dub.sybase.com 1248388183 10.22.241.152 (23 Jul 2009 15:29:43 -0700)
X-Original-Trace: 23 Jul 2009 15:29:43 -0700, vip152.sybase.com
Lines: 166
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28041
Article PK: 77288

Is it a fully prepared statement??? Is it a RPC call??? If either,
no SQL text - period.

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

Alberto wrote:
> I've done more testing on ASE15.0.3ESD#1.
>
> Disabling the statement cache causes about 90%
> of the SQL goto the tracefile.
> About 10% of SQL still shows "No useful sqltext available."
>
> The purpose of "set tracfile" is to capture *all* SQL supplied by the
> session regardless of statement cache settings.
>
> What do DBA's out there use to capture all the SQL?
>
> Alberto
>
> Alberto wrote:
>> Thanks J,
>>
>> Tried sp_configure "statement cache",0
>> Still "No useful sqltext available."
>>
>> The purpose of tracefile is to save all sql.
>> On ASE15.0.2 it works as documented - all SQL is saved.
>> Event with statement cache enabled.
>> All our servers have statement cache enabled.
>>
>> According ASE15/Reference Manual/Commands/set tracefile:
>> "Once enabled, set tracefile saves *all* SQL text for the
>> current session to the specified file, each SQL text batch
>> appending to the previous batch. "
>>
>> On ASE15.0.3ESD#1, it outputs "No useful sqltext available."
>>
>> Seems to be a "feature" of ASE15.0.3ESD#1.
>> Two steps forward, one step back.
>>
>> J wrote:
>>> On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:
>>>
>>> To turn off statement caching for your client session add: set
>>> statement_cache off
>>>
>>> to you list of "set"'s ....
>>>
>>> Jay
>>>
>>>> On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>>>>
>>>> I think you probably have "statement caching" enabled so the sqltext
>>>> is not captured after the first appearance of your statement from
>>>> previous caching.
>>>> Jay
>>>>
>>>>> Greeting Experts,
>>>>>
>>>>> On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>>>> set tracefile '/tmp/apptrace.123' for 123
>>>>> go
>>>>> set show_sqltext on
>>>>> set statistics io on
>>>>> set statistics time off
>>>>> set statistics plancost off
>>>>> set showplan off
>>>>> go
>>>>>
>>>>> I'm getting
>>>>> 2009/07/22 14:31:17.21
>>>>> **No useful sqltext available.**
>>>>> Table: pricing_param_items scan count 1, logical reads:
>>>>> (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>>>> total=0), apf IOs used=0
>>>>> Total actual I/O cost for this command: 12.
>>>>> Total writes for this command: 0
>>>>> End of Batch 72549
>>>>>
>>>>>
>>>>> Well that kind of defeats the purpose of
>>>>> tracefile/show_sqltext.
>>>>>
>>>>> If I do dbcc traceon(11202), I can see the sql coming in,
>>>>> but not in tracefile.
>>>>>
>>>>> How do I get a trace of incoming SQL?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Alberto
>
>> Thanks J,
>>
>> Tried sp_configure "statement cache",0
>> Still "No useful sqltext available."
>>
>> The purpose of tracefile is to save all sql.
>> On ASE15.0.2 it works as documented - all SQL is saved.
>> Event with statement cache enabled.
>> All our servers have statement cache enabled.
>>
>> According ASE15/Reference Manual/Commands/set tracefile:
>> "Once enabled, set tracefile saves *all* SQL text for the
>> current session to the specified file, each SQL text batch
>> appending to the previous batch. "
>>
>> On ASE15.0.3ESD#1, it outputs "No useful sqltext available."
>>
>> Seems to be a "feature" of ASE15.0.3ESD#1.
>> Two steps forward, one step back.
>>
>> J wrote:
>>> On 22 Jul 2009 08:15:51 -0700, jtotally_bogus@sbcglobal.net (J) wrote:
>>>
>>> To turn off statement caching for your client session add: set
>>> statement_cache off
>>>
>>> to you list of "set"'s ....
>>>
>>> Jay
>>>
>>>> On 22 Jul 2009 07:36:02 -0700, Alberto wrote:
>>>>
>>>> I think you probably have "statement caching" enabled so the sqltext
>>>> is not captured after the first appearance of your statement from
>>>> previous caching.
>>>> Jay
>>>>
>>>>> Greeting Experts,
>>>>>
>>>>> On ASE15.0.3ESD#1 x64 on Solaris 10 x64, when I use
>>>>> set tracefile '/tmp/apptrace.123' for 123
>>>>> go
>>>>> set show_sqltext on
>>>>> set statistics io on
>>>>> set statistics time off
>>>>> set statistics plancost off
>>>>> set showplan off
>>>>> go
>>>>>
>>>>> I'm getting
>>>>> 2009/07/22 14:31:17.21
>>>>> **No useful sqltext available.**
>>>>> Table: pricing_param_items scan count 1, logical reads:
>>>>> (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0
>>>>> total=0), apf IOs used=0
>>>>> Total actual I/O cost for this command: 12.
>>>>> Total writes for this command: 0
>>>>> End of Batch 72549
>>>>>
>>>>>
>>>>> Well that kind of defeats the purpose of
>>>>> tracefile/show_sqltext.
>>>>>
>>>>> If I do dbcc traceon(11202), I can see the sql coming in,
>>>>> but not in tracefile.
>>>>>
>>>>> How do I get a trace of incoming SQL?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Alberto


Bret Halford [Sybase] Posted on 2009-07-23 22:58:55.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com> <4a673578@forums-3-dub.sybase.com> <4a6742ba@forums-3-dub.sybase.com>
In-Reply-To: <4a6742ba@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: <4a68eb2f$1@forums-3-dub.sybase.com>
Date: 23 Jul 2009 15:58:55 -0700
X-Trace: forums-3-dub.sybase.com 1248389935 10.22.241.152 (23 Jul 2009 15:58:55 -0700)
X-Original-Trace: 23 Jul 2009 15:58:55 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28042
Article PK: 77289


Alberto wrote:
> I've done more testing on ASE15.0.3ESD#1.
>
> Disabling the statement cache causes about 90%
> of the SQL goto the tracefile.
> About 10% of SQL still shows "No useful sqltext available."
>
> The purpose of "set tracfile" is to capture *all* SQL supplied by the
> session regardless of statement cache settings.
>
> What do DBA's out there use to capture all the SQL?
>
> Alberto

Unfortunately, these is some disagreement on the purpose of the command.
It was developed by the optimizer group to provide information about
sql commands being sent through the optimizer, but was documented
(as you know) to capture all commands. There was a bug, fixed
in 15.0.2 #6, where the whole batch sql was being printed twice
under some circumstances. The solution implemented was to limit
the feature to only capture individual statements which went
through the optimizer. Various utility commands (CREATE, USE, etc)
do not make that journey and the optimizer does not consider them
"useful" (the individual statement text isn't available from that
viewpoint) thus the workding of the message.

In terms of what to use - the MDA table monSysSQLText is one method,
auditing cmdtext is another.

-bret


Alberto Posted on 2009-07-27 19:47:44.0Z
Message-ID: <4A6E046D.8050406@gmail.com>
From: Alberto <Alberto.daSilva@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: "Bret Halford [Sybase]" <bret@sybase.com>
Subject: Re: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com> <4a673578@forums-3-dub.sybase.com> <4a6742ba@forums-3-dub.sybase.com> <4a68eb2f$1@forums-3-dub.sybase.com>
In-Reply-To: <4a68eb2f$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
Date: 27 Jul 2009 12:47:44 -0700
X-Trace: forums-3-dub.sybase.com 1248724064 10.22.241.152 (27 Jul 2009 12:47:44 -0700)
X-Original-Trace: 27 Jul 2009 12:47:44 -0700, vip152.sybase.com
Lines: 76
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28054
Article PK: 77301


Bret Halford [Sybase] wrote:
> Alberto wrote:
>> I've done more testing on ASE15.0.3ESD#1.
>>
>> Disabling the statement cache causes about 90%
>> of the SQL goto the tracefile.
>> About 10% of SQL still shows "No useful sqltext available."
>>
>> The purpose of "set tracfile" is to capture *all* SQL supplied by the
>> session regardless of statement cache settings.
>>
>> What do DBA's out there use to capture all the SQL?
>>
>> Alberto
>
> Unfortunately, these is some disagreement on the purpose of the command.
> It was developed by the optimizer group to provide information about
> sql commands being sent through the optimizer, but was documented
> (as you know) to capture all commands. There was a bug, fixed
> in 15.0.2 #6, where the whole batch sql was being printed twice
> under some circumstances. The solution implemented was to limit
> the feature to only capture individual statements which went
> through the optimizer. Various utility commands (CREATE, USE, etc)
> do not make that journey and the optimizer does not consider them
> "useful" (the individual statement text isn't available from that
> viewpoint) thus the workding of the message.
>
> In terms of what to use - the MDA table monSysSQLText is one method,
> auditing cmdtext is another.
>
> -bret

Thanks Bret,

At least I know why I can only use tracefile with
statement cache disabled - a bit disappointing.

For me, I would prefer *all* SQL that is sent to ASE
to appear in tracefile - almost like a network sniffer.
Logging them to a textfile is fast and efficient, with low overhead.

I'm trying monSysSQLText, requires lots of memory.
Also the overhead of saving the ton of SQL.

If QP metrics are to be believed, we are
seeing 885,000+ SQL statements per *minute* being sent ASE.
Mostly SELECTS, some insert, updates, deletes.

QP cnt INTERVAL 10 mins
--------------------------------
6,233,419 2009/07/24 09:00
6,929,633 2009/07/24 09:10
7,596,008 2009/07/24 09:20
8,196,976 2009/07/24 09:30
8,853,229 2009/07/24 09:40
2,238,289 2009/07/24 09:50
2,827,508 2009/07/24 10:00

2 CPU Xeon Quad Core E5450 3Ghz.

I've enabled cmdtext auditing, and expect the impact to be heavy.

For the problem that I am trying to resolve, I needed to know what the
last 90-100 SQL that was sent to ASE by a session (SPID).
tail -100 tracefile.spid would do the job just nicely.
dbcc traceon(11202) is not session based.

The session seems to not commit the batch, and is causing locks.

QP can't help.
I'm not sure if cmdtext or monSysSQLText will help
as the batch may not have been committed yet.

Any suggestions welcome.

Alberto


Alberto Posted on 2009-07-27 19:51:44.0Z
Message-ID: <4A6E055D.7040005@gmail.com>
From: Alberto <Alberto.daSilva@gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: "Bret Halford [Sybase]" <bret@sybase.com>
Subject: Re: HELP: No useful sqltext available with tracefile
References: <4a6723d2.79c5.1681692777@sybase.com> <4a672c34.1097327@forums.sybase.com> <4a672e0a.1567984@forums.sybase.com> <4a673578@forums-3-dub.sybase.com> <4a6742ba@forums-3-dub.sybase.com> <4a68eb2f$1@forums-3-dub.sybase.com>
In-Reply-To: <4a68eb2f$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
Date: 27 Jul 2009 12:51:44 -0700
X-Trace: forums-3-dub.sybase.com 1248724304 10.22.241.152 (27 Jul 2009 12:51:44 -0700)
X-Original-Trace: 27 Jul 2009 12:51:44 -0700, vip152.sybase.com
Lines: 76
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28055
Article PK: 77302


Bret Halford [Sybase] wrote:
> Alberto wrote:
>> I've done more testing on ASE15.0.3ESD#1.
>>
>> Disabling the statement cache causes about 90%
>> of the SQL goto the tracefile.
>> About 10% of SQL still shows "No useful sqltext available."
>>
>> The purpose of "set tracfile" is to capture *all* SQL supplied by the
>> session regardless of statement cache settings.
>>
>> What do DBA's out there use to capture all the SQL?
>>
>> Alberto
>
> Unfortunately, these is some disagreement on the purpose of the command.
> It was developed by the optimizer group to provide information about
> sql commands being sent through the optimizer, but was documented
> (as you know) to capture all commands. There was a bug, fixed
> in 15.0.2 #6, where the whole batch sql was being printed twice
> under some circumstances. The solution implemented was to limit
> the feature to only capture individual statements which went
> through the optimizer. Various utility commands (CREATE, USE, etc)
> do not make that journey and the optimizer does not consider them
> "useful" (the individual statement text isn't available from that
> viewpoint) thus the workding of the message.
>
> In terms of what to use - the MDA table monSysSQLText is one method,
> auditing cmdtext is another.
>
> -bret

Thanks Bret,

At least I know why I can only use tracefile with
statement cache disabled - a bit disappointing.

For me, I would prefer *all* SQL that is sent to ASE
to appear in tracefile - almost like a network sniffer.
Logging them to a textfile is fast and efficient, with low overhead.

I'm trying monSysSQLText, requires lots of memory.
Also the overhead of saving the ton of SQL.

If QP metrics are to be believed, we are
seeing 885,000+ SQL statements per *minute* being sent ASE.
Mostly SELECTS, some insert, updates, deletes.

QP cnt INTERVAL 10 mins
--------------------------------
6,233,419 2009/07/24 09:00
6,929,633 2009/07/24 09:10
7,596,008 2009/07/24 09:20
8,196,976 2009/07/24 09:30
8,853,229 2009/07/24 09:40
2,238,289 2009/07/24 09:50
2,827,508 2009/07/24 10:00

2 CPU Xeon Quad Core E5450 3Ghz.

I've enabled cmdtext auditing, and expect the impact to be heavy.

For the problem that I am trying to resolve, I needed to know what the
last 90-100 SQL that was sent to ASE by a session (SPID).
tail -100 tracefile.spid would do the job just nicely.
dbcc traceon(11202) is not session based.

The session seems to not commit the batch, and is causing locks.

QP can't help.
I'm not sure if cmdtext or monSysSQLText will help
as the batch may not have been committed yet.

Any suggestions welcome.

Alberto