TutorialDBA - Support | Training | Consultant

Explain Oracle Architecture?   Oracle server is divided into instance and database. Again, INSTANCE  is divided into  Memory structures  Background processes.    Memory structures  SGA  - Shared Global Area or System Global Area PGA - Process Global Area or Program Global Area Background processes   DBWR - Database writer  LGWR - Log writer  SMON - System monitor PMON - Process Monitor ARCH - Archiver  CKPT - Checkpoint DATABASE  is divided as   Physical structures Logical structures Physically database contains Datafiles Online redo Logfiles  Controlfile Archived log files Logically database is divided into Tablespace Segments Extents Blocks Tablespace physically contains one or more datafiles,and logically group of segments. Segments are group of Extents Extents are group of blocks. Below is the basic Diagram of Oracle Architecture. Now lets discuss about each component. 1. Wh...

 When a query is executed for the very first time, the user’s process searches for the data in the database buffer cache. If data is there, it uses it. otherwise, it performs an I/O operation to retrieve data from the datafile on disk into the buffer cache, and from this data, the final result set is displayed.  if another query requires the same data set, the process uses the data from the buffer cache to build the result set required by the user. only if it is present in buffer cache. The Result Cache is an area in the shared pool and contains the end results of a query execution. The Result Cache can be managed either on the client side or the server side. Client side  Result Cache implementation would require the application to use the Oracle Call Interface (OCI) calls.          Server side   The query could be executed with a /*+ RESULT CACHE */ hint (or) the result_cache_mode parameter could be set to AUTO.  If r...

The PGA(program or process global area)is a memory area(ram) that stores data and control information for a single process. It typically contais a sort_area,hash_area,session_cursor cache. pga areas can be sized manually by setting parameters like hash_area_size,sort_area_size Hash_Area_Size Oracle hash_area_size is simple. The hash_area_size parameter value defaults to 1.5 times sort_area_size and is used for performing hash joins of Oracle tables. The higher the value for hash_area_size, the higher the propensity for the CBO to use a hash join. The value for hash_area_size is quickly the oracle show parameters command SQL > show parameter hash_area_size; The hash_area_size is obsolete if you are using pga_aggregate_target,but in oracle 9i with pga_aggregate_target ,a hash area size cannot exceed 5% of the pga area many increase the hash_area_size with " alter session get hash_area_size " or with a use_hash hint. Sort_Area_Size The sort_area_size parameters control the RA...

The basic memory structures associated with Oracle Database include: System Global Area (SGA) The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Program Global Area (PGA) A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total PGA memory allocated for all background and server processes attached to an Oracle Database instance is referred to as the total instance PGA memory, and the collection of all individual PGAs is referred to as the total instance PGA, or just instance PGA. It contains global variables and data structures and control information f...

 its mainly used for sorting purpose A PGA is nonshared memory created by Oracle Database when a server or background process is    started. Using v$session we can check whether dedicated server or shared server One pga for each server process One PGA exists for each Server Process and each Background Process. It stores data and control information for a single Server Process or a single Background Process. The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process.         Monitor GA usage statistics: select * from v$pgastat; Determine a good setting for pga_aggregate_target: select * from v$pga_target_advice order by pga_target_for_estimate; Show the maximum PGA usage per process: select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process; Pga memory divede into three area SESSION MEMORY PRIVAT...

Architecture Oracle Architecture consists of two things, Oracle Instance  – consists of Memory(SGA & PGA) and Background process Oracle database files  – consists of OS level database files like Datafile, logfile and controlfiles. user process  (User Process Interface) : client sending a request from one end. It starts as we use putty to connect to server machine. server process  (Oracle Process Interface) : connecting to db using command sqlplus / as sysdba. Two types of server process in oracle, 1.  shared server(dispatcher) : multiple user using single resources for sending request and receiving reply from the database as using single server process.(till 8i) 2.  Dedicated server(listener)  : each and every user uses dedicate server process as independent of other user for sharing.(from 9i) (from 10g default ). Oracle Instance: Oracle instance consists of memory and background process.  Oracle memory consist of ...