Automatic Oracle performance monitoring for Pro*C programs

Have you ever whished you had more trace data in your Pro*C based project?
Probably yes, but you where scared by the large effort you would have to do to get useful data.
That is the time for post-processing or the introduction of some smart macros.

This is when sqlTrace comes to the rescue :-)
There is no need to change your *.pc files.
You just need to embed the postprocessor into your makefile and link to sqlTrace.o

Example run
[oracle@vbgeneric sqlTrace]$ ./sample scott/tiger

Connected to ORACLE
Enter enter number (0 to quit): 1
res=1

Enter enter number (0 to quit): 2
SQLCODE=1403

Enter enter number (0 to quit): 3
SQLCODE=1403

Enter enter number (0 to quit): 1x
ORA-01722: invalid number  => oops, program terminates
=> First it will show the list of SQL-Statements taking more than 1% of the time
0.058487130 1 sample.pc:32
0.002066788 4 sample.pc:47 select 1 into :b0  from dual where 1=:b1
0.000885475 1 sample.pc:17
=> Than it will show the last 20 SQL-Statements and the SQL-Code
SQLCODE:00000 sample.pc:32
SQLCODE:00000 sample.pc:47 select 1 into :b0  from dual where 1=:b1
SQLCODE:01403 sample.pc:47 select 1 into :b0  from dual where 1=:b1
SQLCODE:01403 sample.pc:47 select 1 into :b0  from dual where 1=:b1
SQLCODE:-1722 sample.pc:47 select 1 into :b0  from dual where 1=:b1
SQLCODE:00000 sample.pc:17
 

Download the code: https://github.com/frankth/sqlTrace

Fun with Pro*Cob

When we moved a very large Cobol application from MVS and DB2 to Solaris and Oracle, the question was what to do with all Cobol-code that checked the SQL-Codes. In our case the programmers didn't use a copybook with all the constants they neeeded. Most of the time the codes where there numerically. Sometimes SQLCODE got stored in other variables, and checked later. As you might already know, I don't fear patching things. So I decided to look at the code generated by procob. That looked very good. All I had to do was adding a call to a C function after every call to SQLBEX. In that function I saved SQLCODE to ORA-SQLCODE and put the mapped DB2-Code into SQLCODE. As I was on it, I enhanced it a bit more. I printed a stack-trace if there was an "unusual" SQLCODE. In case of a rollback I printed the last 20 statements. I also added a call to a C function before SQLBEX to save a hires timestamp. That way I could count the calls and time used of every server call (sql open, fetch, ...). Using an atexit handler I could walk through a list of all used SQL statements and log every statement (source file name and line number in the file before procob ran) that took more than 1% of the runtime of the program ordered by used time. So if someone comes up with "this and that job went very slow last night" I could easily see which statement produced the problem by looking at the last view lines of the log. That was a real killer feature.

I love Oracle

I have to admit it. But it was not a love on first sight when I met version 7. But I think that it was more due to fact that it was partly misused in that project for things that should have been done with standard Unix features.
Things I like about Oracle:

• + Multi-version read consistency
• + No limit to the number of locks
• + Always use row locks if useful
• + No lock escalation (row->page->table)
• + Fast startup, even if you have a large uncommitted transaction. I could tell you a funny story about a DB2 doing recovery at start-up for more than 24h.
• + Nice web interface to pinpoint most problems easily. ( Although the old non web Enterprise-Manager was much faster)
• + Very nice tools for developers to find performance problems (statspack, automatic monitoring with awh*, trace-events!)
• + Very good support. Although the first answer is normally kind of a standard question, I always got to the point where they acknowledged the bug and fixed it.
• + They work hard to make it a better product even at the core level.

Some of the things I don't like:

• - AQ is slow as hell. We used AQ heavily in one part of our application with  small messages. When I looked at a statspack report I couldn't believe it. 700 buffer_gets_per_exec on average to fetch a single message!
• - Sometimes it's hard to find out what you have to licence. Although I like the fact that I could try out every feature, I would like to be able to limit it's functionality to just the things that I licensed or plan to license.