Database-Arch.rar_arch

Server = Instance (RAM)+ database (Disk) Instance = SGA + BP SGA (Shared/System) Shared Pool (Retain Most recently executed queries - Exec Plan) CPU I/O SELECT * FROM emp, dept, salgrade WHERE emp.deptno=dept.deptno AND sal BETWEEN losal AND hisal AND dept.deptno=10 EMP+DEPT--->SALGRADE EMP+SALGRADE ---> DEPT(10) DEPT(idx--deptno)---10----> EMP(idx) DEPTNO DB buffer cache--LRU-> datafiles Redo Log Buffer----> Redo Entries (Changes--DML/DDL) Redo log files V$log Redo1 3 Redo2 2 Arch_ORCL_01 v$archived_log-----> control file ---------------------------------------------------------- BAckground Processes ---------------------- LGWR (Log Writer) Redo Log Buffer-----> Redo log files DBWn (Database Write) n--0-9 DBW0 DBW1 DBW2 DB Buffer Cache--DBWn-> Data Files CKPT (Checkpoint) Give instruction to DBWn Update Control file with database health information ARCn (Archiver) --30 Redo Log Files--->Archive Log Files(30) PMON (Process Monitor) Crash DAtabase Server Release orphan Server process --------------------- SMON (System Monitor) Perform Instance Recovery ----------------------------------------- Application ----> ODBC / JDBC SQL*PLus Quest Software --->Toad (Oracle, SQL Server, MySQL, Access) SQL Engine Oracle-->SQL Developer (SQL server, MySQL, Access, Oracle) Client Machines Toad (User Process) --> send connection request to server --> Send queries to server --> accept result from server Server Process (Server side) --dedicated process allocated to each connection(user process) Update emp set sal=sal+2000 where empno=7788;---> server process ---> Optimizer (Best Exec Plan---Shared Pool) ---> SP search EMP table's data in DB buffer cache ---> Modify Record of emp 7788 (Lock Record) ---> --> commit / Rollback User---SP--Lock Sort (Arrange Asc or descending) select * from emp order by sal -- sort -------------------------------------------------------------- Logical Structure of database 1. Tablespace 2. Segments 3. Extents 4. Blocks 1024 Datafiles Logical groups of datafile are created --Tablespace Tablespace CREATE USER scott IDENTIFIED by tiger DEFAULT TABLESPACE users; Scott SQL> create table dept(deptno number,dname char(10)); Backup Backup tablespace users; Recover tablepsace users; Used for ease of administration of datafiles CREATE TABLESPACE users DATAFILE 'c:\users01.dbf' size 100M Alter tablespace users add datafile 'd:\users02.dbf' size 200M; --------------------------------------------- Alter user allen default tablespace users; ----------------------------------------------------------- Tablespace contain? Tables Indexes These objects are known as Segments Any object that occupy separate space is known as segment ---------------------------------------------------------- EMP -- table/segment (Type : Table) indx_deptno -- index/segment (Type : index) ----------------------------------------------------------- Each segment require "space" Extents : Are units of space allocation. DBA will decide size of each extent CREATE TABLESPACE users DATAFILE 'c:\users01.dbf' size 100M UNIFORM SIZE 10M; ----------- Scott CREATE TABLE EMP(empno number, ename char(10)); EMP--users (10m--1 extent) Growth--1 extent(10Mb) ------------------------------------------------------------- Blocks -------- Units of I/O operation CREATE TABLESPACE users DATAFILE 'c:\users01.dbf' size 100M UNIFORM SIZE 1M BLOCKSIZE 8192 -----> 8K 1M=1024K / 8K = 128 Blocks in each extent Blocksize choices : 2K, 4K, 8K, 16K, 32K ---------------------------------------------------------- Database Server 1. Tablespace 2. Segments 3. Extents 4. Blocks