035.笔记：使用PLSQL提高性能

最新推荐文章于 2020-12-20 11:23:32 发布

cuihu2560

最新推荐文章于 2020-12-20 11:23:32 发布

阅读量260

点赞数

文章标签：数据库 java 嵌入式

1.使用DBMS_APPLICATION_INFO进行实时监控
declare
cursor cur_employee is
select t.deptno,t.loc,rowid from dept t;
lv_new_loc varchar2(20);
lv_count_num pls_integer :=0;
lv_start_time_num pls_integer;
begin
lv_start_time_num := dbms_utility.get_time;
for cur_employee_rec in cur_employee loop
lv_count_num :=lv_count_num+1;
lv_new_loc :=cur_employee_rec.loc;
update dept
set loc=lv_new_loc
where rowid=cur_employee_rec.rowid;
if mod(lv_count_num,1000)=0 then
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
end if;
end loop;
commit;
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
end;

--监控结果
SQL> col username on format a8;
SQL> select username,sid,T.SERIAL#,T.MODULE,T.ACTION from v$session t
2 where t.username='SCOTT';

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 PL/SQL Developer SQL Window - New
SCOTT 111 79 PL/SQL Developer Main session

SQL> col module on format a30;
SQL> select username,sid,T.SERIAL#,T.MODULE,T.ACTION from v$session t
2 where t.username='SCOTT';

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 PL/SQL Developer SQL Window - New
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:15000 Elapsed:2.07 sec
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:24000 Elapsed:3.3 sec
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:29000 Elapsed:4.05 sec
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:34000 Elapsed:4.85 sec
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:39000 Elapsed:5.73 sec
SCOTT 111 79 PL/SQL Developer Main session

SQL> /

USERNAME SID SERIAL# MODULE ACTION
-------- ---------- ---------- ------------------------------ --------------------------------
SCOTT 102 120 PL/SQL Developer SQL Window - New
SCOTT 104 36 Records Processed:43105 Elapsed:6.48 sec
SCOTT 111 79 PL/SQL Developer Main session
【技巧】使用由Oracle提供的DBMS_APPLICATION_INFO包来记录V$SESSION视图在各个时间点上的信息，可以实现对长时间运行的程序的监控。

2.在RAC环境中为实时监控使用自定义包代替DBMS_APPLICATION_INFO
在RAC环境中，多个Oracle实例将用于单个Oracle数据库。在任何时候，每个实例都会占用部分系统的负载。因此，在某一个实例上运行的进程将不能看到（通过
V$SESSION和V$SESSION_LONGOPS)由在RAC的另一个实例上运行的会话所提供的实时反馈。
【提示】在RAC环境中为实时监控使用自定义包代替DBMS_APPLICATION_INFO
【注】书中提供了自定义包的源代码，有9页。

3.在数据库的表中记录计时信息
--建表
create table process_timing_log
(program_name varchar2(30),
execution_date date,
records_processed number,
elapsed_time_sec number);

--在存储过程中记录计时信息
create or replace procedure update_dept as
cursor cur_employee is
select t.deptno,t.loc,rowid from dept t;
lv_new_loc varchar2(20);
lv_count_num pls_integer :=0;
lv_start_time_num pls_integer;
lv_total_time_num number;
begin
lv_start_time_num := dbms_utility.get_time;
for cur_employee_rec in cur_employee loop
lv_count_num :=lv_count_num+1;
lv_new_loc :=cur_employee_rec.loc;
update dept
set loc=lv_new_loc
where rowid=cur_employee_rec.rowid;
end loop;
lv_total_time_num :=(dbms_utility.get_time - lv_start_time_num)/100;
insert into process_timing_log(program_name , execution_date ,records_processed,elapsed_time_sec)
values('update_dept',sysdate,lv_count_num,lv_total_time_num);
commit;
end;
【小结】每个存储过程写两条记录（一条start,一条end）更便于监控，在end记录上保存总用时（秒）。

4.减少PL/SQL程序的单元迭代和迭代的时间
任何牵涉到循环逻辑的PL/SQL程序单元都可能存在大幅提高性能的空间。可以通过两种方式来改善这种类型潜在的性能。第一种方法是通过逻辑的重构来减少迭代
的数量，并保持功能性结果不变。第二种方法是减少每次迭代的时间。
【示例：计时包】
create or replace package stop_watch as
pv_start_time_num pls_integer;
pv_stop_time_num pls_integer;
pv_last_stop_time_num pls_integer;
procedure start_timer;
procedure stop_timer;
end stop_watch;

create or replace package body stop_watch as
procedure start_timer as
begin
pv_start_time_num := dbms_utility.get_time;
pv_last_stop_time_num :=pv_start_time_num;
end start_timer;

procedure stop_timer as
begin
pv_stop_time_num:=dbms_utility.get_time;
dbms_output.put_line('Total Time Elapsed:'||to_char( (pv_stop_time_num - pv_start_time_num)/100,'999,999.99' )||' sec Interval Time:'||
to_char( (pv_stop_time_num - pv_last_stop_time_num)/100,'999,999.99' ) ||' sec');
pv_last_stop_time_num := pv_stop_time_num;
end stop_timer;
END;

【示例】
set serveroutput on;
declare
lv_couter_num pls_integer :=0;
lv_total_couter_num pls_integer :=0;
begin
loop
lv_couter_num := lv_couter_num+1;
lv_total_couter_num := lv_total_couter_num + 1;
if lv_couter_num >=1000000 then
dbms_output.put_line('Processed 1000,000 Records. '||'Total Processed '|| lv_total_couter_num);
lv_couter_num :=0;
exit when lv_total_couter_num>=10000000;
end if;
end loop;
end;
/
--减少迭代：仅当lv_couter_num值到达1000000时，才增加变量lv_total_couter_num的值
declare
lv_couter_num pls_integer :=0;
lv_total_couter_num pls_integer :=0;
begin
loop
lv_couter_num := lv_couter_num+1;
if lv_couter_num >=1000000 then
lv_total_couter_num := lv_total_couter_num + lv_couter_num;
dbms_output.put_line('Processed 1000,000 Records. '||'Total Processed '|| lv_total_couter_num);
lv_couter_num :=0;
exit when lv_total_couter_num>=10000000;
end if;
end loop;
end;
/

【示例测试】
set timing on;
set serveroutput on;
SQL> declare
2 lv_couter_num pls_integer :=0;
3 lv_total_couter_num pls_integer :=0;
4 begin
5 loop
6 lv_couter_num := lv_couter_num+1;
7 lv_total_couter_num := lv_total_couter_num + 1;
8 if lv_couter_num >=1000000 then
9 dbms_output.put_line('Processed 1000,000 Records. '||'Total Processed '|| lv_total_couter_num);
10 lv_couter_num :=0;
11 exit when lv_total_couter_num>=10000000;
12 end if;
13 end loop;
14 end;
15 /
Processed 1000,000 Records. Total Processed 1000000
Processed 1000,000 Records. Total Processed 2000000
Processed 1000,000 Records. Total Processed 3000000
Processed 1000,000 Records. Total Processed 4000000
Processed 1000,000 Records. Total Processed 5000000
Processed 1000,000 Records. Total Processed 6000000
Processed 1000,000 Records. Total Processed 7000000
Processed 1000,000 Records. Total Processed 8000000
Processed 1000,000 Records. Total Processed 9000000
Processed 1000,000 Records. Total Processed 10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.58

--减少迭代：仅当lv_couter_num值到达1000000时，才增加变量lv_total_couter_num的值
SQL> declare
2 lv_couter_num pls_integer :=0;
3 lv_total_couter_num pls_integer :=0;
4 begin
5 loop
6 lv_couter_num := lv_couter_num+1;
7 if lv_couter_num >=1000000 then
8 lv_total_couter_num := lv_total_couter_num + lv_couter_num;
9 dbms_output.put_line('Processed 1000,000 Records. '||'Total Processed '|| lv_total_couter_num);
10 lv_couter_num :=0;
11 exit when lv_total_couter_num>=10000000;
12 end if;
13 end loop;
14 end;
15 /
Processed 1000,000 Records. Total Processed 1000000
Processed 1000,000 Records. Total Processed 2000000
Processed 1000,000 Records. Total Processed 3000000
Processed 1000,000 Records. Total Processed 4000000
Processed 1000,000 Records. Total Processed 5000000
Processed 1000,000 Records. Total Processed 6000000
Processed 1000,000 Records. Total Processed 7000000
Processed 1000,000 Records. Total Processed 8000000
Processed 1000,000 Records. Total Processed 9000000
Processed 1000,000 Records. Total Processed 10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
【小结】减少迭代能有效地提高性能。

【技巧】当一个PL/SQL程序单元涉及到大量的循环或递归时，就应当关注于减少每一次迭代的单位时间。这样的效果很明显，并且也很容易通过数学方法判断出总的改进的性能。
循环或者递归也应当被仔细检查，并通过重构来减少迭代的次数，但要保留函数的功能一致性。由于PL/SQL和SQL本身具有极大灵活性，可以通过使用不同的方式得到同样的结果。
如果PL/SQL程序单元的运行并不十分理想，那么有时必须用其他方式重写逻辑。

5.使用ROWID进行迭代处理
当PL/SQL程序从数据库中检索记录，执行特定的列值计算，再使用UPDATE命令完成更新，并检索出记录后，若使用ROWID则有助于提高这个PL/SQL程序的性能。在检索每一条记录
时，可以将ROWID添加到指定的列中。在更新每一条记录时，可以在谓词子句中使用ROWID。当访问一张表的记录时，ROWID是速度最快的方法，甚至比唯一参考索引还快。

【示例】
SQL> set timing on
SQL> declare
2 cursor cur_employee is
3 select t.deptno,t.loc,rowid from dept t;
4 lv_new_loc varchar2(20);
5 lv_count_num pls_integer :=0;
6 lv_start_time_num pls_integer;
7 begin
8 lv_start_time_num := dbms_utility.get_time;
9 for cur_employee_rec in cur_employee loop
10 lv_count_num :=lv_count_num+1;
11 lv_new_loc :=cur_employee_rec.loc;
12 update dept
13 set loc=lv_new_loc
14 where dept.deptno=cur_employee_rec.deptno;
15 if mod(lv_count_num,1000)=0 then
16 dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
17 end if;
18 end loop;
19 commit;
20 dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
21 end;
22 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.08
SQL> declare
2 cursor cur_employee is
3 select t.deptno,t.loc,rowid from dept t;
4 lv_new_loc varchar2(20);
5 lv_count_num pls_integer :=0;
6 lv_start_time_num pls_integer;
7 begin
8 lv_start_time_num := dbms_utility.get_time;
9 for cur_employee_rec in cur_employee loop
10 lv_count_num :=lv_count_num+1;
11 lv_new_loc :=cur_employee_rec.loc;
12 update dept
13 set loc=lv_new_loc
14 where rowid=cur_employee_rec.rowid;
15 if mod(lv_count_num,1000)=0 then
16 dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
17 end if;
18 end loop;
19 commit;
20 dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
21 end;
22 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.69
【示例小结】唯一索引用时16s，rowid用时3.69s。rowid比唯一索引快了近5倍。

【技巧】当需要在PL/SQL程序单元中选择一条记录，并且该记录需要在同一个PL/SQL程序单元中进行计算时，使用ROWID变量将提高性能。需要注意的是，这不能用于索引
组织表（IOT）。

6.将数据类型、IF语句的排列和PLS_INTEGER标准化
6.1确保比较运算中的数据类型相同
当变量或者常量值互相比较时，它们应该有相同的数据类型定义。当比较牵涉到不同的数据类型时，Oralce将会自动转换其中的一个数值，这样就产生了额外的不希望的开销。
无论何时在一个条件中进行数值比较，参与比较的数值必须是同一种数据类型。在开发PL/SQL程序单元时必须遵循这种标准，这也是很好的编程风格。
【示例】
--用时1.03s
create or replace procedure test_if (p_condition_num number) as
lv_temp_num number :=0;
lv_temp_cond_num number :=p_condition_num;
begin
stop_watch.start_timer;
for lv_count_num in 1..1000000 loop
if lv_temp_cond_num = '1' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '2' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '3' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '4' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '5' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '6' then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = '7' then
lv_temp_num :=lv_temp_num +1;
else
lv_temp_num :=lv_temp_num +1;
end if;
end loop;
stop_watch.stop_timer;
end;

--执行exec test_if(8)

--用时0.4s
create or replace procedure test_if (p_condition_num number) as
lv_temp_num number :=0;
lv_temp_cond_num number :=p_condition_num;
begin
stop_watch.start_timer;
for lv_count_num in 1..1000000 loop
if lv_temp_cond_num = 1 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 2 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 3 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 4 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 5 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 6 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 7 then
lv_temp_num :=lv_temp_num +1;
else
lv_temp_num :=lv_temp_num +1;
end if;
end loop;
stop_watch.stop_timer;
end;

--用时0.3s
create or replace procedure test_if (p_condition_num number) as
lv_temp_num number :=0;
lv_temp_cond_num pls_integer :=p_condition_num;
begin
stop_watch.start_timer;
for lv_count_num in 1..1000000 loop
if lv_temp_cond_num = 1 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 2 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 3 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 4 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 5 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 6 then
lv_temp_num :=lv_temp_num +1;
elsif lv_temp_cond_num = 7 then
lv_temp_num :=lv_temp_num +1;
else
lv_temp_num :=lv_temp_num +1;
end if;
end loop;
stop_watch.stop_timer;
end;
【实验小结】相同数据类型的比较，效率会有小幅提高

【技巧】确保所有比较条件中的比较值具有相同的数据类型。另外，确保同属于一类数值类型的比较值具有相同的子数据类型是很有帮助的。因此，在最后的示例中
，IF语句中的比较量和1、2、3等进行比较，也就是将NUMBER类型与PLS_INTEGER类型进行比较。这仍然将造成Oracle类型转换的开销。要消除这种开销，就当将1、2、
3等数改成1.0、2.0、3.0。
【注】经测试，将1、2、3改成1.0、2.0、3.0后效率没有变化，将比较量的类型改为PLS_INTEGER后，执行时间提高到0.3s

6.2根据条件出现的频率来排序IF条件
exec test_if(8);--用时0.3s
exec test_if(1);--用时0.1s
【技巧】确保PL/SQL的IF条件语句以最经常满足的条件为排列顺序，而不是基于数字或者字母的顺序。

6.3使用PLS_INTEGER PL/SQL数据类型进行整数运算
声明数值数据类型的通用标准是使用NUMBER数据类型。在PL/SQL2.2版本中，Oracle引入了PLS_INTEGER数据类型。
这种数据类型可以用于代替各种数值系列类型的声明中，只要变量的值是一个整数，并且在-2147483647到+2147483647的范围内。因此，绝大部分计数器和操作符都可以
使用这种数据类型。
PLS_INTEGER可以使用更少的内部命令来处理，因此使用这种数据类型就提高了性能。这种变量用的越多，性能的提高就越明显。
【注】6.1中的例子验证了这一点。
【技巧】处理整数时使用PLS_INTEGER类型，可以提高性能。
【技巧】如果将一个带有精度值的数字赋值给一个PLS_INTEGER变量，那么这个值将取整为一个整数，就像对这个数字运行了ROUND取整函数一样。

7.减少对SYSDATE的调用
【示例】
--用时0.5s
declare
lv_current_date date;
begin
for lv_count_num in 1..100000 loop
lv_current_date := sysdate;
end loop;
end;

--用时0.01s
declare
lv_current_date date;
lv_final_date date;
begin
lv_current_date := sysdate;
for lv_count_num in 1..100000 loop
lv_final_date := lv_current_date;
end loop;
end;

【技巧】应当限制在迭代或递归循环中调用SYSDATE，因为这个变量将产生额外的开销。在声明时将一个PL/SQL DATE变量赋一个SYSDATE值，然后引用
这个PL/SQL变量，以减少开销。

8.减少MOD函数的使用
某些PL/SQL函数在使用时比其他函数的开销要大。MOD就是这种函数，最好是使用其他的PL/SQL逻辑来取代它以提升综合性能。

【示例】
--用时0.4s
declare
lv_temp pls_integer;
begin
for lv_count_num in 1..1000000 loop
if mod(lv_count_num,1000)=0 then
lv_temp :=lv_count_num;
end if;
end loop;
end;

--用时0.1s
declare
lv_temp pls_integer;
lv_count_inc_num pls_integer :=0;
begin
for lv_count_num in 1..1000000 loop
lv_count_inc_num :=lv_count_inc_num+1;
if lv_count_inc_num=1000 then
lv_temp :=lv_count_num;
lv_count_inc_num :=0;
end if;
end loop;
end;
【技巧】MOD函数就是那种用其他PL/SQL逻辑来替代执行时速度更快的函数。尽管这是微小的细节，但应当将其作为标准编程技巧引入到您的PL/SQL标准编程技术中。

9.共享池和固定PL/SQL对象
共享池存储了在数据库中执行的所有的SQL和PL/SQL数据块。根据Oracle管理SGA共享池的方法，随着时间推移，SGA共享池将出现碎片。
--清空共享池
alter system flush shared_pool;
9.1将PL/SQL对象语句固定（缓存）到内存中
如果不能保证提供足够大的SHARED_POOL_SIZE在内存中保存所有的语句，那么就应当采取谨慎的措施将最重要的对象固定（缓存）到内存中。
begin
dbms_shared_pool.keep('test_if','p');
end;
或者
execute sys.dbms_shared_pool.keep('sys.standard');
如果将对象固定在内存中，那么在下一次关闭数据库之前，这个对象就不会失效或者被清空。
还需要考虑的是，Metalink的注意事项61760.1:DBMS_SHARED_POOL将被创建为用户SYS。其他用户不拥有这个包。需要访问这个包的任何用户都必须由SYS授予执行
示例（即TEST）的用户授予EXECUTE CATALOG_ROLE角色且在DBMS_SHARED_POOL上给TEST以EXECUTE权限，然后需要在SYS.DBMS_SHARED_POOL.KEEP中完全地限定这个包，
因为dbmspool.sql脚本并不为这个包创建公有同义词
【技巧】使用DBMS_SHARED_POOL.KEEP过程将PL/SQL对象固定到共享池中。
【注意】要使用这个过程，首先必须运行DBMSPOOL.SQL脚本。在启动DBMSPOOL.SQL脚本后，PRVTPOOL.PLB脚本将自动执行。这些脚本不能使用CATPROC.SQL来运行。

9.2固定所有的包
--示例
declare
own varchar2(100);
nam varchar2(100);
cursor pkgs is
select owner,object_name
from dba_objects
where object_type='PACKAGE';
begin
open pkgs;
loop
fetch pkgs into own,nam;
exit when pkgs%notfound;
dbms_shared_pool.keep(own || '.' || nam,'P');
end loop;
end;
一个更有针对性的方法就是仅仅固定需要重载的包，这要比固定所有的包要好。
通常Oracle自带的（也是应该保存的）有问题的包包括STANDARD、DBMS_STANDARD和DIUTIL。
【技巧】使用在PL/SQL中绑定的DBMS_SHARED_POOL.KEEP过程，可以在数据库启动时（如果内存/共享池允许的话）固定所有的包，并避免将来在加载包时出现错误。

10.标识需要固定的PL/SQL对象
--示例
select T.OWNER,T.NAME,T.SHARABLE_MEM
from v$db_object_cache t
where t.SHARABLE_MEM>100000
and t.TYPE in('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')
and kept='NO';
【技巧】通过查询V$DB_OBJECT_CACHE表，可以发现那些没有固定，但由于所需空间太大而很有可能导致潜在问题的对象。

11.使用和修改DBMS_SHARED_POOL.SIZES
通过DBMS_SHARED_POOL.SIZES包提供的过程可以非常方便和精确地查看到共享池的分配情况。
这个调用接受一个MININUM SIZE参数，并将显示共享池所有大于所提供参数的游标和对象。以下程序清单展示了检索该信息的实际语句。

--下面的SQL是DBMS_SHARED_POOL.SIZES的实际代码
SELECT to_char(t.SHARABLE_MEM/1000,'999999') sz,
decode(t.KEPT_VERSIONS,0,' ',rpad('yes('|| to_char(t.KEPT_VERSIONS)||')',6)) keeped,
rawtohex(t.ADDRESS)||','||to_char(t.HASH_VALUE) name,
substr(sql_text,1,354) extra,1 iscursor
FROM V$sqlarea t
where t.SHARABLE_MEM> &min_ksize *1000
union
select to_char(v.SHARABLE_MEM/1000,'999999') sz,
decode(v.KEPT,'YES','YES ',''),
v.OWNER||'.'||v.NAME ||lpad(' ',29-(length(v.OWNER)+length(v.NAME)))||'('||v.TYPE||')' name,
null extra,
0 iscursor
from v$db_object_cache v
where v.SHARABLE_MEM> &min_ksize *1000
order by 1 desc;

【查找大对象】
可以使用DBMS_SHARED_POOL.SIZE包过程来查看那些使用的共享内存高于您设定的阈值的对象。
set serveroutput on size 10000;
begin
sys.dbms_shared_pool.sizes(100);
end;

12.从DBA_OBJECT_SIZE中获取详细的对象信息
查询DBA_OBJECT_SIZE视图，显示特定对象所占用的内存情况，包括更多的关于对象的详细信息，如下所示：
select *
from dba_object_size t
where t.owner='SCOTT' and name='STOP_WATCH';
【获得共享池里当前可使用的连续空间】
select *
from x$ksmsp t
where t.ksmchsiz>10000 and ksmchcom like '%PL/SQL%';
【技巧】通过查询x$ksmsp可以找出在共享池中占用大段空间的PL/SQL代码块。它们是在数据库启动时需要固定的候选对象。

13.发现无效的对象
--查找效对象
select *
from dba_objects t
where OWNER='SCOTT' AND t.status<>'VALID';
--编译效对象
alter procedure test_if compile;

BEGIN
DBMS_UTILITY.compile_schema('scott');
end;

14.发现已禁用的触发器
在某些方面，禁用的触发器比无效的对象更加危险，因为它从不失败----而是根本不执行！
select *
from dba_triggers t
where t.status <>'ENABLED';
【技巧】通过查询DBA_TRIGGERS或者USER_TRIGGERS来查询触发器的状态，避免由于已禁用的触发器而产生的错误。已禁用的触发器将会给应用程序带来致命的
结果：它们不会失败，它们根本就不执行。

15.将PL/SQL表用于快速参考表查询
--15.1调整前的代码
--为示例准备数据-------------------------------------
create table dept_data
as
select t.*,to_number(substr(t.city,1,1)) city_num from DEPT t
where t.deptno<4000;

create table dim_city_data
as
select c.*,to_number(substr(c.city,1,1)) city_num from dim_city c;
alter table dim_city_data add constraint pk_id primary key(city_num);
-----------------------------------------------------
--用时0.5s
declare
v_code_c dim_city_data.city_name%type;
cursor v_lookup_cur (p_code_n in number) is
select city_name
from dim_city_data where city_num=p_code_n;
cursor v_inbound_cur is
select *
from dept_data;
begin
for inbound_cur in v_inbound_cur loop
begin
open v_lookup_cur(inbound_cur.city_num);
fetch v_lookup_cur into v_code_c;
if v_lookup_cur%notfound then
close v_lookup_cur;
raise no_data_found;
end if;
close v_lookup_cur;
commit;
exception
when no_data_found then
null;
when others then
null;
end;
end loop;
end;
--15.2使用PL/SQL表
虽然这个程序看上去效率很高，但事实上它已经受到对参考表的反复查询的影响。尽管Oracle可能已将整个参考表保存在内存中，但由于固定的需要或者其他优先级更高的查询，
处理这些查询时还是将产生一定数量的开销。
一个更加有效的方法就是把整个参考表加载到PL/SQL表中去。数值列（搜索所查找的列）在加载时作为数组索引。当需要查询参考表的数据时，PL/SQL表将代替参考表--输入数据
中需要翻译的代码将作为PL/SQL表中的数组索引。处理PL/SQL表的继承特性是：如果使用一个无效的数组索引（即输入数据的代码无法在参照表中找到匹配值），NO_DATA_FOUND异常
将会抛出。以下是相同的处理程序，只是在重写时使用了PL/SQL表来存储参考数据：
--用时0.2s
declare
type v_ref_table is table of dim_city_data.city_name%type index by binary_integer;
v_ref_array v_ref_table;
v_code_c dim_city_data.city_name%type;
cursor v_lookup_cur is
select city_num,city_name
from dim_city_data ;
cursor v_inbound_cur is
select *
from dept_data;
begin
for lookup_rec in v_lookup_cur loop
v_ref_array(lookup_rec.city_num) := lookup_rec.city_name;
end loop;

for inbound_rec in v_inbound_cur loop
begin
v_code_c := v_ref_array(inbound_rec.city_num);
commit;
exception
when no_data_found then
null;
when others then
null;
end;
end loop;
end;
--15.3使用PL/SQL表：索引可以是一个字符串值
--用时0.2s
declare
type v_ref_table is table of dim_city.city_name%type index by dim_city.city%type;
v_ref_array v_ref_table;
v_code_c dim_city.city_name%type;
cursor v_lookup_cur is
select city,city_name
from dim_city_data ;
cursor v_inbound_cur is
select *
from dept_data;
begin
for lookup_rec in v_lookup_cur loop
v_ref_array(lookup_rec.city) := lookup_rec.city_name;
end loop;

for inbound_rec in v_inbound_cur loop
begin
v_code_c := v_ref_array(inbound_rec.city);
commit;
exception
when no_data_found then
null;
when others then
null;
end;
end loop;
end;
【技巧】将参考表加载到PL/SQL表中可以加快查询速度。这是因为利用了PL/SQL中数组索引的优势。

16.查找和调整所使用对象的SQL
【技巧】要查找PL/SQL数据包过程后的源代码，则要查询user_source和dba_source视图。要查找触发器的源代码，则要查询user_triggers和dba_triggers视图。
要查找PL/SQL对象之间的依赖关系，则要查询user_dependencies和dba_dependencies视图。
【注意】使用WRAP命令包装（保护）了的数据包，是无法读取输出的。如：
select * from dba_source t where t.name='DBMS_JOB' AND TYPE='PACKAGE BODY';

17.在处理DATE数据类型时使用时间信息
【技巧】一个DATE数据类型总是存储完整的临时值，精确到秒。不可能将一个日期变量值直接插入到一个数据类型为DATE的PL/SQL变量或者数据库列上。

18.调整和测试PL/SQL
declare
cust_name char(100);
begin
dbms_output.put_line('Start Time:'||to_char(sysdate,'hh24:mi:ss'));
get_customer(1111,cust_name);
dbms_output.put_line('Complete Time:'||to_char(sysdate,'hh24:mi:ss'));
end;

【技巧】使用PL/SQL来显示PL/SQL的开始和结束时间。主要是不要忘记使用PL/SQL调整您的PL/SQL。可以使用DBMS_PROFILER包为每一行PL/SQL代码获取时间统计信息。

【DBMS_PROFILER安装与使用】
1）以sysdata用户sys运行profload脚本。
该脚本位于$ORACLE_HOME/rdbms/admin/profload.sql
2）在需要进行存储过程性能测试的用户下运行proftab脚本，创建三个必须的表：
PLSQL_PROFILER_DATA
PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
该脚本位于
@$ORACLE_HOME/rdbms/admin/proftab.sql
(有可能还包profrep.sql/profsum.sql)
3）
begin
dbms_profiler.start_profiler(run_comment => 'test1:'||sysdate);
test1(1);
dbms_profiler.stop_profiler;
end;
4）查看表
PLSQL_PROFILER_DATA
PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS

select u.unit_owner,u.unit_name,t.line#,t.total_time
from PLSQL_PROFILER_DATA t,PLSQL_PROFILER_UNITS u
where u.runid=t.runid and u.unit_number=t.unit_number
and u.unit_name='TEST1'
order by t.total_time desc;

19.了解PL/SQL对象定位的含义
【技巧】究竟把PL/SQL代码存储到什么地方将是一个争论不休的问题。一般说来，代码应当存储到服务器端，并随着瘦客户机的流行而变为唯一的选择。

20.使用回滚段打开大型游标
本节是为那些不想使用ORACLE的自动撤消功能的开发人员和DBA准备的。
1）任何有经验的PL/SQL开发人员都知道，在对数据库进行大型的INSERT/UPDATE/DELETES操作时，需要设置合适的空间并使用回滚段。
commit;
set transaction use rollback segment rbs1;
update big_table
set col_1=col_1*0.234;
commit;
2）可能很少有人知道Oracle在使用游标时也使用回滚段，即使在游标的循环中没有用到DML语句。回滚段用作正在执行的游标循环的一种工作区。
commit;
set transaction use rollback segment rbs_big;
for c1_rec in c1 loop
...
end loop;
【技巧】如果不使用自动撤消管理，那么在打开一个大型游标时，必须指定一个空间足够大的回滚段。
3）使用动态事务管理来处理海量数据
动态事务管理是一个由3个部分组成的编程技术：为游标和DML语句设置事务，执行间歇性的数据库COMMITS操作，使用一张表的一列作为处理标志，以说明哪些
记录已经经过处理。
delare
counter number;
cursor c1 is
select rowid,col1,col2
from big_table
where process_time is null;
begin
counter :=0;
commit;
set transaction use rollback segment rbs_big;
for c1_rec in c1 loop
if (counter=0) or (counter>=1000) then
commit;
set transaction use rollback segment rbs_medium;
counter :=0;
else
counter :=0;
end if;

update big_table
set process_time=sysdate
where rowid=c1_rec.rowid;
end loop;
commit;
end;
【技巧】为事务处理指定正确的回滚段空间。限制COMMIT之间操作数据的数量是避免出现回滚段错误的关键。

21.使用数据库的临时表来提高性能
PL/SQL表对于某些特定情况特别有用，尤其是牵涉到重复迭代的使用和相关数据较小的情况；但如果使用不当，内存的使用（每个会话）将会增加。当需要
一个临时存储空间在短时间内存储大量的记录时，创建、索引和查询一张数据库临时表的方法是可行和十分有用的选择。
Oracle通过往临时表中写入撤消数据来帮助实现事务恢复、回滚到保存点、保持读操作的一致性和回收空间等功能。这样，临时表中的事务有可能会生成redo信息，
因为我们需要记录回滚段或者撤消段所做的修改。产生的redo信息比永久表上的DML操作产生的redo信息要少。

22.集成用户跟踪机制以定位执行位置
DBMS_APPLICATION_INFO.SET_MODULE

23.限制动态SQL的使用
Oracle提供了一个由Oracle开发的DBMS_SQL包和动态SQL命令EXECUTE IMMEDIATE，可以创建动态的SQL和PL/SQL命令。这些都是非常强有力的特性，但如果使用不当
也十分危险。
只有需要的时候才考虑应用程序的灵活性，不要把每一个应用程序模块都设计成具有适应将来灵活性。
【技巧】如果您在一个PL/SQL程序单元中整合了DBMS_SQL包，将其用于为一个应用程序产品创建动态的SQL语句，则必须牢记：对生成的SQL语句的优化工作将变得很困难。
【技巧】使用绑定变量和动态SQL可以使资源竞争最小优，性能最大化。

24.使用管道表函数来建立复杂结果集
管道表函数能消除在中间表中保存数据的开销。
管道表函数是指能够产生行集合（例如嵌套表）的函数，它们能像物理数据库表一样进行查询或者能赋值给PL/SQL集合变量。可以使用表函数代替FROM子句中数据库表的名称
或者代替查询select列表中的列名。

【示例】
create table states
(
state_code varchar2(2) not null,
state_name varchar2(100) not null,
constraint states_pk primary key(state_code),
constraint states_uk1 unique (state_name),
constraint states_ck1 check (state_code=upper(state_code))
);
需要解决的问题是：需要一种方法来创建SQL脚本用来复制模式中所有的自定义约束，它们的需求如下：
1）脚本需要在应用程序的服务器上，而不是在数据库服务器上创建，使用Java Server Pages(JSP)方法。
2）脚本需要保证考虑约束之间的依赖关系。
3）脚本需要在复制约束时使得被禁用的约束保持禁用状态。
4）当复制启用的检查和外键约束时，脚本应该保护现有数据的再次验证。
现在，有可能通过使用多表连接、多个UNION子句和大量的DECODE语句的大型SQL查询来解决这个问题，但是所得到的最终结果将难以维护。所以需要更好的解决方案，
其中涉及管道表函数。
通过关键字PIPELINED可以声明管道表函数，PIPELINED关键字说明函数迭代返回行。管道表函数的返回类型必须是支持的集合类型，例如嵌套表或varray。
create or replace package ddl_extract_pkg is
--Record and array types to support pipelined tabled functions
type sg_constraint_ddl_rec is record (ddl_name varchar2(100),ddl_text varchar2(1000));
type sg_constraint_ddl_array is table of sg_constraint_ddl_rec;
--Public routines.
function get_constraint_ddl return sg_constraint_ddl_array pipelined;
end ddl_extract_pkg;

在PL/SQL中，PIPE ROW语句使得管道表函数返回一行并继续处理。该语句能使PL/SQL表函数在产生一个数据行时就返回该数据行。PIPE ROW语句只应该用于管道表函数
的主体部分中，如果用在其他地方将发生错误。对于不返回任何数据行的管道表函数，可以省略PIPE ROW语句。管道表函数可以有不返回任何值的RETURN语句。RETURN语句
将控制权还给客户并保证在下次获取时返回NO_DATA_FOUND异常。
create or replace package body ddl_extract_pkg is

--scrub_raw_ddl function
--
--Description:This function performs basic scrubbing routines on a
-- DDL command returned by dbms_metadata.get_ddl.
--
--Syntax:scrub_raw_ddl(p_status_c,p_cons_type_c,p_ddl_c);
--
--Where:p_status_c =The current status (Enabled/Disabled).
-- p_cons_type_c =The constraint type(P,U,C,R).
-- P_ddl_c =The constraint reconstruction DDL.
FUNCTION scrub_raw_ddl(p_status_c in varchar2,
p_cons_type_c in varchar2,
p_ddl_c in varchar2) return varchar2 is v_new_ddl_c varchar2(1000);
BEGIN
--Capture the passed DDL.
v_new_ddl_c :=p_ddl_c;
--Trim off any carriage returns.
v_new_ddl_c :=replace(v_new_ddl_c,chr(10),null);
--Trim off any whitespace.
v_new_ddl_c := trim(v_new_ddl_c);
--For Check and Relational constraints,if the constraint is
--currently disabled then we will leave it that way.
--Otherwise,we will enable it but without the re-validation of existing data.
if (p_cons_type_c in('C','R')) then
if ((p_status_c ='ENABLED')) then
if (instr(v_new_ddl_c,'NOVALIDATE')=0) then
v_new_ddl_c := v_new_ddl_c||' NOVALIDATE';
end if;
end if;
end if;
--Properly terminate the command.
v_new_ddl_c := v_new_ddl_c||';';
--Return.
return(v_new_ddl_c);
END scrub_raw_ddl;

--get_constraint_ddl function
--
--Description：Pipelined table function returning proper DDL commands to
-- reconstruct the custom constrains(PK,UK,CHK,FK) for all
-- table within the current schema.
--
FUNCTION get_constraint_ddl return sg_constraint_ddl_array pipelined is

v_mdc_i integer;
v_raw_sql_c varchar2(1000);

--The function returns a collection of records of type X.
--So, in the code we will return single records of type X.
v_out_record sg_constraint_ddl_rec;

--Cursor to control the extraction order to prevent dependency errors.
--Check constraints,the PK,the UK,then FK.
--We do this to prevent dependencies errors.
cursor v_extract_order_cur is
select 1 as a_cons_order,
'C' as a_cons_type,
'CONSTRAINT' as a_cons_group
from dual
union all
select 2,'P','CONSTRAINT' from dual
union all
select 3,'U','CONSTRAINT' from dual
union all
select 4,'R','REF_CONSTRAINT' from dual
order by 1;

--Cursor to access the custom constraints from data dictionary
cursor v_constraints_cur(p_type_c in varchar2) is
select owner,table_name,constraint_name,constraint_type,
status,validated
from user_constraints
where table_name ='STATES'
and constraint_type = p_type_c
and generated <>'GENERATED NAME';
BEGIN

--Configure the dbms_metadata package.
--为了避免从不同的字典表中重构DDL的冗长集合，需要使用DBMS_METADDATA包。
v_mdc_i :=dbms_metadata.session_transform;
dbms_metadata.set_transform_param(v_mdc_i,'PRETTY',false);
dbms_metadata.set_transform_param(v_mdc_i,'SEGMENT_ATTRIBUTES',false);
dbms_metadata.set_transform_param(v_mdc_i,'STORAGE',false);
dbms_metadata.set_transform_param(v_mdc_i,'TABLESPACE',false);
dbms_metadata.set_transform_param(v_mdc_i,'CONSTRAINTS_AS_ALTER',false);
dbms_metadata.set_transform_param(v_mdc_i,'CONSTRAINTS',false);
dbms_metadata.set_transform_param(v_mdc_i,'REF_CONSTRAINTS',false);
dbms_metadata.set_transform_param(v_mdc_i,'SQLTERMINATOR',false);

--Open the cursor that controls the extraction order...
for extract_order_rec in v_extract_order_cur loop
for constraints_rec in v_constraints_cur(extract_order_rec.a_cons_type) loop
v_out_record.ddl_name := constraints_rec.constraint_name;
v_out_record.ddl_text := null;
v_raw_sql_c := dbms_metadata.get_ddl(extract_order_rec.a_cons_group,constraints_rec.constraint_name,constraints_rec.owner);
v_out_record.ddl_text := scrub_raw_ddl(constraints_rec.status,extract_order_rec.a_cons_type,v_raw_sql_c);
pipe row(v_out_record);
end loop;
end loop;
return;
END get_constraint_ddl;
END ddl_extract_pkg;

--查询管道表函数
1）必须使用SQL TABLE集合表达式告诉Oracle，从管道表函数返回的集全应该作为一张表用于查询和DML操作
2）需要从集合中访问的列必须显式地说明，不能使用列通配符(*)
select x.ddl_name,x.ddl_text
from table(ddl_extract_pkg.get_constraint_ddl) x
order by 1;
【提示】通过管道表函数可以避免中间表，以建立复杂的结果集
【提示】使用DBMS_METADATA从数据字典中创建重构的DDL

25.别管调试命令
--示例1
create or replace package debug_pkg is
debug constant boolean :=true;
end debug_pkg;

create or replace package worker_pkg as
procedure run_prc;
end worker_pkg;

create or replace package body worker_pkg as
procedure run_prc is
begin
dbms_output.put_line('Processing started.');
$if debug_pkg.debug $then dbms_output.put_line('Debugging is on.'); $end
dbms_output.put_line('Processing completed.');
end;
end worker_pkg;

【查看源代码】
selelct * from user_source

【查看编译后的代码】
exec dbms_preprocessor.print_post_processed_source('PACKAGE BODY','scott','WORKER_PKG')

--示例2
alter session set PLSQL_CCFLAGS='MyDebugMode:TRUE';
create or replace package body worker_pkg as
procedure run_prc is
begin
dbms_output.put_line('Processing started.');
$if $$MyDebugMode $then
dbms_output.put_line('Debugging is on.');
$end
dbms_output.put_line('Processing completed.');
end;
end worker_pkg;

exec worker_pkg.run_prc;--查看结果

alter session set PLSQL_CCFLAGS='MyDebugMode:FALSE';
exec worker_pkg.run_prc;--查看结果:没有重新编译，结果与上次相同

alter package worker_pkg compile;--手工编译
exec worker_pkg.run_prc;--查看结果

alter session set PLSQL_CCFLAGS='MyDebugMode:TRUE' reuse settings;--永久编译参数