Saturday , November 25 2017




  1. 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.
  1. 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.
  1. The request and response between RAM and hard disk is called I/O operation.
  1. Files with more size will be fitted into RAM using swapping (flushing old data – Least Recently Used algorithm)
  1. Why to copy data to RAM?
    1. It will give benefit for next users who are requesting for same data. Accessing information from memory is always faster than accessing it from disk
  1. What happens if data size is more than RAM size?
    1. Data will be splitted based on the RAM size and swapping will takes place (In windows, this is called paging)
  1. How the data will be managed in RAM?
    1. Using Least Recently Used (LRU) algorithm
Note: The primary goal of DBA is to reduce response time there by increasing performance and also avoiding I/O (all these 3 are interlinked)
Note: Any request and response between memory and disk is called I/O.
  1. The functionality of oracle database is similar to operating system functionality.
  2. When a request is placed by user, 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.
  3. Instance also will follow LRU algorithm.
  4. The request and response between an instance and database is also I/O
Instance – it is a way through which users can connect to database.
Database – it is the location where user’s data is stored.
oracle 10g database
  1. 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.
  1. This user process will send a request to establish a connection to server by providing login credentials (sometimes even host string also)
  1. 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 (process monitor)
  1. 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.
  1. Once authenticated, user will receive and acknowledgement statement. This can be either successful / unsuccessful message.
  1. If successful connection, PMON will create server process and a memory will be allocated to that server process which is called as PGA (private global area)
  1. Server process is the one which will do work on behalf of user process.
  1. Base tables store the information i.e. helpful for database functionality. This info is also called as dictionary information.
  1. Base tables will be in the form of XXX$ (i.e. name suffixed with a $ sign) and will reside in SYSTEM tablespace.
  1. Information in base tables will be in cryptic format and because of this we can access but cannot understand data inside them.
  1. 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.
  1. Base tables will be created at the time of database creation using SQL.BSQ script.
  1. Oracle provided 2 types of views to access information inside base tables
    1. Data dictionary views – which will be in the format of dba_XXX (name prefixed with dba_ keyword) and provides permanent info of the database.
    2. 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.
  2. These views will be created after database creation by executing CATALOG.SQL script.
  3. All procedures and packages helpful for DBA will be created using CATPROC.SQL script.
  4. 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.
Read More:
Performance Tuning using SQL Tuning Advisor
Performance Tuning Using AWR, ASH and ADDM
Performance Tuning



Check Also

How to perform RMAN cloning (Easy process to clone a database on different server)

Cloning is a process of making a separate copy from one database to another. Cloning …

Leave a Reply

Your email address will not be published. Required fields are marked *