FBTrace: exclude_filter and stored procedures

[Daniel Achermann]

Hi everyone,

We would like to set up fbtrace for all events which are writing to a database. So all select kind of statements should be excluded.

We have in our database also stored procedures. Some are only selecting data and others are  changing datas. We thought we could trace the execution of stored procedures but add all stored procedures which are only selecting data to the exclude_filter. However this seems not to work. 

Is exclude_filter connected with stored procedures or only with ‘normal’ sql statements? And if it’s not connected any ideas how we could achieve our goal. 

We are using firebird 3.0.10. 

Thank you very much for any help!

Daniel

[Vlad Khorsun]

Hi everyone,

We would like to set up fbtrace for all events which are writing to a database. So all select kind of statements should be excluded.

  Be careful, it could generate a lot of trace logs and make some impact on performance.

 

We have in our database also stored procedures. Some are only selecting data and others are  changing datas. We thought we could trace the execution of stored procedures but add all stored procedures which are only selecting data to the exclude_filter. However this seems not to work. 

Is exclude_filter connected with stored procedures or only with ‘normal’ sql statements?

   SQL query filters (include_ filter and exclude_filter) affects only what was send by application, i.e. 'normal' sql statements.

It not 'see' body of PSQL objects such as stored procedures, triggers and so on.

 

And if it’s not connected any ideas how we could achieve our goal. 

  Of course there is no universal solution. Especially for unknown database ;)

But couple of ideas of how it could be done - below.

  If you don't need to trace calls of stored procedures by another SP's - i.e. you need only "normal' statements,

send by application and you have ability to change app - you might mark any statement to be traced or not

by adding special comment at the start of the query text, for example:

-- TRACE, or

-- NO_TRACE, or even

-- TRACE_LEVEL_NNN

where NNN could be used to group some statements to trace or not. Then, setting include\exclude filter

to corresponding comment you will trace what you need.

  Another approach could be used if your DB already used some kind of naming conventions for SP's and

it could be used to distinguish SP's that get data from SP's that change data. In this case you could create

regular expression to filter only that kind of SP's that you need to trace.

Hope it helps,

Vlad

[Daniel Achermann]

Hi Vlad,

thank you very much for your support!

We would like to set up fbtrace for all events which are writing to a database. So all select kind of statements should be excluded.

  Be careful, it could generate a lot of trace logs and make some impact on performance.

 

We have in our database also stored procedures. Some are only selecting data and others are  changing datas. We thought we could trace the execution of stored procedures but add all stored procedures which are only selecting data to the exclude_filter. However this seems not to work. 

Is exclude_filter connected with stored procedures or only with ‘normal’ sql statements?

   SQL query filters (include_ filter and exclude_filter) affects only what was send by application, i.e. 'normal' sql statements.

It not 'see' body of PSQL objects such as stored procedures, triggers and so on.

I didn't expect that the body of a stored procedure is analysed but I expected that exclude filter will consider the name of a stored procedure.

Let's say I have the following trace configuration:

    exclude_filter = %(MyTable|MyStoredProcedure)%
    log_statement_start = true
    log_procedure_start = true

If I execute a statement

SELECT * FROM MyTable

I don't get any entry into the tracelog.

However if I execute a statement

SELECT * FROM MyStoredProcedure(1)

then I get the following tracelog:

2022-07-18T09:55:11.9800 (XXXX:XXXXX) EXECUTE_PROCEDURE_START
    C:\MyDatabase.FDB (ATT_279, MyUser:NONE, ISO88591, TCPv6:::X/XXXX5)
    C:\MyTool:19460
        (TRA_3855, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Procedure MYSTOREDPROCEDURE:
param0 = bigint, "1"

So to me it seems there is no way to filter Stored Procedure calls by the name of the stored procedure. Can you confirm that this is really the actual behaviour and not any misconfiguration on my side?

Thanks a lot for the support!

Daniel

 

[Pavel Cisar]

Hi,

The problem is that exclude_filter is applied ONLY to DSQL events, not
any others. With your filter you'll not get PREPARE_STATEMENT,
EXECUTE_STATEMENT_START, EXECUTE_STATEMENT_FINISH or FREE_STATEMENT
events for "SELECT * FROM MyStoredProcedure(1)" - the statement, but
you'll get EXECUTE_PROCEDURE_* events. BTW, if MyStoredProcedure will
call other procedures, they will be logged too.

Without "MyStoredProcedure" in exclude_filter, you'll get also
statement-related events.

regards
Pavel

Dne 20. 07. 22 v 9:38 Daniel Achermann napsal(a):