Database Reference
In-Depth Information
Now, the only difference between the two is that one uses a bind variable and the other does not. Both are
using dynamic SQL and the logic is otherwise identical. The only difference is the use of a bind variable in the first.
We are ready to evaluate the two approaches and we'll use
runstats
, a simple tool I've developed, to compare the
two in detail:
EODA@ORA12CR1> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec proc1
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec proc2
PL/SQL procedure successfully completed.
EODA@ORA12CR1> exec runstats_pkg.rs_stop(9500)
Run1 ran in 34 cpu hsecs
Run2 ran in 432 cpu hsecs
run 1 ran in 7.87% of the time
■
for details on runstats and other utilities, see the “Setting up Your environment” section at the beginning
of this topic. You may not observe exactly the same values for Cpu or any metric. Differences are caused by different
oracle versions, different operating systems, or different hardware platforms. the idea will be the same, but the exact
numbers will undoubtedly be marginally different.
Note
Now, the preceding result clearly shows that based on CPU time, it took significantly longer and significantly
more resources to insert 10,000 rows without bind variables than it did with them. In fact, it took more than a
magnitude more CPU time to insert the rows without bind variables. For every insert without bind variables, we spent
the vast preponderance of the time to execute the statement simply
parsing
the statement! But it gets worse. When we
look at other information, we can see a significant difference in the resources utilized by each approach:
Name Run1 Run2 Diff
STAT...CCursor + sql area evic 2 9,965 9,963
STAT...enqueue requests 35 10,012 9,977
STAT...enqueue releases 34 10,012 9,978
STAT...execute count 10,020 20,005 9,985
STAT...opened cursors cumulati 10,019 20,005 9,986
STAT...table scans (short tabl 3 10,000 9,997
STAT...sorts (memory) 3 10,000 9,997
STAT...parse count (hard) 2 10,000 9,998
LATCH.session allocation 5 10,007 10,002
LATCH.session idle bit 17 10,025 10,008
STAT...db block gets 10,447 30,376 19,929
STAT...db block gets from cach 10,447 30,376 19,929
STAT...db block gets from cach 79 20,037 19,958
LATCH.shared pool simulator 8 19,980 19,972
STAT...calls to get snapshot s 22 20,003 19,981
