Resource Consumption comparison between bind variables and non-bind variables

Compare the resource consumption of bind variables and unbind variables first initialize data Createtabletstsasselectlevelascol1, rdbms_random.string (p, 10) ascol2fromdualconnectbylevel10000; %% rowtype; beginforiin1. 100

Compare the resource consumption of bind variables and unbind variables first initialize the data Create table tsts as select level as col1, rdbms_random.string (p, 10) as col2 from dual connect by level = 10000; create or replace procedure p1 as rec_tsts % rowtype; begin for I in1 .. 100

Resource Consumption comparison between bind variables and unbind variables first initialize data

Create table tsts as select level as col1, rdbms_random.string ('P', 10) as col2 from dual connect by level <= 10000;

Create or replace procedure p1

Rec_tsts % rowtype;

Begin

For I in1 .. 10000 loop

Execute immediate 'select * from tsts where col1 = '| I into rec _;

End loop;

End;

Create or replace procedure p2

Rec_tsts % rowtype;

Begin

For I in1 .. 10000 loop

Execute immediate 'select * from tsts where col1 =: 1' into rec _ using I;

End loop;

End;

Start comparison (use the runstat script of tom)

Exec runstats_pkg.rs_start;

Exec p1;

Exec runstats_pkg.rs_middle;

Exec p2;

Exec runstats_pkg.rs_stopped (1000 );

_ Dexter @ FAKE> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

_ Dexter @ FAKE> exec p1;

PL/SQL procedure successfully completed.

_ Dexter @ FAKE> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

_ Dexter @ FAKE> exec p2;

PL/SQL procedure successfully completed.

_ Dexter @ FAKE> exec runstats_pkg.rs_stop (1000 );

Run1 ran in 1163 cpu hsecs

Run2 ran in 64 cpu hsecs

Run 1 ran in 1817.19% of the time

Name Run1 Run2 Diff

STAT... recursive cpu usage 1,119 59-1,060

STAT... DB time 1,150 75-1,075

STAT... CPU used when call star 1,173 72-1,101

STAT... CPU used by this sessio 1,172 64-1,108

STAT... buffer is not pinned co 31,411 30,087-1,324

STAT... consistent gets 41,986 40,457-1,529

STAT... consistent gets from ca 41,986 40,457-1,529

STAT... session logical reads 42,047 40,494-1,553

STAT .... sorts (rows) 1,676 3-1,673

LATCH. cache buffers chains 66,737 61,883-4,854

STAT... SQL area evicted 9,519 4-9,515

STAT... parse count (hard) 10,053 4-10,049

STAT... enqueue releases 10,053 4-10,049

STAT... enqueue requests 10,053 4-10,049

STAT... parse count (total) 10,057 4-10,053

LATCH. enqueue hash chains 20,379 110-20,269

STAT... recursive cballs 41,827 10,157-31,670

LATCH. shared pool simulator 38,862 40-38,822

LATCH. shared pool 455,526 10,683-444,843

LATCH. row cache objects 513,487 441-513,046

STAT... logical read bytes from 344,449,024 12,722,176 ,-

Run1 latches total versus runs -- difference andpct

Run1 Run2 Diff Pct

1,097,601 74,729-1,022,872 1,468.78%

PL/SQL procedure successfully completed.

Analysis

We can see that the resource usage is very different. The SQL statement in our example is typically used in the oltp system. You can see that using the Bind Variable consumes less resources. We observe events with large differences. The execution can be completely affirmed. The bound variable is better than the non-bound variable. This is mainly because of the following several events with large differences.

	Binding	No binding	Desc
Session cursor cache hits	10,018		Cursor hit rate
SQL area evicted	4	9,519	Ageout caused by insufficient Shared pool
Parse count (hard)	4	10,053	Hard Parsing
Enqueue releases	4	10,053	Enqueue lock release
Enqueue requests	4	10,053	Enqueue lock request
Parse count (total)	4	10,057	Total resolution times
Enqueue hash chains	110	20,379	Number of times to obtain the hash chain
Recursive cballs	10,157	41,827	Reading data dictionary information may cause recursive cballs
Shared pool simulator	40	38,862	Advice Information Collection
Shared pool	10,683	455,526	Shared pool usage
Row cache objects	441	513,487	Hard parsing reads more statistics
Logical read byte	331,726,848	344,449,024	Logical read

As you can see, due to hard parsing, the oracle database optimizer needs to re-optimize the statements. It needs to obtain more latch and execute more operations, and for statements in the oltp system, operations performed during the optimization process even take more time than data query. Therefore, it is necessary to use more bound variables in the OLTP system.

Appendix: Use of the tom runstat script

1. Create a temporary table
Create or replace view stats
As select 'stat... '| a. name, B. value
From v $ statname a, v $ mystat B
Where a. statistic # = B. statistic #
Union all
Select 'latch. '| name, gets
From v $ latch
Union all
Select 'stat... Elapsed time', hsecs from v $ timer;

View permissions required

Grant select on v _ $ statname to & username;
Grant select on v _ $ mystat to & username;
Grant select on v _ $ latch to & username;
Grant select on v _ $ timer to & username;


2. Create a temporary table

Create global temporary table run_stats
(Runid varchar2 (15 ),
Name varchar2 (80 ),
Value int)
On commit preserve rows;

3. Create the corresponding runstat package
Create or replace package runstats_pkg
Procedure rs_start;
Procedure rs_middle;
Procedure rs_stop (p_difference_threshold in number default 0 );
End;
/
Create or replace package body runstats_pkg

G_start number;
G_run1 number;
G_run2 number;

Procedure rs_start is
Begin
Delete from run_stats;

Insert into run_stats
Select 'before', stats. * from stats;

G_start: = dbms_utility.get_cpu_time;
End;


Procedure rs_middle is
Begin
G_run1: = (dbms_utility.get_cpu_time-g_start );

Insert into run_stats
Select 'after 1', stats. * from stats;
G_start: = dbms_utility.get_cpu_time;

End;


Procedure rs_stop (p_difference_threshold in number default 0) is
Begin
G_run2: = (dbms_utility.get_cpu_time-g_start );

Dbms_output.put_line ('run1 ran in' | g_run1 | 'cpu hsecs ');
Dbms_output.put_line ('run2 ran in' | g_run2 | 'cpu hsecs ');
If (g_run2 <> 0) then
Dbms_output.put_line ('run 1 ran in' |
Round (g_run1/g_run2 * 100, 2) |
'% Of the time ');
End if;
Dbms_output.put_line (chr (9 ));

Insert into run_stats
Select 'after 2', stats. * from stats;

Dbms_output.put_line (rpad ('name', 30) | lpad ('run1', 12) |
Lpad ('run2', 12) | lpad ('diff', 12 ));

For x in (select rpad (a. name, 30) |
To_char (B. value-a. value, '20140901') |
To_char (c. value-B. value, '20140901') |
To_char (c. value-B. value)-(B. value-a. value )),
'123') data
From run_stats a, run_stats B, run_stats c
Where a. name = B. name
And B. name = c. name
And a. runid = 'before'
And B. runid = 'after 1'
And c. runid = 'after 2'

And abs (c. value-B. value)-(B. value-a. value)>
P_difference_threshold
Order by abs (c. value-B. value)-(B. value-a. value) loop
Dbms_output.put_line (x. data );
End loop;

Dbms_output.put_line (chr (9 ));
Dbms_output.put_line ('run1 latches total versus runs -- difference and pct ');
Dbms_output.put_line (lpad ('run1', 12) | lpad ('run2', 12) |
Lpad ('diff', 12) | lpad ('pct ', 10 ));

For x in (select to_char (run1, '123') |
To_char (run2, '123') |
To_char (diff, '000000') |
To_char (round (run1/
Decode (run2, 0, to_number (0), run2) * 100,
2 ),
'20140901') | '%' data
From (select sum (B. value-a. value) run1,
Sum (c. value-B. value) run2,
Sum (c. value-B. value)-(B. value-a. value) diff
From run_stats a, run_stats B, run_stats c
Where a. name = B. name
And B. name = c. name
And a. runid = 'before'
And B. runid = 'after 1'
And c. runid = 'after 2'
And a. name like 'latch % ') loop
Dbms_output.put_line (x. data );
End loop;
End;
End;
/


4. Usage

Exec runstats_pkg.rs_start;
P1
Exec runstats_pkg.rs_middle;
P2
Exec runstats_pkg.rs_stopped (1000 );


Example:

Dexter @ REPO> exec runstats_pkg.rs_start;

The PL/SQL process is successfully completed.

Dexter @ REPO> delete from t1;

One row has been deleted.

Dexter @ REPO> exec runstats_pkg.rs_middle;

The PL/SQL process is successfully completed.

Dexter @ REPO> insert into t1 select level from dual connect by level <= 10000;

You have created 10000 rows.

Dexter @ REPO & gt; exec runstats_pkg.rs_stop (100 );
Run1 ran in 1 cpu hsecs
Run2 ran in 4 cpu hsecs
Run 1 ran in 25% of the time

Name Run1 Run2 Diff
LATCH. session idle bit 331 207-124
STAT... redo entries 15 167 152
LATCH. SQL memory manager worka 1,012 807-205
STAT... db block changes 77 300 223
LATCH. enqueue hash chains 1,035 794-241
LATCH. cache buffers chains 2,182 2,425 243
STAT... db block gets 60 304 244
STAT... db block gets from cach 60 304 244
STAT... session logical reads 89 374 285
LATCH. shared pool 1,016 515-501
STAT... Elapsed Time 4,370 3,681-689
LATCH. row cache objects 2,063 1,082-981
STAT... undo change vector size 3,908 29,856 25,948
STAT... physical read bytes 8,192 40,960 32,768
STAT... physical read total byt 8,192 40,960 32,768
STAT... cell physical IO interc 8,192 40,960 32,768
STAT... session uga memory max 123,512 65,488-58,024
STAT... session uga memory 65,488 0-65,488
STAT... file io wait time 8,767 113,342 104,575
STAT... redo size 5,220 167,168 161,948
STAT... logical read bytes from 729,088 3,063,808 2,334,720

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
11,252 8,792-2,460 127.98%

The PL/SQL process is successfully completed.