Re: seemingly simple questions

Hi Paul,

I guess you could regard them as seemingly simple questions but gee there's a lot of them ;) Don't have the time to answer everything comprehensively but find some comments embedded.

"Paul Murphy" <pmurphy_at_scsinet.com> wrote in message news:sJqt9.3502$TX.1257298_at_twister.nyroc.rr.com...
> I've been studying for the 9i OCP DBA tests. I passed the SQL test no
> problem and now I'm on to the DBA Fundamentals I test. I really want to
> understand what is physically going on during a transaction, but all the
> books I have either contradict each other or do not fully explain what's
> happening. If anyone wants to take a bit of their time to get a rookie off
> to a good start, I'd greatly appreciate it. Here are a few questions:
>
> Let's say we're doing an update statement. Please note where I'm
> wrong/confused/omit something important
>
> 1. Oracle looks for a duplicate parsed statement already in the library
> cache. (including the values in the WHERE clause?)
> 2. If the parsed statement isn't in the library cache, Oracle checks the
> syntax.
> 3. Oracle checks to make sure all the columns exist (exist only or are
> available to that user: existing locks, permissions?)
> 4. A lock is issued before permissions are checked?

No. If you mean locking the actual data (rows/table), this is performed during the execution phase. Checking permissions is performed way back in the semantic soft parse phase.

> 5. Permissions are checked.

Step 2,3, 5 are all performed before Step 1. The 'soft' parse is always performed which basically entails suzzing out the syntax and the semantics of the statement.

> 6. Oracle parses the statement and puts it in the libarary cache.

OK. Parsing is performed first. It basically consists of a number of different steps. These include syntax checking (seletc * from bowie), semantic checking (select * from bowei), permission checking (what the hell are *you* trying to do !!), optimisizing (what options do I have in executing this) and the generation of the execution plan (how I'm actually going to execute this).

Note that a 'soft' parse is always performed, the 'hard' parse (which consists of the optimizer bits) is only performed once while the statement is cached in memory and is only re-hard parsed if the statement is aged and reloaded into memory or if the statement is invalidated.

> 7. A cursor is opened to hold the selected data.
> 8. The existing data is read from the data files on disk into a cursor
> inside the buffer cache.

Be careful of your terminology here. A cursor (or my definition anyway) is kinda like a handle to the cached statement (in the shared pool). The data is loaded into the buffer cache yes (if it's not already there) but it's incorrect to suggest the cursor resides in the buffer cache.

> 9. A copy of the old data is moved into a rollback segment (in RAM, in a
> UNDO tablespace on disk?)

Next the rollback/undo block is loaded into the buffer cache. Then the data is locked (via changes to the data block). Then the redo is written to the redo log buffer. Then the 'old' data is written to the rollback segment block (which was previously created in memory)

> 10. The statement is executed updating the copy of the data in the buffer
> cache.

The previous steps (8, 9,10) are then repeated for all corresponding data after which the user receives the data processed message.

> 11. The user issues a commit.
> 12. The changed data is copied from the buffer cache to the redo log
cache.

Incorrect. The LGWR is woken and the contents of the redo log buffer are written to the redo logs (note the corresponding redo has already been written to the redo log buffer - step 9).

> 13. LGWR writes the updated data from the redo log cache to the online
redo
> log files with an SCN.
> 14. DBWR writes the updated data to the data files at some point after
that.
> 15. Undo data is generated and written somewhere???

See step 9.

>
> I was trying to simplify this by thinking old data, new data, disk data,
RAM
> data and figuring out which is which.
>
> Old Data on Disk (DBF data files)
> Old Data in RAM (first the buffer cache, then to the RollBack Segment)
> New Data RAM (modified in buffer cache, then to Redo Log Cache)
> New Data on Disk (LGWR writes redo log cache data to redo log files, DBWR
> writes buffer cache data to DBF data files)
>
> Do the redo log buffer, the rollback segment (or undo tablespace) all have
a
> copy of the old data?

Principly yes. Note however that the old data in the rollback segment is used for possible rollback, read consistency and potentially during recovery when any uncommited data at the end of the roll forward recovery process is rolled back. Undo is only used for recovery. The old data as such is used to generate the rollback data during recovery in case changes made and recorded in the redo logs were never actually commited.

> Do the buffer cache and redo log cache both have copies of the changed
data?

Principly yes.

> What the heck is undo? Is it the old data in RAM in the rollback segment?

Undo is the data in the rollback or undo segments. It's basically the *previous* values of any changes made to a data block during the transaction, and used for the above mentioned reasons.

> Is the rollback segment in RAM in the SGA or the PGA?

The SGA or more specifically the Buffer cache. Why ? Because any other database user potentially needs access to the rollback data for read consistency purposes. Also, if the rollback block is "free", it can be reused by any other transaction/user.

> If a rollback segment is in RAM and it's also in an UNDO tablespace on
disk,
> is it buffering to disk only if RAM runs out?

A rollback block behaves exactly the same as any other block. It get loaded into the buffer cache if required, it can age out, it can be used by all manner of users. The key difference perhaps is if a rollback block is required by a transaction to be written to and if the rollback block is not in memory, it is not re-read from disk. Instead, Oracle will 'generate' the block in memory (there's no need to read the block from disk as it's all going to be overwritten anyway).

>
> I've read 5 books on 9i and I'm getting close to the point of utter
disgust
> that none of these books tell you what Oracle is doing. None of them. It's
> rediculous. I can't go into a job with a paper DBA certificate and not
know
> what's happening inside the DB. Please someone help me (and I'm sure a lot
> of other confused people) understand a simple update statement. Thanks
> again...Paul

There are good books (usually ones that discuss the career of David Bowie) and there are bad books however most books should enhance your understanding in some way.

As I hope I have, albeit a tad brief and simplisticly.

Good Luck

Richard

>
>
Received on Thu Oct 24 2002 - 08:09:01 CDT