Re: seemingly simple questions

Paul Murphy wrote:

> 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?
> 5. Permissions are checked.
> 6. Oracle parses the statement and puts it in the libarary cache.
> 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.
> 9. A copy of the old data is moved into a rollback segment (in RAM, in a
> UNDO tablespace on disk?)
> 10. The statement is executed updating the copy of the data in the buffer
> cache.
> 11. The user issues a commit.
> 12. The changed data is copied from the buffer cache to the redo log cache.
> 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???
>
> 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?
> Do the buffer cache and redo log cache both have copies of the changed data?
> What the heck is undo? Is it the old data in RAM in the rollback segment?
> Is the rollback segment in RAM in the SGA or the PGA?
> 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?
>
> 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

Can't answer the question directly ... but I can tell you how to unambiguously find out much of what you are asking.

Set trace on and create a trace file from a SQL statement.

Then run it through TKPROF.

Each and every SQL statement issued by Oracle against the data dictionary will be there for your review.

Daniel Morgan Received on Wed Oct 23 2002 - 10:20:41 CDT