Oracle Basics

This document uses a top down approach to describer end to end of oracle transaction and storage operations.

DB

Once oracle server is installed u need to create a database.

A database is nothing but a set of tablespaces. Each table space contains a set
of a set of data files. Now what each data file is going to contain? It is going to contain table data. We will discuss about it in detail.

So once u know structure it is obvious to create a database u need to specify what tablespaces it is going to contain. If u create a DB without specifying any DB it will be created with default table space viz., SYSTEM TS. Also u can go on adding TS to DB after creating it.

So how do u create a TS? From the structure its obvious that TS is nothing but container for a set of data files. So u need to define how many data files the TS going to contain and size of them. U can specify in a various combinations that we will not be worried.

Tables

Next step is obviously is to create tables. What exactly table is going to contain? Table comprises of basically two parts: structure(schema) of table and data it holds. The structure of all the schema objects is stored in a control file specific to that DB.

Data it contains is stored in segments. Each table has a segment. If table is partitioned then each partition will get a data segment.
Similarly each index has got a segment. If it is a partitioned index then each partition has a segment.
Temporary segments are created by for its work area for parsing and compiling of queries.
Rollback segment are created by ur DBS if u r doing manual undo. Info. in this segment is basically used to undo the things on a roll back. If Automatic undo is set oracle itself manages undo space.

Segment is a set of extents allocated for a logical structure. Extent is a specific number of data blocks obtained in a single allocation, used to store specific type of info.

A data block is the finest level of granularity. Oracle DB is stored in data blocks. One data block corresponds to a specific no. of bytes on physical DB on disk. Standard block size is specified by initialization parameter db_block_size.

SQL Statements

Ok fine. DB and table is created.what would be the next step? To populate the table and query its contents. So now we will discuss what exactly happens under hood when u fire a query on SQL prompt.

To know this u need to understand some of the basic concepts like what is meant by oracle instance?

An oracle instance is a set of background processes started by oracle and memory buffers it holds.

When u fire a query Oracle creates a user process. It basically communicates between external user and DB. It accepts query, sends it to server process, receives output of query or error and displays it to user.

For each user process oracle creates a server process. Server process communicates between user process and interacts with oracle to carry out required task. Creation of a server process for each user process happens only if ORACLE is set to dedicated server mode. If oracle is set to shared server process mode then dispatcher process comes into picture.

In this mode there is a limited no. of server processes are created when oracle starts. When a user process comes with a request server dispatcher process will delegate it into one of the free server processes and send output back to user process. <>

OK .. this is fine. As far as the accepting the request and sending response back to user. However I want to know what exactly happens when u meant by processing of query? <>

For sake of explanation I will divide all the SQL statements into two categories viz., those who don’t modify DB and who does.
Take an ex. of simple query: select * from employee;
which doesn’t modify table.

First time query comes its parsed and compiled and kept in memory. Now fetch data from physical disk and store into a the databuffer. This result is returned to user process. However this databuffer is not released as soon as result is returned to user.

Now when next time this query comes parsing and also those things will not happen again. Also result is not fetched from physical disk once again. The result in the data buffer is straight away returned to user.

Yup u r right.. All the data buffers which contain data and parsed queries are stored in SGA(System Global Area). Ok fine its not that gr8. Being world class DB oracle is supposed to do at least that much. caching of recent results. But gr8 thing about that is that same user may need not have fired that query before.

OOPS!! If u observer fig. more closely the SGA is unique to oracle instance and not to per session. User A fires query to get employee detains. Then any other user fires same query will get results from data buffer. No parsing, no physical access etc.,

Ok fine. Agreed oracle has a bit of intelligence. Move to next thing.

Now take an example of a query that modifies DB:

SQL> update employee set salary = 1000 where id = 10 ;

When this query comes first time same thing will happen. It parses to decide what needs to be done. Then it fetches data and updates data. This is called modified data buffer. Now it needs store back the result.

Again oracle shows off its intelligence here. It won’t store the result back immediately. This modified buffer still kept in memory. There is a process called DB writer which will decide when to write this buffer to physical disk. It does so that no. of disk access will be minimum and oracle gives max. efficiency.

Wait a minute. If I have changed a table and another tries to access it he will be blocked until I issue commit. What was that for? That is basically transaction funda. Transaction is nothing but a sequence of SQL statement. Oracle ensures that either all the SQL statements in a transactions are completed or none. In case any of them fails it roll backs to the state that is before the start of transaction.

Ok fine. U mean to say that when I issue commit DB writer will write contents of modified buffer to data file. Isn’t it? No it’s a misconception that many ppl have about Oracle commit. (I also had this till I understood what exactly happens underhood). Its not true. DB writer will write when it feels its appropriate to do.

One more characteristic of commit is that once a commit happens changes will appear to all the other sessions. Anyway its not very difficult thing to implement once we know

Modified buffers are kept in SVG and any query will search SVG to find if data searching is already in SVG memory cache. Since modified buffers which are not written to data file are always kept in SVG it will always find there and returns new data.

But I know that once I commit the changes are permanent. After that even if db is crashed I should be able to get to that state. If oracle doesn’t serialize that info. then how it is possible?

It is one of best features of ORACLE I liked. It follows log-ahead-serialize. There is a redo log maintained which records all the changes. Once a commit is done all changes are recorded into redo log. Incase of DB crash it gets that info. from redo log and gives committed DB state.

It is called as online redo log. Often these redo logs are archived and kept at separate locations. This is done by ARCHIVER. Usually once archive process is enough. U can configure no. of archiver processes can be configured with initialization parameter log_archive_max_processes.

Ok fine. All this jhamela just to ensure that DB writer will write the modified buffers to data file whenever it wishes to do so. Usually when it writes? Usually it writes whenever no. of buffers available in SVG reduces to small limited no. It writes least used modified buffer to data file and frees it. Then new info. can be stored in that buffer. Another time it writes when check point is signaled. At this point all the modified buffers in SVG are written data file. CKPT is process responsible for signaling checkpoint db writer process.

No. of db writer processes can be configured with initialization parameter db_writer_processes.

Have fun!! Hhacking into internals of oracle ...