[分享知识 原创连载]Oracle Database 开发中的我个人使用的些许优化方法

davidma

个人简介： 毕业三年，主要在某港资企业从事Oracle EBS的开发工作
连载主题：分享成长路上的阳光雨露（Oracle EBS，Oracle Database和心情等等）
开贴日期： 希望8月中旬
计划发布在那个板块:不确定
更新周期（每天/每周/每两周/每月）：希望每周一贴
本连载的亮点是：优化
其他要说的话：只是个人在开发过程中使用一些方法，希望和大家分享、学习(这是我在2012年工作中总结的一些方法)

PKM_OracleOptmize_David_2012112

  优化的方法很多，而且也由于每个人的使用习惯不同，因此，在平时的开发过程中，所想所思都是有所不同。在平时的工作中，也经常的出现数据量很大的报表开发，因此，对于程序开发过程中的优化，也是考虑得最多的事情。

1.     在组织表集合（From）的时候，尽可能的把记录行数少的放到后边。

2.     在过滤条件(Where)中，能够最大程的过滤掉的条件放到最后。

3.     尽可能的使用索引（包括函数索引），学会使用PLSQL，TOAD查看其是否使用索引（当然有些时候在真正执行的时候，也许也没能真正的走所想要的索引）。

4.     学会使用Exists，In，Or等，以及之间的不同、性能等。

5.     在开发报表的过程中，可以使用临时表(Global Temporary Table)，这种表在平时的开发过程中很有益处，可建立索引而优化，可使用Truncate命令而代替Delete，也是会话级别的表，在并发多用户应用程序中也是很好的选择（当然Truncate也不是所有表都可以使用）。

6.     有时候可以考虑使用ROWID来解决一些问题，算是最快的一种方法吧。

7.     有时候，如果可以的话（如从一个表中只是查询出一个栏位等），不用关联表的方式，而是直接作为一子Select查询放在Select和From之间，即可以当作函数来使用，这样做有时候反而更快(select colum1,columb,…(select column_c from table_name) columc  fromtable_list)。

8.     使用批量处理方法，尤其是循环执行相同的动作的时候，可以考虑这种方法(Bulk Bind批量绑定)

8.1  Input Collection，Use the FORALLstatement. 一般用来改善DML(INSERT,UPDATE和DELETE)操作。

8.2  Output Collection，Use the BulkCollect。一般用来提高查询性能(SELECT)的性能。这种方法在处理大量数据的时候，效果还很明显的。

以下是Bulk Bind的一个例子：(Insert,Update,Delete都是一样的使用方法)

  --Global Type

  TYPE num_type IS TABLE OF NUMBER(20, 10) INDEX BY BINARY_INTEGER;

  TYPE num_type2 IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;

  TYPE var_type IS TABLE OF VARCHAR2(2000);

TYPE date_type IS TABLE OF DATE;

   v_sql         VARCHAR2(100) := 'TRUNCATETABLE VCPC_COMM_TEMPORARY_TABLE';

   customer_id     num_type2;

   customer_desc   var_type;

   product_code    var_type;

   scm_month_id    num_type2;

   month_id         num_type2;

   half_year_desc var_type;

   fcst_qty         num_type;

   fcst_amt         num_type;

  BEGIN

    --

    EXECUTE IMMEDIATE v_sql;

    --

    SELECT --/*+index(c index_name)*/

     b.customer_id,

     b.customer_desc,

     a.product_code,

     c.fiscal_month_id scm_month_id,

     substr(c.fiscal_month_id, 5, 2) month_id,

     CASE

       WHEN 4 <= substr(c.fiscal_month_id, 5, 2) AND

            substr(c.fiscal_month_id,5, 2) <= 9 THEN

        'LAST_HALF_YEAR'

       ELSE

        'NEXT_HALF_YEAR'

     END half_year_desc,

     SUM(a.closed_qty) fcst_qty,

     SUM(a.closed_amt * a.exchange_rate *e.exrate) fcst_amt BULK COLLECT

      INTO customer_id,

           customer_desc,

           product_code,

           scm_month_id,

           month_id,

           half_year_desc,

           fcst_qty,

           fcst_amt

      FROM table_name_a   a,

            Talbe_name_b   b,

            Table_name_c  c,

            Table_name_d  d,           

Table_name_e   e

     WHERE 1 = 1

       AND a.customer_billto_id =b.customer_billto_id

……………………………………

     GROUP BY b.customer_id,

              b.customer_desc,

              a.product_code,

              c.fiscal_month_id;

  

    IF SQL%ROWCOUNT> 0 THEN

      FORALL i IN customer_id.first .. customer_id.last

        INSERT INTO vcpc_comm_temporary_table itable

          (itable.batch, --     v_batch_org,

           itable.floor_id, --a_rec.customer_id,

           itable.floor_desc, --a_rec.customer_name,

           itable.line_desc, --a_rec.product_code,

           itable.num1, --a_rec.scm_month_id,

           itable.num2, --a_rec.month_id,

           itable.str1, --half_year_desc,

           itable.num3, --a_rec.fcst_qty,

           itable.num4 --a_rec.fcst_amt

           )

        VALUES

          ('Billing_Actual_Data',

           customer_id(i),

           customer_desc(i),

           product_code(i),

           scm_month_id(i),

           month_id(i),

           half_year_desc(i),

           fcst_qty(i),

           fcst_amt(i));

END IF;

当然我想也可以通过Record_Type来简化些，如：

  Procedure Test_Bulk_Bind_With_Record Is

    Type Rec_Type Is Record(

      Item_Id     Number(10),

      Prod_m_Code Varchar2(100),

      Hs          Number(10),

      Bs          Number(10),

      Headset     Number(10));

    Type Rec_Type_Tab Is Table Of Rec_Type Index By Pls_Integer;

    v_Rec_Tab Rec_Type_Tab;

  Begin

    Select Lp.Inventory_Item_Id,

          Nvl(Lp.Product_Model_Code, ' '),

           Nvl(Lp.Num_Of_Handsets, 0),

           Nvl(Lp.Num_Of_Base, 0),

           Nvl(Lp.Headset, 0) Bulk Collect

      Into v_Rec_Tab

      From Lu_Product Lp

     Where Lp.Company_Id = 84

       And Upper(Lp.Product_Status) <> 'INACTIVE'

       And Not Exists

     (Select Distinct Inventory_Item_Id

              From Lu_Product Xt

             Where Xt.Company_Id = 84

               And Num_Of_Base = 0

               And Num_Of_Handsets = 0

               And Headset = 0

               AndXt.Inventory_Item_Id = Lp.Inventory_Item_Id);

  

    Forall i In 1 .. v_Rec_Tab.Count

      Insert Into Vcpc_Comm_Temp_Table

        (Item_Id, Floor_Desc, Num1,Num2, Num3)

      Values

        (v_Rec_Tab(i).Item_Id,

         v_Rec_Tab(i).Prod_m_Code,

         v_Rec_Tab(i).Hs,

         v_Rec_Tab(i).Bs,

         v_Rec_Tab(i).Headset);

  

    Commit;

  End Test_Bulk_Bind_With_Record;

这个数量有近20万，0.3s可以完全处理了。

davidma

谢谢四海兄的鼓励，希望通过分享方式，至少能够进一步梳理自己的知识结构.

davidma

个人简介： 毕业三年，主要在某港资企业从事Oracle EBS的开发工作
连载主题：分享成长路上的阳光雨露（Oracle EBS，Oracle Database和心情等等）
开贴日期： 希望8月中旬
计划发布在那个板块:不确定
更新周期（每天/每周/每两周/每月）：希望每周一贴
本连载的亮点是：话说Null
其他要说的话：最近打杂的事太多了，搞得没怎么有时间来这里逛逛啊(这时我平时工作中遇到到得最多有有关Null的故事，分享出来相互学习)        more details please references to the attachement.

   

PKM_NullInOracle_David_201308023

   Null在Oracle中总是戴着神秘的面纱，也总是像变色龙一样，和谁在一起它就变成谁,所以有时候可以说Null没有所谓的数据类型。 有时候Null是Number数据类型，有时候Null是Varchar2数据类型，而更有时候Null就是一条语句，所以在开发过程中，务必要特别的留意下Null的存在，不然一不小心就有可能陷入到了Null布下的“天罗地网”中了.

Null 在运算中--------这个问题很常见，也需要注意

在我平时的工作中，更多的时间由PL/SQL组织代码，然后以报表或邮件的方式将信息呈现给相应的用户。 但是问题是由于当初在设计表的时候没指定说某些栏位不能为Null，因而在做有关栏位的运算时，如果不做加以控制便得不到我们所想要的结果了,如:Num1+num2 有时候是不等于nvl(num1,0)+num(num2,0)

Select e.Empno,

      e.Ename,

       e.Sal,

      e.Comm,

       e.Sal +e.Comm Total_Sal_a,--结果A

       e.Sal + Nvl(e.Comm,0)Total_Sal_b--结果B

  FromScott.Emp e

------结果显示

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

所以在平时的工作中，养成一个良好的编程习惯是最好一种解决方法。如果Null不处理，那么很多时候我们得不到数据，也很难查询到底哪里出了问题。

NULL在索引中-----有时候索引失败而效率降低

--Create Table

Create Table test_ta

(a    Number,b Number);

--Create index

Create Unique Index test_ta_n1On test_Ta(a,b);

--Insert Data

Insert Into Test_Ta Values (Null, Null);

Insert Into Test_Ta Values (1, Null);

Insert Into Test_Ta Values (Null, 1);

--commit

Commit;

----

Begin

Dbms_Stats.Gather_Table_Stats(Ownname => User, Tabname=> 'TEST_TA');

  Dbms_Stats.Gather_Index_Stats(Ownname=> User, Indname => 'TEST_TA_N1');

End;

---Execution Plan

---主要是查看索引使用情况

--Execution Plan

Set autotrace On Explain;

Set Null UNKNOWN;

Select * FromTest_Ta Where a Is Null;

---显示Execution Plan

Select * FromTest_Ta Where a Is Not Null;

---显示Execution Plan

由此可以知当我们的条件为a is null时，不会走索引;而a is not null 走了索引。再换一种思路看看情况如何？

--Create Table

Create Table test_ta

(a Number,

b Number Not Null

);

Insert Into Test_Ta Values (1, 1);

Insert Into Test_Ta Values (Null, 1);

-----

Begin

  Dbms_Stats.Gather_Table_Stats(Ownname => User, Tabname=> 'TEST_TA');

  Dbms_Stats.Gather_Index_Stats(Ownname => User, Indname=> 'TEST_TA_N1');

End;

--Execution Plan

Select * FromTest_Ta Where a Is Null;

---显示Execution Plan

现在a is null 也走索引了。原因:Oracle中最基本的索引是B*Tree索引，这种索引不会存储全为NULL的列比如我们在前面所做测试，

所以如果所建立的索引列有可能为Null(复合索引列全为Null)，那么在我们的查询中不会走索引而影响我们的效率,而达不到我们所想要

的优化的效果。但归根结底还是如何建表的问题，所以在建立表的时候，需要充分考虑这些最基本的问题（如Not Null问题），

当然如果在Null在列上继续使用索引，那么就引出了另外一个话题---函数索引。

Null处理中的Case Statement和Decode function.

例子1：

Select Case Null

         When Null Then

          'NULL'

         Else

          'NOTNULL'

       End Results

  From Dual;

----结果是:NOT NULL

Select Case

         When Null Is Null Then

          'NULL'

         Else

          'NOTNULL'

       End Results

  From Dual;

-----结果是：NULL

-----只是有点稍微的不同，但是结果还是存大很大的差异。

Select Decode(Null, Null, 'NULL', 'NOTNULL') From Dual;

-----结果是：NULL

说明：简单的Case Statement不能匹配Null，而Decode可以。

例子2：

With Test_Ta As

(Select Level Id From Dual Connect By Level < 10)

Select Id,

       Case

         When Id < 5 Then

          'low'

         When Id < 8 Then

          'mid'

         Else

          'high'

       EndResults

  From Test_Ta;

Search Case Statement可以匹配这这种非等值的（范围）的Null进行判断，但是如果要Decode来实现这种对非等值或者范围的Null进行判断就有些难了，可是就需要在用Decode之前数据要先处理下！

davidma

个人简介： 毕业三年，主要在某港资企业从事Oracle EBS的开发工作 连载主题：分享成长路上的阳光雨露（Oracle EBS，Oracle Database和心情等等） 开贴日期： 希望8月中旬 计划发布在那个板块:不确定 更新周期（每天/每周/每两周/每月）：希望每周一贴 本连载的亮点是：Group by for Rollup 其他要说的话：以前学过使用Rollup，但一直没总结，希望在开发过程序中能够简化程序，优化程序。

PKM_GroupbyRollup_David_20130828

  

在我们的平时开发过程中，所谓的汇总大部分都是标准分组汇总(GroupBy),然后为了将不同层次的统计结果显示在一起，我们可以先进行各自汇总，然后用Union ALL联连在一起，但是，如果说数据量很大的情况下，这样的做法或许在性能方面存在问题，效率不高而导致很慢，那么这时候我们可以选择用另外的一种方法来处理---Rollup。

关键点:Rollup具有方向性，先按标准分组汇总，再按从右到左的顺序进行汇总，不同的顺序，代表不同的汇总方式，也就是不同的结果。

这里有一个题目是这样的

表B

C1         c2

2005-01-01 1

2005-01-01 3

2005-01-02 5

要求的处理的结果数据

2005-01-01 4

2005-01-02 5

合计 9

试用一个sql语句完成

问题：你会如何来完成？是不用Union ALL呢？如果就数据量很大的话，那将又如何，性能会又如何呢？

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

--需求

--1.每个部门每个职位的薪水和

--2.每个部门所有职位的薪水小计

--3.统计所有部门所有职位的薪水和

采用最简单的方法做完成任务。

--Solution 1: 使用Union ALL来解决问题(最基本而简单的方法)

--每个部门每个职位的薪水和

Select

--D.Deptno,

D.Dname,

E.Job,

sum(E.Sal)sum_sal--sal_by_job

From EmpE,Dept D

Where E.Deptno= D.Deptno

group by --D.Deptno,

D.Dname,

E.Job

union all

--每个部门所有职位的薪水小计

Select

--D.Deptno,

D.Dname,

Null Job,

sum(e.sal)sum_sal--sal_by_dept

From Emp E,Dept D

where e.deptno= d.deptno

Group By

--D.Deptno,

D.Dname

union all

--统计所有部门所有职位的薪水和

Select

--Null Deptno,

Null Dname,

Null Job,

sum(sal)sum_sal--sal_by_all

From EmpE,Dept D

Where E.Deptno= D.Deptno;

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

采用Rollup来完成任务

--Solution 2： 使用ROLLUP来解决----普通的ROLLUP分组

--select ...... group byrollup(grouping_column_reference_list)

--方向性:先按标准分组，然后从右到左的顺序进行小计和合计

Select

D.Dname,

E.Job,

sum(E.Sal)sum_sal

From EmpE,Dept D

Where E.Deptno= D.Deptno

group by rollup(D.Dname,E.Job);

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

再用Rollup来完成另外一个任务

--需求--1. 计算每个入职时间(年)、部门、职位的标准分组的薪水和.

--2. 计算每个入职时间(年)、部门的所有职位的薪水小计.

--3. 计算每个入职时间(年)的所有部门的薪水小计.

--4. 最后合计薪水，显示入职时间(年)、部门名、职业名.

Select

To_Char(E.Hiredate,'yyyy')Hireyear,

D.Dname,E.Job,

sum(e.sal)sum_sal

From Emp E,Dept D

Where E.Deptno= D.Deptno

Group By Rollup(To_Char(E.Hiredate,'yyyy'),D.Dname,E.Job);

总结：用一个Rollup就简化了SQL语句，而且也在性能上也会有所提升，特别在数据量很大的情况下。因此，有时候，可以使用Rollup来处理些问题可能是较好的选择。

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

Rollup结果和列的顺序有关，顺序不同则结果不同。Rollup中指定了n列，则根据rollup的分组过程可以知道，分组方式有n+1种。

比如：

group by rollup(a,b,c)那么就有以下的几种汇总方式了:

1. group by a,b,c -----标准汇总

2. group by a,b

3. group by a

4  group by null

davidma

PKM_Oracle[color=#444444 !important]EBSFormLOVSQL_David_20120829

不属于原创，只是经过了收集整理！欢迎指正！

<span .="font-family:宋体;mso-ascii-font-family:" ti在Oracle EBS中，如何从Form界面中知道或获取后台所运行的相关的SQL语句。

方法一：

在EBS界面的菜单上Help--------diagnostic----examine----- Block:System

                                                  Field: Last_Query

                                                  Value:_this is what yuou want__

这个方法基本能够捕捉到大部分的SQL，但是前提是当前的这个From或LOV刚过行了查询事件。

之前在一次面试中问到相关的方法，只是那个时候自己还不知道这个方法。

比如：想要找到PO对应的Tax相关的取数SQL，在Oracle EBS 11i里可能不是那么容易，因为弹出来的Tax界面已经另外一个Form上了(R12好像是显示在HTML上，不太记得了)，我想即使把相关的From下载来分析也不是好办法，但是前面的这种方法却很轻松的获取想要的SQL语句。

方法二:

  查找LOV的SQL脚本的方法。

A.     get the session Id(SID) from the form

help-------about oracle application-------session Id

B.      open lov to fire the trigger

C.      get the lov sql with the flowing sql statement.

SELECT

s.pre_sql_addr,s.sql_addr,t.sql_text

FROM v$sqltext_with_newlines   t,

      V$session               s

WHERE 1 = 1

  And t.address = s.prev_sql_addr

  And s.sid = v_SID(we get the SID from oracle form);

稍微的组织下就是想要的SQL。

总结：

1.       方法一是很实用的方法，也应该是最为常用的，值得推荐。

2.       方法二对于LOV来说，最为适合的一种方法。

注：使用方法二的时候，触发了LOV查询后，不要关闭它，然后用SID来查询。有时候需执行以下的代码才能查询出数据：

Begin

   dbms_application_info.set_client_info(84);--organization_id

End;

davidma

PKM_BindVariableWithPerformanceInOracle_David_20130322

    PL/SQL编程之绑定变量问题。

    在Oracle中存在硬解析和软解析的问题，也存在锁尤其是轻量级锁-----闩(虽然说是轻量级锁，但是也不能忽视它的存在，否则在自己和开发过程中，

尤其在性能上会带来不想要的结果。)

  个人建议，如果可能的话，尽可能的用绑定变量法，不管是从性能上，可读性，可维护性来说，都是略胜一筹的。

例子：测试使用绑定变量和不使用绑定变量的方法在性能上的影响。

--Create the test table

CREATE TABLEtest_ta(x NUMBER);

--Create two different procedures

--binding variable----软解析

CREATE OR REPLACE PROCEDURE procl

AS

BEGIN

  FOR i IN 1..10000

    LOOP

      EXECUTE IMMEDIATE

      'insertinto test_ta values(:x)' USING i;

    END LOOP;

END;  

--without binding variable----硬解析

CREATE OR REPLACE PROCEDURE procl2

AS

BEGIN

  FOR i IN 1..10000

    LOOP

      EXECUTE IMMEDIATE

      'insertinto test_ta values('||i||')';

    END LOOP;

END;  

EXECrunstats_pkg.Rs_Start;

EXEC PROCL;

EXECrunstats_pkg.Rs_Middle;

EXEC PROCL2;

EXECrunstats_pkg.Rs_Stop;

结果图显示：

从简单的两者的执行时间来说，也就可以明显的看出，没有采用绑定变量的过程，是采用了绑定变量的方法的近7倍时间。然后在两者的所占时间比例中，没有采用绑定变量的方法的占了80%还多。如果数据量更大，那么两者之间的差距也就越来越大，也就能更好的看出绑定变量的性能上的优化。硬解析处理每个插入总是高于软解析。

因此，在Oracle的开发过程中，如果可能的话，尽量采用绑定变量的方法，这样能更做优化以及简化程序，也应该是减少程序的冗余度。想要同时硬解析的语句的用户越多，性能问题，就会变得越来越严重。虽然闩是轻量级的锁，但不能忽视它的存在。执行的解析越多，对共享池的闩的竞争也就越来越厉害，队列也就排得越长，等待的时间也就越久，所以速度也就会越慢。

从长远来看，要尽可能的使用绑定变量，而在需要时候才使用常量，这才是正确的做法。

在整个开发过程中，你都要把性能作为一个目标精心设计，合理地

构建，并且不断地测试。

有时候涉及到最快的方法插入数据，也可能需要想到这种绑定变量的方法。

如：有个表a(x number(20),y number(20))用最快速高效的SQL向该表插入从1开始的连续的1000万记录。

可以自己测试下，随着数据量的递增，两者的差距也会越来越显著！！！