個人檔案好的的好地方相片部落格清單更多  |  工具 說明 |
|
|
8月2日 关于rownum和rowid 在Oracle中，有一个很有趣的东西，那就是rownum。当你从某个表中查询数据的时候，返回的结果集中都会带有rownum这个字段，而且有时候也可以使用rownum进行一些条件查询。
在查询中，我们可以注意到，类似于“select xx from table where rownum < n”（n>1）这样的查询是有正确含义的，而“select xx from table where rownum = n”这样的查询只在n ＝ 1的时候成立，“select xx from table where rownum > n”（n>1）这样的查询只能得到一个空集。另外“select xx from table where rownum > 0 ”这个查询会返回所有的记录。这是为什么呢？原因就在于Oracle对rownum的处理上，rownum是在得到结果集的时候产生的，用于标记结果集中结果顺序的一个字段，这个字段被称为“伪数列”，也就是事实上不存在的一个数列。它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum＝1的记录，才可能有rownum＝2的记录。
让我们回头来分析一下在where中使用rownum作为查询条件的情况。在rownum取=1，或者rownum <= n (n>1)的时候，没有问题。那么为什么当条件为rownum = n或者rownum >= n时明明有数据却只能得到一个空集呢？假设我们的查询条件为rownum = 2，那么在查询出的第一条记录的时候，oracle标记此条记录rownum为1，结果发现和rownum=2的条件不符，于是结果集为空。写到这里，我忽然有一个有趣的想法：假如有一条查询语句为select xx,yy from table where zz > 20 and rownum < 10，那么在执行的时候，是先按照zz>20的条件查询出一个结果集，然后按照rownum取出前10条返回？还是在按照zz>20的条件先查询，然后有一个记录就标记一个rownum，到rownum<10的时候就停止查询？我觉得应该是后者，也就是在执行语句的时候，不是做full scan，而是取够数据就停止查询。要验证这个想法应该很简单，找一个数据量非常大的表进行查询就可以了。可惜目前我没有这样的表。
我们可以看出，直接使用rownum是要受到限制的。但是很容易遇到这样的需求“查出符合条件的第xx条到第xx条记录”，比如页面的分页处理。这个时候如何构造出适合自己的结果集？嗯，墙边那位说全取出来手工挑选的哥们可以拉出去了。当然这样做也是可以的，但是前提是整个数据集的数据条数不多的情况下。假如遇到上十万百条的数据，全部取出来的话，用户就不用干别的事情了。这个时候用户应该怎么做呢？当然就是要用到我们介绍的rownum拉！rownum不是个“伪数列”么，好说，我们现在把它弄成一个实在的字段就可以了。具体做法就是利用子查询，在构建临时表的时候，把rownum也一起构造进去。比如“select xx,yy from (select xx,yy,rownum as xyz from table where zz >20) where xyz between 10 and 20”这样就可以了。另外使用oracle提供的结果集处理函数minus也可以做到，例如“select xx,yy from table where zz > 20 and rownum <20 minus select xx,yy from table where zz>20 and rownum <10”，但是使用minus好像比使用子查询更加消耗资源。
和rownum相似，oracle还提供了另外一个伪数列：rowid。不过rowid和rownum不同，一般说来每一行数据对应的rowid是固定而且唯一的，在这一行数据存入数据库的时候就确定了。可以利用rowid来查询记录，而且通过rowid查询记录是查询速度最快的查询方法。（这个我没有试过，另外要记住一个长度在18位，而且没有太明显规律的字符串是一个很困难的事情，所以我个人认为利用rowid查询记录的实用性不是很大）rowid只有在表发生移动（比如表空间变化，数据导入/导出以后），才会发生变化。 6月8日 锁，悲观锁，乐观锁锁（locking）
在业务逻辑的实现过程中，往往需要保证数据访问的排他性。如在大型系统的日终统计结算处理中，我们需要确保在事务运行过程中数据暂时不再发生变化。此时，我们就需要通过一些机制来保证这些数据在某个操作过程中不会被外界修改，这样的机制，也就是所谓的数据库系统的“锁”。 悲观锁（Pessimistic Locking）
悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。 对于Oracle而言，有六个等级的锁，数字越大锁级别越高, 影响的操作越多：
0：none 1：null 空 2：Row-S 行共享(RS)：共享表锁，sub share 3：Row-X 行独占(RX)：用于行的修改，sub exclusive 4：Share 共享锁(S)：阻止其他DML操作，share 5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 6：exclusive 独占(X)：独立访问使用，exclusive 1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive 没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。 4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。 00054, 00000, "resource busy and acquire with NOWAIT specified" // *Cause: Resource interested is busy. // *Action: Retry if necessary. 5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。 6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
col owner for a12
col object_name for a16 select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b where b.object_id=l.object_id / select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time / 如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：
alter system kill session 'sid,serial#';
如果出现了锁的问题, 某个DML操作可能等待很久没有反应。
当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。
乐观锁（Optimistic Locking） 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。 如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进修改时（如更改用户帐户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对几百上千个并发，这样的情况将导致怎样的后果。 乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。 读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。 对于上面修改用户帐户信息的例子而言，假设数据库中帐户信息表中有一个version字段，当前值为1；而当前帐户余额字段（balance）为$100。 1 操作员A 此时将其读出（version=1），并从其帐户余额中扣除$50（$100-$50）。 2 在操作员A操作的过程中，操作员B也读入此用户信息（version=1），并从其帐户余额中扣除$20（$100-$20）。 3 操作员A完成了修改工作，将数据版本号加一（version=2），连同帐户扣除后余额（balance=$50），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录version更新为2。 4 操作员B完成了操作，也将版本号加一（version=2）试图向数据库提交数据（balance=$80），但此时比对数据库记录版本时发现，操作员B提交的数据版本号为2，数据库记录当前版本也为2，不满足“提交版本必须大于记录当前版本才能执行更新“的乐观锁策略，因此，操作员B 的提交被驳回。 这样，就避免了操作员B 用基于version=1 的旧数据修改的结果覆盖操作员A的操作结果的可能。 从上面的例子可以看出，乐观锁机制避免了长事务中的数据库加锁开销（操作员A和操作员B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。 需要注意的是，乐观锁机制往往基于系统中的数据存储逻辑，因此也具备一定的局限性，如在上例中，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户余额更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。在系统设计阶段，我们应该充分考虑到这些情况出现的可能性，并进行相应调整（如将乐观锁策略在数据库存储过程中实现，对外只开放基于此存储过程的数据更新途径，而不是将数据库表直接对外公开）。 10月18日 oracle专家讲座：数据库调优（五）Join
* 一般常用的Join有： 1、Nested Loops Join 嵌套－循环 2、Sort/Merge Join 排序－匹配 3、Hash Join * 只要查询出的记录少，使用索引必然快。
交易系统与查询统计系统：
一般的应用都可以分为两种系统，一种称为交易系统，另一种称为查询统计系统。 交易系统特点：
1、日常业务操作，主要是前台操作。 2、优先级高，要求系统反应速度高。 3、并发量会很大。 4、单笔业务资源消耗小。 5、cursor问题 6、主要是插入和修改操作。 适用技术： 1、普通B*树索引 2、访问主要按照索引操作 3、多表连接主要使用Nested Loops方式 4、尽量使用Bind变量，提高其共享性 5、慎用并行处理技术 6、分区技术 7、物化视图技术 查询统计系统特点：
1、主要是后台操作。 2、要求系统反应速度高。 3、业务具有周期性。 4、单笔业务资源消耗大。 5、并发量小。 6、主要是大量的查询操作。 适用技术： 1、多索引，除普通B*树索引外，可能还要使用到bitmap索引，bitmap join索引等。 2、多表连接一般使用Sort/Merge Join或者Hash Join 3、不要使用Bind变量 4、使用并行处理技术 5、分区技术 6、大量使用物化视图技术 * 函数（包括系统函数，用户自定义函数）都会抑制索引的使用，所以需要慎用，或者要注意一下 使用的方法。 exp：表A的字段aa是一个日期型字段，上面建有索引。使用下面语句查询 select xxxx from A where TO_CHAR(aa,"YYYY-MM-DD") = "2005-10-01" 这个时候因为aa前出现了TO_CHAR函数，所以在这个时候，这条查询语句就不会使用索引。 类似的还有TRIM，SUBSTR等等函数 oracle专家讲座：数据库调优（四）Hint
Select Update —— /*+ hint */ —— Insert Delete 类似于上面格式的SQL语句就是使用到hint，他的作用是强制在语句执行的时候使用hint的内容。
注意这里/*+之间没有空格，如果有空格就不会使用到hint的内容，而且不会报错。 exp：select /*+ Index(A,idx_LM) */ id from A where .....
这个语句表示在这个查询过程中强制使用A表的idx_LM索引。 排序
* 在排序的时候，如果内存空间不够排序，则系统会占用硬盘的0磁道进行排序。
参数介绍：
sort_area_size 用于控制排序空间大小的参数 workarea_size_policy = auto自动分配内存进行排序 * 在老的版本中，对于登陆系统的每一个用户，系统都会给他分配一个固定的空间进行操作，这样
带来的问题就是有很多用户登陆系统的时候并不是全都要进行排序等操作，于是会造成大量的空 间浪费，而且真正需要进行排序操作的用户有时候会因为得不到足够的内存空间而影响工作效率。 在oracle的新版本中就启用了workarea_size_policy参数，设为auto的时候，系统就会让需要进 行排序等操作的用户自行申请内存，从而避免了内存的浪费。 排序也是会影响系统效率的操作，可以通过下面的手段来改进排序的效率： 1、调整pga_aggregate_target参数，这个参数的作用指定一块内存空间专门用于排序操作，参数 值越大，排序的效率也就越高，但是要注意，这个值一定要在物理内存允许的范围内，否则会引 起死机等问题。 2、设置workarea_size_policy = auto 为了提高查询语句的效率，就需要尽可能的减少内部排序。除了order by这样的排序以外，还有一 些语句在执行的时候要进行内部排序。如何减少内部排序的操作呢？ 1、避免使用distinct、group by 2、用union all代替union（union要排序，而union all不用） 3、使用索引可以避免排序 4、使用topN语句可以减少排序 exp:select xxxx from (select xxxx from xxx order by xx) where rownum <= n oracle专家讲座：数据库调优（三）RBO与CBO
RBO:Rule_based optimizer 基于规则的优化器 CBO:Cost_based optimizer 基于代价的优化器 * RBO在10g版本中已经被淘汰了。在10g之前的版本，如9i中，RBO还是被支持的。
RBO叫做基于规则的优化器，是指在SQL语句执行前，按照某一个具体设定的优先级来选择执行计划
的过程。比如在规则中，利用索引的优先级要高于全表扫描，所以使用RBO的情况下，只要表中建 有索引，就会按照索引进行查询。而我们知道，在表的记录很少，或者按照索引的选中度很高的情 况下，使用索引反而不如全表扫描。为了避免这种情况的出现，oracle在版本7以后引入了CBO机制。 使用CBO的情况下，数据库会自动按照最优的方案选择查询的方式。但是有一点要注意，使用CBO的 前提是在对目标表存在统计信息的情况下才可以正确的工作，如果统计信息不正确，会造成一些误 差，使得CBO的效率反而低于RBO。 统计信息的采集原则：
1、对最消耗资源的SQL语句涉及的表进行采集。 2、可以通过设定采集百分比estimate_percent来减少在采集上消耗的时间。（实际测量表明，按 照100%精度和按照50%，10%精度采集，对结果不会造成太大影响） 3、对数据量变化很大的表进行数据采集。 * 系统管理员可以通过使用exec dbms_stats.gather.......操作来对表，用户，数据库或者索引
进行统计。具体可以查看oracle文档。 * optimizer_mode = Choose | Rule | first_rows_n | all_rows 这是手动设置优化器类型的参数。Choose表示RBO和CBO都可选，由系统自行选择。Rule表示启用 RBO。first_rows_n和all_rows两个选项都是使用CBO。不同之处在于前者的意思是“以选择出前n 条记录最快为目标”，后者是“以选择到所有记录最快为目标”。另外first_rows_n后面的“_n”是9i 的新特性。 * 小技巧：在使用RBO的情况下，可以通过一些处理屏蔽索引的作用。 1、在数字列后加0，如num_col + 0，这样在num_col上的索引就被屏蔽了。 2、日期列后加0 3、字段列后拼一个空，如char_col||"" 索引： * 主键和唯一索引的不同点： 一个表的主键只有一个，而且不能为空；一个表的唯一索引可以有多个，而且可以为空。 关于使用组合索引的一个误解： 在几个字段上建立了一个组合索引以后，只要是涉及到这几个字段的任意查询都会使用到这个组合 索引。 这个见解是错误的，实际上对一个建立组合索引的表的查询中，只有在这个组合索引的第一个字段 被查询的时候，才会使用到建立的组合索引。所以使用组合索引的时候，原则上要把使用频率最高， 可选性最高，最常用的字段放在第一位。 * 使用SQL>alter index customers_pk monitoring usage可以记录用户的索引使用情况。然后可 以在V$OBJECT_USAGE V$OBJECT_STATS 这两个视图中看到具体信息。 Oracle专家讲座：数据库调优（二）SQL语句执行的过程；
Open->Parse->Bind->Execute->Fetch->Close 一般说来，一个SQL语句的Parse和Execute过程是必须的，Bind和Fetch是可选的。 Parse：主要进行对这个SQL语句的语法分析，生成执行计划。 Bind：进行变量替换，对SQL语句中用到的变量进行赋值。 Execute：语句的执行，进行I/O操作 Fetch：取出结果返回（查询语句要有这个过程） × 关于Bind变量：
在书写SQL语句，尤其是查询语句的时候，在条件处不写明具体的值，而用变量代替，这个变量就 叫Bind变量。因为不同的语句，在执行过程中都要进行语法分析。所以在语句不复杂，并发量非常 大的时候，就应该在语句中尽可能的使用Bind变量，因为使用了Bind变量以后，SQL语句就是相同 的了，这样可以节省语法分析的过程。比如普通的交易系统。 但是还有几种情况需要慎用Bind变量，情况一是在表中数据分布很不均匀的情况下，例如在一个表 中有一个“性别”字段，这个表中记录有99％为男性，此时直接使用select * from tab where sex ="男"就比使用Bind变量的select * from tab where sex=a的效率高。情况二是在数据仓库或者数 据统计的应用中要慎用。因为此时的查询语句非常复杂，如果随意使用Bind变量甚至有可能引起语 句执行过程的变化，而且这种情况下，语句的并发量很小，主要开销是在Execute中而不是Parse， 所以使用Bind变量的需求也不大。 数据库自带的分析工具：Explain，autotrace，sql_trace，tkprof 1、Explain： 使用方法： SQL>explain plan for (select xxx from xxxxx where .....) SQL>@?/rdbms/admin/utlxpls.sql 第一行是生成括号中SQL语句分析后的执行计划 第二行是查看执行计划 2、timing： 使用方法： SQL>set timing on[off] 在设置timing参数为on以后，所有在sql plus中执行的语句，在显示结果的时候都会同时显示这个 语句执行所消耗的时间。默认设置为off 3、autotrace： 使用方法： SQL>set autotrace on 设置autotrace为on以后，在sql plus中执行语句，就可以看到效率等信息了 4、SQL_trace和TKPROF： 使用方法： SQL>alter session set sql_trace=true 设置完sql_trace以后，就会在指定目录下生成一个.trc文件。这个时候在命令行使用 tkprof xxx.trc xxx.txt命令，把trc转成txt就可以看了。 使用SQL>show parameter user_命令看user_dump_dest就是指定目录了。 10月12日 Oracle专家讲座：数据库调优（一）× SQL语句的调优主要关注语句运行的过程
数据库调优过程中常见的误区：
1、只需要调整系统或者数据库设置的一些参数就可以从根本上解决数据库的效率问题。 答：这种情况并不是没有，但是出现的情况很少。大多数情况下数据库效率的问题与系统和数据 库设置无关。 2、数据库性能问题只是系统管理员或者DBA的问题。 答：数据库性能的问题与所有的人都有关，包括开发人员，系统管理员以及DBA等。 3、性能问题只是出现在最后的部署阶段，开发阶段可以不考虑性能问题。 答：性能问题在软件开发的各个阶段都要进行考虑，越早考虑收效越大。 4、数据库性能问题主要是SQL语句的语法不对，例如join的顺序等等。 答：数据库性能问题与SQL语句的语法关系不大，光修改SQL语句是不够的。 5、多表连接的过程是一个非常消耗资源的过程，应该尽可能的分步去连接。 答：现有的数据库技术完全可以解决多表连接，分步进行反而会造成资源的浪费，影响数据库的 性能。 6、CPU的利用率越低，说明应用程序的运行越好，效率越高。 答：CPU的利用率并不是衡量系统性能，数据库性能的标准。 exp：收费站有10个通道，大家都挤在一个通道里，那么收费站通道的利用率很低，只有1/10。但 此时整体的效率也是非常低的。 7、要深入了解各个事件、Latch、缓冲池的具体情况。 答：不用了解的这么深入，这些只是表象而已。 数据库的优化过程是一个自顶而下的过程。其经验是越早发现，越早处理，收获越大，效果越好。
如果是操作系统的调优（一般包括增加内存等手段）只能收获百分之几的优化效果； 如果是数据库级的调优，可以收获百分之二十左右的效果； 如果把调优放在应用级，那么可以收获百分之一百以上，甚至百分之二百，三百的效果。 二八法则：
80％的性能问题是由20％的应用导致； 80％的性能问题可以通过20％的优化技术解决。 × 数据库优化主要是索引的正确使用。
数据库调优的先后顺序： 1、应用设计 2、数据库设计 3、过程设计 4、SQL语句 5、物理架构 6、内存情况 7、I/O情况 8、内存分配 9、操作系统 10、网络状况 1月21日 struts+spring+hibernate之间的关系与差别（ZZ）Struts：用来作VC部分，即控制和显示作用； Trackback地址: http://www.yculblog.com/trackback/131645 1月5日 BS vs CS奇怪，为什么不用BS呢？仅仅因为要对GIS的空间数据进行处理，而BS架构不便于处理，就一定要用CS么？ 以Application直接作为Client的CS架构，或者把Applet嵌在页面上作为一种假BS架构来处理，本来就不是Java的强项，而且Java的界面处理起来也不是很方便…… 唉，看了一天Swing了，感觉还不错。还是用HashTable来生成JTree比较方便。用Vector或者Object[]很难生成多层的树状结构。 |
|
|
