Incorrect filling of MON$COMPILED_STATEMENTS

[AlexBekhtin]

Firebird 5

SQL> SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS;

     MON$SQL_TEXT
=================
              0:1
==============================================================================
MON$SQL_TEXT:
SELECT RDB$MAP_USING, RDB$MAP_PLUGIN, RDB$MAP_DB, RDB$MAP_FROM_TYPE,    RDB$MAP_FROM, RDB$MAP_TO_TYPE, RDB$MAP_TO FROM RDB$AUTH_MAPPING
==============================================================================
              0:3
==============================================================================
MON$SQL_TEXT:
SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS
==============================================================================

SQL> SELECT COUNT(*) FROM RDB$RELATIONS;

                COUNT
=====================
                   67

SQL> COMMIT;
SQL> SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS;

     MON$SQL_TEXT
=================
              0:1
==============================================================================
MON$SQL_TEXT:
SELECT RDB$MAP_USING, RDB$MAP_PLUGIN, RDB$MAP_DB, RDB$MAP_FROM_TYPE,    RDB$MAP_FROM, RDB$MAP_TO_TYPE, RDB$MAP_TO FROM RDB$AUTH_MAPPING
==============================================================================
              0:3
==============================================================================
MON$SQL_TEXT:
SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS
==============================================================================
              0:5
==============================================================================
MON$SQL_TEXT:
SELECT COUNT(*) FROM RDB$RELATIONS
==============================================================================

Firebird-6.0.0.2190-0-1d6051b-windows-x64

SQL> SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS;

     MON$SQL_TEXT
=================
              0:1
==============================================================================
MON$SQL_TEXT:
SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS
==============================================================================

SQL> SELECT COUNT(*) FROM RDB$RELATIONS;

                COUNT
=====================
                   71

SQL> COMMIT;
SQL> SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS;

     MON$SQL_TEXT
=================
              0:1
==============================================================================
MON$SQL_TEXT:
SELECT MON$SQL_TEXT FROM MON$COMPILED_STATEMENTS
==============================================================================

[aafemt]

For me result from Firebird 6 seems correct. What's wrong?

[AlexBekhtin]

I don’t see a query to RDB$RELATIONS.
This is a short example. If you call procedures, they also do not get cached in the compiled query cache.

[aafemt]

The query is already freed and not prepared anymore, thus the result is correct. I'm not sure if cached statements should be displayed in monitoring tables because it surely will cause questions like "I freed statement, why it is still there".

[AlexBekhtin]

If I understand correctly, that’s exactly the point of a query cache - to avoid doing the same work again for a query with the same text on the next call. And they should survive the release of the query.

[sim1984]

The query is already freed and not prepared anymore, thus the result is correct. I'm not sure if cached statements should be displayed in monitoring tables because it surely will cause questions like "I freed statement, why it is still there".

If the compiled query cache works the way you describe, then there is no point to it. It was introduced precisely so that the same query—using a prepare–free cycle—would run faster, even if only at the connection level.

[aafemt]

Query in the cache don't need to be seen in the monitoring table. Still they are in the cache. These are just different things.

I would even say that the cached queries must not be seen for a consistency reason: the monitoring table to show only really prepared queries no matter if the cache is active or not.

[sim1984]

Obviously, someone is confusing the mon$statements and mon$compiled_statements tables.