Transaction_id column

[alexeionin]

Please, add a new column for store values from dbms_transaction.local_transaction_id. This is useful for debugging (search) excess commits, which terminates long transactions. It may also help to group the records related to the analysis of some issues. Also, we can use this column as a foreign key for linking to other tables (audit, staging areas, batch process logs, etc) if we also keep the transaction IDs in the tables.

[martindsouza]

@alexeionin I'm just catching up on outstanding tickets. I've never used dbms_transaction.local_transaction_id but can see value for grouping logical transactions together. Thoughts on the following:

-- internal logger function
log_internal ...
....
l_transaction_id := dbms_transaction.local_transaction_id(create_transaction => true);

insert into logger_logs(..., transaction_id)
values(..., l_transaction_id);

From my understanding (could be wrong on this) but creating the transaction by default (if not already created) then everything up to the point of a commit or rollback would be "grouped" together with the same transaction_id. Is this correct?

Do you see any negative things to this approach (primarily the creation of the transaction by default)? I'm hesitant that logger would affect anything in the calling application. The only reason I'd want to set it is so that all calls are grouped (even if a dml hasn't been triggered yet).

[alexeionin]

The logger should not interfere with the application's transaction management logic under any circumstances. We must not create a new transaction! If the application hasn't opened a new transaction yet, we should save an empty value in the transaction_id column to make it clear that there was no transaction at the time of logging. Thus the value of create_transaction => false must be passed.

Also note that dbms_transaction.local_transaction_id must be called before calling the function with an autonomus transaction. The main transaction in the application and the standalone transaction where the insertion of logger_logs into the table will have different identifiers and we should not confuse them. It is important for us to save the identifier from the main transaction.

[martindsouza]

@alexeionin good points. Agreed on the autonomous transaction part. When I was looking at your ticket I checked that the public calls to Logger (i.e. logger.log etc) are not autonomous transactions. They all call an internal procedure that is autonomous so we can easily manage the "2" different transactions i.e. the transaction_id logged will be the one that is from the main program.

A few other ideas/thoughts/questions.

• If we do this it needs to be clear that this field will only be populated when the transaction starts. I'm concerned this may be confusing for people who want to "trace/filter" through an entire process. In this case they need to "start a transaction" ex:

logger.log('start'); -- transaction_id = null
...
update some_table ... 
logger.log('message');  -- transaction_id = <some value>
...
commit;
logger.log('another message');  -- transaction_id = null

• On this note what do you think about creating a wrapper function called logger.start_transaction (or similar) that will create the transaction (true) by default. This way in the example above everything will have a value (except for the commit).
• In the package there's a step_id which refers to the DML step. Thoughts on. logging this as well? Not sure of the value / usage.
• I'm getting a bit concerned about the potential of the log table "getting too long". What do you think about storing this info as a JSON object. It will allow us to store more meta data and be more flexible as time goes on. I still need to look at the performance impact / gains we'd make. I realize this would also put a hard cut off at 12.1 for a minimum requirement to support JSON.

[martindsouza]

@alexeionin do you absolutely need the value from dbms_transaction.local_transaction_id or is the main purpose to group together all the calls to Logger for a given transaction?

[alexeionin]

I would like to see in the new column simply the current value dbms_transaction.local_transaction_id, so that I can track the moment when transaction management actions have taken place inside the investigated complex procedure. You gave an absolutely exact example of use in code - exactly as I wanted it to be when I created this ticket. Thank you!

I like having null in the column as it clearly shows that there was no transaction at the time logger.log was called and this information can be trusted. People who want to use the column values for tracing and grouping should understand how transactions work and this behavior will help them learn how Oracle Database behaves in respect to transactions. If I need to use the values in this column to group, I can always explicitly start a transaction in my procedure in a hundred different ways using SQL, PL/SQL constructs or Oracle libraries. I don't see why I should put this task on Logger. I believe that logger.start_transaction is obviously outside the scope of your library's purpose. This is not to add new meaning or functions into my procedure. In my opinion, Logger is an objective tool for collecting telemetry. Its non-interference in the operation of the procedure is an important element of its credibility. As for step_id, it will not add anything new to the data in the logger_logs table . We will always be able to calculate the DML step, for example using the analytical function row_number() over (partition by sid, transaction_id order by id) as step_id.

[alexeionin]

If we store values inside a JSON object instead of columns, which will be stored in a separate VARCHAR2 column in logger_logs table, we will lose by the amount of space occupied. Additional markup symbols and value names will take up additional space compared to how Oracle stores column values in data blocks. Also we will lose in performance - when reading data from the table we will need to parse JSON-objects. When writing to JSON we can use simple string concatenation - there will be no performance loss. Also people will lose the ability to index the values of columns they need at their own discretion.

Oracle 12c is not a prerequisite for working with JSON. I now quite successfully use pljson library in Oracle 11g2 for serialization and deserialization of objects.