ITPub博客

首页 > 数据库 > Oracle > oracle 笔记

oracle 笔记

原创 Oracle 作者:next_junction 时间:2014-01-18 01:26:00 0 删除 编辑
1:An Oracle server: Is a database management system that provides an open,comprehensive,integrated approach to information management. Consists of an Oracle instance and an Oracle database.

2:An Oracle instance: Is a means to access an Oracle database.Always opens one and only one database. Consists of memory(memory structures) and background process structures.

3:Connection and Session:
user process, server process,background process

4:An Oracle database: Is a collection of data that is treated as a unit. Consists of three file types(Data files,Control files,Redo Log files. Parameter file,Password file,Archived Log files).

$ps -ef --查看进程
$sqlplus /nolog --以nolog连接到sqlpus
sql> conn / as sysdba --以sysdba连接到db
sql> startup --启动instance

Oracle's memory structure consits of two memory areas known as: System Global Area(SGA):Allocated at instance start up,and is a fundamental component of an Oracle instance. Program Global Area(PGA):Allocated when the server process is started.(一个instance只有一个SGA,但是每个server process都对应一个PGA,有几个server process 就有几个 PGA)

$ipcs --查看内存状态(SGA在linux和unix中的实现方式就是共享内存,是一个大的内存块)

The SGA consists of serveral memory structures:Shared PoolDatabase Buffer CacheRedo Log Buffer
Other structures(for example,lock and latch menagement,statistical data)
There are two additional memory structures that can be configured within the SGA:Large PoolJava Pool

$ps -ef | grep oracle --查看oracle进程
sql>show sga --查看sga(9i以后sga可以在线调整)

SGA: Is dynamic. Sized by the SGA_MAX_SIZE parameter. Allocated and tracked in granules(granules是sga分配内存的基本单位) by SGA.
Contiguous virtual memory allocation.Granule size based on total estimated SGA_MAX_SIZE.(sga小于128mb时候granules是4mb,大于128mb时候是16mb)

sql>select componect,granule_size from v$sga_dynamic_components;--查看sga的组成部分和对应的granules。

sga有一个总的最大的尺寸,里面的每个部件又有各自的尺寸。
1.DB_CACHE_SIZE
2.LOG_BUFFER
3.SHARED_POOL_SIZE
4.LARGE_POOL_SIZE
5.JAVA_POOL_SIZE

1:Shared Pool:
Used to store:Most recently executed SQL statements.Most recently used data definitions.
It consists of two key performance-related menory structures:Library Cache.Data Dictionary Cache.
Sized by the parameter:shared_pool_size(alter system set shared_pool_size=64m--这个语句改变其大小)
(1.1)Library Cache:Stores information about the most recently used SQL and PL/SQL statements.Enables the sharing of commonly used statements.Is managed by a least recently used(LRU) algorithm.Consists of two structures:Shared SQL area and Shared PL/SQL area. Size determined by the Shared Pool sizing.
(1.2)Data Dictionary Cache:A collection of the most recently used definitions in the database. Includes information about database files,tables,indexes,columns,users,privileges,and other database objects.During the parse phase,the server process looks at the data dictionary for information to resolve object names and validate access.Caching data dictionary information into memory improves respinse time on queries and DML.Size determined by the Shared Pool sizing.

2:Database Buffer Cache:
Stores copies of data blocks the have been retrieved from the data files. Enables great performance gains when you obtain and updata data. Managed through an LRU algorithm. DB_BLOCK_SIZE determines primary block size.
Consists of independent subcaches:DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE. Can be dynamically resized(alter system set DB_CACHE_SIZE=96M).DB_CACHE_ADVICE set to gather statistics for predicting different cache size bebavior.statistics displayed by V$DB_CACHE_ADVICE.

3:Redo Log Buffer:
Records all changes made to the database data blocks. Primary purpose is recovery. Changes recorded within are called redo entries(每次做改变都会在redo log中记录,这个记录称为redo entries,一条redo entries就可以恢复一次对DB的改变). Redo entries contain information to reconstruct or redo changes. Size defined by LOG_BUFFER.

4:Large Pool:
An optional area of memory in the SGA. Relieves the burden placed on the Shared Pool.Used for:Session memory(UGA) for the Shared Server,I/O server processes,Backup and restore operations or RMAN,Parallel execution message buffers(PARALLEL_AUTOMATIC_TUNING set to TRUE). Does not use an LRU list. Sized by LARGE_POOL_SIZE. Can be dynamically resized.

5:Java Pool:
Services parsing requirements for Java commands.Required if installing and using Java. Sized by JAVA_POOL_SIZE parameter.

PGA:Program Global Area:
Memory reserved for each user process connection to an Oracle database. Allocated when a process is created. Deallocated when the process is terminated. Used by only one process.

Process Structure:
Oracle takes advantage of various types of processes->
(1)User process:Started at the time a database user requests connection to the Oracle server.
(2)Server process:Connects to the Oracle instance and is tarted when a user establishes a session.
(3)Background processes:Started when an Oracle instance is started.

(1):User Process(客户端工具:sqlplus,toad,sql developer等),A program that requests interaction with the Oracle server. Must first establish a connection. Does not interact directly with the Oracle server.(过程:[Database user]-User process-{connection established}-Server process)
(2):Server Process,A program that directly interacts with the Oracle server. Fulfills calls generated and returns results. Can be dedicated or shared server.(过程:[Database user]-User process-{connection established}-Server process-{Session created}-[Oracle server]).
--》关于server process,也就是服务器进程,可以这样讲:oracle对用于用户发出sql请求如果通过后台进程去处理,那么当用户量很大,sql请求很多时候,oracle得需要同样多的后台进程来处理,这种情况会导致oracle不可能有那么多的后台进程来完成请求处理,只有通过一个服务器进程也就是server process来对应一个用户请求,这样就可以面对大量用户请求处理而轻松对付--(dba的思想天空)).

(2.1):IPC AND TCP/IP(IPC:Inter Process Communication,包括共享内存,队列,信号量等几种形式),两台不同的机器进程进行通信,走网卡(eht0)的话一定是用TCP/IP协议进行通信。如果同一台机器的进程可以走IPC协议进行通信,也可以通过lo(local loopback)走模拟的TCP/IP协议进行通信。
(3):Background Process($ps -ef | grep oracle--查看oracle的后台进程),Maintains and enforces relationships between physical and memory structures(可以有两种形式的进程,必须有的,可选的):Mandatory background processes:DBWn,PMON,CKPT,LGWR,SMON.Optional background processes:ARCn,LMDn,QMNn,CJQ0,LMON,RECO,Dnnn,LMS,Snnn,LCKn,Pnnn.
(3.1):Database Writer(DBWn),DNWn writes when:Checkpoint occers,Drity buffers reach threshold,There are no free buffers,Timeout occers,RAC ping request is made,Tablespace OFFLINE,Tablespace READ ONLY,Table DROP OR TRUNCATE,Tablespace BEGIN BACKUP.
(3.2):Log Writer(LGWR),LGWR writes:At commit,When one-third full,When there is 1 MB of redo,Every three seconds,Before DBWn writes.
(3.3):System Monitor(SMON),Responsibilities:Instance recovery:Rolls forward changes in online redo log files,Opens database for user access,Rolls back uncommitted transactions.Coalesces free space.Deallocates temporary segments.
(3.4):Process Monitor(PMON),Cleans up after failed processes by:Rolling back the transaction,Releasing locks,Releasing other resources,Restarting dead dispatchers.
(3.5):Checkpoint(CKPT,早期是lgwr负责此工作),Responsible for:Signaling DBWn at checkpoints,Updating datafile headers with checkpoint information,Updating control files with checkpoint information.
(3.6):Archiver(ARCn,生产库必要模式),Optional background process,Automatically archives online redo log files when ARCHIVELOG mode is set,Preserves the record of all changes made to the database.
(4):Dictates how the physical space of a database is used.Hierarchy consisting of tablespaces,segments,extents,and blocks.
                    datafile<-tablespace->segment->extent->blocks
                                datafile<--->segment(多对多)
(5):Processing SQL Statements,Connect to an instance using:User process,Server process. The Oracle server components that are used depend on the type of SQL statement:Queries return rows,DML statements log changes,Commit ensures transaction recovery. Some Oracle server components do not participate in SQL statement processing.
    oracle处理sql语句的过程:首先parssing(解析),客户端连接到数据库,建立一个连接会话,发出一个sql语句到后台,这之前还要做一个安全检查(权限等),解析成原子语句,然后绑定变量,执行语句,最后返回给客户端。
总结:数据库物理结构:数据文件,控制文件,联机重做日志文件等
数据库逻辑结构:表空间,段,扩展,逻辑快
SGA内存结构:数据缓冲区,共享池,日志缓冲区
后台进程:DBWn,LGWR,CKPT,PMON,SMON,ARCn
A connection is a communication pathway betwwn a user process and an Oracle server.一个用户只会和数据库instance的server process进行连接,并不会一个用户去启动一个instance,instance是数据库管理员来启动的。
A session starts when a user is validated by the Oracle server.
Connection:Communication between a user process and an instance.
Session:Specific connection of a user to an instance through a user process.

    {User--Sql>select ...--(user process--server process)--session}--->>()之间是一个connection,{}之间是一个session
Teh PGA is a memory region that contains data and control information for single(唯一) server process or multiple(多个) background processes.
Oracle的文件类型:Control files,Data files Online redo log files,Parameter file,Backup files,Archived redo log files,Pawwsord file,Alert log and trace files.

----------------------------------------------------------------------------------------------------------------------------(待续1)
















来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29117696/viewspace-1072384/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle 笔记(续1)
请登录后发表评论 登录
全部评论

注册时间:2013-08-21

  • 博文量
    19
  • 访问量
    166948