RE: About Bulk Collect
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 20 Feb 2004 13:15:29 -0800
Mark,
The first method is more efficient. The question is what is a context
switch.
I believe Jonathan Lewis asked someone on the list ( me possibly ) what
a PL/SQL context switch was. I didn't have a good answer.
So now I am asking "What is taking place here that is a context switch?"
Not that I'm trying to be a smart aleck, just trying to get a definition.
VOS = Virtual Operating System.
I little knowledge of Oracle internals at this level.
I think you have to be Anjo or someone that has worked on the code to know
that. :)
The VOS is written to interact with the actual platform: Linux, Solaris,
Win32. It provides
a consistent API for the Oracle kernel.
This section does the actual IO and memory management. Not positive about
the memory part. My 'Scaling Oracle 8i' is at home. This is the book
where you
can read about VOS.
Other than the 'kernel' I can't recall what the other part is called.
IAC, it is written to the API for the VOS. Writing this way allows the
kernel to
be developed much more simply, as it is call a consistent API. There are
probably some exceptions to this, but I would think exceptions would be,
well, exceptional.
SAP is designed this way as well.
Jared
"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/20/2004 10:00 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
cc:
Subject: RE: About Bulk Collect
Seems to me, a context switch occurs every time a SQL statement is
executed from PL/SQL.
So, taking the example below:
-- Efficient method, using a bulk bind
FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
The above does a single context switch and does a bulk operation.
-- Slower method, running the UPDATE statements within a regular loop
FOR i IN Id.FIRST..Id.LAST LOOP
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
END LOOP;
This, on the other hand, does a number of context switches equal to the
size of the Id array.
What's VOS?
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is." --Horace
Walpole
-----Original Message-----
From: Jared.Still@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]=20
Sent: Friday, February 20, 2004 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: About Bulk Collect
Let's see if I can be the first to ask this question: ;)
How would you define a context switch in this situation?
Are values from registers in the VOS ( don't know if VOS actually has=20
registers )
pushed onto a stack, and it's address saved for later execution?
Each time a SQL statement is executed?
Jared
"Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/20/2004 07:03 AM
Please respond to oracle-l
=20
To: <oracle-l@xxxxxxxxxxxxx>
cc:=20
Subject: About Bulk Collect
Here is a very brief introduction to bulk collect if you never heard
about
it.
There are to engines to run PL/SQL blocks and subprograms.
PL/SQL engine runs procedural statements, while the SQL engine runs SQL
statements.
During execution, every SQL statement causes a context switch between
the
two engines. Performance can be improved reducing the number of contexts
switches using FORALL for bulk collection.
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine
for
each DML( Insert, Update, Delete) command you can use too with SELECT
statements.
DECLARE
TYPE Numlist IS VARRAY (100) OF NUMBER;
Id NUMLIST :=3D NUMLIST(7902, 7698, 7839);
BEGIN
-- Efficient method, using a bulk bind
FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
-- Slower method, running the UPDATE statements within a regular loop
FOR i IN Id.FIRST..Id.LAST LOOP
UPDATE Emp_tab SET Sal =3D 1.1 * Sal
WHERE Mgr =3D Id(i);
END LOOP;
END;
DECLARE
TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
Empno VAR_TAB;
Ename VAR_TAB;
Counter NUMBER;
CURSOR C IS SELECT Empno, Ename FROM Emp_tab WHERE Mgr =3D 7698;
BEGIN
-- Efficient method, using a bulk bind
SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
FROM Emp_Tab WHERE Mgr =3D 7698;
-- Slower method, assigning each collection element within a loop.
counter :=3D 1;
FOR rec IN C LOOP
Empno(Counter) :=3D rec.Empno;
Ename(Counter) :=3D rec.Ename;
Counter :=3D Counter + 1;
END LOOP;
END;
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList :=3D NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno =3D depts(i);
END;
DECLARE
TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
BEGIN
FOR j IN 1..5000 LOOP -- load index-by tables
pnums(j) :=3D j;
pnames(j) :=3D 'Part No. ' || TO_CHAR(j);
END LOOP;
FORALL i IN 1..5000 -- use FORALL statement
INSERT INTO parts VALUES (pnums(i), pnames(i));
FOR i IN 1..5000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
END;
To use bulk operation read PL/SQL users guides and reference, to know=20
about
more features of bulk operations.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
