Re: [Firebird-devel] ODP: SQL profiler
Hello! There are 2 threads: PSQL profiler and SQL profiler. I've decided to continue here) It seems to me that profiling only the same attachment is not as useful as it could be. Imagine a working application. It's ready but you want to know where it spends time. You attach to the server by profiler and run profiling session in *your* attachment but specifying the attachment ID of the target. Then you run a command in the application and see collected data in the profiling attachment. Like the trace does where you can trace any activity. It would be nice to have it implemented. -- Best, Roman Simakov Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 08/11/2021 17:10, Mark Rotteveel wrote: >> Also, if DSQL requests have stable ID, it's weird that more than one >> called routines would be linked to it (even if not at the same time) >> through MON$CALL_STACK (where request IDs are more correctly used). > > I'm confused about the case, or maybe I don't understand the problem you > have. As far as I understand, the statement id in MON$STATEMENTS > represents the statement handle. Yes, at least apparently - only for DSQL. As DSQL statements handle always have a single request (it can't be concurrently called, now they can be called by others statements). But it's the request ID. An internal statement may have many requests. And that happens for functions and procedures, also present in MON$STATEMENTS. When a function is recursively called many requests are created (and many different records appear in MON$STATEMENTS. > So, if a statement handle has a > statement prepared, it should be the same for each execution of that > statement handle. If the statement handle is unprepared, it should still > have the same idea, same when it gets a new statement text. > The idea is to have MON$STATEMENTS representing requests and MON$COMPILED_STATEMENT representing internal (and future shareable part of a) statement. This shareable part already exist for PSQL statements. And I think memory usage for this part is not present in MON$ per statements currently. It goes only to the attachment or database stats. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 08/11/2021 14:48, Dmitry Yemanov wrote: > 08.11.2021 19:54, Adriano dos Santos Fernandes wrote: >> >> In this case I think we better sooner introduce MON$COMPILED_STATEMENTS >> with some duplicate information already present in MON$STATEMENTS, > > Obviously, SQL text and plan belong to the statement. The rest looks > request-specific (timestamp will represent request startup time). > Yes. Some columns from MON$CALL_STACK also looks better to be present in MON$COMPILED_STATEMENTS: OBJECT_NAME, OBJECT_TYPE, PACKAGE_NAME. When compiled statement cache is present, more columns (for example, idle time). > However, MON$ATTACHMENT_ID is tricky. It's needed in > MON$COMPILED_STATEMENTS now, but likely to be not needed once the shared > statement cache is implemented. I don't think it's necessary now. MON$STATEMENTS.MON$COMPILED_STATEMENT_ID will point to it (MON$COMPILED_STATEMENTS.MON$COMPILED_STATEMENT_ID). Even if it's not true now, MON$COMPILED_STATEMENTS may appear as not directly linked to an attachment. > Also deprecate it then? Or maybe in the > future some statements could not be globally cacheable and instead > prepared per-attachment (as now)? > I think all statements should be split in MON$STATEMENTS and MON$COMPILED_STATEMENTS. Maybe the name MON$COMPILED_STATEMENTS does not look very good (as in this context MON$STATEMENTS also doesn't). >> declaring some columns of MON$STATEMENTS as deprecated to be removed in >> future versions. > > We never removed columns from system tables and I'm not sure this is a > good practice, as apps / components may have field names hardcoded / > cached. Returning NULL for deprecated columns should be perfectly > enough, IMO. > IMO returning NULL is worse as it hides possible problems. Tools will going to support the different ODS and may inadvertently use the always-NULL columns in new ODS. If remove them will not be an option, I think it's better to mirror they content. >> And make (now) MON$STATEMENTS ids represent request IDs generated on >> request start (for DSQL and routines). > > This may break existing apps (Dimitry Sibiryakov's question). But given > that an alternative (via new MON$ table) will be available, perhaps this > is not so critical. > It's what I also think. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 2021-11-08 17:54, Adriano dos Santos Fernandes wrote: On 08/11/2021 12:11, Dmitry Yemanov wrote: 08.11.2021 17:54, Dimitry Sibiryakov wrote: Nope, I believe the new ID must be generated also by findRequest() if the clone was found in the cache. Will it make impossible to detect repeatable execution of a prepared statement as opposite for execution of a new one every time? findRequest() is not used for user DSQL requests, only for procedures/functions. So the top-level statement ID will be always stable, as before. The solution does not work for the profiler then. Every request start must have its own ID, even DSQL ones. And preferable one ID link-able to MON$. Also, if DSQL requests have stable ID, it's weird that more than one called routines would be linked to it (even if not at the same time) through MON$CALL_STACK (where request IDs are more correctly used). I'm confused about the case, or maybe I don't understand the problem you have. As far as I understand, the statement id in MON$STATEMENTS represents the statement handle. So, if a statement handle has a statement prepared, it should be the same for each execution of that statement handle. If the statement handle is unprepared, it should still have the same idea, same when it gets a new statement text. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
08.11.2021 19:54, Adriano dos Santos Fernandes wrote: In this case I think we better sooner introduce MON$COMPILED_STATEMENTS with some duplicate information already present in MON$STATEMENTS, Obviously, SQL text and plan belong to the statement. The rest looks request-specific (timestamp will represent request startup time). However, MON$ATTACHMENT_ID is tricky. It's needed in MON$COMPILED_STATEMENTS now, but likely to be not needed once the shared statement cache is implemented. Also deprecate it then? Or maybe in the future some statements could not be globally cacheable and instead prepared per-attachment (as now)? declaring some columns of MON$STATEMENTS as deprecated to be removed in future versions. We never removed columns from system tables and I'm not sure this is a good practice, as apps / components may have field names hardcoded / cached. Returning NULL for deprecated columns should be perfectly enough, IMO. And make (now) MON$STATEMENTS ids represent request IDs generated on request start (for DSQL and routines). This may break existing apps (Dimitry Sibiryakov's question). But given that an alternative (via new MON$ table) will be available, perhaps this is not so critical. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 08/11/2021 14:05, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 08.11.2021 17:54: >> The solution does not work for the profiler then. > > Why? Isn't the profiler supposed to help detect cases when fast (by > itself) statement is executed enormous number of times degrading overall > application performance?.. > Yes but it also record things per running request, so you have start/end times of them if you want more detailed information, and aggregate the data to have a general view. In this case DSQL requests is no different than stored routines. They may be called multiple types and with the "compiled statement ID" one is able to aggregate the data. Through the non-stable request ID one is able to see the call path over the time. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
Adriano dos Santos Fernandes wrote 08.11.2021 17:54: The solution does not work for the profiler then. Why? Isn't the profiler supposed to help detect cases when fast (by itself) statement is executed enormous number of times degrading overall application performance?.. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 08/11/2021 12:11, Dmitry Yemanov wrote: > 08.11.2021 17:54, Dimitry Sibiryakov wrote: >> >>> Nope, I believe the new ID must be generated also by findRequest() if >>> the clone was found in the cache. >> >> Will it make impossible to detect repeatable execution of a prepared >> statement as opposite for execution of a new one every time? > > findRequest() is not used for user DSQL requests, only for > procedures/functions. So the top-level statement ID will be always > stable, as before. > The solution does not work for the profiler then. Every request start must have its own ID, even DSQL ones. And preferable one ID link-able to MON$. Also, if DSQL requests have stable ID, it's weird that more than one called routines would be linked to it (even if not at the same time) through MON$CALL_STACK (where request IDs are more correctly used). > But if/when we separate compiled (cached) statements and running > statements, the ID will be reset before every execution and your > question may become actual. We'll either need a different "runtime" ID > or some backpointer (ID) from request to its statement. > In this case I think we better sooner introduce MON$COMPILED_STATEMENTS with some duplicate information already present in MON$STATEMENTS, declaring some columns of MON$STATEMENTS as deprecated to be removed in future versions. And make (now) MON$STATEMENTS ids represent request IDs generated on request start (for DSQL and routines). Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
08.11.2021 17:54, Dimitry Sibiryakov wrote: Nope, I believe the new ID must be generated also by findRequest() if the clone was found in the cache. Will it make impossible to detect repeatable execution of a prepared statement as opposite for execution of a new one every time? findRequest() is not used for user DSQL requests, only for procedures/functions. So the top-level statement ID will be always stable, as before. But if/when we separate compiled (cached) statements and running statements, the ID will be reset before every execution and your question may become actual. We'll either need a different "runtime" ID or some backpointer (ID) from request to its statement. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
Dmitry Yemanov wrote 08.11.2021 15:28: Is it an important design of MON$STATEMENTS that this (subsequent execution of a request maintain its ID) happens? Nope, I believe the new ID must be generated also by findRequest() if the clone was found in the cache. Will it make impossible to detect repeatable execution of a prepared statement as opposite for execution of a new one every time? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
08.11.2021 16:50, Adriano dos Santos Fernandes wrote: Despite its name, what MON$STATEMENTS show are requests (not statements). True, but statement/request separation didn't exist when MON$STATEMENTS was implemented ;-) And end users deal with statements, they have no idea what "request" is. Nevertheless, it would be good to find a way to separate cached statements from its running instances (aka requests), ideally without breaking backward compatibility. It will be important once we start caching user statements. When a stored routine is executed, finished and executed again, its request is re-used. Re-used request maintain its ID and is mapped to MON$STATEMENTS.MON$STATEMENT_ID. Looks like a bug. Is it an important design of MON$STATEMENTS that this (subsequent execution of a request maintain its ID) happens? Nope, I believe the new ID must be generated also by findRequest() if the clone was found in the cache. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
Hi! Despite its name, what MON$STATEMENTS show are requests (not statements). When a stored routine is executed, finished and executed again, its request is re-used. Re-used request maintain its ID and is mapped to MON$STATEMENTS.MON$STATEMENT_ID. Is it an important design of MON$STATEMENTS that this (subsequent execution of a request maintain its ID) happens? I'm having problem with this behavior in the profiler. The profiler main unit of aggregation are requests. But for the profiler, it should not mix (and sometimes not mix - when different request incarnation is get) data. Each request start should be an own thing always. So for this case, request ID would need to be reset when resurrecting a dead request to be executed again. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Plans for 3.0.8
06.11.2021 10:30, Omacht András wrote: it's November now. Any chance of coming out in the near future? 3.0.7 was released October 20, 2020. I'm going to tag the tree tomorrow. The release will come shortly after. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
