oracle10g优化数据库

一基本概念1实例和数据库（1）什么是实例:数据库启动以后,各个进程调入到内存中,各个进程之间的相互协调构成了实例（2）什么是数据库:各个数据文件的集合: 10.0\oradata\%sid%\目下有各种文件(CTL(控制文件),LOG(日志文件),DBF(数据文件))（3）数据库的物理结构系统的初始化参数：存放的位置为% oracle_home%\product\10.0\Db_1\database\initorcl.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）数据块查看数据文件的信息：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;二调优工具介绍1SQL_TRACE（1）SQL_TRACE说明：注意：从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被显示出来使用步骤：⏹在数据库上设置TIMED_STATISTICS为TRUE（在9i上默认为true，可以使用命令：show parameter timed_statistics查看）；Alter system set timed_statistics=true;⏹启动当前会话的sql_trace：alter session set sql_trace=true;(停止当前会话的sql_trace: alter session set sql_trace=false;)⏹跟踪文件的存放位置由初始化参数user_dump_dest控制，（可以用命令show parameter user_dump_dest来查看）．可能通过修改时间来找到最新生成的跟踪文件。

生成的跟踪文件扩展名为.trc．是二进制文件。

找到了对应的跟踪文件orcl_ora_3888.trc．⏹对trace文件使用tkprof工具进行分析Tkprof tracefile outfile [options]例子：tkprof orcl_ora_3576.trc report.txt sort=fchcpu sys=no;Tfprof文件参数解析：⏹CALL：每次SQL语句的处理都分成三个部分（Parse，Execute,Fetch）⏹Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

⏹Execute：这步是真正的由Oracle来执行语句。

对于insert、update、delete操作，这步会修改数据，对于select操作,这步就只是确定选择的记录。

⏹Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。

⏹COUNT: 这个语句被parse、execute、fetch的次数。

⏹CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。

⏹ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。

⏹DISK：从磁盘上的数据文件中物理读取的块的数量。

一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。

⏹QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。

一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。

⏹CURRENT：在current模式下所获得的buffer的数量。

一般在current模式下执行insert、update、delete操作都会获取buffer。

在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。

⏹ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。

对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。

问题判断：⏹elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源⏹cpu OR elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化3在pl/slq中使用set timing on与使用sql_trace的区别三调优方式：（SQL）1在数据量比较大表上建立相关的索引Create index abc_b_index on abc(b);2Where 子句的连接顺序⏹ORACLE采用自上而下的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤最大数据记录的条件必须写在WHERE子句的末尾。

select empno,ename,job,sal from emp wherejob='PRESIDENT' and sal>1000select empno,ename,job,sal from emp where sal>1000and job='PRESIDENT'注：（select count(*) from emp where sal>1000:（查询的数目为15068））（select count(*) from emp where job='PRESIDENT'; 查询的数目为1952）3Select 子句中避免使用’*’;如果在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 = 'SMITH'6删除重复记录Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。