从PLSQL Developer开始学优化

从PLSQL Developer开始学优化从PL/SQL DEVELOPER 开始学优化 Author: XiaoJun.Shen Creation Date: 2008-3-23 Last Updated: 2008-3-23 Document Ref: Version: DRAFT Approvals: Copy Number _____ PL/SQL Developer File Ref: 从PLSQL...

从PL/SQL DEVELOPER 开始学优化 Author: XiaoJun.Shen Creation Date: 2008-3-23 Last Updated: 2008-3-23 Document Ref: Version: DRAFT Approvals: Copy Number _____ PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: Document Control ii Document Control Change Record 3 Date Author Version Change Reference 2008-3-23 XiaoJun.Shen Draft No Previous Document Reviewers Name Position Distribution Copy No. Name Location 1 2 3 4 Note To Holders: If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes. If you receive a hard copy of this document, please write your name on the front cover, for document control purposes. PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: Document Control iii Contents Document Control ..................................................................................................................ii 1. DBMS_PROFILER工具篇 ........................................................................................1 1.1. 总体说明..............................................................................................................1 1.2. 实现步骤..............................................................................................................2 2. DBMS_PROFILER进阶篇 ........................................................................................4 2.1. 总体说明..............................................................................................................4 2.2. 安装设置..............................................................................................................4 2.3. 收集统计数据......................................................................................................4 2.4. 分析统计数据......................................................................................................5 2.5. 简单示例..............................................................................................................5 3. DBMS_TRACE工具篇 ..............................................................................................6 3.1. 总体说明..............................................................................................................6 3.2. 实现步骤..............................................................................................................7 4. DBMS_TRACE进阶篇 ..............................................................................................9 4.1. 总体说明..............................................................................................................9 4.2. 安装设置..............................................................................................................9 4.3. 收集统计数据......................................................................................................9 4.4. 限制要收集的数据量及选择跟踪级别 ............................................................10 4.5. 分析统计数据....................................................................................................10 4.6. 简单示例............................................................................................................10 5. Open and Closed Issues for this Deliverable......................................................11 Open Issues.....................................................................................................................11 Closed Issues ..................................................................................................................11 1. DBMS_PROFILER工具篇 1.1. 总体说明需求说明一个比较复杂的优化需求，如针对数据仓库的某一个子主题或程序量比较大，调用的过程比较多的程序优化，首先要定位制约程序性能的瓶颈位置。一般来说，对耗时较多的部分进行优化，往往会起到立竿见影的效果。 PL/SQL Developer把ORACLE的Profiler Report通过图形化的形式展现在我们面前。只需轻轻一点即可实现。使用场合可作为后台数据库程序性能优化的辅助手段，用于快速定位后台程序性能的瓶颈，在程序运行过程中，要排除服务器性能和服务器负载等其他因素对统计数据造成的干挠。 Profiler生成的跟踪信息主要是执行时间信息，远没有Trace生成的详细，没有执行计划、CPU及IO 等信息。可能出现的错误 1) 包DBMS_PROFILER不存在或没权限访问根据提示，找系统管理员安装，可参考下一节中的安装设置。 2) 存放统计信息的表不存在 PL/SQL Deveoper会提示你是否需要在当前用户下创建三张存放统计信息的表，如点击确认，则在当前用户下创建。前提是当前用户要有建表的权限。 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_PROFILER工具篇 2 of 14 1.2. 实现步骤收集统计数据 PL/SQL Developer Æ Test Windows 在运行程序之前，在测试窗口工具栏里点击 “Create Profiler report” 其实这个步骤就是在过程前后增加了控制语句，如下： dbms_profiler.start_profiler(‘Test Script –‘||to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)); 待运行的过程 dbms_profiler.stop_profiler; PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_PROFILER工具篇 3 of 14 查看统计数据程序运行完成之前，就可以在Profiler页面查看结果了。在这个页面，可以： 1) 查看现在或以前的统计数据 2) 删除统计数据 3) 通过“Preference”变更时间单位、显示内容等 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_PROFILER进阶篇 4 of 14 2. DBMS_PROFILER进阶篇 2.1. 总体说明 DBMS_PROFILER是ORACLE内置的程序包，通过它可以查看每一行源代码总共执行消耗时间，最大/最小执行时间，总共的执行次数等信息，并可以追溯到被调用过程中。有了这些信息的辅助，可以非常迅速的定位后台程序性能的瓶颈。建议为使统计分析结果更精确，建议在收集数据之前，使数据库"warmed up"，即在没有收集数据的情况下，运行相应的程序一次或多次。 2.2. 安装设置创建DBMS_PROFILER程序包 1) 使用SYS用户(或有SYSDBA权限的用户)运行oracle安装目录下oracle_home \rdbms\admin中的脚本profload.sql，创建dbms_profiler包。 2) 授权给需要使用的用户。创建存放DBMS_PROFILER统计结果的表使用需要统计结果的用户，运行oracle安装目录下oracle_home \rdbms\admin中的脚本proftab.sql，在当前用户下创建存放dbms_profiler分析结果的三张表。 1) plsql_profiler_runs PL/SQL配置的每次运行信息. 2) plsql_profiler_units 运行中每一个库单元的信息. 3) plsql_profiler_data 所有配置文件运行时的数据累积. 2.3. 收集统计数据 DBMS_PROFILER.START_PROFILER 在待收集统计数据的程序代码段起始处加上 DBMS_PROFILER.START_PROFILER(p_run_comment)，用于指明收集统计数据的开始，其中p_run_comment是注释(缺省值是当前系统时间)，用于区分每次运行程序后输出的统计信息，否则待收集统计数据的程序在数次运行后输出的统计结果就难以分辨了。 DBMS_PROFILER. STOP_PROFILER 在待收集统计数据的程序代码段结尾处加上DBMS_PROFILER.STOP_PROFILER，用于指明收集统计数据的结束。 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_PROFILER进阶篇 5 of 14 2.4. 分析统计数据使用PL/SQL Developer或者TOAD查看直接通过界面观看，更加直观、明了。明细程度对于一般优化已经足够了。在程序中控制收集统计数据的范围，通过工具展现，是一种比较好的实现方式，可以成为开发人员的有力工具。简单SQL查询可以使用如下简单代码来查看运行的结果， SELECT als.line, als.text, round(pd.total_time / 1000000000, 2) run_time, -- 秒 pd.total_occur FROM all_source als, plsql_profiler_data pd, plsql_profiler_units pu, plsql_profiler_runs pr WHERE pd.runid = pu.runid AND pd.unit_number = pu.unit_number AND pr.runid = pu.runid AND pr.run_comment = 'Test01' AND als.line = pd.line# AND als.NAME = pu.unit_name AND als.NAME = 'CUX_PROF_TEST' ORDER BY PD.RUNID, PD.LINE# PROF_REPORT_UTILITIES 运行oracle安装目录下oracle_home \plsql\demo\profrep.sql ，在当前用户下创建四张视图和一个包prof_report_utilities，包含二个主要过程： 1) Print_Detailed_Report 查看明细报告 2) Print_Summarized_Report 查看汇总报告 oracle安装目录下oracle_home \plsql\demo\profsum.sql，存放有多角度的、明细或汇总的查询语句，请自行参考。 2.5. 简单示例 CREATE OR REPLACE PROCEDURE cux_prof_test AS ln_rand NUMBER := 0; lv_lang VARCHAR2(30); BEGIN SELECT userenv('LANGUAGE') INTO lv_lang FROM dual; dbms_profiler.start_profiler('Test01'); FOR i IN 1 .. 3000 LOOP ln_rand := rand.int(1, i); IF (MOD(ln_rand, 100) = 0) THEN dbms_output.put_line(to_char(ln_rand)); END IF; END LOOP; dbms_profiler.stop_profiler; END; ORACLE自带的示例在oracle_home \plsql\demo\profdemo.sql中 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_TRACE工具篇 6 of 14 3. DBMS_TRACE工具篇 3.1. 总体说明需求说明在一些比较复杂的程序中，有时难以跟踪子程序的调用，难以准确判断对异常的捕捉。另外，开发人员常常需要跟踪程序的PL/SQL 代码的运行情况。了解整个程序是否按我们预设的流程执行。而通过DBMS_TRACE可以设定需要跟踪的事件：调用、异常、SQL 或者甚至每一个 PL/SQL 代码的可运行行。 PL/SQL Developer把ORACLE的Trace Report通过图形化的形式展现在我们面前。只需轻轻一点即可实现。使用场合可作为后台数据库程序开发的辅助手段，用于跟踪后台程序的PL/SQL代码的执行情况，有助于开发人员开发出高性能的应用程序。使用DBMS_TRACE需要一定的技术基础，要控制好Trace的范围和等级，否则，如出现数据量爆增的现象，用PL/SQL Developer的图形界面就难以展现。可能出现的错误 1) 包DBMS_TRACE不存在或没权限访问根据提示，找系统管理员授权。 2) 存放统计信息的表不存在根据提示，找系统管理员安装并授权，具体方法可参考下一节中的安装设置部分 3) 删除时提示表或视图不存在一般是权限不足引起的，对这两个表的删除一定要显式授权，即使有“delete any table”的系统权限也不能对这二个表进行删除操作。 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_TRACE工具篇 7 of 14 3.2. 实现步骤收集统计数据并选择跟踪级别 PL/SQL Developer Æ Test Windows 在运行程序之前，在测试窗口工具栏里点击 “Create Trace report”，还可以点击“Select Trace Levels”选择跟踪级别其实这个步骤就是在过程前后增加了控制语句，如下： dbms_trace.set_plsql_trace(trace_level); -- trace_level 根据选择的跟踪级别相加而得。待运行的过程 dbms_trace.clear_plsql_trace; PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_TRACE工具篇 8 of 14 查看统计数据程序运行完成之前，就可以在Trace页面查看结果了。在这个页面，可以： 1) 查看现在或以前的Trace数据 2) 删除Trace数据(需要被显式授权) 3) 通过“Preference”变更显示内容 PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_TRACE进阶篇 9 of 14 4. DBMS_TRACE进阶篇 4.1. 总体说明 DBMS_TRACE是ORACLE内置的程序包，通过它可以跟踪后台程序的运行情况，了解过程调用、异常、SQL等信息，甚至到第一行。有了这些信息的辅助，可以非常迅速的定位后台程序流程的异常。 4.2. 安装设置创建DBMS_TRACE程序包系统自动安装创建存放DBMS_TRACE统计结果的表 1) 使用SYS用户建表使用SYS运行oracle安装目录下oracle_home \rdbms\admin中的脚本tracetab.sql，创建存放跟踪信息的表。 sys.plsql_trace_runs PL/SQL Trace每次运行信息 sys.plsql_trace_events 所有Trace运行的数据累积 2) 授权给需要使用的用户 grant select,insert,update,delete on plsql_trace_events to apps; grant select,insert,update,delete on plsql_trace_runs to apps; 3) 创建别名 create or replace public synonym plsql_trace_runs for sys.plsql_trace_runs; create or replace public synonym plsql_trace_events for sys.plsql_trace_events; 4.3. 收集统计数据 DBMS_TRACE.SET_PLSQL_TRACE 在待收集统计数据的程序代码段起始处加上DBMS_PROFILER.SET_PLSQL_TRACE，用于开启Trace统计数据的收集。 sys.dbms_trace.set_plsql_trace(trace_level); trace_all_calls constant INTEGER := 1; trace_enabled_calls constant INTEGER := 2; trace_all_exceptions constant INTEGER := 4; trace_enabled_exceptions constant INTEGER := 8; trace_all_sql constant INTEGER := 32; trace_enabled_sql constant INTEGER := 64; trace_all_lines constant INTEGER := 128; trace_enabled_lines constant INTEGER := 256; trace_stop constant INTEGER := 16384; trace_pause constant INTEGER := 4096; trace_resume constant INTEGER := 8192; trace_limit constant INTEGER := 16; PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: DBMS_TRACE进阶篇 10 of 14 DBMS_TRACE. CLEAR_PLSQL_TRACE 在待收集统计数据的程序代码段结尾处加上DBMS_TRACE. CLEAR_PLSQL_TRACE，用于停止Trace统计数据的收集。 4.4. 限制要收集的数据量及选择跟踪级别跟踪大型应用程序可能会制造出大量的难以管理的数据。在开启跟踪之前，我们可以选择是否限制要收集的数据量。此外，还可以选择跟踪级别。例如，如果我们可以选择跟踪全部的子程序和异常或是只跟踪与调试信息一起编译的子程序和异常。 4.5. 分析统计数据 SELECT pte.runid, pte.event_seq, to_char(pte.event_time, 'YYYY-MM-DD HH24:MI:SS') AS event_time, pte.event_unit_owner, pte.event_unit, pte.event_unit_kind, pte.proc_line, pte.event_comment FROM plsql_trace_runs ptr, plsql_trace_events pte WHERE ptr.runid = pte.runid AND ptr.runid = &1 ORDER BY pae.event_seq 4.6. 简单示例 CREATE OR REPLACE PROCEDURE cux_trace_test AS ln_rand NUMBER := 0; lv_lang VARCHAR2(30); BEGIN SELECT userenv('LANGUAGE') INTO lv_lang FROM dual; dbms_trace.set_plsql_trace(1); FOR i IN 1 .. 30 LOOP ln_rand := rand.INT(1, i); IF (MOD(ln_rand, 100) = 0) THEN dbms_output.put_line(to_char(ln_rand)); END IF; END LOOP; dbms_trace.clear_plsql_trace; END; PL/SQL Developer File Ref: 从PLSQL Developer开始学优化.doc (v. DRAFT ) Company Confidential - For internal use only Doc Ref: Open and Closed Issues for this Deliverable 11 of 14 5. Open and Closed Issues for this Deliverable Open Issues ID Issue Resolution Responsibility Target Date Impact Date Closed Issues ID Issue Resolution Responsibility Target Date Impact Date

                    本文档为【从PLSQL Developer开始学优化】，请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑，
                    图片更改请在作品中右键图片并更换，文字修改请直接点击文字进行修改，也可以新增和删除文档中的内容。 
 该文档来自用户分享，如有侵权行为请发邮件ishare@vip.sina.com联系网站客服，我们会及时删除。

                    [版权声明] 本站所有资料为用户分享产生，若发现您的权利被侵害，请联系客服邮件isharekefu@iask.cn，我们尽快处理。

                    本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权，请谨慎使用。

                    网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传，仅限个人学习分享使用，禁止用于任何广告和商用目的。
                

下载需要：免费已有0 人下载

立即下载

从PLSQL Developer开始学优化

你可能还喜欢