Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID
On 10/11/2021 11:41, Dmitry Yemanov wrote: > 09.11.2021 03:21, Adriano dos Santos Fernandes wrote: >> >> I think all statements should be split in MON$STATEMENTS and >> MON$COMPILED_STATEMENTS. > > What about execute_immediate? Should they be reported only inside > MON$STATEMENTS or also have a record in MON$COMPILED_STATEMENTS but as > not shareable (bound to the current attachment) or be globally shared > but "shortlived" (destroyed when the request is finished)? > Considering that in the future even these statements may be stored in the compiled cache for reuse, I'd say they have no special thing. Also I think the mirroring in MON$STATEMENTS would be only due to compatibility. If new columns should be used and are related to Statement classes, they should only be in MON$COMPILED_STATEMENTS, so all statements (requests) must be in both (or only in MON$COMPILED_STATEMENTS when only prepared but not executing). 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
09.11.2021 03:21, Adriano dos Santos Fernandes wrote: I think all statements should be split in MON$STATEMENTS and MON$COMPILED_STATEMENTS. What about execute_immediate? Should they be reported only inside MON$STATEMENTS or also have a record in MON$COMPILED_STATEMENTS but as not shareable (bound to the current attachment) or be globally shared but "shortlived" (destroyed when the request is finished)? 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 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
