Oracle基础培训

1、Shenzhen Share Info Co.,Ltd.nok.w 2006 Shenzhen Share Info Co.,Ltd. 对Oracle体系结构和主要模块有一个初步的了解了解Oracle内存结构，清楚相关配置项了解Oracle进程结构了解Oracle存储结构了解Oracle启动、关闭、跟踪排错等知识了解表、索引，清楚建立索引的一些原则编写高效SQL的一些原则nok.w 2006 Shenzhen Share Info Co.,Ltd. InstanceSGARedo logbuffer cacheShared poolData Dict.cacheLibrarycacheDBWR

2、SMONPMONCKPTLGWROthersUserprocessServerprocessPGAControl filesData files Archived log filesParameterfilePasswordfileRedo log filesDatabaseDatabasebuffer cachenok.w 2006 Shenzhen Share Info Co.,Ltd. Oracle server:是一个数据库管理系统，它提供开放的、全面的、集成的信息管理包括Oracle Oracle Servernok.w 2006 Shenzhen Share Info Co.,Lt

3、d. Oracle instance:意味着可以访问Oracle database总是打开一个Oracle database，也只能打开一个由内存结构和进程结构组成Background structuresMemory structuresSGARedo logbuffer cacheDatabase buffer cacheShared poolDBWRSMONPMONCKPTLGWROthersData DictionarycacheLibrarycacheInstancenok.w 2006 Shenzhen Share Info Co.,Ltd. 客户端连接到一个Oracle inst

4、ance包括：建立一个用户连接和创建一个对话（Session）Connection establishedSession createdDatabase userUserprocessOracle serverServerprocessnok.w 2006 Shenzhen Share Info Co.,Ltd. Oracle database:是一些经过处理的数据集合一个Database允许多个Instance打开，OPS包括三种文件类型Control filesData filesRedo log filesControl filesData files Archived log file

5、sParameterfilePasswordfileRedo log filesOracle DatabaseControlfilesData files(includes data dictionary) HeaderOnlineredo logfiles)nok.w 2006 Shenzhen Share Info Co.,Ltd. Oracle的内存结构主要包括两种内存区域：System Global Area (SGA): 系统全局区 实例启动时分配，是Oracle实例的基础部件 是动态可配的，最大值可以由SGA_MAX_SIZE限制Program Global Area (PGA):

6、 程序全局区 Oracle服务进程启动时分配 是为每一个连接到Oracle database的用户进程保留的内存区nok.w 2006 Shenzhen Share Info Co.,Ltd. System Global Area (SGA包括下面几种内存结构: Shared pool：缓存最近使用的SQL句子和数据字典 Database buffer cache：缓存最近使用数据 Redo log buffer ：缓存被改变的数据记录，主要用于恢复 其他存储结构 (如 lock和latch管理,统计数据)两种可选内存区: Large pool：用于Shared Server的环境，缓解Sha

7、red Pool压力 Java pool：用于使用Java解析的环境SGARedo logbuffer cacheDatabase buffer cacheShared poolData DictionarycacheLibrarycachenok.w 2006 Shenzhen Share Info Co.,Ltd. Shared Poolshared pool用于存储最近执行的SQL句子和最近使用的数据字典信息包括两个关键的跟性能相关的内存结构: Library cache： V$LIBRARYCACHE保存最近使用的SQL和PL/SQL句子least recently used(LRU)

8、最近少用淘汰算法 Data dictionary cache：V$ROWCHACHE 保存最近使用的数据定义包括数据文件、表、索引、列、用户、权限等信息由参数控制大小：SHARED_POOL_SIZE.ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;Shared poolData dictionarycacheLibrarycachenok.w 2006 Shenzhen Share Info Co.,Ltd. Database Buffer Cachedatabase buffer cache保存从数据文件中获取的数据库块的副本。对数据查询、数据更新产生很大的

9、性能影响。由 least recently used (LRU) 最近少用淘汰算法管理DB_BLOCK_SIZE：每一物理块的大小，在实例安装时指定DB_CACHE_SIZE：高速缓存池，至少16M， V$SYSSTAT DB_KEEP_CACHE_SIZE：KEEP缓存池，可缓存量少、频繁使用的对象DB_RECYCLE_CACHE_SIZE：RECYCLE缓存池，被使用后对象长时间不用DB_CACHE_ADVICE：高速缓存统计数据收集开关Database buffercacheALTER SYSTEM SET DB_CACHE_SIZE = 96M;nok.w 2006 Shenzhen

10、Share Info Co.,Ltd. Oracle进程是一些程序，这些进程执行一序列步骤，或执行一些特定任务，不同的操作系统Oracle对其进程有不同处理方式，Unix/Linux时是一个普通的进程，Windows时是一个线程。几种进程类型：User process: 用户请求连接到Oracle server 的时候启动。Server process: 连接到Oracle Instance，当用户创建一个Session是启动Background process: 处理特定任务的进程，随着Oracle Instance启动而启动。Connection establishedSession cr

11、eatedDatabase userUserprocessOracle serverServerprocessnok.w 2006 Shenzhen Share Info Co.,Ltd. DBWRSMONPMONCKPTLGWROthersInstance必须的background processesDBWn：数据库写进程，把数据库高速缓存中的数据写入到数据文件上。PMON：进程监控进程，当其他进程出现问题时做相关清除工作。CKPT：Checkpoint进程，向DBWn发送CKPT指令，更新数据文件，更新控制文件。LGWR：日志写进程，把重做日志缓存数据写入到日志文件上。SMON：系统监控进

12、程，职责：实例恢复、结合零散自由空间、释放临时段RECO：恢复进程，用于分布式数据库中的失败处理可选的background processesARCn：日志归档进程Snnn：用来管理需要专用服务器的数据库的连接LCKn：当ORACLE以并行服务器方式进行时，解决内部实例的锁定问题Dnnn：在MTS模式下，用来减少多重连接所需的信息源SNPn：快照进程，用来管理快照的刷新。nok.w 2006 Shenzhen Share Info Co.,Ltd. Oracle体系结构的逻辑结构说明了Oracle的物理空间是如何使用的。 分为：tablespaces, segments, extents, 和

13、blocks等层次。TablespaceData fileSegmentBlocksExtentSegmentnok.w 2006 Shenzhen Share Info Co.,Ltd. DatabaseLogicalPhysicalTablespaceData fileOS blockOracle blockSegmentExtentnok.w 2006 Shenzhen Share Info Co.,Ltd. Oracle InstanceSGARedo logbufferData buffercacheShared poolData dict.cacheLibrarycacheDBW0

14、SMONPMONCKPTLGWROthersspfiledb01.ora初始化文件描述数据库实例的细节；PFILE（ initSID.ora ）和SPFILE（ spfileSID.ora ）；PFILE是一个文本文件，可以手工修改；SPFILE是一个二进制文件，可用命令 ALTER SYSTEM修改其中的参数值；在实例启动之前，可以用命令CREATE SPFILE由PFILE文件(initSID.ora)创建SPFILE文件；SQL CONNECT / AS SYSDBASQL STARTUP db_domain=db_name=smsdbjava_pool_size=33554432lar

15、ge_pool_size=8388608shared_pool_size=50331648processes=150job_queue_processes=10db_block_size=8192db_cache_size=25165824nok.w 2006 Shenzhen Share Info Co.,Ltd. OPENMOUNTNOMOUNTSHUTDOWN打开所有文件打开所有文件打开控制文件打开控制文件打开实例打开实例STARTUPSHUTDOWNSTARTUP modeSTARTUP PFILE=$ORACLE_HOME/dbs/initdb01.oraSTARTUP RESTRI

16、CTnok.w 2006 Shenzhen Share Info Co.,Ltd. Shutdown Mode: NORMAL（默认） TRANSACTIONAL IMMEDIATE ABORTAxxxxTxxooIxxxoShutdown Mode允许新连接允许新连接等待当前等待当前Sessions结束结束等待当前事务结束等待当前事务结束强迫强迫Checkpoint并且关闭文件并且关闭文件NxoooYESNOxoSHUTDOWN modenok.w 2006 Shenzhen Share Info Co.,Ltd. 后台跟踪文件 当后台进程发生错误时生成 所在目录：BACKGROUND_DU

17、MP_DEST(bdump)用户跟踪文件 包含一些跟踪SQL句子的统计数据和用户错误信息； 发生Session错误时生成 所在目录： USER_DUMP_DEST(udump) 命令使能用户跟踪文件： Session级别用 ALTER SESSION命令: ALTER SESSION SET SQL_TRACE = TRUE Session级别也可以用内置DBMS存储过程:dbms_system.SET_SQL_TRACE_IN_SESSION 实例级别可以设定初始化参数:SQL_TRACE = TRUEnok.w 2006 Shenzhen Share Info Co.,Ltd. 在数据库创

18、建时Oracle在数据文件中也创建附加的对象结构：数据字典表：描述数据库和对象，包括基表和视图动态性能表：记录当前数据库活动，以V$xxxx开通的视图DBA_xxxUSER_xxxALL_xxx当前用户的所有对象当前用户的所有对象当前用户可访问的所有对象当前用户可访问的所有对象数据库中的所有对象数据库中的所有对象nok.w 2006 Shenzhen Share Info Co.,Ltd. 控制文件是一个二进制文件，它定义了物理数据库的当前状态。 启动在 MOUNT 以上模式时打开支持多路读写保持数据库的完整性包含的内容：数据库名和标识数据库创建的时间戳表空间名数据文件重做日志文件的名字和路径

19、当前重做日志文件的序号Checkpoint信息Undo段的开始和结束重做日志的归档信息备份信息Control filesDatabasenok.w 2006 Shenzhen Share Info Co.,Ltd. 重做日志文件记录所有数据库变化，并在系统异常或数据库异常时提供数据恢复机制。以组和成员方式组织重做日志文件一个数据库至少两个日志组每一个重做日志作为日志组成员被调用以循环方式写日志文件Redo log filesDatabaseGroup 2Group 3Group 1MemberDisk 1Disk 2MemberMemberMemberMemberMembernok.w 200

20、6 Shenzhen Share Info Co.,Ltd. 创建表空间：创建表空间：CREATE TABLESPACE userdata DATAFILE /u01/oradata/userdata01.dbf SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;表空间在线、离线操作：表空间在线、离线操作：ALTER TABLESPACE userdata OFFLINE/ONLINE;删除表空间：删除表空间：DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;添加表空间数据文件：添加表空间

21、数据文件：ALTER TABLESPACE app_data ADD DATAFILE /u01/oradata/userdata03.dbf SIZE 200M;nok.w 2006 Shenzhen Share Info Co.,Ltd. CREATE TABLE hr.employees(employee_id NUMBER(6), first_nameVARCHAR2(20),last_name VARCHAR2(25)email VARCHAR2(25)phone_number VARCHAR2(20)hire_date DATE DEFAULT SYSDATEjob_id VARC

22、HAR2(10)salary NUMBER(8,2)commission_pct NUMBER (2,2)manager_id NUMBER(6)department_id NUMBER(4);把不同的表存储在不同的表空间上；用本地管理表空间避免出现碎片；用小一些的标准大小的extent减少表空间碎片；DROP TABLE hr.departmentCASCADE CONSTRAINTS;nok.w 2006 Shenzhen Share Info Co.,Ltd. 建立在Table的一列或多列上的辅助对象，有助于快速访问表中数据；索引也有开销，开销可能超作对表的全表扫描；Oracle以B-T

23、ree存储索引信息，即使是Bitmap索引也一样；逻辑上分为：单列索引和组合索引、唯一索引和非唯一索引、基于函数的索引；物理上分为：分区索引和非分区索引、B-Tree 索引和Bitmap索引；B-tree(默认默认)合适于高基数集的列合适于高基数集的列对索引键的更新开销相对不是对索引键的更新开销相对不是很大很大不适合用不适合用OR查询查询适合适合 OLTP（联机事务处理）联机事务处理）Bitmap合适于低基数集的列合适于低基数集的列对索引键的更新开销很大对索引键的更新开销很大适合用适合用OR查询查询适合数据仓库适合数据仓库RootBranchLeafnok.w 2006 Shenzhen Sh

24、are Info Co.,Ltd. 需要平衡query合DML的需要，常用于(子)查询的表应建立索引；把索引建到不同的表空间中；用统一的extent大小: 五个block的倍数或者tablespace指定的MINIMUM EXTENT的倍数；创建索引考虑用NOLOGGING参数，重建索引的时候也一样；创建索引时INITRANS值应该比相应的table的值高一些；对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效；对于组合索引，根据列的唯一值概率，安排索引顺序；如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列；如果where语句中不得

25、不对查询列采用函数查询，如upper函数，最好建立相应函数索引；对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引；避免在有大量并发DML运算的表中使用Bitmap索引；nok.w 2006 Shenzhen Share Info Co.,Ltd. CREATE INDEX hr.employees_last_name_idxON hr.employees(last_name)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLES

26、PACE indx;CREATE BITMAP INDEX orders_region_id_idxON orders(region_id)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;ALTER INDEX orders_region_id_idx REBUILDTABLESPACE indx02;ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;nok.w 2006 Shenzhen Share Info Co.,Lt