壹佰网|ERP100 - 企业信息化知识门户

 找回密码
 注册

QQ登录

只需一步,快速开始

查看: 2310|回复: 10

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

[复制链接]
发表于 2013/8/15 14:01:02 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ: 83569622  。

您需要 登录 才可以下载或查看,没有帐号?注册

x
个人简介: 毕业三年,主要在某港资企业从事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可以完全处理了。

点评

对开发pl/sql有几条特别有指导意义;  发表于 2013/8/16 20:40
都是干货噢;期待后文;  发表于 2013/8/16 20:39
分享是一种态度;  发表于 2013/8/15 22:22
敢写你的分享  发表于 2013/8/15 14:08

本帖被以下淘专辑推荐:

 楼主| 发表于 2013/8/16 08:37:30 | 显示全部楼层
谢谢四海兄的鼓励,希望通过分享方式,至少能够进一步梳理自己的知识结构.
 楼主| 发表于 2013/8/23 21:31:04 | 显示全部楼层
本帖最后由 davidma 于 2013/8/23 21:40 编辑

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

   
PKM_NullInOracle_David_201308023
   NullOracle中总是戴着神秘的面纱,也总是像变色龙一样,和谁在一起它就变成谁,所以有时候可以说Null没有所谓的数据类型。 有时候NullNumber数据类型,有时候NullVarchar2数据类型,而更有时候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 StatementDecode 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之前数据要先处理下!



PKM_NullInOracle_David_201308023.doc

130 KB, 下载次数: 0, 下载积分: 努力值 -5 点

NullInOracle

 楼主| 发表于 2013/8/30 12:00:09 | 显示全部楼层
本帖最后由 davidma 于 2013/8/30 12:14 编辑

个人简介: 毕业三年,主要在某港资企业从事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


 楼主| 发表于 2013/9/6 09:28:37 | 显示全部楼层

在Oracle EBS中,如何从Form界面中知道或获取后台所运行的相关的SQL语句

本帖最后由 davidma 于 2013/9/6 09:34 编辑

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;


点评

值得学习  发表于 2013/9/6 09:37
 楼主| 发表于 2013/9/16 10:00:15 | 显示全部楼层
本帖最后由 davidma 于 2013/9/16 10:03 编辑

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万记录。

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



您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|Archiver|小黑屋|手机版|壹佰网 ERP100 ( 京ICP备19053597号-2 )

Copyright © 2005-2012 北京海之大网络技术有限责任公司 服务器托管由互联互通
手机:13911575376
网站技术点击发送消息给对方83569622   广告&合作 点击发送消息给对方27675401   点击发送消息给对方634043306   咨询及人才点击发送消息给对方138011526

GMT+8, 2022/6/29 15:08 , Processed in 0.034159 second(s), 10 queries , File On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表