Semana i – PL/SQL, BULK and Context Switch Costs



At my university we have this week called “semana i” (or innovative week). It is a week in which students can take workshops or courses in the topics they like, independently from the career they are studying. A lot of people use this time to travel and goof around, because there are also attractive activities like field trips, camps, visits outside the campus and even national and international trips, depends on the course you choose. All in order to develop a skill  or to “innnovate” on different levels (like the slogan says).

In my case (and because I’m too poor to pay for traveling expenses) I wanted to learn something related to my career (CS) and also learn something new, so I took a course of Oracle’s PL/SQL language for databases and a course on finance about wages and salaries, because it sounded interesting.

Right now I’m already finishing the PL/SQL one that had a duration of two days (from monday to tuesday). For it a previous teacher from a Database course organized the workshop with two Oracle’s MDC employees. Overall the course was fair, we saw features and syntax of the language, also in the last hour of the course we say an application developtment tool known as APEX, but just the plain basics.

The following is a small review of the language and something that caught up my attention. Hope you like it.


PL/SQL like the letters indicate is a procedural language, or easier to say an imperative language (the one that specifies in the the program HOW to do things). Is the language that integrated with SQL introduces a language able to run within the same server process both SQL and PL/SQL sentences. Also the procedural part manifest itself in the form of conditionals,  data structures like collections and hash tables, the use of loops and assignments, that SQL doesn’t has.


PL/SQL works with blocks, let me define a block as portions of code with some kind of meaning inside the application, blocks have 3 main sections: a declarative part (where several variables can be declared and initialized), an executable part (where the program or instructions to execute live) and a exception handling part (where all the raised errors in the block fall and can be treated).

What I actually wanted to talk about is a powerful feature that mainly characterizes the language, that is the use of Bulk statements.

Bulk statements

When performing an SQL sentence, sometimes the result can become pretty big (bulky). If we oversee that a query will be returning large portions of data and records on it’s execution, bulk statement inside PL/SQL can become very handy.

Bulk collect comes as an optimization tool to manipulate large amounts of collections resulting from a query. But how does it optimizes it? What the bulk processing features does is a significant reduction in the number of context switches, that are required in order to perform both SQL and PL/SQL statements.

The thing is that when performing a query inside PL/SQL language both language engines have to communicate. So one process has to be paused while the other finishes its tasks and rapidly continue with the one paused once the current one finish processing. This when handling large amount like in the example below.

 FOR i IN 1..150
 UPDATE employees SET salary = employees.salary + 150 WHERE employee_id = i;

When executing the block above we have that the update statement is perfomed 150 times, meaning that in total we are going to have 150 context switch operations in order to have SQL execute each one of them.

Of course we can do other approaches, this is just the naive one, the idea is that with bulk statements just one context switch is needed because on the first execution it fetches multiple rows into one or more collections. Having positive impact in the overall performance of the program.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s