Hello Sean, >>>>> I am also very skeptical about the "need" that to add this >>>>> functionality to >>>> the Trace API, as it falls into the area of PSQL debugging/profiling >>>> which is not what the current functionality is about*. >>>> >>>> I don't propose to extend the Trace API in a way, which goes into >>>> debuging a stored procedure with all that stuff like local variable values >> etc. >>>> >>>> IMHO this should be part of a separate Debug API, which AFAIK has >>>> been discussed in the past to expose something more reliable to >>>> third-party developer tools like Database Workbench, IBExpert ... >>>> than what they currently try to "simulate". >>> >>> I am happy that you agree with the general functional boundaries. >>> >>> >>>> For databases, which are very PSQL-centric, which have a lot of >>>> business logic implemented in PSQL, the Trace API is not of that much >>>> use today, >>>> because: >>>> >>>> * No I/O statistics for procedure/trigger trace events. According to >>>> Vlad, it should be available, he has a look on that >>> >>> I agree that this is a problem. >> > ... >> So, a mix of DML inside the SP and a call of another SP. >> >> >> * SP gets executed via: >> >> execute procedure P_ENDLESSLOOP(2); >> >> >> In this example, we get the following trace output: > > >> Hmm, see above. :-) IMHO, it would add value already at Trace output >> level to focus/pin-point a problem for forthcoming debugging tasks more >> easily. > > IMHO, the fact that the master SP runs slowly/poorly is what a Trace API/tool > should highlight. Getting into the performance within an SP is the role of a > debugger/profiler. > The Trace API is about getting high level details, finding the "worse > offenders" so that developers can spend their limited time to the maximum > benefit. It is not about measuring each single operation. > > The answer to whether something should be done is not only about whether > there is "added value" to the feature, it is also about "the cost". Adding > more details to the trace output has a cost -- in the overhead in collecting > the stats/information, in the increased disk IO that more stats would > generate and in the time required to parse/analyze the increased data.
What we currently already have are I/O statistics at PSQL level in the Trace API, including I/O statistics for embedded procedure calls, so that can be considered done. This was just a fault in my test run where these statistics didn't show up. I'm gonna run a few additional tests to check, if all I/O measures are covered. I and quite a few other Trace API users believe, that a traced PSQL call is too much of a black-box currently. We already have a trace output for embedded procedure calls anyway, so why not offer the expected EXECUTE_STATEMENT_* trace events for embedded DML calls as well? If this has too much cost, then make it configurable via an additional trace configuration option. The problem with in-depth debugging/profiling usually is, that this needs to happen in a test environment, cause you simply can't execute a critical SP in a production database that easy. And you usually don't have the same conditions in respect to data volume and especially the real production load, thus IMHO it would be useful to have at least the various EXECUTE_STATEMENT_* calls resulting from PSQL tracing with real-life statistics, execution plan etc. If technical possible. This all doesn't replace deeper analysis / debugging anyway. Regards, Thomas > Sean > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
