oracle数据库状态查询

1 状态查询

启动状态

SQL语句

结果

nomount

select status from v$instance;

STARTED

select open_mode from v$database;

ERROR at line 1:

ORA-01507: database not mounted

mount

select status from v$instance;

MOUNTED

select open_mode from v$database; MOUNTED

open

select status from v$instance;

OPEN

select open_mode from v$database;

READ WRITE 或者READ ONL Y

2 实验过程

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 125829120 bytes

Fixed Size 1247684 bytes

Variable Size 92276284 bytes

Database Buffers 25165824 bytes Redo Buffers 7139328 bytes

SQL> select status from v$instance;

STA TUS

------------

STARTED

SQL> select open_mode from v$database; select open_mode from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STA TUS

MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STA TUS

------------

OPEN

SQL> select open_mode from v$database; OPEN_MODE

READ WRITE

2020年(Oracle管理)Oracle SQL性能优化方法

（Oracle管理）Oracle SQL性能优化方法

OracleSQL性能优化方法探讨 Oracle性能优化方法(SQL篇)1 1综述2 2表分区的应用2 3访问Table的方式3 4共享SQL语句3 5选择最有效率的表名顺序5 6WHERE子句中的连接顺序．6 7SELECT子句中避免使用’*’6 8减少访问数据库的次数6 9使用DECODE函数来减少处理时间7 10整合简单,无关联的数据库访问8 11删除重复记录8 12用TRUNCATE替代DELETE9 13尽量多使用COMMIT9 14计算记录条数9 15用Where子句替换HAVING子句9 16减少对表的查询10 17通过内部函数提高SQL效率.11 18使用表的别名(Alias)12 19用EXISTS替代IN12 20用NOT EXISTS替代NOT IN13 21识别低效执行的SQL语句13

22使用TKPROF 工具来查询SQL性能状态14 23用EXPLAIN PLAN 分析SQL语句14 24实时批量的处理16

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要经过反反复复的过程。在数据库建立时，就能根据应用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有很大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积累经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来说明对数据库性能如何进行优化。 2表分区的应用 对于海量数据的表，可以考虑建立分区以提高操作效率。建立分区一般以关键字为分区的标志，也可以以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时可以进行说明： createtableTABLENAME() partitionbyrange(PutOutNo) (partitionPART1valueslessthan(200312319999) partitionPART2valueslessthan(200412319999)

OracleSQL的优化

Oracle SQL的优化 标签：oraclesql优化date数据库subquery 2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类： Oracle Basic Knowledge（208） SQL的优化应该从5个方面进行调整： 1.去掉不必要的大型表的全表扫描 2.缓存小型表的全表扫描 3.检验优化索引的使用 4.检验优化的连接技术 5.尽可能减少执行计划的Cost SQL语句： 是对数据库(数据)进行操作的惟一途径； 消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低； 可以有不同的写法；易学，难精通。 SQL优化： 固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。 应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致 ORACLE优化器： 在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是 要么结果表达式能够比源表达式具有更快的速度 要么源表达式只是结果表达式的一个等价语义结构 不同的SQL结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。 1 常量优化： 常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式： sal > 24000/12

sal > 2000 sal*12 > 24000 如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。 优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。 2 操作符优化： 优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。 例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH' 优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是CHAR(10)，那么优化器将不做任何转换。 一般来讲LIKE比较难以优化。 其中： ~~IN 操作符优化： 优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。 例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为 ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘ oracle 会将 in 后面的东西生成一存中的临时表。然后进行查询。 如何编写高效的SQL: 当然要考虑sql常量的优化和操作符的优化啦，另外，还需要： 1 合理的索引设计： 例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：语句A SELECT count(*) FROM record WHERE date >'19991201' and date <'19991214‘and amount >2000 语句B

2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率

（Oracle管理）如何优化SQL语句以提高Oracle执 行效率

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。 （2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。 （4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。 （8）删除重复记录： 最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)

OracleSQL性能优化方法

OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME() partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。。。。。。 如此，在进行大部分数据查询，数据更新和数据插入时，Oracle自动判定操作应该在哪个分区进行，幸免了整表操作，提高了执行的效率

Oracle SQL性能优化方法研究

Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)

18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建

Oracle性能优化总结

个人理解，数据库性能最关键的因素在于IO，因为操作存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 物理优化： 一、优化存

3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in('db block gets','consistent gets','physica l reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密 加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分 4.使用RAID raid使用应注意： 选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 5.分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理存的两倍。

Oracle_SQL性能优化技巧大总结

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. （2） WHERE子句中的连接顺序．： ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾. （3） SELECT子句中避免使用 * ： ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 （4）减少访问数据库的次数： ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等； （5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) （8）删除重复记录： 最高效的删除重复记录方法 ( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9）用TRUNCATE替代DELETE： 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 译者按: TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML) （10）尽量多使用COMMIT： 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求

oracle查询优化改写技巧和案例(学习笔记)

oracle查询优化改写技巧和案例（学习笔记） 第一章 将空值转换成实际值 函数coalesce(exp1,exp2,...) 返回第一个为非空的值，避免了返回空值； 查找满足多个条件的行 用到or：只要达到其中一个条件就可以的； 查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子； 用括号把多个条件给分隔开； 在where字句中引用别名的列； 即要引用别名的列时必须是一张表中的字段； select * from (sal as 工资 ,comm as 提成from emp) x where 工资 >2000; 拼接列 用“||”把各列连起来 员工的工作是：ename || ‘的工作是’|| job 在slect语句中使用条件逻辑 即用case来解决：格式为 字段， case when then ; when then ; else ; end as 别名 情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”， 复习考题：（P9）要按照工资档次统计人数； 限制返回的行数 用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号； rownum<= 2而不能rownum = 2； 查询某个序号可以这样： select * from (select rownum as sn,emp.* from emp) where rownum <=2) where sn = 2;

从表中随机返回n条记录 先随机排序，再取数据（正确）： select empno,ename from (select empno,ename from emp order by ()) where rownum <=3;先取数据再随机排序（错误）： select empno,ename from emp where rownum <=3 order by (); 模糊查询 通配符主要有“like"、"_"、"%" 如果查询中包含通配符就要用到转义字符： select * from v where vname like '\_like' ESCAPE '\'; 第二章给查询结果排序 以指定的次序返回查询结果 ASC：升序排序，从小到大排序；desc：倒序排序，即从大到小； order by 3 asc:表示按第三列排序：这种排序适用于该列取值不定时或者说经常改变的列，很方便，比如查询的列增加了，而我们只是排序第一列； 按多个字段排序 排序的字段要用","分开，比如：order by A desc, B asc; 按字符串排序 用到了substr（）函数；主要是运用了快速查找顾客的电话的尾号4的顺序； substr(phone_number,-4) 表示后四位； ()函数 translate(expr,from_string,to_string):from_string和to_string以字符为单位，对应字符一一替换； 如果to_string 为空，则返回空值；如果to_string对应的位置没有字符，删除 from_string中列出的字符将会被消掉； 运用： 按数字和字母混合字符串中的字母排序 把重要的东西提取出来，次要的东西踢掉，这道题是对translate的运用； 先构造视图 create or replace view v as select empno || '' || ename as data from emp;

基于ORACLE的查询优化

基于ORACLE的查询优化 一、DBMS查询规则 查询优化是在查询执行引擎生成一个执行策略的过程中，尽量使查询的总开销和总时间达到最小。 任何关系数据库都有一套解决查询的规则，而各种关系数据库查询的过程稍有所区别，但是基本的操作思想和过程是一致的。DBMS的查询规则一般分为以下几个过程：语法分析与翻译处理；查询优化处理；执行。 图1 DBMS查询规则图 1.语法分析与翻译处理 查询处理开始之前，系统必须在扩展关系代数的基础上将

查询语句翻译成可使用的形式，为此这一阶段完成两个主要的功能：一是检查查询语句语法的正确性；二是把查询语句分析成关系数据库能够理解的各个组成部分，构造该查询的语法分析树，并将其翻译成关系代数表达式。 2.查询优化处理 查询优化是为查询选择最有效的查询执行计划的过程。查询优化一方面在关系代数级进行优化，要做的是力图找出与给定表达式等价且执行效率最高的一个表达式。为了在诸多查询执行计划中做出选择，优化器必须估计每个查询执行计划的代价。在没有真正执行查询计划之前，准确计算出查询计划的代价通常是不可能的。因此，优化器要利用各关系的统计信息，来对计划做出最佳估计。 3.执行 执行就是将优化器所选择的执行计划执行，执行引擎从相应的数据库文件中筛选数据，将结果输出。 优化器在选取优化规则时，大多数DBMS(如ORACLE、SYBASE、SQL SERVER等)都是采用基于代价的优化方法，优化器从数据字典中获取许多统计信息，根据不同的算法估计出不同查询规划的代价，然后选择一个较优的规划。

对于ORACLE，在ORACLE7之前，主要是使用基于规则的优化器RULE BASED OPTIMIZATION (简称RBO)。从ORACLE8开始，ORACLE把一个代价引擎(COST ENGINE)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划，即基于代价的优化器COST BASED OPTIMIZATION(简称CBO)。 要注意的是：虽然CBO的功能随着ORACLE新版本的推出，功能越来越强，但它不是能包治百病的神药，否则就不再需要DBA了。 由于一系列因素都会影响语句的执行，优化器综合权衡各个因素，在众多的执行计划中选择认为是最佳的执行计划。然而，应用设计人员通常比优化器更知道关于特定应用的数据特点。无论优化器多么智能，在某些情况下开发人员能选择出比优化器选择的最优执行计划还要好的执行计划，这是需要人工干预数据库优化的主要原因。事实表明，在某些情况下，确实需要DBA对某些语句进行手工优化。 二SQL语句优化步骤 数据库系统的生命周期可分为设计、开发和成品三个阶段。

oracle性能优化总结

Oracle 性能优化50个方法https://www.wendangku.net/doc/0a18535142.html,

1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描,这样的访问方式是效率最低的. b. 通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索 引列的查询就可以得到性能上的提高. 3. 共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE 只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达 成共享,SQL语句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp;

ORACLE性能优化之SQL优化-优化器

Oracle9i优化器介绍 By Davis E-Mail:todavis@https://www.wendangku.net/doc/0a18535142.html, Blog:https://www.wendangku.net/doc/0a18535142.html, 选择合适的优化器目标 默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句 来说可能消耗更多的资源。 优化器产生的执行计划会因―优化器目标‖的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。 例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。 选择优化器目标要以你的应用为基础，一般规则是： 1、对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle 报表应用程序。 2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS 的查询。 影响优化器优化目标的因素主要有： 1、OPTIMIZER_MODE 初始化参数。 2、数据字典中的CBO 统计数据。 3、用来改变CBO 优化目标的Hints。 OPTIMIZER_MODE初始化参数 这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下： Value CHOOSE ALL_ROWS Description 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计 信息存在，则优化器使用基于成本的方法。 2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信 息，优化器仍然会使用基于成本的方法，但是优化器必须为无统 计信息的表利用一些内部信息去尝试其他的统计，比如分配给这 些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则 优化器使用基于规则的方法。 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞 1

ORACLE 性能优化系列

ORACLE SQL性能优化系列 关键字ORA CEL SQL Performance tuning 出处https://www.wendangku.net/doc/0a18535142.html, 1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式:

a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描. b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3. 共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL 语句(有时被称为一个游标)时,如果它 和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须

oracle查询优化改写技巧和案例(学习笔记)

o r a c l e查询优化改写技巧和案例(学习笔记) -标准化文件发布号：（9556-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII

oracle查询优化改写技巧和案例（学习笔记） 第一章 将空值转换成实际值 函数coalesce(exp1,exp2,...) 返回第一个为非空的值，避免了返回空值； 查找满足多个条件的行 用到or：只要达到其中一个条件就可以的； 查询所有的提成的员工：即提成不为空，comm is NOT NULL;这个很好的例子； 用括号把多个条件给分隔开； 在where字句中引用别名的列； 即要引用别名的列时必须是一张表中的字段； select * from (sal as 工资 ,comm as 提成from emp) x where 工资 >2000; 拼接列 用“||”把各列连起来 员工的工作是：ename || ‘的工作是’|| job 在slect语句中使用条件逻辑 即用case来解决：格式为 字段， case when then ; when then ; else ; end as 别名 情景：当工资<2000元时，就返回“过低”，<4000 就返回“过高”， 复习考题：（P9）要按照工资档次统计人数； 限制返回的行数 用rownum是依次对数据做出的标识，是所有的数据取出来后才能确定其序号；rownum<= 2而不能rownum = 2； 查询某个序号可以这样： select * from (select rownum as sn,emp.* from emp) where rownum <=2) where sn = 2;

ORACLE多表查询优化

ORACLE多表查询优化 时间:2009-10-29 13:34:35来源:网络作者:未知点击:156次 ORACLE多表查询优化 这里提供的是执行性能的优化,而不是后台数据库优化器资料: 参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引等优化方案太过简单就不列入了,嘿嘿): 执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了.这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等). 共享的语句必须满足三个条件: A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; B. 两个语句所指的对象必须完全相同: 用户对象名如何访问 Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner 考虑一下下列SQL语句能否在这两个用户之间共享. SQL 能否共享原因 select max(sal_cap) from sal_limit; 不能每个用户都有一个private synonym - sal_limit , 它们是不同的对象 select count(*) from work_city where sdesc like 'NEW%'; 能两个用户访问相同的对象public synonym - work_city select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同. C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)