Assignment 2 ORACLE DATABASE ARCHITECTURE - eshan3101/DataBase-Architech GitHub Wiki
ORACLE DATABASE ARCHITECTURE
A)How a DQL get execute 1.1.A user logs in to the database and runs a SQL query. 2.The user process sends the query to the database instance. 3.The server process receives the query and parses it. 4.The query is converted into SQL code and checked in the Library Cache (SQL Area). 5.Syntax errors are checked. If any error is found, it is returned to the user. 6.If syntax is correct, the server process checks the Data Dictionary to validate objects (table name, column name, privileges, etc.). 7.If any semantic error exists, it is sent back to the user. 8.If everything is correct, the query goes to the Optimizer, which creates the most efficient execution plan. 10.The required data blocks are read from the data files and loaded into the Database Buffer Cache. 11.Finally, the result is returned to the user.
B)How a DDL or DML get execute 1.A user runs a DDL or DML statement (INSERT, UPDATE, DELETE, CREATE, etc.). 2.The user process sends the SQL statement to the database instance. 3.The server process parses the query, checks syntax, and verifies objects using the Data Dictionary (same steps as DQL). 4.If everything is valid, the required data blocks are read from the data files and copied into the Database Buffer Cache. 5.All changes are first made in the Database Buffer Cache, not directly in the data files. 6.The details of the change are written into the Redo Log Buffer. 7.The LGWR (Log Writer) process writes the redo information from the redo log buffer to the Redo Log Files when: Every 3 seconds 1/3 of the redo log buffer is full A COMMIT occurs 1 MB of redo information is generated
8.Each transaction is tracked using an SCN (System Change Number), which identifies committed and uncommitted changes.
C)CKPT 1)Incremental Checkpoint Occurs frequently (around every 3 seconds). Synchronizes the Data Files, Control File, and Redo Log Files. Updates the latest SCN (System Change Number) in the Control File and Data File headers. Also signals DBWn (Database Writer) to write dirty blocks from the Database Buffer Cache to the Data Files. 2)Full Checkpoint Occurs when the database shuts down or during a log switch. Ensures that all dirty blocks in memory are written to the Data Files, keeping the database fully synchronized.
D)DBWN 1)DBWn writes dirty blocks from the Database Buffer Cache to the Data Files. 2)When a checkpoint occurs, CKPT signals DBWn to start writing these dirty blocks to disk. 3)If DBWn cannot write all the dirty blocks due to high activity or heavy workload, a full checkpoint ensures that all remaining dirty blocks are written to the data files. 4)This helps maintain data consistency and synchronization in the database
E)PMON 1)If multiple users are connected and one user disconnects unexpectedly (due to network or hardware issues), PMON detects it. 2)PMON then cleans up the failed user session by killing the associated server process. 3)It also releases the resources allocated to that session, such as PGA memory. 4)Because it cleans unused processes and resources, PMON is also known as the cleaner of the database.
F)SMON 1)If the database shuts down unexpectedly (due to hardware failure, power failure, etc.), SMON performs recovery when the database starts again. 2)After startup, SMON checks the Redo Log Files. 3)It identifies the committed transactions using the SCN (System Change Number) that were not written to the data files. 4)SMON then rolls forward those changes and writes them to the data blocks in the data files, ensuring the database is consistent.
1.Library cache- In library cache parsing generated query code stores for further information. Library cache first verify the command as SQL command rules, for example if you type selct instead of select then library cache given error as Syntax error immediately to the user. User query fulfil as sql rules in library cache it pass to data dictionary for further process.
2.Data dictionary- Data dictionary stores metadata in dictionary means data of data. Data dictionary taken query from library cache to check semantic error. To check rows or columns present or not and to check user query table available or not in our database. If user type table name employee instead of employees then data dictionary given semantic error to the user. If user query fulfill as sql rules then pass over to the optimizer.
3.Optimizer- In optimizer creates multiple ways to execute query path to the data file. It create shortest path to the data file and pass user query to the data file.
4.Data file- In data file collect the query from optimizer and make copy of original data and pass to the Data Buffer Cache.
5.Data Buffer Cache - Data buffer cache accept the copy data file from data block. The data in the buffer matches exactly what is stored on blocks, than this blocks known as clean block. This clean block data pass throw to the Library cache. Those clean blocks data in library cache store in a code data for Furter execute. Library cache pass query data to server process and server process pass to the user.
Parsing- Parsing means server process breakdown the query into parts and server process generated a code for pass to the library cache. Library cache present into SGA(system global area) at share pool.
Hard Parsing - Process from user process query to datafile blocks copy file to buffer cache to received query result to user process this process known as HARD PARSING.
Soft Parsing - If user or another users at the same server connected and execute the same query at the server in same day Library cache code generated match it then library cache do not pass thru same query to data dictionary. It will direct pass data file to the user or users then this is known as SOFT PARSING.
share Pool- Caches parsed SQL statements, execution plans, and data dictionary information.