oracle开发分享Word文档格式.docx
《oracle开发分享Word文档格式.docx》由会员分享，可在线阅读，更多相关《oracle开发分享Word文档格式.docx（48页珍藏版）》请在冰点文库上搜索。
2.7.分布式查询24
2.8.用exist代替distinct25
2.9.避免在索引列上使用计算26
2.10.用>
=替代>
26
2.11.用TRUNCATE替代DELETE删除全表26
2.12.大事务多使用COMMIT26
2.13.避免在索引列上使用ISNULL和ISNOTNULL26
2.14.用Where子句替换HAVING子句：
27
2.15.使用WITHAS语句减少访问基表的次数27
2.16.hint的使用28
2.17.基于规则优化器的默认选择32
3.SQL调试32
3.1.通过V$SQLAREA查找有问题的查询33
3.2.实时查询最耗CPU资源的SQL语句33
3.3.怎样看懂Oracle的执行计划33
3.4.Oracle优化器介绍33
4.SQL性能监控34
4.1.常用视图介绍34
4.1.1.v$sqlarea34
4.1.2.v$sql36
4.1.3.v$sqltext36
4.1.4.v$session36
4.1.5.v$Lock41
4.1.6.v$locked_object41
4.2.锁查询42
4.2.1.Oracle的TM锁类型42
4.2.2.锁查询42
4.2.3.解锁43
4.3.查看表空间大小43
4.4.查看表大小43
orderby3desc43
5oracleSQL开发工具介绍43
PL/SQLdeveloper43
1Oracle概念
1.1.Oracle服务器概述
数据库是一个数据的集合，不仅是指物理上的数据，也指物理、存储及进程对象的一个集合。
下面主要从应用结构，进程结构，内存结构，逻辑结构等几个方面对其进行描述：
1.1.1.应用体系结构
在一个Oracle数据库系统中，数据库应用程序与数据库被分为两部分：
前端（front-end）或称为客户端（client）；
后台（back-end）或称为服务端（server）——这就是客户端/服务器体系结构（client/serverarchitecture）名字的由来。
客户端运行数据库应用程序，此程序能够从数据库获取数据，并通过键盘，鼠标，显示器之类的设备和用户交互。
服务端运行Oracle数据库程序，这些程序能实现多用户并发、共享地访问数据库。
目前的应用结构有：
客户端/服务器体系结构和多层体系结构。
图1-1客户端/服务器体系结构及分布式处理
图1-2多层体系结构示例
1.1.2.进程体系结构
1.1.2.1.进程的种类
●执行应用程序或Oracle工具代码的用户进程（userprocess）
●执行Oracle数据库服务器代码的Oracle进程（Oracleprocess）。
其中包括服务进程（serverprocess）和后台进程（backgroundprocess）
图1-3显示了一个运行在专用服务器模式下的Oracle实例。
每个连接到系统的用户拥有一个独立的用户进程，同时系统中还存在多个运行Oracle模块的后台进程
图1-3中间为SGA。
其上为用户进程，及表示各进程与SGA交换数据的双向箭头。
其下为Oracle进程（后台进程），例如RECO，PMON，SMON，DBWn，LGWR和ARC0。
在各Oracle进程与SGA间也有表示数据交换的双向箭头。
1.1.2.2.连接与会话
连接（connection）与会话（session）这两个概念均与用户进程（userprocess）紧密相关，但二者又具有不同的含义。
连接是用户进程和Oracle实例间的通信通道（communicationpathway）。
这个通信通道是通过进程间的通信机制（interprocesscommunicationmechanisms）或网络软件（networksoftware）建立的。
会话是用户通过用户进程与Oracle实例建立的连接[此处连接与上文中的连接含义不同，主要指用户和数据库间的联系]。
例如，当用户启动SQL*Plus时必须提供有效的用户名和密码，之后Oracle为此用户建立一个会话。
从用户开始连接到用户断开连接（或退出数据库应用程序）期间，会话一直持续。
1.1.2.3.服务进程
∙Oracle需要创建服务进程（serverprocess）来处理连接到实例的用户进程（userprocess）提交的请求。
当应用程序与Oracle服务器运行在同一计算机时，某些用户进程可以和其服务进程合并为一个进程，以便减小系统开销。
但是，当应用程序与Oracle服务器运行在不同计算机时，用户进程必须通过一个服务进程与Oracle通信。
∙为用户应用程序创建的服务进程（或用户/服务进程合并后的服务进程部分）可以完成以下工作：
∙解析、运行应用程序提交的SQL语句
∙如果数据在SGA中不存在，则将所需的数据块从磁盘上的数据文件读入SGA的数据缓存区（shareddatabasebuffer）
∙以应用程序能理解的形式返回SQL语句的执行结果
1.1.2.4.后台进程
一个Oracle实例中可以包含多种后台进程，这些进程不一定全部出现在实例中。
系统中运行的后台进程数量众多，用户可以通过V$BGPROCESS视图查询关于后台进程的信息。
Oracle实例中可能运行的后台进程有：
∙数据写入进程（DBWn）:
数据写入进程（databasewriterprocess，DBWn）的功能是将数据缓冲区的内容写入数据文件。
DBWn进程负责将数据缓存区（databasebuffercache）内修改过的缓冲区（即dirtybuffer）写入磁盘.
∙日志写入进程（LGWR）:
日志写入进程（logwriterprocess，LGWR）负责对重做日志进行管理——将重做日志缓冲区（redologbuffer）内的数据写入磁盘上的重做日志文件中。
LGWR进程将上次写入之后进入缓冲区的所有重做条目（redoentry）写入磁盘中。
∙检查点进程（CKPT）:
当一个检查点（checkpoint）事件发生时，Oracle需要更新所有数据文件的文件头来记录检查点事件的详细信息。
这个工作是由CKPT进程完成的。
但是将数据块写入数据文件的不是CKPT进程，而是DBWn进程。
∙系统监控进程（SMON）:
系统监控进程（systemmonitorprocess，SMON）将负责进行恢复（recovery）工作。
此外，SMON还负责清除系统中不再使用的临时段（temporarysegment），以及为数据字典管理的表空间（dictionarymanagedtablespace）合并相邻的可用数据扩展（extent）
∙进程监控进程（PMON）:
进程监控进程（processmonitor，PMON）将对其进行恢复。
PMON进程将清除相关的数据缓存区（databasebuffercache）并释放被此用户进程使用的资源。
例如，PMON进程将重置活动事务表（activetransactiontable），释放锁，并从活动进程列表（listofactiveprocess）中删除出错进程的ID。
∙恢复进程（RECO）:
恢复进程（recovererprocess，RECO）是在分布式数据库环境（distributeddatabaseconfiguration）中自动地解决分布式事务（distributedtransaction）错误的后台进程。
一个节点（node）上的RECO进程能够连接到出现了不可信的分布式事务（in-doubtdistributedtransaction）的数据库。
当RECO进程重新连接到与不可信的事务相关的数据库后，她将负责对此事务进行处理，并从相关数据库的活动事务表（pendingtransactiontable）中移除和此事务有关的数据
∙作业队列进程:
作业队列进程（jobqueueprocess）的功能是进行批处理（batchprocessing）。
这种进程用于运行用户的作业（job）.
∙归档进程（ARCn）:
归档进程（archiverprocess，ARCn）在发生日志切换（logswitch）时将重做日志文件复制到指定的存储设备中。
只有当数据库运行在ARCHIVELOG模式下，且自动归档功能被开启时，系统才会启动ARCn进程。
∙队列监控进程（QMNn）:
队列监控进程（queuemonitorprocess）是供Oracle工作流高级队列（OracleStreamsAdvancedQueuing）使用的可选的进程，用于监控消息队列
∙其他后台进程
图1-4中间为SGA。
上部为RECO，PMON，及SMON进程，其间的双向箭头表示各进程与实例间的通信。
下部左侧为DBW0和LGWR进程，这两个进程分别和数据缓存区与重做日志缓冲区进行通信，同时还分别访问数据文件和重做日志文件。
本图中还展示了一些其他进程，例如ARC0，需要访问脱机存储设备和控制文件；
以及CKPT，需要访问数据文件和控制文件。
1.1.3.内存体系结构
Oracle中的基本内存结构包括：
∙系统全局区（SystemGlobalArea，SGA），此区域由所有的服务进程（serverprocess）和后台进程（backgroundprocess）共享。
∙程序全局区（ProgramGlobalAreas，PGA），此区域是每个服务进程和后台进程所私有的；
即每个进程都有一个属于自己的PGA。
其中包括Java池，数据缓存区，重做日志缓冲区，共享池，数据流池，及大型池。
在SGA之外，存在服务进程，后台进程，及Oracle进程，她们能够和SGA交换信息。
同时每个进程还需与其PGA通信。
SGA中还包含了数据库及实例的状态信息，供后台进程使用。
这部分内容被称为固定SGA（fixedSGA）。
用户数据不会存储在这个区域内。
SGA中还包含需要在Oracle进程间进行交换的信息（例如，锁信息）。
1.1.3.1.数据缓存区（databasebuffercache）
用于存储从数据文件（datafile）读出的数据块（datablock）副本。
所有并发地连接到实例上的用户进程（userprocess）都将共享同一个数据缓存区。
1.1.3.2.重做日志缓冲区（redologbuffer）
是SGA内一块被循环使用的缓冲区，用于记录数据库内的数据变化信息。
这些信息以重做条目（redoentry）的形式进行存储。
Oracle利用重做条目内的信息就可以重做由INSERT，UPDATE，DELETE，CREATE，ALTER，及DROP等操作对数据库进行的修改。
重做条目可以被用于进行数据库恢复（databaserecovery）。
1.1.3.3.共享池（sharedpool）
SGA的共享池（sharedpool）内包含了库缓存（librarycache），数据字典缓存区（dictionarycache），并行执行消息缓冲区（buffersforparallelexecutionmessages），以及用于系统控制的各种内存结构。
库缓存（librarycache）中包含共享SQL区（sharedSQLarea），私有SQL区（privateSQLarea）（当系统运行在共享服务器模式下时），PL/SQL过程和包，以及用于系统控制的各种内存结构，例如锁（lock）及库缓存句柄（librarycachehandle）等。
Oracle为其所执行的每个SQL提供一个共享SQL区（sharedSQLarea）及一个私有SQL区（privateSQLarea）,共享SQL区（sharedSQLarea）中存储了此SQL的解析树（parsetree）及执行计划（executionplan）。
令多次运行的SQL语句使用同一个共享SQL区可以为Oracle节约大量的内存开销，这在大量用户运行相同应用的环境里尤为明显。
当一个新的SQL语句被解析后，Oracle会从共享池（sharedpool）中分配一块内存创建共享SQL区，以保存解析结果。
所分配内存的容量大小取决于语句的复杂程度。
如果共享池内没有可用的内存，Oracle将使用改进的LRU（modifiedLRU）算法清除共享池内已有的共享SQL区，直到其中有足够的空间容纳新语句的共享SQL区。
一个共享SQL区被Oracle清除出共享池后，相应的SQL语句再次执行时需要重新解析并分配新的共享SQL区。
数据字典缓存区（datadictionarycache）是一系列保存了数据库参考信息（例如数据库结构，数据库用户等）的表和视图。
Oracle需要频繁地使用经过解析的SQL语句访问数据字典。
数据字典信息对Oracle能否正常运行至关重要。
由于Oracle对数据字典的访问极为频繁，因此内存中有两个特殊区域用于存储数据字典信息。
一个区域是数据字典缓存区（datadictionarycache），因为数据在其中是以数据行的形式存储的（通常缓冲区内保存的是完整的数据块），所以此区域也被称为行缓存（rowcache）。
另一个区域为库缓存（librarycache）。
所有Oracle数据库进程在访问数据字典信息时都能够共享这两个缓存区.
1.1.3.4.大型池（largepool）
数据库管理员可以配置一个称为大型池（largepool）的可选内存区域，供一次性大量的内存分配使用，例如：
∙共享服务器（sharedserver）及OracleXA接口（当一个事务与多个数据库交互时使用的接口）使用的会话内存（sessionmemory）
∙I/O服务进程
∙Oracle备份与恢复操作
1.1.3.5.Java池（Javapool）
供各会话内运行的Java代码及JVM内的数据使用的。
Java池的内存使用方式与Oracle服务器的运行模式有关。
1.1.3.6.数据流池（Streamspool）
是从10g开始才增加的一个新的SGA结构。
流池会用于缓存流进程在数据库间移动/复制数据时使用的队列消息。
比如expdb，impdb等。
1.1.3.7.PGA中内容
PGA内存中存储的内容依据实例服务进程（serverprocess）的模式而有所不同。
但是通常来说，PGA中含有以下内容
●私有SQL区（privateSQLarea）中包含绑定信息（bindinformation）及运行时内存结构（runtimememorystructure）等数据。
每个提交了SQL语句的会话都有一个私有SQL区。
每个提交了相同SQL语句的用户都有自己的私有SQL区，但她们使用同一个共享SQL区（sharedSQLarea）。
即多个私有SQL区可以和同一个共享SQL区相联系
●Oracle预编译程序（precompilerprogram）及OCI程序的开发者可以显式地打开游标（cursor）（游标即私有SQL区的句柄（handle）），并在程序运行过程中依据游标名称使用相关资源。
当Oracle执行某些SQL语句时隐式提交的递归游标（recursivecursor）还需使用共享SQL区（sharedSQLarea）。
●会话内存（sessionmemory）用于存储会话的变量（登录信息）及其他与会话有关的信息。
对于共享服务器（sharedserver）而言，会话内存是共享的而非为某个会话所私有。
1.1.4.逻辑结构
数据库逻辑结构包含表空间、段、范围（extent）、数据块和模式对象。
1.1.4.1.表空间概述
数据库是由一个或多个被称为表空间（tablespace）的逻辑存储单位构成。
表空间内的逻辑存储单位为段（segment），段又可以继续划分为数据扩展（extent）。
而数据扩展是由一组连续的数据块（datablock）构成。
Oracle中的数据逻辑上存储于表空间（tablespace）中，而物理上则存储于属于表空间的数据文件（datafile）中。
1.1.4.2.数据块，数据扩展，段简介
Oracle数据库负责为所有数据分配逻辑存储空间。
数据库空间的分配单位有数据块（DataBlock），数据扩展（Extent），和段（Segment）
段，数据扩展，数据块之间的关系
Oracle存储数据的最小粒度（finestlevelofgranularity）被称为数据块（datablock）（也叫做逻辑块（logicalblock），Oracle块（Oracleblock）或页（page））。
一个数据块对应于磁盘上数个字节（byte）的物理数据库空间。
处于数据块之上的逻辑数据库空间是数据扩展（extent）。
数据扩展是为存储数据而分配的一组连续的数据块
位于数据扩展之上的逻辑数据库存储结构是段（segment）。
段由一组数据扩展（extent）构成，这些数据扩展位于同一表空间（tablespace）中，用于存储各种逻辑数据结构。
例如每个表（table）的数据都存储在其自身的数据段（datasegment）中，每个索引（index）的数据都存储在其自身的索引段（indexsegment）中。
如果表或索引是分区存储（partitioned）的，则每个分区拥有自己的段。
Oracle为段（segment）分配空间时以数据扩展（extent）为单位。
当段内已有的数据扩展没有可用空间时，Oracle为此段分配一个新的数据扩展。
因为数据扩展是随需分配的，所以一个段内的数据扩展在磁盘上未必是连续的。
一个段（segment）以及属于她的所有数据扩展（extent）必须包含在同一表空间（tablespace）中。
但在一个表空间内，属于同一个段的数据扩展可以分布在多个数据文件（datafile）上，即段可以跨文件存储。
但是每个数据扩展只能包含于同一个数据文件中。
在用户分配一个新的数据扩展（extent）时，其中的数据块（datablock）未必被同时分配。
如果用户是为某个数据库对象分配数据扩展，那么数据块也同时被立即分配并加入可用块列表（freelist）中。
如果数据扩展并非专为某数据库对象分配，那么数据块只在高水位线（highwatermark）移动时才被分配。
高水位线是段（segment）中已用和未用空间的边界。
1.1.4.3.数据块结构
本图显示了数据块的各个组成部分，包括：
数据块头（包括标准内容和可变内容）（commonandvariableheader），表目录区（tabledirectory），行目录区（rowdirectory），可用空间区（freespace），行数据区（rowdata）。
以下各节将分别讲解各个组成部分。
图中两个箭头表示一个数据块中的可用空间区的容量是可变的
●数据块头（header）中包含了此数据块的概要信息，例如块地址（blockaddress）及此数据块所属的段（segment）的类型（例如，表或索引）。
●如果一个数据表在此数据块中储存了数据行，那么数据表的信息将被记录在数据块的表目录区（tabledirectory）中。
●此区域包含数据块中存储的数据行的信息（每个数据行片断（rowpiece）在行数据区（rowdataarea）中的地址）。
[一个数据块中可能保存一个完整的数据行，也可能只保存数据行的一部分，所以文中使用rowpiece]
●当一个数据块（datablock）的行目录区（rowdirectory）空间被使用后，即使数据行被删除（delete），行目录区空间也不会被回收。
举例来说，当一个曾经包含50条记录的数据块被清空后，其块头（header）的行目录区仍然占用100字节（byte）的空间。
只有在数据块中插入（insert）新数据时，行目录区空间才会被重新利用。
1.2.基本的索引原理
选择性
Oracle根据查询和数据，提供了多种方法来判断使用索引的价值。
第一个方法就是判断索引中的唯一键或不同键的数量。
可以对表或索引进行分析来确定不同键的数量。
可以查询USER_INDEXES视图的Distinct_Keys列来研究分析的结果。
比较一下唯一键的数量和表中的行数（如USER_INDEXES视图的Num_Rows列所示），就可以判断索引的选择性。
选择性越高，索引返回的行数就越少，该索引就越好。
索引的选择性可以帮助基于成本的优化器来判断执行路径。
索引的选择性越高，针对每个不同值返回的行数也越少。
对于组合索引在索引中添加额外的列不会显著改善选择性，并且使用额外列的成本会超出收益。
集群因子
集群因子是索引与它所基于的表相比较而得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本（将集群因子与选择性相乘即可得到该操作的成本）。
集群因子记录在扫描索引时将读取的块数量。
如果使用的索引具有较大的集群因子，则必须访问更多的表数据块才可以获得每个索引块中的行（因为邻近行位于不同的块中）。
如果集群因子接近于表中的块数量，则表示索引适当排序；
但是，如果集群因子接近于表中的行数量，则表示索引没有适当排序。
集群因子的计算简要介绍如下：
（1）按顺序扫描索引。
（2）将当前索引值指向的ROWID的块部分与以前的索引值进行比较（比较索引中的邻近行）。
（3）如果ROWID指向不同的TABLE块，则增加集群因子（对整个索引执行该操作）。
Clustering_Factor列位于USER_INDEXES视图中，该列反映了数据相对于已索引的列是否显得有序。
如果Clustering_Fac
升级会员 