Oracle性能优化

ORACLEDATABASE10G性能优化概念与方法最常见的两个问题索引造成的性能问题首先我们创建一个只有一个字段的表，字段类型为char(20)：songxn@T1createtablet(cchar(20));然后插入一万行，并计时：songxn@T1settimingonsongxn@T1begin2foriin1..100003loop4executeimmediate5‘insertintotvalues(:x)’usingi;6endloop;7end;8/PL/SQLproceduresuccessfullycompleted.Elapsed:00:00:00.83耗时0.83秒索引造成的性能问题接下来我们为表T建立索引：songxn@T1createindexidx_tont(c);再插入一万行，并计时：songxn@T1settimingonsongxn@T1begin2foriin1..100003loop4executeimmediate5‘insertintotvalues(:x)’usingi;6endloop;7end;8/PL/SQLproceduresuccessfullycompleted.Elapsed:00:00:01.89耗时1.89秒索引造成的性能问题可以很清楚的看到，为表T加了索引后，插入耗时增加了一倍以上，而这仅仅是非常简单的表和非常简单的索引结构，复杂的表和复杂的索引，差距还会更大。这个例子说明了，数据库维护索引结构会带来开销，而这个开销通常比你想象的要大很多。所以，原则是，只建立必不可少的索引。不使用绑定变量造成的性能问题继续使用表T，现在我们换一种插入方式，再插入一万笔数据，并计时：songxn@T1begin2foriin1..100003loop4executeimmediate5'insertintotvalues('||i||')';6endloop;7end;8/PL/SQLproceduresuccessfullycompleted.Elapsed:00:00:12.47还记得前面的插入耗时吗？是1.89秒，现在居然是12.47秒，有了将近一个数量级的差异！而造成这种差异的原因只是，这次插入没有使用绑定变量！不使用绑定变量造成的性能问题如果在SQL语句中使用直接量，那么在数据库看来，每个语句都是全新的，以前从未见过，必须对它进行解析、限定、安全性检查、优化等。简单地讲，就是你执行的每条不同的语句都要在执行时进行编译。如果使用绑定变量，则这个SQL语句只编译一次，随后会把执行计划存储在一个共享池中，以便以后获取和重用这个计划。以上两个种方法在性能和可扩缩性方面有很大差别，甚至可以说有天壤之别。共享池是个缓存，一个不使用绑定变量的应用，不但自己执行的慢，而且会将缓存中其他应用的执行计划冲掉，导致其他应用也运行缓慢。如果你想让你的数据库瘫痪，最简单的办法就是不使用绑定变量。小结只建立必要的索引使用绑定变量Oracle的与众不同之处Oracle独特的锁定策略并发控制是数据库最基本的特性，并发控制由锁机制来实现，而Oracle的锁机制几乎与其他所有数据库都不一样。Oracle只在修改时才对数据加行级锁。如果只是读数据，Oracle绝不会对数据锁定。写入器不会阻塞读取器。换种说法：读不会被写阻塞。写入器想写某行数据，但另一个写入器已经锁定了这行数据，此时该写入器才会被阻塞。读取器绝对不会阻塞写入器。Oracle独特的锁定策略充分理解Oracle的锁机制，对今后的开发工作非常重要！Oracle的锁机制有一个副作用，如果想保证一次最多只能有一个用户访问一行数据（即串行访问），开发人员就要自己做些工作了。常用的解决方式就是使用selectforupdate将查询串行化。并发控制与多版本多版本与并发控制的关系非常紧密，这正是Oracle并发控制机制的基础，Oracle采用了一种多版本、读一致的并发模型，Oracle利用这种机制提供了以下特性：读一致查询：对于一个时间点，查询会产生一致的结果。继续用表T做演示，我们在表T上打开一个游标，然后清空表T，看一下会发生什么。并发控制与多版本songxn@T1varxrefcursorsongxn@T1begin2open:xforselect*fromtwherec='10000';3end;4/PL/SQLproceduresuccessfullycompleted.这里我只是打开了游标，并没有从该游标获取数据，然后我清空表Tsongxn@T1deletefromt;30000rowsdeleted.songxn@T1commit;Commitcomplete.现在我提交了，数据没有了，那这时候游标会是什么情况呢？并发控制与多版本songxn@T1printx;C--------------------100001000010000游标中的数据还在，而且，并不是打开游标的时候，Oracle偷偷将数据复制到了某一个位置，而是这些数据作为表T的前映像，被保存在回滚段中。这就是Oracle的多版本策略，游标中的数据在打开游标那一时刻就被确定了，不会因为后续的或者是其他人的操作而改变！小结读不会被写阻塞读一致查询：对于一个时间点，查询会产生一致的结果Oracle体系结构文件参数文件，即spfile，默认位置为$ORACLE_HOME/dbs跟踪文件和警告文件，默认位置为$ORACLE_BASE/admin/$ORACLE_SID数据文件，即我们常说的datafile临时文件，即我们常说的临时表空间控制文件，即controlfile，里面记录了数据库的配置信息重做日志文件，即redolog使用参数文件配置数据库一般我们修改数据库参数的时候，会使用altersystem命令，比如我想改大SGA：sys@T1altersystemsetsga_target=500Mscope=spfile;重启数据库后，配置就生效了也可以使用参数文件配置数据库，首先创建spfile的文本文件，然后停止数据库：sys@T1createpfilefromspfile;Filecreated.sys@T1shutdownimmediate然后编辑这个文本文件，比如修改*.sga_target=500000000然后再由这个文件创建spfile，启动数据库，新的参数即可生效：sys@T1createspfilefrompfile;Filecreated.sys@T1startup借助警告文件诊断数据库问题数据库出现问题的时候，第一个要查看的文件就是alertlog，我的数据库很健康，不过当我查看alertlog的时候，还是发现了一些小问题：MemoryNotification:LibraryCacheObjectloadedintoSGAHeapsize2403Kexceedsnotificationthreshold(2048K)Detailsintracefile/u01/app/oracle/admin/t1/udump/t1_ora_11102.trc当时我进行了一些比较繁重的数据库操作，导致内存紧张，所以alertlog里记录了一条警告，并给出了详细的跟踪文件位置。重做日志文件与数据库性能重做日志用来保护数据库，几乎所有的数据库操作都会生成重做日志，以防止突然的掉电或者是磁盘损坏。这是个同步操作，几乎所有的数据库操作都要等待重做日志的写入，所以重做日志应该放到速度最快的磁盘上，以免成为系统的性能瓶颈。内存结构系统全局区SGA：这是一个很大的共享内存段，几乎所有Oracle进程都要访问这个区中的某一点。SGA中最重要的部分就是数据库的缓存。进程全局区PGA：这是一个进程或线程专用的内存，其他进程/线程不能访问。用户全局区UGA：UGA）：这个内存区与特定的会话相关联。它可能在SGA中分配，也可能在PGA中分配，这取决于是用共享服务器还是用专用服务器来连接数据库。如果使用共享服务器，UGA就在SGA中分配；如果使用专用服务器，UGA就会在PGA（即进程内存区）中。小结可以使用spfile配置数据库数据库出现问题，首先要查看警告日志重做日志需要放在性能最好的IO设备上SGA是Oracle最重要的内存结构锁机制死锁Oracle认为死锁很少见，而且由于如此少见，所以每次出现死锁时它都会在服务器上创建一个跟踪文件，这里我故意造成一个死锁，数据库会报错：songxn@T1updatetsetx=x+1000wherex=1000;updatetsetx=x+1000wherex=1000*ERRORatline1:ORA-00060:deadlockdetectedwhilewaitingforresource同时alertlog里也会做记录：ThuDec3014:48:572010ORA-00060:Deadlockdetected.Moreinfoinfile/u01/app/oracle/admin/t1/udump/t1_ora_13142.trc.我们可以到对应的跟踪文件里查看详细的死锁信息：---------Blocker(s)-----------------Waiter(s)---------ResourceNameprocesssessionholdswaitsprocesssessionholdswaitsTX-000b000e-000000251349X1833XTX-00010026-000000b81833X1349XOracle的锁类型DML锁：DML代表数据操纵语言。一般来讲，这表示SELECT、INSERT、UPDATE、MERGE和DELETE语句。DML锁机制允许并发执行数据修改。例如，DML锁可能是特定数据行上的锁，或者是锁定表中所有行的表级锁。DDL锁：DDL代表数据定义语言，如CREATE和ALTER语句等。DDL锁可以保护对象结构定义。内部锁和闩：Oracle使用这些锁来保护其内部数据结构。例如，Oracle解析一个查询并生成优化的查询计划时，它会把库缓存“临时闩”，将计划放在那里，以供其他会话使用。闩（latch）是Oracle采用的一种轻量级的低级串行化设备，功能上类似于锁。不要被“轻量级”这个词搞糊涂或蒙骗了，你会看到，闩是数据库中导致竞争的一个常见原因。轻量级指的是闩的实现，而不是闩的作用。DML锁DML锁（DMLLock）用于确保一次只有一个人能修改某一行，而且你正在处理一个表时别人不能删除这个表。在你工作时，Oracle会透明程度不一地为你加这些锁。TX锁（事务锁）：事务发起第一个修改时会得到TX锁，而且会一直持有这个锁，直至事务执行提交或回滚。Oracle中的锁定过程如下：(1)找到想锁定的那一行的地址。(2)到达那一行。(3)锁定这一行（如果这一行已经锁定，则等待锁住它的事务结束，除非使用了NOWAIT选项）。DML锁TM(DMLEnqueue)锁：TM锁用于确保在修改表的内容时，表的结构不会改变。例如，如果你已经更新了一个表，会得到这个表的一个TM锁。这会防止另一个用户在该表上执行DROP或ALTER命令。如果你有表的一个TM锁，而另一位用户试图在这个表上执行DDL，他就会得到以下错误消息：droptabledept*ERRORatline1:ORA-00054:resourcebusyandacquirewithNOWAITspecified初看上去，这是一条让人摸不着头脑的消息，因为根本没有办法在DROPTABLE上指定NOWAIT或WAIT。如果你要执行的操作将要阻塞，但是这个操作不允许阻塞，总是会得到这样一条一般性的消息。前面已经看到，如果在一个锁定的行上发出SELECTFORUPDATENOWAIT命令，也会得到同样的消息。小结事务在发起第一个修改