对比PL/SQL profiler剖析结果
1、用于实施剖析的存储过程 [sql] --环境 sys@USBO> select * from v$version where rownum<2; BANNER ------------------------------------------------------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production --用于实施剖析的原过程，下面是使用字面量的过程，注意代码中包含了启用与停止profiler scott@USBO> create or replace procedure binds 2 is 3 v_num number; 4 begin 5 dbms_profiler.start_profiler('binds'); 6 for i in 1..100000 loop 7 v_num := dbms_random.random; 8 insert into t1 values (v_num,v_num); 9 end loop; 10 dbms_profiler.stop_profiler; 11 end; 12 / Procedure created. --用于实施剖析的原过程，下面是使用绑定量的过程，注意代码中包含了启用与停止profiler scott@USBO> create or replace procedure literals 2 is 3 v_num number; 4 begin 5 dbms_profiler.start_profiler('literals'); 6 for i in 1..100000 loop 7 v_num := dbms_random.random; 8 execute immediate 9 'insert into t1 values ('||v_num||','||v_num||')'; 10 end loop; 11 dbms_profiler.stop_profiler; 12 end; 13 / Procedure created. --Author : Leshami --Blog : http://blog.csdn.net/leshami --清除剖析表中的历史数据(每次剖析对比前执行) scott@USBO> delete from plsql_profiler_data; scott@USBO> delete from plsql_profiler_units; scott@USBO> delete from plsql_profiler_runs; --分别执行两个不同的过程 scott@USBO> exec literals; scott@USBO> exec binds; 2、提取剖析对比结果 [sql] scott@USBO> @profsum 2 rows updated. PL/SQL procedure successfully completed. = = ==================== total time GRAND_TOTAL ----------- 58.93 = = ==================== total time spent on each run RUNID RUN_COMMENT SECS ------- ----------- --------- 7 literals 53.19 8 binds 5.75 = = ==================== percentage of time in each module, for each run separately RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECS PERCEN ------- ----------- ----------- -------------- --------- ------ 7 literals SCOTT LITERALS 53.19 100.0 8 binds SCOTT BINDS 5.75 100.0 = = ==================== percentage of time in each module, summarized across runs UNIT_OWNER UNIT_NAME SECS PERCENTAG ----------- -------------- --------- --------- SCOTT LITERALS 53.19 90.25 SCOTT BINDS 5.75 9.75 = = ==================== lines taking more than 1% of the total time, each run separate RUNID HSECS PCT OWNER UNIT_NAME LINE# TEXT ------- --------- ------- ----------- -------------- ------ --------------------- 7 5221.18 88.6 SCOTT LITERALS 8 execute immediate 8 502.97 8.5 SCOTT BINDS 8 insert into t1 values (v_num,v_num); 7 73.04 1.2 SCOTT LITERALS 7 v_num := dbms_random.random; = = ==================== most popular lines (more than 1%), summarize across all runs HSECS PCT UNIT_OWNER UNIT_NAME LINE# TEXT --------- ------- ----------- -------------- ------ --------------------- 5221.18 88.6 SCOTT LITERALS 8 execute immediate 502.97 8.5 SCOTT BINDS 8 insert into t1 values (v_num,v_num); 73.04 1.2 SCOTT LITERALS 7 v_num := dbms_random.random; PL/SQL procedure successfully completed. = = ==================== Number of lines actually executed in different units (by unit_name) UNIT_OWNER UNIT_NAME LINES_EXECUTED LINES_PRESENT PCT ----------- -------------- -------------- ------------- ------- SCOTT LITERALS 4 7 57.1 SCOTT BINDS 4 7 57.1 = = ==================== Number of lines actually executed for all units LINES_EXECUTED -------------- 8