Oracle基础培训

深圳市协尔信息系统有限公司,ShenzhenShareInfoCo.,Ltd.,Oracle基础培训,目的,对Oracle体系结构和主要模块有一个初步的了解了解Oracle内存结构，清楚相关配置项了解Oracle进程结构了解Oracle存储结构了解Oracle启动、关闭、跟踪排错等知识了解表、索引，清楚建立索引的一些原则编写高效SQL的一些原则,Oracle体系结构,Instance,SGA,Redologbuffercache,Sharedpool,DataDict.cache,Librarycache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Controlfiles,Datafiles,Archivedlogfiles,Parameterfile,Passwordfile,Redologfiles,Database,Databasebuffercache,OracleServer,Oracleserver:是一个数据库管理系统，它提供开放的、全面的、集成的信息管理包括OracleInstance和OracleDatabase,OracleServer,OracleInstance,Oracleinstance:意味着可以访问Oracledatabase总是打开一个Oracledatabase，也只能打开一个由内存结构和进程结构组成,Backgroundstructures,Memorystructures,SGA,Redologbuffercache,Databasebuffercache,Sharedpool,DBWR,SMON,PMON,CKPT,LGWR,Others,DataDictionarycache,Librarycache,Instance,连接和Session,客户端连接到一个Oracleinstance包括：建立一个用户连接和创建一个对话（Session）,Connectionestablished,Sessioncreated,Databaseuser,Userprocess,Oracleserver,Serverprocess,OracleDatabase,Oracledatabase:是一些经过处理的数据集合一个Database允许多个Instance打开，OPS包括三种文件类型ControlfilesDatafilesRedologfiles,Controlfiles,Datafiles,Archivedlogfiles,Parameterfile,Passwordfile,Redologfiles,OracleDatabase,内存结构,Oracle的内存结构主要包括两种内存区域：SystemGlobalArea(SGA):系统全局区实例启动时分配，是Oracle实例的基础部件是动态可配的，最大值可以由SGA_MAX_SIZE限制ProgramGlobalArea(PGA):程序全局区Oracle服务进程启动时分配是为每一个连接到Oracledatabase的用户进程保留的内存区,SystemGlobalArea(SGA),SGA包括下面几种内存结构:Sharedpool：缓存最近使用的SQL句子和数据字典Databasebuffercache：缓存最近使用数据Redologbuffer：缓存被改变的数据记录，主要用于恢复其他存储结构(如lock和latch管理,统计数据)两种可选内存区:Largepool：用于SharedServer的环境，缓解SharedPool压力Javapool：用于使用Java解析的环境,SGA,Redologbuffercache,Databasebuffercache,Sharedpool,DataDictionarycache,Librarycache,SharedPool,sharedpool用于存储最近执行的SQL句子和最近使用的数据字典信息包括两个关键的跟性能相关的内存结构:Librarycache：V$LIBRARYCACHE保存最近使用的SQL和PL/SQL句子leastrecentlyused(LRU)最近少用淘汰算法Datadictionarycache：V$ROWCHACHE保存最近使用的数据定义包括数据文件、表、索引、列、用户、权限等信息由参数控制大小：SHARED_POOL_SIZE.,ALTERSYSTEMSETSHARED_POOL_SIZE=64M;,DatabaseBufferCache,databasebuffercache保存从数据文件中获取的数据库块的副本。对数据查询、数据更新产生很大的性能影响。由leastrecentlyused(LRU)最近少用淘汰算法管理DB_BLOCK_SIZE：每一物理块的大小，在实例安装时指定DB_CACHE_SIZE：高速缓存池，至少16M，V$SYSSTATDB_KEEP_CACHE_SIZE：KEEP缓存池，可缓存量少、频繁使用的对象DB_RECYCLE_CACHE_SIZE：RECYCLE缓存池，被使用后对象长时间不用DB_CACHE_ADVICE：高速缓存统计数据收集开关,Databasebuffercache,ALTERSYSTEMSETDB_CACHE_SIZE=96M;,进程结构,Oracle进程是一些程序，这些进程执行一序列步骤，或执行一些特定任务，不同的操作系统Oracle对其进程有不同处理方式，Unix/Linux时是一个普通的进程，Windows时是一个线程。几种进程类型：Userprocess:用户请求连接到Oracleserver的时候启动。Serverprocess:连接到OracleInstance，当用户创建一个Session是启动Backgroundprocess:处理特定任务的进程，随着OracleInstance启动而启动。,BackgroundProcess,必须的backgroundprocessesDBWn：数据库写进程，把数据库高速缓存中的数据写入到数据文件上。PMON：进程监控进程，当其他进程出现问题时做相关清除工作。CKPT：Checkpoint进程，向DBWn发送CKPT指令，更新数据文件，更新控制文件。LGWR：日志写进程，把重做日志缓存数据写入到日志文件上。SMON：系统监控进程，职责：实例恢复、结合零散自由空间、释放临时段RECO：恢复进程，用于分布式数据库中的失败处理可选的backgroundprocessesARCn：日志归档进程Snnn：用来管理需要专用服务器的数据库的连接LCKn：当ORACLE以并行服务器方式进行时，解决内部实例的锁定问题Dnnn：在MTS模式下，用来减少多重连接所需的信息源SNPn：快照进程，用来管理快照的刷新。,逻辑结构,Oracle体系结构的逻辑结构说明了Oracle的物理空间是如何使用的。分为：tablespaces,segments,extents,和blocks等层次。,Tablespace,数据库存储层次,Database,Logical,Physical,Tablespace,Datafile,OSblock,Oracleblock,Segment,Extent,初始化参数文件,初始化文件描述数据库实例的细节；PFILE（initSID.ora）和SPFILE（spfileSID.ora）；PFILE是一个文本文件，可以手工修改；SPFILE是一个二进制文件，可用命令ALTERSYSTEM修改其中的参数值；在实例启动之前，可以用命令CREATESPFILE由PFILE文件(initSID.ora)创建SPFILE文件；,SQLCONNECT/ASSYSDBASQLSTARTUP,db_domain=db_name=smsdbjava_pool_size=33554432large_pool_size=8388608shared_pool_size=50331648processes=150job_queue_processes=10db_block_size=8192db_cache_size=25165824,启动,STARTUPmode,STARTUPPFILE=$ORACLE_HOME/dbs/initdb01.ora,STARTUPRESTRICT,关闭,ShutdownMode:NORMAL（默认）TRANSACTIONALIMMEDIATEABORT,YES,NO,xo,SHUTDOWNmode,跟踪,后台跟踪文件当后台进程发生错误时生成所在目录：BACKGROUND_DUMP_DEST(bdump)用户跟踪文件包含一些跟踪SQL句子的统计数据和用户错误信息；发生Session错误时生成所在目录：USER_DUMP_DEST(udump)命令使能用户跟踪文件：Session级别用ALTERSESSION命令:ALTERSESSIONSETSQL_TRACE=TRUESession级别也可以用内置DBMS存储过程:dbms_system.SET_SQL_TRACE_IN_SESSION实例级别可以设定初始化参数:SQL_TRACE=TRUE,数据字典,在数据库创建时Oracle在数据文件中也创建附加的对象结构：数据字典表：描述数据库和对象，包括基表和视图动态性能表：记录当前数据库活动，以V$xxxx开通的视图,控制文件,控制文件是一个二进制文件，它定义了物理数据库的当前状态。启动在MOUNT以上模式时打开支持多路读写保持数据库的完整性包含的内容：数据库名和标识数据库创建的时间戳表空间名数据文件重做日志文件的名字和路径当前重做日志文件的序号Checkpoint信息Undo段的开始和结束重做日志的归档信息备份信息,Controlfiles,Database,重做日志文件,重做日志文件记录所有数据库变化，并在系统异常或数据库异常时提供数据恢复机制。以组和成员方式组织重做日志文件一个数据库至少两个日志组每一个重做日志作为日志组成员被调用以循环方式写日志文件,表空间操作,创建表空间：CREATETABLESPACEuserdataDATAFILE/u01/oradata/userdata01.dbfSIZE100MAUTOEXTENDONNEXT5MMAXSIZE200M;,表空间在线、离线操作：ALTERTABLESPACEuserdataOFFLINE/ONLINE;,删除表空间：DROPTABLESPACEuserdataINCLUDINGCONTENTSANDDATAFILES;,添加表空间数据文件：ALTERTABLESPACEapp_dataADDDATAFILE/u01/oradata/userdata03.dbfSIZE200M;,Table,CREATETABLEhr.employees(employee_idNUMBER(6),first_nameVARCHAR2(20),last_nameVARCHAR2(25)emailVARCHAR2(25)phone_numberVARCHAR2(20)hire_dateDATEDEFAULTSYSDATEjob_idVARCHAR2(10)salaryNUMBER(8,2)commission_pctNUMBER(2,2)manager_idNUMBER(6)department_idNUMBER(4);,把不同的表存储在不同的表空间上；用本地管理表空间避免出现碎片；用小一些的标准大小的extent减少表空间碎片；,DROPTABLEhr.departmentCASCADECONSTRAINTS;,Index,建立在Table的一列或多列上的辅助对象，有助于快速访问表中数据；索引也有开销，开销可能超作对表的全表扫描；Oracle以B-Tree存储索引信息，即使是Bitmap索引也一样；逻辑上分为：单列索引和组合索引、唯一索引和非唯一索引、基于函数的索引；物理上分为：分区索引和非分区索引、B-Tree索引和Bitmap索引；,Index创建原则,需要平衡query合DML的需要，常用于(子)查询的表应建立索引；把索引建到不同的表空间中；用统一的extent大小:五个block的倍数或者tablespace指定的MINIMUMEXTENT的倍数；创建索引考虑用NOLOGGING参数，重建索引的时候也一样；创建索引时INITRANS值应该比相应的table的值高一些；对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效；对于组合索引，根据列的唯一值概率，安排索引顺序；如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列；如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引；对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引；避免在有大量并发DML运算的表中使用Bitmap索引；,Index操作,CREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEindx;,CREATEBITMAPINDEXorders_region_id_idxONorders(region_id)PCTFREE30STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEindx;,ALTERINDEXorders_region_id_idxREBUILDTABLESPACEindx02;,ANALYZEINDEXorders_region_id_idxVALIDATESTRUCTURE;,数据类型,CHAR(N),NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)DATETIMESTAMPRAW(N)BLOB,CLOB,NCLOB,BFILELONG,LONGRAWROWID,UROWID,VARRA