The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
We mostly concentrate on the SQL statement to tune performance issues. It is worth noting that excessive context switching can affect performance. This would be substantially significant when we are carrying out SQL statements in a loop. The features discussed below were introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i, these features are being improved on with every new release.
Two PL/SQL features, Bulk Bind and Bulk collect help in improving performance and utilizing resources effectively from within PL/SQL code. These features reduce context switching, (i.e., multiple SQL statements being executed from the code resulting in calls to SQL engine), and carry out the operation on the concerned object at one go. Since the SQL statements are fired in a bundle, frequent access to SQL engine is reduced.
In cases where the PL/SQL code is being executed on a different terminal than the server itself, this would also result in optimal network usage rather than too frequent network calls to access the SQL engine.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing. Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.
Syntax:
... bulk collect into collection...
For example, let us assume that we need to load all pending transactions into a temporary table and process them one by one. As part of validation, there is a need to refer to the data in the same table, from time to time, for each transaction being processed. One possible method to write the code would be to load all of the data in the temporary table to a collection type. This way, additional queries on the table could be avoided (context switch) and the processing could be carried out in PL/SQL itself. This idea is further improved on by the use of the bulk collect option, as all data is loaded into PL/SQL at the same time.
declare
type pndidr is table of mtl_pending_trx.trx_id%type index by binary_integer;
type pndqty is table of mtl_pending_trx.trx_qty%type index by binary_integer;
type pndval is table of mtl_pending_trx.trx_cost%type index by binary_integer;
l_pndidr pndidr;
l_pndqty pndqty;
l_pndval pndval;
...
begin
select trx_id, trx_qty, trx_cost * trx_qty bulk collect
into l_pndidr, l_pndqty, l_pndval
from mtl_pending_trx;
...
for i in l_pndidr.first .. l_pndidr.last loop
dbms_output.put_line(l_pndidr(i));
dbms_output.put_line(l_pndqty(i));
dbms_output.put_line(l_pndval(i));
end loop;
...
end;
Bulk Binds (Writing data in bulk)
Bulk binds improve performance of DML statements by minimizing the number of switches between the PL/SQL and SQL engines.
You may have a piece of code, which has multiple update, delete or insert statements on the same table. This results in multiple calls to the SQL engine for carrying out the transaction. By using bulk binds, you can carry out mass scale data manipulation at one shot. The altered data has to be stored in a PL/SQL collection in the code.
The FORALL statement is used for doing the bulk-processing job at one go. This statement is similar to the FOR-LOOP statement except that LOOP/END LOOP key words are not used. The FORALL statement needs a range to work on, along with whatever DML activity is to be carried.
Syntax:
forall
update/insert/delete
Below is a simple example without Bulk Binds. Notice the number of times the update is performed. Every SQL statement present in the PL/SQL code results in a call to the SQL engine for processing.
create or replace procedure updsal is
cursor cr_emp is
select empno, job, sal
from amemp
where job in ('MANAGER', 'PRESIDENT', 'DBA');
begin
for rec in cr_emp loop
... some checks on the employee
...
if rec.job = 'MANAGER' then
update amemp
set sal = sal * 1.1
where empno = rec.empno;
else
update amemp
set sal = sal * 1.2
where empno = rec.empno;
end if;
end loop;
end;
The above code is changed as shown below, using the bulk binding option. Notice the single update call to the backend.
create or replace procedure updsal is
cursor cr_emp is
select empno, job, sal
from amemp
where job in ('MANAGER', 'PRESIDENT', 'DBA');
type amemp_tab1 is table of amemp.empno%type
index by binary_integer;
type amemp_tab2 is table of amemp.sal%type
index by binary_integer;
empnum amemp_tab1;
empsal amemp_tab2;
cnt number := 0;
begin
for rec in cr_emp loop
... some checks on the employee
...
cnt := cnt + 1;
empnum(cnt) := rec.empno;
if rec.job = 'MANAGER' then
empsal(cnt) := rec.sal * 1.1;
else
empsal(cnt) := rec.sal * 1.2;
end if;
end loop;
forall i in 1 .. cnt
update amemp
set sal = empsal(i)
where empno = empnum(i);
end;
/
Below is an example that uses both bulk binding and collection together.
declare
type emp_t is table of amemp.empno%type;
lst emp_t;
begin
select empno bulk collect
into lst
from amemp
where sal < sal =" sal" empno =" lst(i);" job =" 'DBA';" sal =" sal" empno =" lno(i)" object_type =" 'INDEX';"> declare
2 type objtab is table of dba_objects%rowtype;
3 l_objtab objtab;
4 begin
5 select * bulk collect
6 into l_objtab
7 from dba_objects
8 where object_type = 'INDEX';
9
10 for i in l_objtab.first .. l_objtab.last loop
11 dbms_output.put_line('index name:' l_objtab(i).object_name);
12 end loop;
13 end;
14 /
into l_objtab
*
ERROR at line 6:
ORA-06550: line 6, column 10:
PLS-00597: expression 'L_OBJTAB' in the INTO list is of wrong type
ORA-06550: line 7, column 3:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
You can also use VARRAYs with bulk binding options. Below is a simple example:
declare
type pndtab is varray(200) of mtl_pending_trx%rowtype;
l_pndtab pndtab;
cursor cr_rec is
select trx_id, trx_qty, trx_cost
from mtl_pending_trx;
begin
open cr_rec;
fetch cr_rec bulk collect into l_pndtab;
close cr_rec;
...
for i in l_pndtab.first .. l_pndtab.last loop
dbms_output.put_line(l_pndtab(i).trx_id);
end loop;
end;
/
The example below processes records in a loop, using the FETCH-LIMIT option. Please note that every time the FETCH command is executed, it returns the next 500 records and so on. The EXIT statement is present at the end of the loop so that the last batch of records is processed before exiting the loop.
declare
type segtab is table of dba_segments%rowtype;
type instab is table of dba_segments.segment_name%type index by binary_integer;
l_segtab segtab;
l_instab instab;
cursor cr_rec is
select *
from dba_segments
where segment_type = 'INDEX';
begin
open cr_rec;
loop
l_segtab.delete;
fetch cr_rec bulk collect into l_segtab limit 500;
if l_segtab.count > 0 then
l_instab.delete;
for i in l_segtab.first .. l_segtab.last loop
...
l_instab(i) := l_segtab(i).segment_name;
end loop;
forall i in l_segtab.first .. l_segtab.last
insert into am_segments(segment_name)
values(l_instab(i));
end if;
exit when cr_rec%notfound;
end loop;
close cr_rec;
end;
/
%BULK_ROWCOUNT
Oracle provides a composite attribute that is designed for use with the FORALL statement. For each DML activity carried out in the FORALL statement, a record will be stored in this attribute that provides information about the rows processed. If no data is processed the attribute returns 0 for the concerned record. The example below will clarify the use of this attribute.
declare
type instab is table of dba_users.username%type index by binary_integer;
l_instab instab;
begin
select username bulk collect
into l_instabf
from dba_users;
forall i in l_instab.first .. l_instab.last
insert into am_segments(segment_name)
select segment_name
from dba_segments
where owner = l_instab(i);
dbms_output.put_line('Total count: ' sql%rowcount);
for i in l_instab.first .. l_instab.last loop
dbms_output.put_line(l_instab(i) ': ' sql%bulk_rowcount(i));
end loop;
end;
/
The above block returns the output shown below. The SQL%ROWCOUNT attribute returns the total records processed in the FORALL statement and the SQL%BULK_ROWCOUNT attribute returns the records processed for each individual record in the collection.
Total count: 4010
SYS: 1408
OUTLN: 6
DBSNP: 4
SYSMAN: 735
MGMT_VIEW: 0
WK_TEST: 34
MDSYS: 86
ORDSYS: 8
CTXSYS: 74
ANONYMOUS: 0
...
Conclusion
The above-mentioned bulk binds and collects are basically PL/SQL development features. Developers should make these a regular part of coding. In the long run, such features will immensely benefit the over all performance. Care should be taken to carry out collection processing without excessive hits on memory.
No comments:
Post a Comment