在OLAP系统中不宜使用绑定变量，但如果是OLTP系统，就要尽量考虑使用绑定变量。

因为OLTP系统的特点是SQL执行非常频繁，并且用时非常短。

此时，重用shared pool中的执行计划，减少硬解析所节省的时间就非常可观了。

点击(此处)折叠或打开

DROP TABLE t;
CREATE TABLE t(col1 VARCHAR2(10));
INSERT INTO t SELECT to_char(ROWNUM) FROM dual CONNECT BY LEVEL <= 100000;
EXECUTE runstats_pkg.rs_start;
--未使用绑定变量
DECLARE
TYPE rc IS REF CURSOR;
l_cursor rc;
BEGIN
FOR i IN 1 .. 500 LOOP
OPEN l_cursor FOR \'SELECT col1 FROM t WHERE col1 = \' || to_char(i);
CLOSE l_cursor;
END LOOP;
END;
/
EXECUTE runstats_pkg.rs_middle;
--使用绑定变量
DECLARE
TYPE rc IS REF CURSOR;
l_cursor rc;
BEGIN
FOR i IN 1 .. 500 LOOP
OPEN l_cursor FOR \'SELECT col1 FROM t WHERE col1 = :x\' USING i;
CLOSE l_cursor;
END LOOP;
END;
/
set serveroutput on size 10000;
EXECUTE runstats_pkg.rs_stop(500);
Run1 ran in 2936hsec
Run2 ran in 1520hsec
run 1 ran in 193.16% of the time
Name Run1 Run2 Diff
STAT...enqueue releases 509 8 -501
STAT...enqueue requests 509 8 -501
STAT...db block gets from cach 557 50 -507
STAT...db block gets 557 50 -507
STAT...bytes received via SQL* 763 1,365 602
LATCH.shared pool simulator 790 13 -777
LATCH.call allocation 1,009 19 -990
LATCH.session allocation 1,006 15 -991
LATCH.enqueue hash chains 1,418 302 -1,116
STAT...bytes sent via SQL*Net 342 1,459 1,117
LATCH.simulator hash latch 1,501 33 -1,468
STAT...recursive calls 3,506 1,519 -1,987
STAT...calls to kcmgcs 6,022 37 -5,985
LATCH.row cache objects 13,610 658 -12,952
LATCH.shared pool 28,744 546 -28,198
STAT...buffer is not pinned co 29,501 68 -29,433
STAT...table scan blocks gotte 29,500 59 -29,441
STAT...no work - consistent re 29,501 59 -29,442
STAT...consistent gets from ca 34,509 78 -34,431
STAT...consistent gets from ca 35,010 93 -34,917
STAT...consistent gets 35,010 93 -34,917
STAT...session logical reads 35,567 143 -35,424
STAT...session uga memory 65,488 0 -65,488
LATCH.cache buffers chains 70,767 1,214 -69,553
STAT...table scan rows gotten ########## 31,680##########
STAT...logical read bytes from########## 1,171,456##########
Run1 latches total versus runs ----difference and pct
Run1 Run2 Diff Pct
121,445 4,247 -117,198#######%
PL/SQL procedure successfully completed.

ITPub博客

OLTP系统中尽量使用绑定变量

最新文章