Last week I was busy with my training on PL/SQL for PSI Data Systems. I was discussing the “PL/SQL Performance tuning with BULK COLLECT” with participants and I demonstrated the performance boost with the example. I would like to share the discussion with this post.
Scenario: Having a table with 10 million records needs to store into the PL/SQL Table by Index collection using the Cursors. The code snippet is as below.
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
CURSOR empcur IS SELECT * FROM testemp;
TYPE emp_table_type IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
idx number := 1;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
OPEN empcur;
LOOP
FETCH empcur INTO emp_table(idx);
idx := idx + 1;
EXIT WHEN empcur%NOTFOUND;
END LOOP;
CLOSE empcur;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (round((time_after – time_before)/60));
END;/
The above code without any performance trick, it took nearly 5 minutes (in my laptop with normal configuration) to complete.
The reason behind the bad performance is, all we know that in PL/SQL there are two statement executors to execute the program. One is SQL statement executor which executes only SQL statements in the program and the other one is PL/SQL statement executor who will execute procedural instructions.
Whenever a PL/SQL program is executing, a context switching will take place between the two statement executors. Especially if the program is doing some iterative task which contains the combination of SQL and PL/SQL code, it needs frequent context switching which Leeds into performance drawback.
This is what happening in the above code while fetching the records one by one in the loop. For each iteration there is a context switching and this will happens over 10 million times.
To avoid the context switching repeatedly, we need to collect the data at once using the below code.
The same code has been modified with the BULK COLLECT as follows.
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
CURSOR empcur IS SELECT * FROM testemp;
TYPE emp_table_type IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
OPEN empcur;
FETCH empcur BULK COLLECT INTO emp_table;
CLOSE empcur;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (round((time_after – time_before)/60));
END;
/
The above code with BULK COLLECT took just 1 minute to complete. So we nearly increased 80% of the performance.
Similarly in PL/SQL there are many such tuning tips are there. So keep visit my blog for more tips.
Happy programming.
Dan Bricklin was co-creator of VisiCalc, the first spreadsheet software available for personal computers. He received the Grace Hopper Award in 1981 for VisiCalc. He also founded Trellix, a website software company now owned by Web.com, and is founder and president of Software Garden.
Larry Ellison founded Oracle in 1977 with $2000 of his own money. Oracle published the first commercially-available relational database and essentially launched the database market. Today, Oracle is the world’s leading supplier of database software and the second largest independent software company with nearly $10 billion in annual revenue. Oracle recently acquired PeopleSoft, Siebel Systems and Hyperion.
Linus Torvalds is a Finnish software engineer who initiated development of the Linux kernel. At the time, the GNU Project had created many of the components necessary for a free software operating system but lacked a solid kernel. The result is Linux, the world’s most popular operating system that’s not Windows.
Marc Andreessen was the co-author of Mosaic, the first widely-used web browser, and co-founder of Netscape Communications. AOL acquired Netscape in 1999 for $4.2 billion and made Andreessen its Chief Technology Officer. Andreessen was also the co-founder and chairman of software company Opsware (originally Loudcloud) when it was acquired by Hewlett-Packard. Currently he is co-founder of Ning, a company that provides a platform for social-networking websites.
Larry Page and Sergey Brin, Ph.D. students at Stanford, invented Google in 1996 as a research project. The Google website launched in 1997, and by the end of 1998 it had indexed about 60 million web pages. In 2000, Google began selling advertisements associated with search keywords and launched the pay-per-click model, until then a rarity in advertising. Google launched its IPO in 2004, raising $1.67 billion and giving it a market capitalization of $23 billion, making Page and Brin instant billionaires.
Steve Jobs is the CEO, chairman and co-founder of Apple Inc., and is the founder and former CEO of Pixar Animation Studios. In fiscal 2007, Apple had worldwide sales of $24 billion. Apple’s iPod and iTunes dominate the portable and online music markets with nearly 80% market share. The Apple Macintosh owns 8% of the personal computer market and provides an effective counter-weight to the dominance of Microsoft Windows. Pixar led the surge of software-animated feature films, including Toy Story and Finding Nemo, resulting in eight academy awards. The Walt Disney Company purchased Pixar from Jobs in 2006 for $7.4 billion.
Sir Tim Berners-Lee is an English developer who invented the World Wide Web in March 1989. Berners-Lee published the first website on August 6, 1991. The site provided an explanation about what the World Wide Web was, how to use a web browser, and how to set up a web server. Berners-Lee made his idea freely available, with no patent or royalties due. In 1994, Berners-Lee founded the World Wide Web Consortium (W3C) at the Massachusetts Institute of Technology. The W3C declared their standards must be based on royalty-free technology so they can be easily adopted by anyone.
Bill Gates is co-founder and chairman of Microsoft, the world’s largest software company with 79,000 employees in 102 countries and $51 billion in annual sales. More than 90% of personal computers use the Microsoft Windows operating system, and nearly 50% of web servers run Microsoft software. Microsoft also dabbles in cable TV, Internet portals, computer hardware, and gaming with the XBox. Microsoft is currently in process to acquire Yahoo.
