ORACLE
Oracle (Oak Ridge Automatic Computer and Logical Engine).
FUNCTIONALITY OF ORACLE DATABASE
Inatance:- Instance is a way through which we connect to the database.
Database:-It is a storeg , where the meaningfull data is stored.
Whenever user sends a request, primary search for the data will be done in RAM. If the information is available, it will be given to the user.Otherwise, secondary search will be done in hard disk and copy of that info will be placed in RAM before giving that to the user
The functionality of Oracle database is same as oprating system .When the user palaced request, primary search will be done in Instance. If info is available, it will be given to the user. Otherwise, secondary search will be done in database and a copy will be placed in the instance and then that info will be given to the user.
INTERNAL USER CONNECTIVITY PROCESSES
-Whenever user starts and application, a user process will be created on the client side. E.g.: sqlplusw.exe process will be started when a user clicks on sqlplus executable on a windows operating system.
INTERNAL USER CONNECTIVITY PROCESSES
-Whenever user starts and application, a user process will be created on the client side. E.g.: sqlplusw.exe process will be started when a user clicks on sqlplus executable on a windows operating system.
-This user process will send a request to establish a connection to server by providing login credentials.
-On server side, Listener service will accept all connections that are coming in and will hand over user information (like username, password, ip address, network etc) to a background process called PMON .
-PMON will then perform the authentication of the user using base tables. For this it will do a primary search in data dictionary cache and if a copy of base table is not available in that, then it will copy from the database.
-Once authenticated, user will receive and acknowledgement statement. This can be either successful / unsuccessful message .
-If successful connection, PMON will create server process and a memory will be allocated to that server process which is called as PGA.
-Server process is the one which will do work on behalf of user process .
BASE TABLES
-Base tables store the information i.e. helpful for database functionality. This info is also called as dictionary information .
-Information in base tables will be in cryptic format and because of this we can access but cannot understand data inside them .
-A try to modify base tables (performing DML or DDL operations) may lead to database corruption. Only oracle processes are having authority to modify them .
-Base tables will be created at the time of database creation using SQL.BSQ script .
-Oracle provided 2 types of views to access information inside base tables .
-a. Data dictionary views – which will be in the format of dba_XXX (name prefixed with dba_ keyword) and provides permanent info of the database .
-b. Dynamic performance views – which will be in the format of v$XXX (name prefixed with v$ sign) and provides ongoing (current actions) of the database.
-These views will be created after database creation by executing CATALOG.SQL script.
-All procedures and packages helpful for DBA will be created using CATPROC.SQL script .
-Catalog.sql and catproc.sql scripts should be run after database creation, if database is created manually. While creating database using DBCA, oracle will execute them 5. These two scripts will reside in ORACLE_HOME/rdbms/admin path .
PHASES OF SQL EXECUTION
1. Any SQL statement will undergo following phases to get executed .
a. PARSING : This phase will perform following actions .
i. Syntax checking of the SQL statement .
ii. Semantic checking of the statement i.e. checking for the privileges using base tables.
iii. Diving the statement into literals .
b. EXECUTION : This phase will perform following actions.
i. Converting the statement into ASCII format.
ii. Compiling the statement.
iii. Running or executing the statement.
c. FETCH : Data will be retrieved in this phase.
Note: For a PL/SQL program, BINDING will happen after PARSING phase.
SELECT STATEMENT PROCESSING
- Server process will receive the statement sent by user process on server side and will handover that to library cache of shared pool .
- The 1st phase of sql execution i.e. Parsing will be done in library cache.
- Then, OPTIMIZER (brain of oracle sql engine) will generate many execution plans, but chooses the best one based on time & cost (time – response time, cost – cpu resource utilization).
- Server process will send the parsed statement with its execution plan to PGA and 2nd phase i.e. EXECUTION will be done there.
- After execution, server process will start searching for the data from LRU end of LRU list and this search will continue till it founds data or reaches MRU end. If it found data, it will be given to the user. If it didn’t found any data, it means data is not there in database buffer cache.
- In such cases, server process will copy data from datafiles to MRU end of LRU list of database buffer cache.
- From MRU end again blocks will be copied to PGA for filtering required rows and then it will be given to user.
Note: server process will not start searching from MRU end because there may be a chance of missing the data by the time it reaches LRU end in searching .
Note: for statements issued for the second time, parsing and fetch phases are skipped, subject to the availability of data and parsed statement in the instance .
-On server side, Listener service will accept all connections that are coming in and will hand over user information (like username, password, ip address, network etc) to a background process called PMON .
-PMON will then perform the authentication of the user using base tables. For this it will do a primary search in data dictionary cache and if a copy of base table is not available in that, then it will copy from the database.
-Once authenticated, user will receive and acknowledgement statement. This can be either successful / unsuccessful message .
-If successful connection, PMON will create server process and a memory will be allocated to that server process which is called as PGA.
-Server process is the one which will do work on behalf of user process .
BASE TABLES
-Base tables store the information i.e. helpful for database functionality. This info is also called as dictionary information .
-Information in base tables will be in cryptic format and because of this we can access but cannot understand data inside them .
-A try to modify base tables (performing DML or DDL operations) may lead to database corruption. Only oracle processes are having authority to modify them .
-Base tables will be created at the time of database creation using SQL.BSQ script .
-Oracle provided 2 types of views to access information inside base tables .
-a. Data dictionary views – which will be in the format of dba_XXX (name prefixed with dba_ keyword) and provides permanent info of the database .
-b. Dynamic performance views – which will be in the format of v$XXX (name prefixed with v$ sign) and provides ongoing (current actions) of the database.
-These views will be created after database creation by executing CATALOG.SQL script.
-All procedures and packages helpful for DBA will be created using CATPROC.SQL script .
-Catalog.sql and catproc.sql scripts should be run after database creation, if database is created manually. While creating database using DBCA, oracle will execute them 5. These two scripts will reside in ORACLE_HOME/rdbms/admin path .
PHASES OF SQL EXECUTION
1. Any SQL statement will undergo following phases to get executed .
a. PARSING : This phase will perform following actions .
i. Syntax checking of the SQL statement .
ii. Semantic checking of the statement i.e. checking for the privileges using base tables.
iii. Diving the statement into literals .
b. EXECUTION : This phase will perform following actions.
i. Converting the statement into ASCII format.
ii. Compiling the statement.
iii. Running or executing the statement.
c. FETCH : Data will be retrieved in this phase.
Note: For a PL/SQL program, BINDING will happen after PARSING phase.
SELECT STATEMENT PROCESSING
- Server process will receive the statement sent by user process on server side and will handover that to library cache of shared pool .
- The 1st phase of sql execution i.e. Parsing will be done in library cache.
- Then, OPTIMIZER (brain of oracle sql engine) will generate many execution plans, but chooses the best one based on time & cost (time – response time, cost – cpu resource utilization).
- Server process will send the parsed statement with its execution plan to PGA and 2nd phase i.e. EXECUTION will be done there.
- After execution, server process will start searching for the data from LRU end of LRU list and this search will continue till it founds data or reaches MRU end. If it found data, it will be given to the user. If it didn’t found any data, it means data is not there in database buffer cache.
- In such cases, server process will copy data from datafiles to MRU end of LRU list of database buffer cache.
- From MRU end again blocks will be copied to PGA for filtering required rows and then it will be given to user.
Note: server process will not start searching from MRU end because there may be a chance of missing the data by the time it reaches LRU end in searching .
Note: for statements issued for the second time, parsing and fetch phases are skipped, subject to the availability of data and parsed statement in the instance .
No comments:
Post a Comment