Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

[Tim Ward t...@telensa.com [firebird-support]]

I lost the logging code some time ago so can't quote it I'm afraid. I 
defined an external table, mapped to a file, with columns for timestamp, 
a couple of numeric values, and a text message. Then a procedure which 
wrote a record to this table. Then called the procedure at points in my 
code where I suspected there were bottlenecks.


I haven't used this approach for a while because I found the profiling 
output easier to use (no need to instrument the code) and just as 
useful. This involves running the query


select * from mon$call_stack order by mon$call_id desc

from a PHP script once a second and (if you can be bothered) doing 
various post-processing on the result. The query appears to only work 
sometimes, and I don't know why, but it works often enough to be useful. 
This is Firebird 2.1.


On 13/10/2015 19:30, 'stwizard' stwiz...@att.net [firebird-support] wrote:


Hi Tim,

Thanks for the reply.  You mentioned that you did it with a logging 
procedure.  Can you share what you exactly what you did to accomplish 
this?


Mike





--
Tim Ward

Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am 14.10.2015 
10:09:

> I think a tool like FBTraceManager from Thomas (Upscene) can do that for you

Unfortunately not. The Trace API does not provide that level of granularity for 
PSQL code modules.

This has been a long discussion in firebird-devel in the past, people basically 
argued that the Trace API isn't the right place for that sort of granularity, 
but more appropriate for a debugging/profiling feature. I still believe in the 
Trace API being a good profiling tool though. ;-)



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> 
> 
> 
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Sent: 13 October 2015 05:31 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How can I see which query within a stored
> procedure execution takes the longest time?
> 
> 
> 
>  
> 
> Greetings All,
> 
> 
> 
> Firebird 2.5.4
> 
> I would like to know if there is any way that I can retrieve the execution
> time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 
> 
> Any tool to accomplish this?
> 
> Thanks,
> 
> Mike
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "Louis van Alphen" 
> 
> 
> ++
> 
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu 
> there.
> 
> Also search the knowledgebases at 
> http://www.ibphoenix.com/resources/documents/
> 
> 
> ++
> 
> 
> Yahoo Groups Links
> 
> 
> 

RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I think a tool like FBTraceManager from Thomas (Upscene) can do that for you

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 13 October 2015 05:31 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How can I see which query within a stored
procedure execution takes the longest time?

 

  

Greetings All,

 

Firebird 2.5.4

I would like to know if there is any way that I can retrieve the execution
time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 

Any tool to accomplish this?

Thanks,

Mike

 

 

 

 





[Non-text portions of this message have been removed]

RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['Louis van Alphen' lo...@nucleo.co.za [firebird-support]]

I see.. but I agree with you

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 14 October 2015 11:20 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How can I see which query within a stored
procedure execution takes the longest time?

 

  

'Louis van Alphen' lo...@nucleo.co.za [firebird-support] schrieb am
14.10.2015 10:09:

> I think a tool like FBTraceManager from Thomas (Upscene) can do that for
you

Unfortunately not. The Trace API does not provide that level of granularity
for PSQL code modules.

This has been a long discussion in firebird-devel in the past, people
basically argued that the Trace API isn't the right place for that sort of
granularity, but more appropriate for a debugging/profiling feature. I still
believe in the Trace API being a good profiling tool though. ;-)

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> 
> 
> 
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] 
> Sent: 13 October 2015 05:31 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How can I see which query within a stored
> procedure execution takes the longest time?
> 
> 
> 
> 
> 
> Greetings All,
> 
> 
> 
> Firebird 2.5.4
> 
> I would like to know if there is any way that I can retrieve the execution
> time of each SQL SELECT or EXECUTE PROCEDURE within a stored procedure? 
> 
> Any tool to accomplish this?
> 
> Thanks,
> 
> Mike
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: "Louis van Alphen" <lo...@nucleo.co.za>
> 
> 
> ++
> 
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu
there.
> 
> Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/
> 
> 
> ++
> 
> 
> Yahoo Groups Links
> 
> 
> 





[Non-text portions of this message have been removed]

Re: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

[Tim Ward t...@telensa.com [firebird-support]]

I did it by writing a logging procedure which wrote timestamps to an 
external file mapped to a text file, then calling it where I was interested.


An alternative is Monte Carlo profiling - I've also got a script which 
polls the call stack from the RDB$ tables. The procedure or statement it 
hits most often is the one taking the most time! (This turns out not to 
be terribly reliable in terms of actually getting call stack data every 
time it asks, but it does seem to be pretty reliable in pinpointing the 
performance bottlenecks.)


On 13/10/2015 16:30, 'stwizard' stwiz...@att.net [firebird-support] wrote:


Greetings All,

Firebird 2.5.4

I would like to know if there is any way that I can retrieve the 
execution time of each SQL SELECT or EXECUTE PROCEDURE within a stored 
procedure?


Any tool to accomplish this?

Thanks,

Mike





--
Tim Ward

RE: [firebird-support] How can I see which query within a stored procedure execution takes the longest time?

['stwizard' stwiz...@att.net [firebird-support]]

Hi Tim,

 

Thanks for the reply.  You mentioned that you did it with a logging
procedure.  Can you share what you exactly what you did to accomplish this?


 

Mike