set timing on set serveroutput on begin -- setup scenerios -- to warm up with trace enabled pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'NOBIND', arg_vscnrio_name => to_char(0), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*0||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = '' || i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 1); for i in 1 .. 15 loop pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'NOBIND', arg_vscnrio_name => to_char(i), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*i||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = '' || i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 0); end loop; -- last one with trace enabled pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'NOBIND', arg_vscnrio_name => to_char(16), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*16||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = '' || i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 1); -- run scenerios pkg_cncrnt_loader.prc_run_scnrio(arg_vmain_scnrio_name => 'NOBIND'); -- wait scenerios to finish and report results dbms_lock.sleep( 60 ); pkg_cncrnt_loader.prc_report_scnrio(arg_vtask_name => 'NOBIND'); end; / begin -- setup scenerios -- to warm up with trace enabled pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'BIND', arg_vscnrio_name => to_char(0), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*0||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = :x'' USING i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 1); for i in 1 .. 15 loop pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'BIND', arg_vscnrio_name => to_char(i), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*i||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = :x'' USING i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 0); end loop; -- last one with trace enabled pkg_cncrnt_loader.prc_set_scnrio(arg_vmain_scnrio_name => 'BIND', arg_vscnrio_name => to_char(16), arg_vscnrio_dfntn => 'declare i NUMBER DEFAULT '||50*16||'; TYPE rc IS REF CURSOR; l_rc rc; l_dummy all_objects.object_name%TYPE; BEGIN OPEN l_rc FOR ''select object_name from all_objects where object_id = :x'' USING i; FETCH l_rc INTO l_dummy; CLOSE l_rc; END;', arg_nenable_trace => 1); -- run scenerios pkg_cncrnt_loader.prc_run_scnrio(arg_vmain_scnrio_name => 'BIND'); -- wait scenerios to finish and report results dbms_lock.sleep( 60 ); pkg_cncrnt_loader.prc_report_scnrio(arg_vtask_name => 'BIND'); end; / ========== ==================== ========== ========== ========== ========== ========== ========== Task Name Average Std.Dev. Maximum Minimum Count ========== ==================== ========== ========== ========== ========== ========== Elapsed NOBIND 2.8667 10.5551 41 0 15 CPU Used NOBIND .82 .023 .85 .77 15 ========== ==================== ========== ========== ========== ========== ========== LATCH NOBIND_1 1.LATCH.cache buffers chains - 1206 2.LATCH.library cache - 644 3.LATCH.library cache lock - 330 LATCH NOBIND_10 1.LATCH.library cache - 820 2.LATCH.cache buffers chains - 740 3.LATCH.library cache lock - 477 LATCH NOBIND_11 1.LATCH.cache buffers chains - 404 2.LATCH.row cache objects - 168 3.LATCH.library cache - 143 LATCH NOBIND_12 1.LATCH.library cache - 1171 2.LATCH.cache buffers chains - 996 3.LATCH.library cache lock - 652 LATCH NOBIND_13 1.LATCH.cache buffers chains - 1855 2.LATCH.library cache - 828 3.LATCH.library cache lock - 466 LATCH NOBIND_14 1.LATCH.cache buffers chains - 1050 2.LATCH.library cache - 454 3.LATCH.row cache objects - 270 LATCH NOBIND_15 1.LATCH.cache buffers chains - 1810 2.LATCH.library cache - 1266 3.LATCH.library cache lock - 702 LATCH NOBIND_2 1.LATCH.library cache - 1118 2.LATCH.cache buffers chains - 1067 3.LATCH.library cache lock - 627 LATCH NOBIND_3 1.LATCH.cache buffers chains - 2669 2.LATCH.library cache - 1065 3.LATCH.library cache lock - 584 LATCH NOBIND_4 1.LATCH.cache buffers chains - 1773 2.LATCH.library cache - 1373 3.LATCH.library cache lock - 743 LATCH NOBIND_5 1.LATCH.cache buffers chains - 1074 2.LATCH.library cache - 435 3.LATCH.library cache lock - 318 LATCH NOBIND_6 1.LATCH.cache buffers chains - 5435 2.LATCH.library cache - 2042 3.LATCH.library cache lock - 1714 LATCH NOBIND_7 1.LATCH.cache buffers chains - 1241 2.LATCH.library cache - 1118 3.LATCH.library cache lock - 627 LATCH NOBIND_8 1.LATCH.cache buffers chains - 3312 2.LATCH.library cache - 1539 3.LATCH.library cache lock - 1091 LATCH NOBIND_9 1.LATCH.cache buffers chains - 2605 2.LATCH.library cache - 1562 3.LATCH.library cache lock - 1070 ========== ==================== ========== ========== ========== ========== ========== STATS NOBIND_1 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_10 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_11 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_12 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_13 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_14 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_15 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_2 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS NOBIND_3 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_4 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_5 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_6 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_7 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_8 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 STATS NOBIND_9 1.STAT...session uga memory max - 211904 2.STAT...session pga memory max - 196608 3.STAT...session uga memory - 65568 ========== ==================== ========== ========== ========== ========== ========== WARNING 2 scenerios were configured to produce sql trace files for detailed profiling(tkprof) analysis. These threads are not included in the above results since tracing have its own cost. Trace files can be found under /u01/app/oracle/product/admin/ASMTEST/bdump folder since job processes are traced. ========== ==================== ========== ========== ========== ========== ========== PL/SQL procedure successfully completed. Elapsed: 00:01:05.44 ========== ==================== ========== ========== ========== ========== ========== ========== Task Name Average Std.Dev. Maximum Minimum Count ========== ==================== ========== ========== ========== ========== ========== Elapsed BIND .1333 .3519 1 0 15 CPU Used BIND .8587 .0146 .89 .83 15 ========== ==================== ========== ========== ========== ========== ========== LATCH BIND_1 1.LATCH.cache buffers chains - 2465 2.LATCH.library cache - 701 3.LATCH.library cache lock - 626 LATCH BIND_10 1.LATCH.cache buffers chains - 1248 2.LATCH.row cache objects - 459 3.LATCH.library cache - 329 LATCH BIND_11 1.LATCH.cache buffers chains - 1233 2.LATCH.library cache - 309 3.LATCH.library cache lock - 307 LATCH BIND_12 1.LATCH.cache buffers chains - 1607 2.LATCH.row cache objects - 513 3.LATCH.library cache - 381 LATCH BIND_13 1.LATCH.cache buffers chains - 3301 2.LATCH.library cache - 613 3.LATCH.library cache lock - 546 LATCH BIND_14 1.LATCH.cache buffers chains - 2117 2.LATCH.row cache objects - 417 3.LATCH.library cache - 318 LATCH BIND_15 1.LATCH.cache buffers chains - 2316 2.LATCH.row cache objects - 446 3.LATCH.library cache - 407 LATCH BIND_2 1.LATCH.cache buffers chains - 2072 2.LATCH.row cache objects - 500 3.LATCH.library cache - 432 LATCH BIND_3 1.LATCH.cache buffers chains - 2107 2.LATCH.row cache objects - 423 3.LATCH.library cache - 322 LATCH BIND_4 1.LATCH.cache buffers chains - 6202 2.LATCH.library cache lock - 2315 3.LATCH.library cache - 1766 LATCH BIND_5 1.LATCH.cache buffers chains - 2030 2.LATCH.library cache - 701 3.LATCH.library cache lock - 626 LATCH BIND_6 1.LATCH.cache buffers chains - 3202 2.LATCH.library cache - 553 3.LATCH.row cache objects - 531 LATCH BIND_7 1.LATCH.cache buffers chains - 1781 2.LATCH.row cache objects - 399 3.LATCH.library cache lock - 299 LATCH BIND_8 1.LATCH.cache buffers chains - 2054 2.LATCH.row cache objects - 411 3.LATCH.library cache lock - 267 LATCH BIND_9 1.LATCH.cache buffers chains - 2054 2.LATCH.row cache objects - 357 3.LATCH.library cache lock - 269 ========== ==================== ========== ========== ========== ========== ========== STATS BIND_1 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_10 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_11 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_12 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_13 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_14 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_15 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_2 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_3 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_4 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_5 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_6 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_7 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_8 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 STATS BIND_9 1.STAT...session uga memory max - 211904 2.STAT...session uga memory - 65568 3.STAT...session pga memory - 65536 ========== ==================== ========== ========== ========== ========== ========== WARNING 2 scenerios were configured to produce sql trace files for detailed profiling(tkprof) analysis. These threads are not included in the above results since tracing have its own cost. Trace files can be found under /u01/app/oracle/product/admin/ASMTEST/bdump folder since job processes are traced. ========== ==================== ========== ========== ========== ========== ========== PL/SQL procedure successfully completed. Elapsed: 00:01:06.39