最近集中进行了一系列SQL优化,主要通过ADDM和AWR对捕捉COST或物理读、逻辑读很高的语句,问题的主要原因是全表扫描。但提交开发后开发反馈找不到SQL语句,因为属于存储过程。其实通过EDITPLUS全目录检索肯定能够找到该语句的位置,但为了便于日后定位,笔者还是通过ORACLE的dba_source视图进行了步骤。
什么是dba_source字典视图:
存储所有函数、过程、程序包和Java源的完整的文本,对于你了解一个数据库对象的源代码很有用。存储过程在该视图中是以行为单位进行存储的。
SAMPLE:
问题sql语句
Select Count(*)
From Sample_Con_Sta_No Cs
Where Cs.Deleted_Flag = 0 And (Cs.In_Out_Group_Status != '1' And Cs.In_Out_Group_Status != '2') And
Cs.Con_Id In
(Select Con_Id
From Sample_Ent_Con Ec
Where Ec.Ent_Id In (Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id = :B1))
定位方法:
截取“Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id”去视图查询(注意dba_source视图中存储的是行为单位拆分的存储过程等对象,关键词太长肯定找不到)
查询语句:
Select * From dba_source Where text Like '%Select Ent_Id From Sample_Ent_Con Ecc%'
查询结果:
OWNER
NAME
TYPE
LINE
TEXT
SAMPLE
PKG_SAMPLE_CON_STATUS
PACKAGE BODY
35
(Select Ent_Id From Sample_Ent_Con Ecc
Where Ecc.Con_Id = v_Con_Id));
分析:
该语句在PKG_SAMPLE_CON_STATUS的35行
确认:如下存储过程35行确实有该行内容
CREATE OR REPLACE Package Body Pkg_Sample_Con_Status Is
-- Private type declarations
-- Private constant declarations
-- Private variable declarations
-- Function and procedure implementations
Function Func_Is_Over_Clearance_State(v_Con_Id In Number, v_Clearance_State In Varchar2) Return Number Is
v_Num Number;
Begin
Select Count(*)
Into v_Num
From Sample_Ent_Status_Note
Where Deleted_Flag = 0 And
Ent_Id In (Select Ent_Id From Sample_Ent_Con Where Con_Id = v_Con_Id) And
Clearance_State < v_Clearance_State;
If v_Num > 0 Then
Return 0;
Else
Return 1;
End If;
End Func_Is_Over_Clearance_State;
-- 判断
Function Func_Is_All_Con_In(v_Con_Id In Number) Return Number Is
v_Num Number;
Begin
Select Count(*)
Into v_Num
From Sample_Con_Sta_No Cs
Where Cs.Deleted_Flag = 0 And (Cs.In_Out_Group_Status != '1' And Cs.In_Out_Group_Status != '2') And
Cs.Con_Id In
(Select Con_Id
From Sample_Ent_Con Ec
Where Ec.Ent_Id In
(Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id = v_Con_Id));
If v_Num > 0 Then
Return 0;
Else
Return 1;
End If;
End Func_Is_All_Con_In;
什么是dba_source字典视图:
存储所有函数、过程、程序包和Java源的完整的文本,对于你了解一个数据库对象的源代码很有用。存储过程在该视图中是以行为单位进行存储的。
SAMPLE:
问题sql语句
Select Count(*)
From Sample_Con_Sta_No Cs
Where Cs.Deleted_Flag = 0 And (Cs.In_Out_Group_Status != '1' And Cs.In_Out_Group_Status != '2') And
Cs.Con_Id In
(Select Con_Id
From Sample_Ent_Con Ec
Where Ec.Ent_Id In (Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id = :B1))
定位方法:
截取“Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id”去视图查询(注意dba_source视图中存储的是行为单位拆分的存储过程等对象,关键词太长肯定找不到)
查询语句:
Select * From dba_source Where text Like '%Select Ent_Id From Sample_Ent_Con Ecc%'
查询结果:
OWNER
NAME
TYPE
LINE
TEXT
SAMPLE
PKG_SAMPLE_CON_STATUS
PACKAGE BODY
35
(Select Ent_Id From Sample_Ent_Con Ecc
Where Ecc.Con_Id = v_Con_Id));
分析:
该语句在PKG_SAMPLE_CON_STATUS的35行
确认:如下存储过程35行确实有该行内容
CREATE OR REPLACE Package Body Pkg_Sample_Con_Status Is
-- Private type declarations
-- Private constant declarations
-- Private variable declarations
-- Function and procedure implementations
Function Func_Is_Over_Clearance_State(v_Con_Id In Number, v_Clearance_State In Varchar2) Return Number Is
v_Num Number;
Begin
Select Count(*)
Into v_Num
From Sample_Ent_Status_Note
Where Deleted_Flag = 0 And
Ent_Id In (Select Ent_Id From Sample_Ent_Con Where Con_Id = v_Con_Id) And
Clearance_State < v_Clearance_State;
If v_Num > 0 Then
Return 0;
Else
Return 1;
End If;
End Func_Is_Over_Clearance_State;
-- 判断
Function Func_Is_All_Con_In(v_Con_Id In Number) Return Number Is
v_Num Number;
Begin
Select Count(*)
Into v_Num
From Sample_Con_Sta_No Cs
Where Cs.Deleted_Flag = 0 And (Cs.In_Out_Group_Status != '1' And Cs.In_Out_Group_Status != '2') And
Cs.Con_Id In
(Select Con_Id
From Sample_Ent_Con Ec
Where Ec.Ent_Id In
(Select Ent_Id From Sample_Ent_Con Ecc Where Ecc.Con_Id = v_Con_Id));
If v_Num > 0 Then
Return 0;
Else
Return 1;
End If;
End Func_Is_All_Con_In;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-2122138/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29289867/viewspace-2122138/