第10章 使用PLSQL提高性能针对DBA和开发人员.docx
《第10章 使用PLSQL提高性能针对DBA和开发人员.docx》由会员分享，可在线阅读，更多相关《第10章 使用PLSQL提高性能针对DBA和开发人员.docx（88页珍藏版）》请在冰豆网上搜索。
第10章使用PLSQL提高性能针对DBA和开发人员
第10章使用PL/SQL提高性能（针对DBA和开发人员）
Oracle10g使PL/SQL又进了一步。
本章将主要介绍10g中有用的新提示（到10gR2为止）以及在以前版本中继续有用的提示。
一旦可以使用所有强有力的查询来监控您的系统，那么就有必要使它们自动化运行。
PL/SQL除了可完成以上的工作外，同时还将提供强大而有效的数据包和可用于性能调整的过程。
PL/SQL引擎处理所有的PL/SQL请求，并且将代码传递给Oracle去执行。
当PL/SQL被传递给Oracle之后，通常是放置在Oracle的系统全局区（SGA）中，特殊情况下会放置在共享池内。
在Oracle中，PL/SQL的源代码可以以过程、函数、数据包，或者触发器的形式被存储在数据库中。
一旦这些对象以编译过的格式存储在数据库中，那么用户只要获得相应对象的执行特权就都可以使用任何的Oracle工具来执行这些对象。
一旦开始执行对象，p-代码（可执行代码）将被加载到SGA共享池中，再由Oracle执行。
一个PL/SQL对象将会一直保存在SGA共享池里，直到根据最近最少使用（LeastRecentlyUsed，简写为LRU）算法将该对象设置为失效。
因此，如果有任何程序需要调用对象，只要该对象还没有失效，它就不必被重新加载到SGA共享池中。
所以，Oracle通常是查询SGA共享池（效率很高）中的对象，而不是到磁盘（效率较低）上加载对象。
如何使用PL/SQL更好地调整SQL很有可能就是影响性能的最大驱动因素，当然，本章节中也将介绍其他的调整方案。
本章的第一部分着重介绍对PL/SQL的理解和定位。
本章主要内容：
● 使用DBMS_APPLICATION_INFO进行实时监控
● 在RAC环境中为实时监控使用DBMS_APPLICATION_INFO的自定义替换
● 在数据库的表中记录计时信息
● 减少PL/SQL程序的单元迭代和迭代的时间
● 使用ROWID进行迭代处理
● 将数据类型、IF语句的排列和PLS_INTEGER标准化
● 减少对SYSDATE的调用
● 减少MOD函数的使用
● 在共享池中查找特定对象
● 当错误发生时清空共享池
● 在共享池中固定（pin）对象
● 标识需要被固定的PL/SQL对象
● 使用PL/SQL在共享池中固定所有包
● 使用和修改DBMS_SHARED_POOL.SIZES
● 从DBA_OBJECT_SIZE中获取详细的对象信息
● 发现无效的对象
● 发现已禁用的触发器
● 使用PL/SQL关联数组用于快速引用表查找
● 访问USER_SOURCE、USER_TRIGGER和USER_DEPENDENCIES
● 在PL/SQL中使用Oracle的Date数据类型
● 使用PL/SQL来调整PL/SQL
● 了解PL/SQL对象定位的含义
● 使用回滚段打开大型游标
● 使用数据库的临时表来提高性能
● 集成用户跟踪机制以定位执行位置
● 限制动态SQL的使用
● 使用管道表函数来建立复杂的结果集
● 使用条件编译限制调式命令
● 为初学者提供的例子
10.1 使用DBMS_APPLICATION_INFO进行实时监控
DMBS_APPLICATION_INFO包为用户提供了一个强有力的机制，用于交换环境中执行处理的时间点信息。
下面的程序清单列举了一个相关的例子，即每隔1000行，就让一个长时间运行的PL/SQL程序提供处理信息。
PL/SQL代码段每隔1000行记录就更新应用程序的信息，更新的内容主要是处理的记录数和花费的时间。
以下是说明更新所有雇员工资的示例：
DECLARE
CURSORcur_employeeIS
SELECTemployee_id,salary,ROWID
FROM s_employee_test;
lv_new_salary_numNUMBER;
lv_count_num PLS_INTEGER:
=0;
lv_start_time_numPLS_INTEGER;
BEGIN
lv_start_time_num:
=DBMS_UTILITY.GET_TIME;
FORcur_employee_recINcur_employeeLOOP
lv_count_num:
=lv_count_num+1;
--Determinationofsalaryincrease
lv_new_salary_num:
=cur_employee_rec.salary;
UPDATEs_employee_test
SETsalary=lv_new_salary_num
WHERErowid=cur_employee_rec.ROWID;
IFMOD（lv_count_num,1000）=0THEN
DBMS_APPLICATION_INFO.SET_MODULE（'RecordsProcessed:
'||
lv_count_num,'Elapsed:
'||（DBMS_UTILITY.GET_TIME-
lv_start_time_num）/100||'sec'）;
ENDIF;
ENDLOOP;
COMMIT;
DBMS_APPLICATION_INFO.SET_MODULE（'RecordsProcessed:
'||
lv_count_num,'Elapsed:
'||（DBMS_UTILITY.GET_TIME-
lv_start_time_num）/100||'sec'）;
END;
/
通过查询V$SESSION视图，可以监控处理过程，示例如下：
SELECT username,sid,serial#,module,action
FROM V$SESSION
WHERE username='SCOTT';
注意，与执行PL/SQL块的查询不同，该查询需要运行于单独的会话中。
以下是查询V$SESSION视图的输出结果，分3次不同的时间进行查询。
最后一次查询的时间是在PL/SQL程序单元已经完成时。
USERNAME SID SERIAL# MODULE ACTION
---------- ---------- ------------------------------------------
SCOTT 7 4 SQL*Plus
SCOTT 10 10 RecordsProcessed:
1000 Elapsed:
0.71sec
USERNAME SIDSERIAL# MODULE ACTION
-------------------- --------------------------------------------
SCOTT 7 4 SQL*Plus
SCOTT 10 10 RecordsProcessed:
10000 Elapsed:
4.19sec
USERNAME SID SERIAL# MODULE ACTION
------------- ------- ---------------------------------------------
SCOTT 7 4 SQL*Plus
SCOTT 10 10 RecordsProcessed:
25000 Elapsed:
9.89sec
所获得的响应时间主要取决于系统的运行速度，以及体系结构的完美程度。
在上面的输出中，会发现每个查询都返回了两个记录，这是因为在SCOTT模式中有两个不同的SQL*Plus会话在运行，一个是正在执行的更新雇员工资信息的PL/SQL程序单元，另一个是正在通过V$SESSION视图监控进程的SQL语句。
以上示例演示了一种用于特定环境的有价值的技巧，并提供了实时监控的机制。
这样就能更加容易地准确测量出程序已经运行的时间，并可估计出程序还要多长时间才能完成。
如果DBA不想让用户都有权使用V$SESSION视图来获取所有用户的信息，那么他们可以在V$SESSION视图上创建一个新视图，以限制用户只能检索正在执行的用户的会话信息。
可以以SYS用户的身份，通过执行以下命令来完成这项任务。
该语法可以创建新的视图（这个新的视图称为session_log，当然也可以使用其他的名称）。
在查询中使用USER，接下来将返回使用数据类型VARCHAR2的会话用户名称（已登录用户）。
CREATE VIEWsession_logAS
SELECT *
FROM V$SESSION
WHERE username=USER;
以下语法创建了一个公共同义词（publicsynonym）。
CREATEPUBLICSYNONYMsession_logFORsession_log;
下面的语法为所有的用户授予了SELECT权限:
GRANTSELECTONsession_logTOPUBLIC;
在建立session_log视图之后，如前面的代码所示，可将前面V$SESSION视图的查询替换为以下查询中所示的session_log视图中的SELECT查询，以限制输出结果只能包含用户执行查询的信息。
SELECT username,sid,serial#,module,action
FROM session_log;
技巧：
使用由Oracle提供的DBMS_APPLICATION_INFO包来记录V$SESSION视图在各个时间点上的信息，可以实现对长时间运行的程序的监控。
10.2 在RAC环境中为实时监控使用自定义包代替DBMS_APPLICATION_INFO
上面的内容只能应用于RAC环境，因为实例特有的表（V$表）将成为提供应用级实时反馈的不适当机制。
在RAC环境中，多个Oracle实例将用于单个Oracle数据库。
在任何时候，每个实例都会占用部分系统的负载。
因此，在某一个实例上运行的进程将不能看到（通过V$SESSION和V$SESSION_LONGOPS）由在RAC的另一个实例上运行的会话所提供的实时反馈。
通过引入类似于DBMS_APPLICATION_INFO调用和行为的自定义包可以克服这样的限制。
本质上，这是通过物理表（T$SESSION）在数据库级别上提供数据库使用的所有实例信息的持久显示来完成的。
自定义包包含了大部分DBMS_APPLICATION_INFO中常用的设置函数（set_action、set_client_info、set_module）。
当在包中调用这些设置函数时，它们首先会将调用传递给DBMS_APPLICATION_INFO包，这样实例特定的V$表就会被更新，然后这个包将会复制T$SESSION表中会话的V$记录。
如果您只关注用于会话的实例上运行的内容（或者不在RAC环境中），则可以继续查询V$SESSION表查看由自定义应用代码提供的实时反馈。
如果在RAC环境中，并且想要查看由运行的进程提供的实时反馈信息，则无论这些进程使用哪些实例，都应该查询T$SESSION表。
注意，本节的提示内容只是特定于由DBMS_APPLICATION_INFO提供的基本监控功能，并不涉及自定义包提供的长时间操作支持。
然而，这里介绍的技术可以方便地扩展到长时间的操作上，这样它们都能用于监控RAC环境中的任何实例。
首先介绍一个物理表，它类似于V$SESSION中的某些列：
createtablet$session
（
instance varchar2（100）notnull,
audsid number notnull,
sid number,
serial# number,
program varchar2（100）,
module varchar2（100）,
action varchar2（100）,
client_info varchar2（100）,
osuser varchar2（100）,
username varchar2（100）,
machine varchar2（100）,
terminal varchar2（100）,
logon_time date,
last_update_time datenotnull
）
pctfree50
pctused40
initrans10
maxtrans255
storage （initial1M
next1M
minextents1
maxextentsunlimited
pctincrease0）
nologging;
commentontablet$sessionis'SessionInfoPersistentStorageTable.';
commentoncolumnt$session.instanceis'TheInstanceName.';
commentoncolumnt$session.audsidis'TheAudittingSID（fromV$SESSION.';
commentoncolumnt$session.sidis'TheSID（fromV$SESSION）.';
commentoncolumnt$session.serial#is'TheSerial#（fromV$SESSION）.';
commentoncolumnt$session.programis'TheProgram（fromV$SESSION）.';
commentoncolumnt$session.moduleis'TheModule（specfiedbytheuserin
theapicall）.';
commentoncolumnt$session.actionis'TheAction（specfiedbytheuserin
theapicall）.';
commentoncolumnt$session.client_infois'TheClientInfo（specfiedbythe
userintheapicall）.';
commentoncolumnt$session.osuseris'TheOSUser（fromV$SESSION）.';
commentoncolumnt$session.usernameis'TheUserName（fromV$SESSION）.';
commentoncolumnt$session.machineis'TheMachine（fromV$SESSION）.';
commentoncolumnt$session.terminalis'TheTerminal（fromV$SESSION）.';
commentoncolumnt$session.logon_timeis'TheLogonTime（fromV$SESSION.';
commentoncolumnt$session.last_update_timeis'Thelastupdatetimeofthis
record.';
createindext$session_idx1ont$session
（
instance,
audsid
）
pctfree10
initrans100
maxtrans255
storage （initial500K
next500K
minextents1
maxextentsunlimited
pctincrease0）
nologging;
注意对表的设计有以下几个重要点：
● 为了便于在API包中对“值太大”的保护，所有varchar2列的长度都相同。
● 为了在高度并发的环境中保持最佳性能，这个表不使用任何PK或UK约束。
然而，列可以由以下约束进行逻辑绑定。
• PK=instance，audsid
• UK=instance，sid，serial#
下面将介绍DBMS_APPLICATION_INFO的自定义版本，它支持相同的调用规范。
在介绍源代码前，首先将简述必要的过程：
● init_sessinfo_record（procedure,internal） 初始化会话信息记录，它会将当前会话的V$SESSION内容写入持久存储表中。
● persistence_cleanup_session（procedure,internal） 使用运行代码的实例的V$SESSION记录同步会话信息持久存储表。
● persistence_cleanup（procedure,internal） 协调持久会话记录与实例的同步。
● write_sessinfo_record（procedure,internal） 将会话信息记录写入持久表中。
● release_session_records（procedure） 删除与本次会话相关的持久记录。
● set_action（procedure） 更新执行调用代码的实例和会话的V$SESSION和持久表的动作。
● set_client_info（procedure） 更新执行调用代码的实例和会话的V$SESSION和持久表的客户信息。
● set_module（procedure） 更新执行调用代码的实例和会话的V$SESSION和持久表的模块和动作。
源代码如下：
CREATEORREPLACEPACKAGEtsc_appinfo_pkgis
--Acustomproceduretoprovidecleanupoperations.
procedurerelease_session_records;
--SettercallsthatmimicDBMS_APPLICATION_INFO.
procedureset_action（p_action_name_cinvarchar2）;
procedureset_client_info（p_client_info_cinvarchar2）;
procedureset_module（p_module_name_cinvarchar2,
p_action_name_cinvarchar2）;
ENDtsc_appinfo_pkg;
/
CREATEORREPLACEPACKAGEBODYtsc_appinfo_pkgis
--**************************
--Declarepackagevariables.
--**************************
--ThenameoftheInstanceservicingthecurrentsession.
pg_session_instance_name_cvarchar2（100）;
--TheAUDSIDofthecurrentsession.
--ThisisneededtofindtheappropriaterecordinV$SESSION.
pg_session_audsid_nnumber;
--Theearliesttimewhenthenextpersistencetablecleanupcanoccur.
--Thedefaultwillbeinthepastsothatthefirstcalltothispackage
--withinasessionwilldriveacleanupoperation.
pg_next_cleanup_timedate:
=sysdate-1;
--ThisSessionInfoRecord.
--Thisrecordisusedtoreplicatetheupdatesbeingperformedagainst
--V$SESSIONsothattheycanbewrittentotheSessionInfoPersistent
--StorageTablewithouttheneedtoreadV$SESSION.
--ImportantNote:
Agivensessionhasoneandonlyonerecordin
-- V$SESSION.
pg_max_length_iconstantinteger:
=100;
typepg_sessinfo_typeisrecord
（arowid rowid,
sid t$session.sid%type,
serial# t$session.serial#%type,
program t$session.program%type,
module t$session.module%type,
action t$session.action%type,
client
升级会员 