A brief about how oracle works for a Query.

I Deepti Lakhani Roll No 14 from DBM2010 Batch  will like to share few points on Oracle 9i.

First history of oracle:

Ø  Founder of Database- Larry Ellison, SDL laboratories

Ø  1977- Oracle- RDMS(Relational Database Management System)

Ø  1979- SDL change to RSI- Relational software: Oracle V2 Incorporation

Ø  1983- RSI (Oracle V3: Support Unix Platform) was renamed to ORACLE Corporation

Ø  1984- Oracle V4: Read Consistency

Ø  1985- Oracle V5: Client/ Server , distributed queries

Ø  1988- Oracle V6: PL/SQL, no backups, ERP- Oracle finance

Ø  1992- Oracle V7: Most stable version, Integrity constraints, stored procedures, triggers

Ø  1997- Oracle V8: Known as Oracle 8- OO development, multimedia

Ø  1999- Oracle 8i- Java Virtual Machine(JVM)

Ø  2001- Oracle9i- Read/ Write XML documents. Oracle RAC( Real Application Cluster)

Ø  2003- Oracle 10g

Ø  Latest- Oracle 11i

Role & Responsibilities of DBA( Data Base Administrator)

1.       Plan & create database

2.       Backups, manage database availability.

3.       Manage physical and logical structure

4.       Manage storage management and security.

5.       Network administration

6.       Backup & recovery

7.       Tuning.

Oracle Server has three components- Database, Oracle Instance, and Oracle Utilities.

Let’s look at the Oracle Instance and see how it works when we fire a query.

Query- Select * from emp;

1.       Check for syntax & semantic

2.       Convert into hash

3.       Create hard parse

4.       Choose optimizer

5.       Create space in SQL

6.       Check in Data Dictionary for privileges/ constraints & create space in data dictionary.

7.       If the data is not in Database Buffer Cache(DBC), it will go to Data File(DF) & create Logical Read, when we do Insert/ Update it creates Logical Read in Database Buffer Cache in Undo Segment & new data is stored in Log Buffer.

8.       When we say Commit, data is written to Redo Log file from Log Buffer through Log Writer (LGWR), after LGWR is finished Check Point (CHKPT) signals to Database Writer (DBWR) to write in Data files. If database shuts down while writing in DF, suppose there are 1000 records and out of these 200 records are remaining to write then when we start System Monitor(SMON) checks & writes remaining data in DF from Log File.

9.       When SQL area is full it creates new space using FIFO in  that also we have MRU( Most Recently Used) & LRU( Least Recently Used).

10.   When we again say Select Statement then the data is fetched from DBC Logical Buffer.

Thank you for spending your precious time.