Oracle基础培训

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,nok.w 2006 Shenzhen Share Info Co.,Ltd,.,深圳市协尔信息系统有限公司,Shenzhen Share Info Co.,Ltd,.,Oracle,基础培训,目的,对,Oracle,体系结构和主要,模,模块有一个初步,的,的了解,了解,Oracle,内存结构，清楚,相,相关配置项,了解,Oracle,进程结构,了解,Oracle,存储结构,了解,Oracle,启动、关闭、跟,踪,踪排错等知识,了解表、索引，,清,清楚建立索引的,一,一些原则,编写高效,SQL,的一些原则,Oracle,体系结构,Instance,SGA,Redo logbuffer cache,Sharedpool,Data Dict.cache,Library,cache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Controlfiles,Data files,Archived,log files,Parameter,file,Password,file,Redo log files,Database,Databasebuffer cache,OracleServer,Oracleserver:,是一个数据库管,理,理系统，它提供,开,开放的、全面的,、,、集成的信息管,理,理,包括,OracleInstance,和,OracleDatabase,OracleServer,OracleInstance,Oracleinstance:,意味着可以访问,Oracledatabase,总是打开一个,Oracledatabase，,也只能打开一个,由内存结构和进,程,程结构组成,Background structures,Memorystructures,SGA,Redo logbuffer cache,Database buffer cache,Sharedpool,DBWR,SMON,PMON,CKPT,LGWR,Others,Data Dictionarycache,Library,cache,Instance,连接和,Session,客户端连接到一,个,个,Oracleinstance,包括：,建立一个用户连,接,接和创建一个对,话,话（,Session,）,）,Connection,established,Sessioncreated,Database user,Userprocess,Oracleserver,Serverprocess,OracleDatabase,Oracledatabase:,是一些经过处理,的,的数据集合,一个,Database,允许多个,Instance,打开，,OPS,包括三种文件,类,类型,Control files,Data files,Redo log files,Control files,Data files,Archived,log files,Parameter,file,Password,file,Redo log files,OracleDatabase,Control,files,Data files,(includes,data,dictionary),Header,Online,redo log,files),内存结构,Oracle,的内存结构主,要,要包括两种内,存,存区域：,SystemGlobal Area(SGA):,系统全局区,实例启动时分,配,配，是,Oracle,实例的基础部,件,件,是动态可配的,，,，最大值可以,由,由,SGA_MAX_SIZE,限制,Program Global Area (PGA):,程序全局区,Oracle,服务进程启动,时,时分配,是为每一个连,接,接到,Oracledatabase,的用户进程保,留,留的内存区,SystemGlobal Area(,SGA),SGA,包括下面几种,内,内存结构:,Sharedpool：,缓存最近使用,的,的,SQL,句子和数据字,典,典,Database buffer cache：,缓存最近使用,数,数据,Redo log buffer ：,缓存被改变的,数,数据记录，主,要,要用于恢复,其他存储结构(如,lock,和,latch,管理,统计数据),两种可选内存,区,区,:,Largepool：,用于,SharedServer,的环境，缓解,SharedPool,压力,Java pool：,用于使用,Java,解析的环境,SGA,Redo logbuffer cache,Database buffer cache,Sharedpool,Data Dictionarycache,Library,cache,SharedPool,sharedpool,用于存储最近,执,执行的,SQL,句子和最近使,用,用的数据字典,信,信息,包括两个关键,的,的跟性能相关,的,的内存结构,:,Library cache：,V$LIBRARYCACHE,保存最近使用,的,的,SQL,和,PL/SQL,句子,leastrecently used(LRU),最近少用淘汰,算,算法,Data dictionary cache：,V$ROWCHACHE,保存最近使用,的,的数据定义,包括数据文件,、,、表、索引、,列,列、用户、权,限,限等信息,由参数控制大,小,小：,SHARED_POOL_SIZE.,ALTERSYSTEMSET,SHARED_POOL_SIZE =64M;,Shared pool,Data dictionary,cache,Library,cache,Database Buffer Cache,database buffer cache,保存从数据文,件,件中获取的数,据,据库块的副本,。,。,对数据查询、,数,数据更新产生,很,很大的性能影,响,响。,由,leastrecently used (LRU),最近少用淘汰,算,算法管理,DB_BLOCK_SIZE：,每一物理块的,大,大小，在实例,安,安装时指定,DB_CACHE_SIZE：,高速缓存池，,至,至少16,M，V$SYSSTAT,DB_KEEP_CACHE_SIZE,：,：KEEP,缓存池，可缓,存,存量少、频繁,使,使用的对象,DB_RECYCLE_CACHE_SIZE：RECYCLE,缓存池，被使,用,用后对象长时,间,间不用,DB_CACHE_ADVICE：,高速缓存统计,数,数据收集开关,Database buffercache,ALTERSYSTEMSET DB_CACHE_SIZE= 96M;,进程结构,Oracle,进程是一些程,序,序，这些进程,执,执行一序列步,骤,骤，或执行一,些,些特定任务，,不,不同的操作系,统,统,Oracle,对其进程有不,同,同处理方式，,Unix/Linux,时是一个普通,的,的进程，,Windows,时是一个线程,。,。,几种进程类型,：,：,User process:,用户请求连接,到,到,Oracleserver,的时候启动,。,Serverprocess:,连接到,OracleInstance，,当用户创建一,个,个,Session,是启动,Background process:,处理特定任务,的,的进程，随着,OracleInstance,启动而启动。,Connection established,Session created,Database user,Userprocess,Oracle server,Serverprocess,DBWR,SMON,PMON,CKPT,LGWR,Others,Instance,Background Process,必须的,background processes,DBWn：,数据库写进程,，,，把数据库高,速,速缓存中的数,据,据写入到数据,文,文件上。,PMON：,进程监控进程,，,，当其他进程,出,出现问题时做,相,相关清除工作,。,。,CKPT：Checkpoint,进程，向,DBWn,发送,CKPT,指令，更新数,据,据文件，更新,控,控制文件。,LGWR：,日志写进程，,把,把重做日志缓,存,存数据写入到,日,日志文件上。,SMON：,系统监控进程,，,，职责：实例,恢,恢复、结合零,散,散自由空间、,释,释放临时段,RECO：,恢复进程，用,于,于分布式数据,库,库中的失败处,理,理,可选的,background processes,ARCn：,日志归档进程,Snnn：,用来管理需要,专,专用服务器的,数,数据库的连接,LCKn：,当,ORACLE,以并行服务器,方,方式进行时，,解,解决内部实例,的,的锁定问题,Dnnn：,在,MTS,模式下，用来,减,减少多重连接,所,所需的信息源,SNPn：,快照进程，用,来,来管理快照的,刷,刷新。,逻辑结构,Oracle,体系结构的逻,辑,辑结构说明了,Oracle,的物理空间是,如,如何使用的。,分为：,tablespaces,segments, extents,和,blocks,等层次。,Tablespace,Data file,Segment,Blocks,Extent,Segment,数据库存储层,次,次,Database,Logical,Physical,Tablespace,Data file,OS block,Oracleblock,Segment,Extent,初始化参数文,件,件,Oracle Instance,SGA,Redo logbuffer,Data buffercache,Shared pool,Data dict.cache,Library,cache,DBW0,SMON,PMON,CKPT,LGWR,Others,spfiledb01.ora,初始化文件描,述,述数据库实例,的,的细节；,PFILE（,initSID.ora,）,和,SPFILE,（,（ spfileSID.ora ）；,PFILE,是一个文本文,件,件，可以手工,修,修改；,SPFILE,是一个二进制,文,文件，可用,命令,ALTERSYSTEM,修改其中的参,数,数值；,在实例启动之,前,前，可以用命,令,令,CREATESPFILE,由,PFILE,文件,(,initSID.ora),创建,SPFILE,文件；,SQL CONNECT/ ASSYSDBA,SQL STARTUP,db_domain=,db_name=smsdb,java_pool_size=33554432,large_pool_size=8388608,shared_pool_size=50331648,processes=150,job_queue_processes=10,db_block_size=8192,db_cache_size=25165824,启动,OPEN,MOUNT,NOMOUNT,SHUTDOWN,打开所有文件,打开控制文件,打开实例,STARTUP,SHUTDOWN,STARTUP,mode,STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora,STARTUP RESTRICT,关闭,Shutdown Mode:,NORMAL,（,（,默认）,TRANSACTIONAL,IMMEDIATE,ABORT,A,x,x,x,x,T,x,x,o,o,I,x,x,x,o,Shutdown Mode,允许新连接,等待当前,Sessions,结束,等待当前事务结束,强迫,Checkpoint,并且关闭文件,N,x,o,o,o,YES,NO,x,o,SHUTDOWN,mode,跟踪,后台,跟,跟踪,文,文件,当后,台,台进,程,程发,生,生错,误,误时,生,生成,所在,目,目录,：,：,BACKGROUND_DUMP_DEST(bdump),用户,跟,跟踪,文,文件,包含,一,一些,跟,跟踪,SQL,句子,的,的统,计,计数,据,据和,用,用户,错,错误,信,信息,；,；,发生,Session,错误,时,时生,成,成,所在,目,目录,：,：,USER_DUMP_DEST(udump),命令,使,使能,用,用户,跟,跟踪,文,文件,：,：,Session,级别,用,用,ALTERSESSION,命令:,ALTERSESSIONSETSQL_TRACE=TRUE,Session,级别,也,也可,以,以用,内,内置,DBMS,存储,过,过程,:,dbms_system.SET_SQL_TRACE_IN_SESSION,实例,级,级别,可,可以,设,设定,初,初始,化,化参,数,数,:,SQL_TRACE=TRUE,数据,字,字典,在数,据,据库,创,创建,时,时,Oracle,在数,据,据文,件,件中,也,也创,建,建附,加,加的,对,对象,结,结构,：,：,数据,字,字典,表,表：,描,描述,数,数据,库,库和,对,对象,，,，包,括,括基,表,表和,视,视图,动态,性,性能,表,表：,记,记录,当,当前,数,数据,库,库活,动,动，,以,以,V$xxxx,开通,的,的视,图,图,DBA_,xxx,USER_,xxx,ALL_,xxx,当前用户的所有对象,当前用户可访问的所有对象,数据库中的所有对象,控制,文,文件,控制,文,文件,是,是一,个,个二,进,进制,文,文件,，,，它,定,定义,了,了物,理,理数,据,据库,的,的当,前,前状,态,态,。,启动,在,在,MOUNT,以上,模,模式,时,时打,开,开,支持,多,多路,读,读写,保持,数,数据,库,库的,完,完整,性,性,包含,的,的内,容,容：,数据,库,库名,和,和标,识,识,数据,库,库创,建,建的,时,时间,戳,戳,表空,间,间名,数据,文,文件,重,重做,日,日志,文,文件,的,的名,字,字和,路,路径,当前,重,重做,日,日志,文,文件,的,的序,号,号,Checkpoint,信息,Undo,段的,开,开始,和,和结,束,束,重做,日,日志,的,的归,档,档信,息,息,备份,信,信息,Controlfiles,Database,重做,日,日志,文,文件,重做,日,日志,文,文件,记,记录,所,所有,数,数据,库,库变,化,化，,并,并在,系,系统,异,异常,或,或数,据,据库,异,异常,时,时提,供,供数,据,据恢,复,复机,制,制。,以组,和,和成,员,员方,式,式组,织,织重,做,做日,志,志文,件,件,一个,数,数据,库,库至,少,少两,个,个日,志,志组,每一,个,个重,做,做日,志,志作,为,为日,志,志组,成,成员,被,被调,用,用,以循,环,环方,式,式写,日,日志,文,文件,Redo log files,Database,Group 2,Group 3,Group 1,Member,Disk 1,Disk 2,Member,Member,Member,Member,Member,表空,间,间操,作,作,创建,表,表空,间,间：,CREATETABLESPACEuserdata,DATAFILE/u01/oradata/userdata01.dbfSIZE100M,AUTOEXTENDONNEXT5MMAXSIZE200M;,表空,间,间在,线,线、,离,离线,操,操作,：,：,ALTERTABLESPACEuserdataOFFLINE/ONLINE;,删除表空,间,间：,DROPTABLESPACEuserdata,INCLUDING CONTENTS AND DATAFILES;,添加表空,间,间数据文,件,件：,ALTER TABLESPACEapp_data,ADDDATAFILE/u01/oradata/userdata03.dbf,SIZE200M;,Table,CREATE TABLEhr.employees(,employee_idNUMBER(6),first_nameVARCHAR2(20),last_name VARCHAR2(25),email VARCHAR2(25),phone_numberVARCHAR2(20),hire_date DATE DEFAULT SYSDATE,job_idVARCHAR2(10),salaryNUMBER(8,2),commission_pctNUMBER (2,2),manager_idNUMBER(6),department_id NUMBER(4),);,把不同的,表,表存储在,不,不同的表,空,空间上；,用本地管,理,理表空间,避,避免出现,碎,碎片；,用小一些,的,的标准大,小,小的,extent,减少表空,间,间碎片；,DROPTABLE hr.department,CASCADECONSTRAINTS;,Index,建立在,Table,的一列或,多,多列上的,辅,辅助对象,，,，有助于,快,快速访问,表,表中数据,；,；,索引也有,开,开销，开,销,销可能超,作,作对表的,全,全表扫描,；,；,Oracle,以,B-Tree,存储索引,信,信息，即,使,使是,Bitmap,索引也一,样,样；,逻辑上分,为,为：单列,索,索引和组,合,合索引、,唯,唯一索引,和,和非唯一,索,索引、基,于,于函数的,索,索引；,物理上分,为,为：分区,索,索引和非,分,分区索引,、,、,B-Tree,索引和,Bitmap,索引；,B-tree(,默认),合适于高基数集的列,对索引键的更新开销相对不是很大,不适合用,OR,查询,适合,OLTP（,联机事务处理）,Bitmap,合适于低基数集的列,对索引键的更新开销很大,适合用,OR,查询,适合数据仓库,Root,Branch,Leaf,Index,创建原则,需要平衡,query,合,DML,的需要，,常,常用于(,子,子)查询,的,的表应建,立,立索引；,把索引建,到,到不同的,表,表空间中,；,；,用统一的,extent,大小:,五,五个,block,的倍数或,者,者,tablespace,指定的,MINIMUMEXTENT,的倍数；,创建索引,考,考虑用,NOLOGGING,参数，重,建,建索引的,时,时候也一,样,样；,创建索引,时,时,INITRANS,值应该比,相,相应的,table,的值高一,些,些；,对常用,SQL,语句的,where,条件中的,列,列建立唯,一,一索引或,组,组合索引,，,，组合条,件,件查询中,相,相应的组,合,合索引更,有,有效；,对于组合,索,索引，根,据,据列的唯,一,一值概率,，,，安排索,引,引顺序；,如果一个,列,列具有很,低,低的数据,基,基数，并,且,且或者可,具,具有空值,，,，不应作,为,为索引列,；,；,如果,where,语句中不,得,得不对查,询,询列采用,函,函数查询,，,，如,upper,函数，最,好,好建立相,应,应函数索,引,引；,对于低基,数,数集的列,，,，并包含,OR,等逻辑运,算,算，考虑,用,用,Bitmap,索引，对,于,于从大量,行,行的表中,返,返回大量,的,的行时也,可,可以考虑,Bitmap,索引；,避免在有,大,大量并发,DML,运算的表,中,中使用,Bitmap,索引；,Index,操作,CREATEINDEXhr.employees_last_name_idx,ONhr.employees(last_name),PCTFREE 30,STORAGE(INITIAL200KNEXT 200K,PCTINCREASE0MAXEXTENTS 50),TABLESPACE indx;,CREATEBITMAP INDEX orders_region_id_idx,ONorders(region_id),PCTFREE 30,STORAGE(INITIAL200KNEXT 200K,PCTINCREASE0MAXEXTENTS 50),TABLESPACE indx;,ALTERINDEXorders_region_id_idxREBUILDTABLESPACEindx02;,ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;,数据类,型,型,CHAR(N),NCHAR(N)VARCHAR2(N),NVARCHAR2(N),NUMBER(P,S),DATETIMESTAMP,RAW(N)BLOB,CLOB,NCLOB,BFILE,LONG,LONG RAW,ROWID,UROWID,VARRAYRECORDTABLE,REF,数据类,型,型,内建类,型,型,用户定,义,义类型,标量,关联,集合,高效,PL/SQL,原则,良好的,SQL,语句风,格,格易于,发,发现问,题,题、易,于,于阅读,，,，移植,性,性好,80,的,的性能,问,问题是,由,由不良,SQL,语句引,发,发的,尽可能,对,对查询,条,条件的,列,列建立,索,索引；,尽量不,要,要在,where,条件中,对,对查询,列,列使用,函,函数，,除,除非建,立,立了相,应,应的函,数,数索引,，,，如可,用,用带前,导,导字符,的,的,like,代替,substr,；,；,任何在,where,子句中,使,使用,isnull,或,isnotnull,的语句,优,优化器,是,是不允,许,许使用,索,索引的,；,；,对于有,联,联接的,列,列，即,使,使最后,的,的联接,值,值为一,个,个静态,值,值，优,化,化器是,不,不会使,用,用索引,的,的；,通配符,（,（%）,在,在搜寻,词,词首出,现,现，所,以,以,Oracle,系统不,使,使用索,引,引；,任何在,Orderby,语句的,非,非索引,项,项或者,有,有计算,表,表达式,都,都将降,低,低查询,速,速度；,如果要,使,使用,NOT,，,，,则应在,取,取反的,短,短语前,面,面加上,括,括号，,并,并在短,语,语前面,加,加上,NOT,运算符,，,，用,NOT,就不能,用,用索引,，,，尽量,用,用“”,代替“”,；,；,IN,和,EXISTS,，,，,绝大多,数,数情况,下,下,EXISTS,比,IN,性能高,，,，加上,NOT,也一样,；,；,Where,子句中,要,要将值,和,和列的,类,类型匹,配,配，如,数,数字和,字,字符间,，,，否则,将,将导致,全,全表扫,描,描；,不要在,应,应用程,序,序中使,用,用关联,子,子查询,，,，可使,用,用联机,视,视图来,代,代替；,对于带,有,有很多,OR,的非常,复,复杂查,询,询，可,以,以考虑,用,用,unionall,来重写,；,；,可批量,完,完成的,操,操作不,应,应该由,循,循环方,式,式来进,行,行；,用格式,一,一致的,SQL,语句，,包括字,母,母的大,小,小写、,标,标点符,号,号、换,行,行的位,置,置等都,要,要一致,，以减,少,少,SQL,分析编,译,译开销,；,；,游标、,动,动态,SQL,语句尽,量,量用绑,定,定变量,方,方式调,用,用，以,减,减少,SQL,分析编,译,译开销,；,；,在可能,产,产生异,常,常的地,方,方用异,常,常处理,；,；,把一些,常,常用功,能,能模块,用,用函数,或,或存储,过,过程封,装,装；,自由讨,论,论!,谢谢!,