Python
Debugging DCOracle2 applications
DCOracle2 is the Oracle interface module for Python I use most often. It is advertised as "beta", but quite suitable for production use, aside from a few minor rough edges. There are a few others, most notably cx_oracle, but I can't vouch for them.
Debugging applications that make use of DCOracle2 can be challenging, as with any database environment, specially in a multi-threaded server context. I have developed a small utility module to aid in development. When it is imported, it will automatically trace all database calls made through DCOracle2, including arguments such as bind variables. More interestingly, it will also automatically run EXPLAIN PLAN on queries taking longer than 2 seconds (by default), to aid in tuning SQL statements. As a side bonus, if run by itself, it provides a (very basic) SQL shell that does offer command-line history and editing, something Oracle hasn't managed to provide in SQL*Plus in almost 30 years :-)
This code works with Python 2.2 and DCOracle2 1.1 and 1.3 beta. It will not work with 2.1 and earlier.
The latest version of the module file can be downloaded here: debug_ora.py, as well as the RCS repository debug_ora.py,vfor those who care about this kind of stuff.
An example run of the module:
% python debug_ora.py scott/tiger@repos SQL> select ename, job, dname from emp, dept where emp.deptno=dept.deptno; SQL: Oct-03-2003 17:32:39:897 select ename, job, dname from emp, dept where emp.deptno=dept.deptno ARG: () {} SQL: !!!!!!!!!!!!!!!! slow query, time = 0.0 sec SQL: !!!!!!!!!!!!!!!! execution plan follows 000 SELECT STATEMENT Optimizer=CHOOSE 001 NESTED LOOPS 002 001 TABLE ACCESS (FULL) ON EMP 003 001 TABLE ACCESS (BY INDEX ROWID) ON DEPT 004 003 INDEX (UNIQUE SCAN) ON PK_DEPT ENAME JOB DNAME ------ --------- ---------- SMITH CLERK RESEARCH ALLEN SALESMAN SALES WARD SALESMAN SALES JONES MANAGER RESEARCH MARTIN SALESMAN SALES BLAKE MANAGER SALES CLARK MANAGER ACCOUNTING SCOTT ANALYST RESEARCH KING PRESIDENT ACCOUNTING TURNER SALESMAN SALES ADAMS CLERK RESEARCH JAMES CLERK SALES FORD ANALYST RESEARCH MILLER CLERK ACCOUNTING SQL>