sql语句优化.sql

oracle sql语句优化 hawk 收集

SQL> @d:\am\evaluate_profiler_results.sql 
Enter value for runid: 8 
Enter value for name: am_perf_chk 
Enter value for owner: scott 
      Line      Occur       Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
         1                       procedure am_perf_chk (pi_seq     in            number,
         2                                              pio_status in out nocopy varchar2) is
         3          2   43.05965   l_dat date := sysdate;
         4                       begin
         5          1   86.35732   if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
         6          0          0     pio_status := 'OK';
         7                         else
         8          1   8.416151     pio_status := 'Invalid tape loaded';
         9                         end if;
        10                       exception
        11                         when others then
        12          0          0     pio_status := 'Error in am_perf_chek';!
        13          1   2.410361 end;
13 rows selected.
 
Code% coverage
--------------
    66.6666667
 

4.         正如你看到的,第三行执行时间提高到86毫秒.但是改变if语句,重新执行上面的过程,将会得到新的结果:

                          Line      Occur       Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
         1                       procedure am_perf_chk (pi_seq     in            number,
         2                                              pio_status in out nocopy varchar2) is
         3          2  17.978816   l_dat date := sysdate;
         4                       begin
         5          1   8.419503   if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then
         6          0          0     pio_status := 'OK';
         7                         else
         8          1   7.512684     pio_status := 'Invalid tape loaded';
         9                         end if;
        10                       exception
        11                         when others then
        12          0          0     pio_status := 'Error in !am_perf_chek';
        13          1    .731657 end;
13 rows selected.
 
Code% coverage
--------------
    66.6666667
 
5.         正如你看到的, 这种情境下第三行执行时间从86毫秒减少到8毫秒,多余的时间是由于内置trunc()函数引起., 
这种情境下如果第一个条件为false,则不会执行trunc()函数.这仅仅是个简单的例子,当你测试的例程越大,你面临的挑战更大.

这个配置结果也证明了执行期间代码被覆盖多少行,从而让我们知道处于性能监视中的代码范围。如果任何PL/SQL块性能出现问题，
它也能提炼出各种不同情景的正在在执行的代码并检查配置结果，从而查明问题所在。

6.         对于一个特定的情景，如果执行一段特殊的代码段，可以得到合理的分析，即使代码根本一点都不能运行。

 

环境的创建

       默认安装或数据库的创建状态下，DBMS_PROFILER包不会自动安装，请DBA用profload.sql脚本创建它.
       用一个权限较大的或一个单独的用户，创建存储统计信息的表。如果

用如SYS用户创建，则给其它用户授予DML权限，并且对这些表创建一个共同的简写名.

 

       创建表的如下:

       PLSQL_PROFILER_RUNS表:PL/SQL配置的运行细节.

       PLSQL_PROFILER_UNITS表:运行中每一个库单元的信息.

       PLSQL_PROFILER_DATA表:所有配置文件运行时的数据累积.

       PLSQL_PROFILER_RUNNUMBER序列提供了RUNID

 

运行和解释配置数据

       ORACLE提供了三个表来统计，填充RUNID。有许多第三方的工具可以提供自定义的基于这些数据的报告，
       ORACLE提供profrep.sql脚本评估数据（在<oracle_home>\plsql\demo\目录下）,下面的两个简单脚本就是上面用到的，
       用来检查程序单元的执行时间.执行时间以毫秒存储

            -----------------------------------------------------------
Script: call_profiler.sql
-----------------------------------------------------------
set head off
set pages 0
select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
from   dual;
 
--< place your routine in the below block >-- 
declare
  l_status varchar2(200);
begin
  am_perf_chk(2, l_status);
  dbms_output.put_line(l_status);
end;
/
 
select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
from   dual;
select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
from   dual;
select 'runid:' || plsql_profiler_runnumber.currval 
from   dual;
set head on
set pages 200
 
-----------------------------------------------------------
Script: evaluate_profiler_results.sql
-----------------------------------------------------------
undef runid
undef owner
undef name
set verify off
select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from   all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
                             d.total_occur, d.total_time/1000000 total_time
                      from   plsql_profiler_data d, plsql_profiler_units u
                      where  u.runid = &&runid
                      and    u.runid = d.runid
                      and    u.unit_number = d.unit_number) p
where  s.owner = p.unit_owner (+)
and    s.name = p.unit_name (+)
and    s.type = p.unit_type (+)
and    s.line = p.line# (+)
and    s.name = upper('&&name')
and    s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from  (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')) total,
     (select count(1) cnt
      from plsql_profiler_data d, plsql_profiler_units u
      where d.runid = &&runid
      and u.runid = d.runid
      and u.unit_number = d.unit_number
      and u.unit_name = upper('&&name')
      and u.unit_owner = upper('&&owner')
      and d.total_occur > 0) exec;
undef runid
undef owner
undef name

 

结论

 DBMS_PROFILER是非常强大的工具，其一就是可以识别PL/SQL的性能问题.这个工具最好用在开发时期,用来调整基于各种应用的情景的代码，
 它也能用很好的调整已在使用中的例程并且采取显而易见的时间去执行。总之，这个工具可以给每一行代码给予性能统计，
 它可以帮助我们评估和调整到一个出色的水平,当检查SQL语句的性能问题时，PL/SQL代码不应该忽略，相反应该调整到最佳的结果.


--ERP系统常用SQL集锦(转, 一个网友写的,感觉有点虎头蛇尾,不过还是有价值参考) 
查看关键用户
得到员工的部门和成本中心
导出AR退款的SQL
基于gl_balances 的部门费用SQL

有查询User具体权限的SQL吗?



查看关键用户
select distinct login_name
from (select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.RESPONSIBILITY_NAME not in
('员工自助 (1)')
order by c.description, c.user_name, a.responsibility_id) a
; 
得到员工的部门和成本中心
select a.full_name as employee_name,
c.name as hr_depart,
cux_hr11_report.getDepartmentNameByName(a.full_name) as account_name
from (select *
from apps.PER_PEOPLE_f a
where a.PERSON_TYPE_ID = 6
and a.LAST_NAME not like '虚拟%'
and sysdate > a.EFFECTIVE_START_DATE
and sysdate < a.EFFECTIVE_END_DATE) a,
per_assignments_f b,
hr_all_organization_units_tl c
where a.PERSON_ID = b.ASSIGNMENT_ID
and b.ORGANIZATION_ID = c.organization_id
and c.language = 'ZHS'
and sysdate > b.EFFECTIVE_START_DATE
and sysdate < b.EFFECTIVE_END_DATE
order by c.name



导出AR退款的SQL

begin
fnd_client_info.set_org_context(85);
end;
select tt.customer_id,
tt.customer_name,
tt.customer_number,
tt.gl_date as shoukuan_date,
t.GL_DATE as tuikuan_date,
t.AMOUNT_APPLIED,
t.TRX_NUMBER,
tt.receipt_number
from AR_RECEIVABLE_APPLICATIONS_V t, AR_CASH_RECEIPTS_V tt
where t.TRX_NUMBER = 'Receipt Write-off'
and t.CASH_RECEIPT_ID = tt.cash_receipt_id
and t.GL_DATE > to_date('20050101', 'YYYYMMDD')
and t.GL_DATE < to_date('20050331', 'YYYYMMDD')
;
基于gl_balances 的部门费用SQL
这是一个统计某个会计科目在某个会计期内的费用合计，在SQL中有
and b.segment2 = p_department_id
and b.segment3 = p_account_id
这即是假设你的segment2 为部门段，segment3为会计科目
budget_version_id为预算的ID，可以用select * from gl.gl_budgets t确认相应的预算id
set_of_books_id 的值可以用 select * from gl_sets_of_books确认
该SQL实际证明是完全可*和可信赖的，我们的很多取值都用这个SQL。
select sum(a.period_net_dr - a.period_net_cr)
from apps.gl_balances a, apps.gl_code_combinations b
where b.enabled_flag = 'Y'
and a.set_of_books_id = 1
and a.code_combination_id = b.code_combination_id
and nvl(a.budget_version_id, 1022) = 1022
AND (upper(a.ACTUAL_FLAG) = upper(p_money_type))
AND trim(nvl(a.PERIOD_NAME, 'XXX')) =
trim(to_char(to_date(2005 || '-' || 1, 'YYYY-MM'),
'MON-YY',
'NLS_DATE_LANGUAGE=American'))
and b.segment2 = p_department_id
and b.segment3 = p_account_id

有查询User具体权限的SQL吗?
这就是
select c.user_name as login_name,