Tracing for fun and (tk)profit

If you ever wanted proof that time is relative, just consider The Good Old Days.
Depending on your age, nationality, personal preferences etc, that time could be when rationing finally ended; or when Trevor Brooking won the Cup for West Ham with a “bullet” header; or possibly when Joe Carter hit a three-run homer to seal back-to-back World Series for the Blue Jays.
Alternatively, it could be when you were able to get on to the database server and use tkprof to analyse those tricky database performance issues.

In these days of siloed IT Departments, Oracle trace files, nevermind the tkprof utility are out of the reach of many developers.
The database server itself is the preserve of Unix Admins and DBAs, groups which, with good reason, are a bit reluctant to allow anyone else access to the Server at the OS level.

Which is a pity. Sometimes there is just no substitute for getting into the nitty gritty of exactly what is happening inside a given session.

For those of you who miss The Good Old Days of tkprof, what follows is an exploration of how to access both trace files and even the tkprof utility itself without leaving the comfort of your database.
I’ll go through a quick recap of :

  • how to generate a trace file for a session
  • using tkprof to make sense of it all

Then, coming bang up to date :

  • viewing a trace file using an external table – and why you might want to
  • Using a preprocessor to generate tkprof output
  • implementing a multi-user solution for tkprof

Continue reading

Oracle Batch Job Logging – a framework for domestic harmony

Like most men, I have a standard of tidiness and cleanliness that I think of as “Bloke Clean”.
Deb’s standards are rather higher ( she would say normal). The difference can occasionally be a source of tension.
“Maybe I’ll just run off with that Steven Feuerstein bloke !”, she may have said during one of our discussions about the state of the study.
“Oh really ?”, I might retort, “and what’s he got that I haven’t ?”,
“Money, fame, talent and his own framework…not to mention a cleaner, I’ll bet”.
“Well…at least I have more hair”, I might say, disconcerted by her surprisingly comprehensive knowledge of someone who, it must be said, is not exactly famous outside of the wonderful world of Oracle.
“Not by much.” would probably have been the devastating reply.
Predictably, a compromise has now been reached…the upshot of which is that I’ve just spent the afternoon becoming intoxicated by the fumes from various cleaning products…and the study is now gleaming and all the papers filed away…and we’re getting a cleaner.
As for the money, fame and talent…well, I’ll just have to make do with the framework for now.
Truth be told, calling it a framework is overstating things a bit. But hey, it does give me an excuse to come up with a (possibly) amusing name. Continue reading