Oracle SQL Trace

I came across a good deep dive white paper from an Oracle Development Expert, Cary Millsap. It was about SQL Trace. Well, as yet as a DBA, I have been avoiding SQL Trace without a second thought ever, but it indeed is one of most insightful utilities – not just for Developers but for DBAs (and more so because there are many Dev-DBAs that I know personally). There has to be some reasons why SQLTrace persisted to be a constant offering from Oracle since Oracle 6. From performance standpoint, enabling SQL Trace never hits >5%, which is quite acceptable for amount of time it can save.

Before speaking of anything else, always use following when doing a trace:
- binds => true : so it can tell what values the system is running with
- waits => true : so it can capture extended waits as (i.e. waits caused by OS operations)

Here’s how you can be using Traces:

1. Session Tracing
- dbms_monitor.session_trace_enable (or session_trace_disable). You can mention session_id, serial_num alongside which doest the magic.
- Inside a Trigger, specifically for the fast moving sessions which lasts for a couple of seconds. You can code them with a small trigger definition and with (session_id=>NULL, serial_num=>NULL)
- With, sys_context( ‘<USERENV>’,’<IP>’). It tells the environment details and the IP details
- Or, you can capture the suspicious SQL and can run them inside your own current session.

2. End to End Tracing
- dbms_monitor.client_id_trace_enable (client_id= ‘<IP>’, waits=> true, binds=> true, plan_stat=> ‘ALL_EXECUTIONS’);
- dbms_monitor.serv_mod_act_trace_enable (service_name=> ‘<SERVICE>’, module_name=> ‘<MODULE’, action_name=> ‘<ACTION>’, waits=> true, binds=> true, plan_stat=> ‘ALL_EXECUTIONS’);

3. Database or Instance Level Tracing: For end to end tracing at instance or database level, its important that your code passes back the client, service, module and action details. You can take this as a Development Best Practice, which by the way, is seldom mentioned in any Best Practices document. It can be done by making use of:
1. PL/SQL packages, i.e. 
    – dbms_session,
    – dbms_application_info
- ILO or Instrumentation Library for Oracle, i.e. 
    – OCI_ATTR_CLIENT_IDENTIFIER,
    – OCI_ATTR_SERVICE,
    – OCI_ATTR_MODULE,
    – OCI_ATTR_ACTION

What SQL Server Data Access Driver you using?

There are times when team runs into issues and we try to find what version of SQL Server data access driver is application using. This maps back to something called SNAC (SQL Server Native Client). The reason for this unawareness can also be because of the reason that every SNAC version supports current SQL Server version, two previous SQL Server versions, and two new SQL Server versions. For example, SQL Server 2005 had SNAC9, which supports SQL Server 2005, SQL Server 2000, SQL Server 7.0, SQL Server 2008 and would support SQL Server 2011 (if thats the to be version for same). Current SQL Server 2008 has SNAC10.More details on SNAC, WDAC, MDAC can be referenced here.

So, to determine what SNAC version your application is using (which can be different from whats on your database server – leading to issues), following script can be used:

SELECT session_id, protocol_type, driver_version = CASE CONVERT(CHAR(4), CAST(protocol_version AS BINARY(4)), 1)
WHEN ’0×70′ THEN ‘SQL Server 7.0′
WHEN ’0×71′ THEN ‘SQLServer2000′
WHEN ’0×72′ THEN ‘SQLServer2005′
WHEN ’0×73′ THEN ‘SQLServer2008′
ELSE  ‘Unknown Version’
END
FROM sys.dm_exec_connections

The binary conversion (to hex) is for the reason that sys.dm_exec_connections store the protocol_version info in integer form for TDS version. The hex value is mapped already to respective SQL Server versions in query above.

ACID Characteristic of Databases

So often we forget the most underlying reason for Database’s existence. Atleast I do at times. So here’s the ACID part of Databases:

- Atomic: either all of its data modifications are performed, or none of them is performed
- Consistent: once completed, a transaction must leave all data in a consistent state
- Isolation: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions
- Durability: After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.