ITPub博客

Sql优化是有一定难度的工作，需要dba具有扎实的知识和较丰富的经验。在oracle10g以后，引入了一些自动调优技术，包括sql tuning advisor、sql access advisor、ADDM等，不需要了解JOIN的类型、次序，也不需了解优化器，新手也能用这些工具进行一些优化工作。随着自动管理、自动监控技术的发展，今后dba这一职业是否会消失呢？[@more@]本篇介绍如何用sql tuning advisor来创建sql profile对sql进行调优。(一) Sql tuning advisorSql tuning advisor可以在enterprise manager图形界面中调用，也可通过过程包调用。使用方法见后面的例子。以下

在Sql优化（六）中，我们讨论过使用绑定变量的重要性：如果不使用绑定变量而使用常量，会导致大量硬解析。由于硬解析的种种危害，tom认为不使用绑定变量往往是影响oracle性能和扩展性的最大问题一、 错误写法和正确...

我们介绍一些常用的sql调优工具。sql调优工具有很多，大同小异，有些图形工具很好用。但Dba了解基本工具也很重要，一方面便于理解基本原理，另一方面你总不好意思让客户先安装enterprise manager之类的工具先吧。也...

上篇介绍了sqlplus的arraysize和plsql中bulk collect技术对性能的影响，在PLSQL、oci、java、proc等专业开发语言中都有设置数组访问的方法，本篇仅简单介绍，具体请看oracle应用开发文档。一．PLSQL中的bulk binding...

客户端Arraysize的设置实际上可以理解为设置数组的大小来保存sql调用的返回值。设置适当的arraysize有几个好处：1. 减少读data block的次数，也就是consistent gets的次数。假设一个block有n行，如果每次取一行，则...

在分布式环境中，sql访问多个数据库中的表，优化器需要选择一个运算节点，将其他节点的数据传送到运算节点再执行sql。由于本地数据库无法知道远程数据库表的统计信息，因此会采用假设，例如假设远程表的记录数为100...

在分布式环境中经常需要通过dblink在系统之间访问数据。通过dblink，编程人员可以访问在一个sql语句中访问多个数据库中的表，就好像在一个数据库中一样。那么分布式环境中编写sql需要注意什么呢？Dblink的访问对数据...

上篇介绍了几种避免对数据重复扫描的sql写法，本篇介绍利用复杂子查询with as来提高sql性能。先看个例子：WITH dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e JOIN depar...

对数据的读取操作是非常消耗资源的,如何减少对数据的扫描,是提升sql效率的一个重要方面,例如物化视图技术。本篇介绍几种sql写法，分别是CASE expression/DML with returning clause /multitable insert。[@more@]一、 用CASE EXPRESSION将多句查询组合在一起SELECT COUNT (*)FROM employeesWHERE salary < 2000;SELECT COUNT (*)FROM employeesWHERE salary BETWEEN 2000 AND 4000;SELECT COUNT (*)FROM employeesWHERE salary>4000;改写成SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHE

提高程序可扩展性一定要减少各类资源冲突 某批处理进程中有一个步骤，单进程运行10分钟，10个进程时运行超过3小时，即使使用parallel参数也无法提高速读，是cpu资源不足吗？语句如下： [@more@]create table test_table asselect to_number(substr(bpid,1, 11)) ACCOUNT_NO,bill_ref_no,statement_date,seq_bid_row.nextval BILL_INVOICE_ROW,prd_inst_id SUBSCR_NO,main_devno USER_ID,…from BILL_TABLE; （千万记录级别）通过监控发现系统资源并没有非常紧张，而是产生大量row cache lock的等待事件，进一步发现这个等待事件和se

在官方文档《oracle performance tuning guide》中提到Connecting to the database is an expensive operation that is highly unscalable。数据库的连接操作是昂贵的，且难以扩展（支持大量并发）。感觉上一个数据库登录操作是瞬间的事，它有多昂贵呢？简单说，监听器收到远程连接请求后，转给server process；对于每个session数据库都会生成一个server process，要为session分配内存,要进行鉴权、审计…[@more@]另外在一个session中,可以采用一些方法重用游标，例如PLSQL程序，所有static sql都是被cache的，重复调用时不会进行soft par

本篇继续介绍不合理设计导致程序可扩展性差的例子。 数据库运行出账程序时，出现enq: TM - contention等待事件，主要原因是多个进程在insert bill_invoice_*时，使用了insert /*+ append*/这一方式，本来想通过append即direct insert方式提升速度，结果产生表级锁。这样开帐进程相互等待，实际上是变成串行的操作了，反而影响了速度。更严重的是，如果是生产系统，还会导致账单表上所有修改操作都无法进行，影响其他重要业务，这个后果就很严重了。[@more@]Direct insert这一用法有其速度快的优点，但也有其缺点，必须注意适用场合

应用设计不合理导致的session之间的互锁(enqueue)是影响程序可扩展性最常见的原因。此外，一些共享资源的争用，也会导致性能下降。本篇介绍两个由并发insert操作导致的等待事件(wait event)，以及如何通过优化物理设计来进行改善。普通Insert操作本身产生的是行锁，因此进程相互之间不会锁住(enqueue)，但当很多进程insert同一张表时，会有资源上冲突。以下是两个例子：[@more@]1. Buffer busy wait批量进程insert test_table表，当进程多时（超过100个）速度明显下降。在awr报告中看到（等待事件占了93%,cpu占0.95%。也就是说全都在等了，

有时单个sql运行效率还不错，但程序一发布，并发进程多了后，系统就运行很慢，这说明程序的扩展性较差。程序扩展性(scalability)差的原因有很多，例如设计不合理导致的互锁。除了锁之外，硬解析也是一个重要原因。硬解析会造成服务器cpu资源紧张，tom甚至认为不使用绑定变量往往是影响oracle性能和扩展性的最大问题。[@more@]一、 soft parse和hard parse当oracle执行一句sql时，会先在library cache中进行匹配（相同sql是否运行过？），如果匹配成功则直接使用，这称为soft parse;如果匹配失败，则需要parse,name resolved,security-chec

上篇介绍了oracle优化器。尽管oracle优化器很智能，但有时候你想自己选择执行计划，可以通过hint实现。在开发测试环境中，可以通过hint测试不同执行计划的性能。Hint的缺点是增加了管理代码的额外负担，当数据库或环境发生变化时，如果不修改hint，可能导致性能下降。例如，代码中用hint指定索引，但重建索引时索引名变化。因此oracle建议使用hint测试性能后，用其他工具来管理执行计划，如oracle 10g以后的sql tuning advisor或sql plan baseline。但hint仍旧是很常用的优化手段，特别是有些动态sql，表名不固定，就无法使用sql plan等工

对于一句sql，oracle可以有不同的执行方法，例如full table scan或者走索引，nested loop join或者hash join等等。Oracle的优化器负责选择合适的执行计划。1. 什么是优化器：当oracle对Sql进行解析时，优化器用来决定sql的执行计划(execute plan).2. RBO和CBORBO(rule based optimizer)是基于规则的优化器，即优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，如主键优先于普通索引;CBO(cost based optimizer)是基于代价的，这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照表的统计信息（通过analyze得到）

Oracle的并发技术可以将一个大任务分解为多个小任务由多个进程共同完成。合理地使用并发可以充分利用系统资源，提高效率。一、 并发的种类Parallel queryParallel DML(PDML)Parallel DDL Parallel recovery[@more@]二、 适用场合适用parallel的两个条件 1）大的任务，如全表扫描大表这和日常生活中的经验是一样的，小任务自己完成都比派发任务省事 2）系统有足够的资源(cpu/io)换句话说，并发是在系统资源充足、用户少的系统上，为了充分利用系统资源以提高任务处理速度而设计的一种技术。以下是几种场景： 1）OLTP系统 有大量用户和se

当sql访问多个表时，关联对sql效率就有很重要的影响。关联要考虑两个因素，join的类型和join的次序。一、 JOIN的分类(一) Nested loop join1. 适用条件1)关联少量数据(rows)，返回集小2)关联条件能高效访问第二张表(inner table)。高效访问的关联条件如'='，反之非高效的关联条件如'!=','>'等;inner table（即非驱动表）上要有索引。因此比较适合OLTP系统，因为OLTP系统中一般返回数据量小，而且表上面索引较多。[@more@]2. 实现步骤1) 优化器选择驱动表(driving table)，指定其为outer table2) 指定另一张表为inner table(非驱动表)3)

对于sql的执行效率而言，有两个非常重要的因素，一个是索引，另外一个是关联(join)。绝大多数的sql性能问题和不当的索引和关联相关。本篇先说说索引。一、 索引类型1. B* tree index，即普通索引2. 位图索引3. Function based index函数索引其他索引还包括bitmap join index，应用索引等，很少使用。下面介绍这3种最常用索引。 [@more@]二、 B*tree index(一) 索引结构1. 索引结构包括branch block和leaf block（leaf nodes）。最上面的branch block称为root block。Leaf block(叶子节点)包含index key和rowid信息。Root block到leaf bloc