Re: Does Oracle "forget" a long running query?

bmlam <310064202272#0001_at_online.de> wrote in message <382612CF.75383642_at_online.de>...

<details snipped>
>Could some please shed some light into this mystery?

No, Oracle can not "forget" about any query. The closest to this I've ever seen happening is when the kernel "forget" about telling Oracle that an async i/o has completed and Oracle was patiently waiting for several hours. A kernel patch fixed that problem . :-)

Oracle keeps two internal system tables that keeps track on processes and sessions, called V$SESSION and V$PROCESS. Not every process is a session, but every session is a process. The easiest way to explain this is with the parallel query (PW) option. When PQ is enabled, Oracle creates a specified number of processes to assist with queries. These processes will be listed in the V$PROCESS table. Only when a PQ processed is actually being used by a query (session), will it have a corresponding row in V$SESSION. The V$SESSION table keeps track of all the sessions in Oracle. Including any user session. A session will -always- have an associated V$PROCESS row.

OK, now how do you find out what's going on inside Oracle? The first thing to do is to look at V$SESSION and find the session that is giving problems. Each session is uniquely identified by a session id or sid (not to be confused with the database System ID or SID). Oracle stores data about sessions and processes in other system tables. Some of these you can access by only using the session id. In the case of the problem looks-like-it's-hanging session, we can query the V$SESSION_WAIT and V$SESSION_EVENT tables. The first one is extremely useful as it tells us exactly what the session is waiting for right at this moment (milisecond) in time. It could be waiting for a data block to be read (in which case we can see the file id and block numbers that are to be read). Or waiting for a lock to be released. Whatever. Query this table using the session id rapidly a few times to get a rough idea of what the session is busy currently doing. Next on the menu is the events table. This tells us what the session has been up to thus far. Usually one or two event type stick out as it has occurred a lot more than the others. This is also indicative of what the session has been up to. A large number of DB Scattered Read events usually indicates that the data is been accessed by an index (which is why it's using scattered reads). A large number of Db Sequential Reads usually indicates the opposite - the data is access sequentially as no indexes are being used.

Via the V$PROCESS table you can access other data about a session - the cursors that are opened. The SQL statement that is currently running. Then there's the session stats table that will give you stats about the session, enabling you to determine cash hit ratios achieved by that session, amount of rows processed and so on. Oracle's system table is a mine of information - a unique strength I have yet to come across in any other database system.

So next time you have a "funny" query that's not responding the way you're expecting, open the hood of Oracle and use the V$ tables to investigate. :-)

regards,
Billy Received on Mon Nov 08 1999 - 02:59:20 CST