ODP.NET custom trace listener possible?

user5543674

Hi,

I wonder, if its possible in ODP.NET to do a custom trace, e.g. writing to custom logger or e.g. Cloud Watch, etc. I can see Trace.cs resits in OracleInternal and Trace.Write is internal static. It would be nice, if there e.g. would be a custom listener possible to register. E.g. delegate OnCustomTraceWrite....

-Michael

Alex Keh-Oracle

What is the business requirement for this feature request? It would help the Oracle .NET team understand the feature's importance.

In general, the consumer of the trace is Oracle Dev and Support to be able to diagnose problems.

user5543674

Hi Alex,

the business requirement would be: As an ODP.NET client user I want to be able to add custom trace loggers, so that I can log and analyze Oracle client library on a daily basis, e.g. by logging to rolling file appenders, 3rd party logging frameworks or cloud logging providers such as AWS cloudwatch or Application Insights.

Alex Keh-Oracle

Almost all users turn off tracing in production because it slows down performance, which is one reason not to have it running all the time.

My question was more along the lines of what do you plan to do with the ODP.NET tracing info? What actionable decisions will you make based on what it outputs? The real useful data from the trace are ODP.NET's internal calls and APIs, which only Oracle Development can modify.

user5543674

Hi Alex,

I can see your point. Not all oracle traces are internal only, see OracleTraceLevel.Public vs OracleTraceLevel.Private. I am very interested in OracleTraceTag.SQL to be able to track SQL commands with execution time and success/non success. It would be best to have the below specific delegate to optionally use to get callbacks after every OracleCommand (ExecuteNonQuery, ExecuteReader, ...).

delegate void OnCommandExecuted(string commandText, DateTimeOffset startTime, TimeSpan duration, bool success);

AndrewSayer

user5543674 wrote:Hi Alex, I can see your point. Not all oracle traces are internal only, see OracleTraceLevel.Public vs OracleTraceLevel.Private. I am very interested in OracleTraceTag.SQL to be able to track SQL commands with execution time and success/non success. It would be best to have the below specific delegate to optionally use to get callbacks after every OracleCommand (ExecuteNonQuery, ExecuteReader, ...). delegate void OnCommandExecuted(string commandText, DateTimeOffset startTime, TimeSpan duration, bool success);

That sounds more like a job for extended SQL trace on the database. You can enable it for particular sessions or use dbms_monitor to enable it for any client that uses a certain module/action. Have a read of https://docs.oracle.com/database/121/TGSQL/tgsql_trace.htm#TGSQL792 

Not only will you see what SQL you spend your time on, you’ll see why you spend time on that SQL

Alex Keh-Oracle

I agree with Andrew. What you're looking for can be accomplished through Oracle DB's tools. It has the advantage of being able to turn tracing on for individual SQL commands through module or action instead of tracing every single ODP.NET foreground and background operation.