oracle10g优化数据库.doc

一 基本概念1 实例和数据库（1） 什么是实例:数据库启动以后,各个进程调入到内存中,各个进程之间的相互协调构成了实例（2） 什么是数据库:各个数据文件的集合: 10.0oradata%sid%目下有各种文件(CTL(控制文件),LOG(日志文件),DBF(数据文件)（3） 数据库的物理结构 系统的初始化参数：存放的位置为% oracle_home%product10.0Db_1databaseinitorcl.ora 数据库日志文件：分为联机日志文件(重做日志文件(redo日志文件):不停的覆盖)和归档日志文件(在数据库热备份的时候使用)；联机日志文件写完以后，依次往下写，全部写满，重新覆盖原来的，此时就是非归档形式。 控制文件：控制日志文件和数据文件。数据库启动的时候，首先启动控制文件，然后由控制文件打开数据文件，对应数据中的形式就是：database mount ，然后是database open。 查看数据库得文件：数据库的连接：sqlplus system/bjsxt as sysdba对应的控制文件从v$controlfile视图中查看。 desc v$controlfile （查看描述） select status ,controlfile from v$controlfile。（查看内容）对应的数据文件从v$datafile视图中查看。desc v$datafileselect status, name from v$datafile对应的日志文件从v$logfile视图中查看。desc v$logfileselect member from v$logfile2 内存结构（1） SGA（系统全局区）的各个缓冲区 SGA被实例的所有进程所共享，包含高速缓存区（DB Buffer）、大的共享区、共享池、日志缓冲区、固定的SGA。 DB 高速缓冲区：使用系统内存，加快数据库访问数据文件中的数据。 共享池：缓冲执行的SQL语句、程序块、执行计划、常用的数据字典。 Redo buffer：日志文件缓冲区。 大共享区：提供了一个可选的内存块区，当需要备份或者执行大的SQL语句时可能需要它。 固定SGA：完全由oracle内部控制。 查看SGA的使用情况：SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC3 逻辑结构（1） 表空间（2） 段（3） 盘区（4） 数据块块block盘区extent段segment表空间tablespace数据文件datafile查看数据文件的信息：desc dba_data_files;select substr(file_name,1,50),tablespace_name,blocks from dba_data_files；查看表空间信息： desc dba_tablespaces;查看段信息： desc dba_segments查看盘区信息： desc dba_extents;二 调优工具介绍1 SQL_TRACE（1） SQL_TRACE说明：参数说明布尔型缺省值False参数类别静态取值范围True|false注意：从oracle 10g开始，SQL_TRACE成为了动态参数 SQL_TRACE为true，表示运行，会对整个实例产生严重的性能影响，所以在产品环境下，如非必要，一定不要设置这个参数。如果必须在系统下启动SQL_TRACE,必须保证以下条件对系统性能影响最小： 保证25的cpu idle 为user_dump_dest分配足够的空间。（2） 在使用SQL_TRACE之前，几个注意事项需要说明一下： 初始化参数TIMED_STATISTICS参数TIMED_STATISTICS最好设置为true，否则一些重要信息将无法收集 设MAX_DUMP_FILE_SIZE在9i中默认为unlimited，表示可以任意大小Alter session set MAX_DUMP_FILE_SIZE=unlimited（3） SQL_TRACE可以通过初始化参数来设置，也可以通过alter system (从10g开始)在全局中设置，也可以在具体的session中设置 在参数文件中设置（pfile/spfile）指定：Sql_trace=true; 在oracle 10g动态全局指定Alter system set sql_trace=true; 在当前session中指定Alter session set sql_trace =true;2 使用tkprof工具来查询sql性能工具SQL trace工具集收集正在执行的sql的性能状态数据，并将数据记录到一个跟踪文件中，这个跟踪文件提供很多有用的信息，如cpu时间，总消耗时间，读取磁盘数量，查询中返回的记录数目等。 使用命令查看一下tkprof的帮助 fkprof 然后回车在这些参数比较有用的是：fchela （按照elapsed time fetching来对分析的结果排序）sys这个参数设置为no可以阻止所有以sys用户执行的sql被显示出来 使用步骤：n 在数据库上设置TIMED_STATISTICS为TRUE（在9i上默认为true，可以使用命令：show parameter timed_statistics 查看）；Alter system set timed_statistics=true;n 启动当前会话的sql_trace：alter session set sql_trace=true; (停止当前会话的sql_trace: alter session set sql_trace=false;)n 跟踪文件的存放位置由初始化参数user_dump_dest控制，（可以用命令show parameter user_dump_dest来查看）可能通过修改时间来找到最新生成的跟踪文件。生成的跟踪文件扩展名为.trc是二进制文件。找到了对应的跟踪文件orcl_ora_3888.trcn 对trace文件使用tkprof工具进行分析Tkprof tracefile outfile options例子：tkprof orcl_ora_3576.trc report.txt sort=fchcpu sys=no; Tfprof文件参数解析：n CALL： 每次SQL语句的处理都分成三个部分（Parse，Execute,Fetch）n Parse： 这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。 n Execute： 这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作,这步就只是确定选择的记录。n Fetch： 返回查询语句中所获得的记录，这步只有select语句会被执行。n COUNT: 这个语句被parse、execute、fetch的次数。n CPU： 这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。n ELAPSED： 这个语句所有消耗在parse、execute、fetch的总的时间。n DISK： 从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。n QUERY： 在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。 一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。n CURRENT： 在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。n ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。 问题判断：n elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源n cpuORelapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化3 在pl/slq中使用set timing on与使用sql_trace的区别三 调优方式：（SQL）1 在数据量比较大表上建立相关的索引 Create index abc_b_index on abc(b);2 Where 子句的连接顺序n ORACLE采用自上而下的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤最大数据记录的条件必须写在WHERE子句的末尾。select empno,ename,job,sal from emp where job=PRESIDENT and sal1000select empno,ename,job,sal from emp where sal1000 and job=PRESIDENT注：（select count(*) from emp where sal1000:（查询的数目为15068）（select count(*) from emp where job=PRESIDENT; 查询的数目为1952）3 Select 子句中避免使用*;如果在select子句中查询所有的字段，使用*是比较方便的，但是效率比较低，原因在于ORACLE在解析* 的时候，要将* 依次转化为字段名称，这个转化过程是通过查询数据字典完成的。4 减少访问数据库的次数当执行每条sql语句时，ORACLE在内部执行很多工作：解析SQL，估算索引的利用率，绑定变量，读数据块等。查询编号为34的员工的姓名，工作，工资；再查询一下编号为35的员工(低效)select ename,job,sal from emp where empno=34;select ename,job,sal from emp where empno=35;在一个sql直接实现查询编号为34和35的员工信息(高效)select ename,job,sal from emp where empno=34 or empno=355 使用DECODE函数来减少处理时间（1） DECODE使用说明：decode(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值)该函数的含义如下：IF 条件=值1 THENRETURN(翻译值1)ELSIF 条件=值2 THENRETURN(翻译值2).ELSIF 条件=值n THENRETURN(翻译值n)ELSERETURN(缺省值)END IF（2） 使DECODE可以避免重复扫描相同记录或者重复连接相同的表select count(*),sum(sal) from emp where ename=SMITH and deptno=20；select count(*),sum(sal) from emp where ename=SMITH and deptno=10select count(decode(deptno,10,X,NULL) D10_COUNT,count(decode(deptno,20,X,NULL) D20_COUNT,sum(decode(deptno,10,sal,NULL) D10_sum,sum(decode(deptno,20,sal,NULL) D20_sumfrom emp where ename = SMITH6 删除重复记录Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的， rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中 那些具有最大rowid的就可以了，其余全部删除。（1） 方法一 创建一个相同备份表create table tbl_test_temp as select * from tbl_test 在原有的表里增加记录insert into tbl_test select * from tbl_test 查询重复记录的信息select id,name,pid ,count(*) from tbl_test group by id,name ,pid having count(*)1 删除重复记录delete from tbl_test_temp x where x.rowid(select min(rowid) from tbl_test_temp e where x.id=e.id)(或者)delete from tbl_test_temp x where x.rowid(select max(rowid) from tbl_test_temp e where x.id=e.id)(或者)delete from tbl_test_temp x where x.rowid!=(select max(rowid) from tbl_test_temp e where x.id=e.id)（2） 第二种方法：n 创建一个临时表，保存不重复的数据create table temp as select distinct * from tbl_test_tempn 丢弃原来的表：drop table tbl_test_tempn 创建原来的表create table tbl_test_temp as select * from tempn 丢弃临时表drop table temp7 TRUNCATE代替DELETE当删除表的记录时，通常情况下，回滚段（rollback segments）用来保存可以被恢复的信息，如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态，而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息，当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短。8 尽量多用commit 只要有可能尽量多用COMMIT,这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少，COMMIT释放的资源：n 回滚段上用于恢复数据的信息n 被程序语句获得的锁n Redo log buffer中的空间9 计算记录条目数和一般的观点相反，count(*)要比count（1）快，但是count(索引字段)是最快的。10 用Where子句替换HAVING子句避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。(导入新的表)n select sum(zdkpje),nsrsbh,swjg_dm from zzs_da_ybnsr group by nsrsbh,swjg_dm having swjg_dm not like 1000%（低校）n select sum(zdkpje),nsrsbh,swjg_dm from zzs_da_ybnsr where swjg_dm not like 1000% group by nsrsbh,swjg_dm （高效）n11 在含有子查询的语句中，尽量减少对表的查询n select * from emp where deptno in (select deptno from dept where loc like NEW YORK ) or deptno in (select deptno from dept where DNAME like SALES )n select * from emp where deptno in (select deptno from dept where loc like NEW YORK or DNAME like SALES)12 使用表的别名（Alias），这样可以一方面减少系统解析的时间，另一方面可以减少由column造成的歧义错误。13 Exist和 in(not exist 和not in)n in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。如：select * from emp where deptno in (select deptno from dept)（或者）select * from emp where exists(select x from dept where dept.deptno=emp.deptno)分析如下：n Select * from T1 where x in ( select y from T2 )执行的过程相当于:select * from t1, ( select distinct y from t2 ) t2where t1.x = t2.y;n select * from t1 where exists ( select null from t2 where y = x )执行的过程相当于:for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end ifend loop表T1 不可避免的要被完全扫描一遍四 常见的Oracle的面试题：1 oracle中truncate和delete命令有何区别？（1）delete将在回滚段中产生回滚信息，truncate不产生，因此无论表中的记录多少，truncate执行都很快。（2）truncate 是 DDL，执行隐含的commit，truncate不能回滚。任何没有提交的 DML 改变也将会和truncate一起提交。 （3）truncate 重置表及其索引高水位标志。全表扫描和索引快速全扫描读高水位标志下的所有数据块，因此在delete后全扫描性能没有提高，但truncate后将会变快。 （4）truncate 不触发 delete 触发器。 （5）没有对象权限允许一个用户 truncate 另一个用户的表。这样做需要 DROP ANY TABLE 系统权限。 （6）当一个表被 truncate，表及其索引的存储将被重置回初始大小。而 delete 不收缩表及其索引的大小。 （7）当父表有一个可用的引用完整性约束时不能被 truncate。必须先disable引用父表的外键约束，再truncat