ITPub博客

在分布式环境中经常需要通过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

影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序(CBO)的正确判断等，这些往往和DBA密切相关。纯粹从SQL语句出发，笔者认为影响SQL性能不外乎以下四个重要原因...

Oracle 左外连接、右外连接、全外连接小总结:   1.左外连接：      left outer join 或者 left join      左外连接就是在等值连接的基础上加上主表中的未匹配数据，例：      SELECT *      FROM TESTA       LEFT OUTER...

都是标量子查询惹的祸系统又报了一个跑的慢的sql语句，看图就知道这个很恐怖的，已经跑了1天了，还需要跑6个月的时间， 把sql语句拿出来瞅瞅：--201406 XXselectSUM(UNPOSTING_AMT_CYC)分期未结清余额,SUM(greatest(nvl(BAL_TOTAL,0),0))余额,COUNT(1),DELQ_LEVEL_CI,city,...

 本篇我们继续讨论NO_INVALIDATE参数。从上篇（http://blog.itpub.net/17203031/viewspace-1067312/）讨论情况看，无论是取值true还是false，Oracle进行的行为都是缺乏考量的。如果选择true，表示旧的执行计划会持续的在shared pool中驻留，新的执行计划不会生成，如果系统SQL运行比较频繁、Age Out现象比较...

 Oracle统计量对于CBO执行是至关重要的。RBO是建立在数据结构的基础上的，DDL结构、约束会将SQL语句分为不同的成本结构等级。而CBO是在数据结构的基础上，加入数据表细粒度信息，将成本结构细化为成本cost值。相对于数据表的DDL结构，统计量反映了当下数据表数据分布情况，可变性更强。我们经常遇到这样的场...

生产上有一张 200 万条的表，需要进行 SQL 优化；测试环境中的这张表，里面只有 10 万行的数据。不需要真正插入 190 万条的数据，可以通过DBMS_STATS.SET_TABLE_STATS 这个包进行表统计信息的设置，来模拟大数据量的场景。首先，对测试环境的这张表进行统计信息收集，以得到准确的统计信息BEGIN   DBMS_STA...

1.采用分批操作并不能提高执行速度，执行效率不如单条DML语句。    2.分批插入可以减少对undo空间的占用，但频繁的提交，可能会导致前面提交的undo空间被其他事务占用而可能导致ORA-0155错误。    3.若分批操作中间半截失败掉，会将你数据库置于一种未知的状态。（DELETE操作不会出现这种情...