We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.

In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.


select * from BULK_COLLECT_TEST;

20000 rows created.

–BLOCK1:Using Loops
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
      open c1;
       fetch c1 into rec1;
    exit when c1%notfound;
    end loop;

total Elapsed Time is : 45 Secs

–BLOCK2: Using Bulk Collecting
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
open c1;
   fetch c1 bulk collect into rec1;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

–Bulk Collect Example using LIMIT :
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
    open c1;
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    end loop;
    exit when c1%notfound;
    end loop;

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like ‘c_sgoud’ — User name of the terminal ( may be your windows login name )

Alter system kill session ‘146,46619’

select sid, serial# from v$session where username = ‘USER’;

alter system kill session ‘SID,SERIAL#’;

you need to find first lock
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;
by this query you can find lock

then you can kill
col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
— b.module,
from v$session b, v$process a
b.paddr = a.addr
and type=’USER’
and b.status=’ACTIVE’
–and b.module like ‘%blbn%’
–and b.username=’ENBAPP23′
order by program ,OS_USER

 How to Unlock the Objects

The following query could be useful :

Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))

Get the Process id from the query. Login as unix user and run the following command to kill the process.

From your command prompt, type
sqlplus “/ as sysdba”

Once logged in as SYSDBA, you need to unlock the scott account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;