ORA-00942

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.

If you enable sql trace, you can see Oracle runs queries against obj$ and objauth$.

select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 
from obj$ 
where owner#=:1 and name=:2 and namespace=:3 
and remoteowner is null 
and linkname is null and subname is null

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from objauth$ 
where obj#=:1 
group by grantee#,privilege#,nvl(col#,0) 
order by grantee#

The first checks for the object's existence, the second looks for privileges granted on the object. By the way, the results from these queries may be cached so you won't always see these in the trace file. Exactly what is going on behind the scene here is unknown to me, and I also think that the implementation of this has changed during the years with different versions of the database. But if you look at the second query, that fact that no row is returned may come from either the fact that no privileges have been given, or that the table does not exist. So, instead of going an extra round in the dictionary to see if the table exists since no privileges exist on it, Oracle simply returns the same error message "table or view does not exist".

It is probably a good security practise to not inform unprivileged users about the existence of objects they have no access to, but I thought it was a smart optimisation by Oracle back then, because I was used to see error messages like "access denied". The way they have done this saves extra work. By know I think I have lost the attention of everyone except my best oracle nerd friends, so I thought I could sneak in a confession here at the end. Some years ago I started to practise ORA-942 in my own life. You know in those situations where people suspect you know some secret and start asking away about stuff you are not allowed to pass on? Instead of me having the burden of creating excuses or explaining why I can't tell, I simply answer "I don't know". That saves me a lot of extra work and I don't consider it a worse practice than mining other people's brain.

Have a nice weekend!

Extract SQL from trace file with Perl

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:

perl xtract.pl your_trace_file.trc | more

There is an option (record=filename.sql) in tkprof to extract the SQL, but it does not include recursive statements. I guess the difference is that this is something to build on when you have a more complex task.

Two free profilers for 10046 trace files

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.

When I have a tasks that runs for a few hours I enable extended Oracle trace from start to end, avoiding as best as possible anything outside the time interval for the task itself, meaning I coordinate the tracing with the end user. When I have the trace file ready I check it briefly to make sure I don't have some rubbish before or after the time window. Especially on Windows it is important to check that the tracefile has not already been written to by a previous session, may be it is the same on Unix, I haven't checked since I'm in Windows land for the moment. I use two free tools that create a profile in html-format:

Oracle Session Resource Profiler (OraSRP) by Egor Starostin and TVD$XTAT by Christian Antognini, the author of the book Troubleshooting Oracle Performance. In that book he describes his profiler that you can download from his site. Excellent book by the way.

I often use both profilers because I like to compare the results. They usually agree surprisingly well, the only difference is what is considered unaccounted for time. Whenever unaccounted for time is large I check the tracefile again to see if there is a long period of time spent waiting for the client at the end. Sometimes the missing time is located inside the session, and at one occasion when I was stuck I bought a license of mrls that parse the tracefile and makes it easy to track down where this happens.

In addition to create an overall profile for your session they have profiles for individual events, OraSRP is especially good at this. This is interesting because it adds another level. One of the tricks you encounter on the web is to increase the parameter DB_FILE_MULTIBLOCK_READ_COUNT. This parameter decides how much Oracle can try to fetch in each call when reading many blocks at a time, typically db file scattered read or direct path read usually when doing table scans as opposed to single key search in an index. Reading 128 at once must be better than 16 that is often the default, but this profile will in some cases show you that Oracle cannot read a maximum number of blocks for each read call. In one query I was working on the event db file scattered read contributed to 51% of the response time, but a profile on this event showed that the number of blocks pr read call was almost flat with a variation from 1 block pr call (7.1%), 128 blocks pr call (1.0%) to 73 blocks pr call (0.1%). The benefit of increasing the parameter was not as much as I hoped for, and the solution was found elsewhere.

One of the important points with profiling is to discover where not to waste time. You might suspect a full table scan to be the problem (because some people believe FTS is always bad), but large tables can be scanned really fast these days. With tracing and profiling you don't have to guess, you measure and draw a conclusion. There are so many things that can go wrong in a complex system, if you can measure and do simple math you are much more likely to reach your goal early and know that you actually have reached it than when you apply all those tricks and guesswork.

"Filter early" is important when optimizing, with profiling you filter out early where you don't want to apply the focus of your brain.