Trace

When I had my first Oracle DBA course some years before the start of this millennium I made a special mental note about a very common error message, ORA-00942, “table or view does not exist”. In most programming languages there are a similar message for a common situation; when you refer to an object that does not exist, quite often because you wrote the name wrong. What I found a bit remarkable when I heard about it first time was that ORA-942 may mean two things, either that the table or view does indeed not exist, or you do not have access to it. An example of the latter is when some user has created a table in her schema, and you try to query it, but you have not been granted a privilege to do so. Instead of you receiving some error message like “access denied”, Oracle responds with the same error message; “table or view does not exist”, even if it does exist. It may have added “for you” to make it more correct. I think there is a good reason why this is so, and I think it has to do with optimisation. ...

Perl is included with the Oracle database software (SE and EE), even on Windows. I wrote this to extract the SQL statements from a trace file (generated with sql_trace=true or setting the 10046 event). A simple indentation - one tab for each level - is used to show recursive statements. while (<>) { if(/^PARSING IN CURSOR/) { ($level)=$_=~/\\s+dep=(\\d+)\\s+/ ; $line=<>; while ($line!~/END OF STMT/){ for($i=0;$i<$level;$i++) { print "\\t" ; } print $line; $line=<>; } print "\\n" ; } } You usually have to expand your PATH on Windows to find Perl; you’ll find Perl.exe somewhere below %ORACLE_HOME%. The code will of course work on Linux and other OS where you have Perl. Store the code above in a file called xtract.pl and call it with: ...

I’m a big fan of Method R, both the company and the way to optimize SQL (or any other process that can possibly be measured). The method is explained in details in their classic book Optimizing Oracle Performance . But it is hard to practice method R without a good profiler. In every installation of Oracle database the profiler tkprof is included and it does serve for some problems, but hides a lot of information from you. ...