FORALL BULK COLLECT Performance Test in Oracle

Examples to Test the BULK Collect:

Example-1:

Test to compare the performance of populating plsql table type variable using for loop cursor and bulk collect.

declare

    type bulk_test_mah_tab is table of bulk_test_mah%rowtype;

    v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();

    v_start_time  pls_integer;

begin

    v_start_time :=dbms_utility.get_time;

    for i in (select * from bulk_test_mah) loop

        v_bulk_test_mah_tab.extend; -- to increment one cell to hold new values       

        v_bulk_test_mah_tab (v_bulk_test_mah_tab.last) := i;

    end loop;

    dbms_output.put_line ('Time taken by for loop -> ' || (dbms_utility.get_time - v_start_time ) ||' for rows -'|| v_bulk_test_mah_tab.count);

    rollback;

    v_start_time :=dbms_utility.get_time;

    select * bulk collect into v_bulk_test_mah_tab from bulk_test_mah;

    dbms_output.put_line ('Time taken by bulk collect ->' || (dbms_utility.get_time - v_start_time) || ' for rows -' || v_bulk_test_mah_tab.count);

exception

    when others then

        dbms_output.put_line ('error -' || sqlerrm );

end;

output1:

Time taken by for loop -> 30 for rows -57958

Time taken by bulk collect ->10 for rows -57958

Conclusion -1: It is proven with above example-1 that populating plsql table type with bulk collect is much more efficient than populating it using for loop.

------------------------------------------------------------------------------------------------

Example-2:

Test to compare the time taken by select bulk collect into and for all statement with DML associated with it.

declare

    st_time pls_integer ;

    type ord_mah_tab is table of ord_mah.order_no%type;

    v_ord_mah_tab ord_mah_tab;

begin

    st_time := dbms_utility.get_time;

    dbms_output.put_line ('st_time 1 is -> ' || st_time );

select order_no bulk collect into v_ord_mah_tab from ord_mah;

dbms_output.put_line ('Time taken for select with bulk collect ->' || (dbms_utility.get_time - st_time ) );

    st_time := dbms_utility.get_time;

    dbms_output.put_line ('st_time 2 is -> ' || st_time );

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count

    update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);

    dbms_output.put_line ('Time taken for exeuction using forall  ->' || (dbms_utility.get_time - st_time ) );

    rollback;

exception

    when others then

        dbms_output.put_line ( dbms_utility.format_error_stack || '-' || dbms_utility.format_error_backtrace );

end;

output2:

st_time 1 is -> 431409877

Time taken for select with bulk collect ->4

st_time 2 is -> 431409881

Time taken for exeuction using forall  ->25009

Conclusion -2:

From the above test, it is proven that select bulk collect into is executed in no time and the over head is associated with update statement in forall.

------------------------------------------------------------------------------------------------

Example-3:

Test to compare DML using direct update, using for loop and using FORALL statements

declare

    st_time pls_integer ;

    type ord_mah_tab is table of ord_mah.order_no%type;

    v_ord_mah_tab ord_mah_tab;

begin

    st_time := dbms_utility.get_time;

    dbms_output.put_line ('st_time 1 is -> ' || st_time );

    update ord_mah set customer_po_no = substr(customer_po_no,1,7);

    dbms_output.put_line ('Time taken for exeuction using direct update ->' || (dbms_utility.get_time - st_time ) );

    rollback;

    st_time := dbms_utility.get_time;

    dbms_output.put_line ('st_time 2 is -> ' || st_time );

    for i in (select order_no from ord_mah) loop

        update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = i.order_no;

    end loop;

    dbms_output.put_line ('Time taken for exeuction using for loop ->' || (dbms_utility.get_time - st_time ) );

    rollback;

    st_time := dbms_utility.get_time;

    dbms_output.put_line ('st_time 3 is -> ' || st_time );

    select order_no bulk collect into v_ord_mah_tab from ord_mah;

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count

    update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);

    dbms_output.put_line ('Time taken for exeuction using forall  ->' || (dbms_utility.get_time - st_time ) );

    rollback;

exception

    when others then

        dbms_output.put_line ( dbms_utility.format_error_stack || '-' || dbms_utility.format_error_backtrace );

end;

output3:

st_time 1 is -> 431311406

Time taken for exeuction using direct update ->137

st_time 2 is -> 431311654

Time taken for exeuction using for loop ->22853

st_time 3 is -> 431334572

Time taken for exeuction using forall  ->23074

------------------------------------------------------------------------------------------------

LIMIT Clause:

Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. This gives you the benefits of bulk binds, without hogging all the server memory.

Example-4:

Test to limit the bulk collect into given number of rows

declare

    type bulk_test_mah_tab is table of bulk_test_mah%rowtype;

    v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();

    v_start_time  pls_integer;

    cursor c1 is select * from bulk_test_mah;

begin

    v_start_time :=dbms_utility.get_time;

    open c1;

    loop

    fetch c1 bulk collect into v_bulk_test_mah_tab limit 10000;

    --exit when c1%notfound;

    exit when v_bulk_test_mah_tab.count = 0;

   

    dbms_output.put_line ('No of rows in table type ->' || v_bulk_test_mah_tab.count );

    end loop;

    close c1;

exception

    when others then

        dbms_output.put_line ('error -' || sqlerrm );

end;

Output-4:

No of rows in table type ->10000

No of rows in table type ->10000

No of rows in table type ->10000

No of rows in table type ->10000

No of rows in table type ->10000

No of rows in table type ->7958

-----------------------------------------------------------------------------------------------

Example-5:

Test to track the errors using SAVE EXCEPTIONS while  performing DML using FORALL

declare

    type ord_mah_tab is table of ord_mah.order_no%type;

    v_ord_mah_tab ord_mah_tab;

begin

    select order_no bulk collect into v_ord_mah_tab from ord_mah;

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count save exceptions

        update ord_mah set customer_po_no = customer_po_no ||'X' where order_no = v_ord_mah_tab(i);

exception

    when others then

        if sqlcode= -24381 then

            for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop

               dbms_output.put_line ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );

            end loop;       

        else

            dbms_output.put_line ( dbms_utility.format_error_stack || '- ORA-' || dbms_utility.format_error_backtrace );

        end if;

end;

Output-5: The below are the failed rows while performing update

1- ORA-12899

2- ORA-12899

3- ORA-12899

9199- ORA-12899

9313- ORA-12899

14968- ORA-12899

ORA-12899: value too large for column

-----------------------------------------------------------------------------------------------

Example-5:

Test FORALL with sparse collections by using VALUES OF and/or INDICES OF clauses

Use INDICES OF for Nested table type collection and VALUES OF for associative array type collection

In a FORALL statement, the VALUES OF clause allows the values of one collection to be used as a index pointers to another collection.

DECLARE

  TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;

  TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

  l_tab1  t_tab1 := t_tab1();

  l_tab2  t_tab2 := t_tab2();

BEGIN

  FOR i IN 1 .. 1000 LOOP

    l_tab1.extend;

    l_tab1(l_tab1.last).id := i;

    IF MOD(i, 100) = 0 THEN

      l_tab2.extend;

      l_tab2(l_tab2.last) := i;

    END IF;

  END LOOP;

  l_tab1.delete(301);

  l_tab1.delete(601);

  l_tab1.delete(901);

  -- This would fail due to sparse collection.   --FORALL i IN l_tab.first .. l_tab.last

  --  INSERT INTO tab1 VALUES l_tab(i);    -- This works fine with sparse collections.

  FORALL i IN INDICES OF l_tab1

    INSERT INTO tab1 VALUES l_tab1(i);

  -- This works fine for collections of indexes   -- pointing to elements of another collection.

  FORALL i IN VALUES OF l_tab2

    INSERT INTO tab1 VALUES l_tab1(i);

END;

PLS-00436 Restriction in FORALL Statements Removed in 11G

The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct.

If we execute the following test in ORACLE 10G, we will get the following error

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Example-6:

CREATE TABLE forall_test (

  id          NUMBER,

  description VARCHAR2(50)

);

INSERT INTO forall_test VALUES (1, 'ONE');

INSERT INTO forall_test VALUES (2, 'TWO');

INSERT INTO forall_test VALUES (3, 'THREE');

INSERT INTO forall_test VALUES (4, 'FOUR');

INSERT INTO forall_test VALUES (5, 'FIVE');

COMMIT;

DECLARE

  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab t_forall_test_tab;

BEGIN

  -- Retrieve the existing data into a collection.

  SELECT *

  BULK COLLECT INTO l_tab

  FROM   forall_test;

  -- Alter the data in the collection.

  FOR i IN l_tab.first .. l_tab.last LOOP

    l_tab(i).description := 'Description for ' || i;

  END LOOP;

  -- Update the table using the collection.

  FORALL i IN l_tab.first .. l_tab.last

    UPDATE forall_test

    SET    description = l_tab(i).description

    WHERE  id          = l_tab(i).id;

  COMMIT;

END;

Output:

-----------------------------------------------------------------------------------------------

Example-7: Testing BULK COLLECT with RETURNING clause

declare

    type ord_mah_tab is table of ord_mah.order_no%type;

    v_ord_mah_tab ord_mah_tab;

begin

    delete from ord_mah  returning order_no bulk collect into v_ord_mah_tab;

    rollback;

    dbms_output.put_line ('No of Rows going to be deleted -> '  || v_ord_mah_tab.count );

end;

Output-7:

No of Rows going to be deleted -> 17960

Example-8: Testing BULK COLLECT with FORALL, RETURNING

declare

    type ord_mah_ord is table of ord_mah.order_no%type;

    v_ord_mah_ord ord_mah_ord;

    v_ord_mah_tab ord_mah_ord;

begin

    select order_no bulk collect into v_ord_mah_ord from ord_mah where rownum < 101;

    forall i in 1..v_ord_mah_ord.count

    delete from ord_mah  where order_no = v_ord_mah_ord(i) returning order_no bulk collect into v_ord_mah_tab;

    rollback;

    dbms_output.put_line ('No of Rows in v_ord_mah_ord -> '  || v_ord_mah_ord.count );

    dbms_output.put_line ('No of Rows in v_ord_mah_tab -> '  || v_ord_mah_tab.count );

end;

output-8:

No of Rows in v_ord_mah_ord -> 100

No of Rows in v_ord_mah_tab -> 100

Example-9: Testing SQL%BULK_ROWCOUNT

declare

    type ord_mah_tab is table of ord_mah.customer_po_no%type;

    v_ord_mah_tab ord_mah_tab ;

begin

    select distinct customer_po_no bulk collect into v_ord_mah_tab from  ord_mah

        where customer_po_no in  ('621000441XX','621000442XX','253P14XX','M7312210BXX','FG04/TOOLCRIBXX','PLANNEDXX','AC449AXX','5500452851-0001188335XX','5500452851-0001180273XX');

        dbms_output.put_line ('no of rows ->' || v_ord_mah_tab.count );

    forall idx in v_ord_mah_tab.first..v_ord_mah_tab.last

        delete from ord_mah where customer_po_no = v_ord_mah_tab(idx);

    for idx in  1..v_ord_mah_tab.count loop

        dbms_output.put_line ('No of orders assosciated with customer po no ->' || v_ord_mah_tab(idx) ||' --- '|| SQL%BULK_ROWCOUNT(idx) );

    end loop;

    rollback;

exception

    when others then

        dbms_output.put_line ('Error-' || sqlerrm );

end;

Output-9:

no of rows ->9

No of orders associated with customer po no ->621000441XX --- 13

No of orders associated with customer po no ->621000442XX --- 11

No of orders associated with customer po no ->253P14XX --- 2

No of orders associated with customer po no ->M7312210BXX --- 2

No of orders associated with customer po no ->FG04/TOOLCRIBXX --- 2

No of orders associated with customer po no ->PLANNEDXX --- 2

No of orders associated with customer po no ->AC449AXX --- 13

No of orders associated with customer po no ->5500452851-0001188335XX --- 3

No of orders associated with customer po no ->5500452851-0001180273XX --- 5

Note:

·         In Oracle 10g and above, the optimizing PL/SQL compiler rewrites conventional cursor for loops to use a BULK COLLECT with a LIMIT 100, so code that previously didn't take advantage of bulk binds may now run faster.

·         Oracle 10g introduced support for handling sparse collections in FORALL statements (here).

·         The restriction on accessing individual columns of the collection with a FORALL has been removed in Oracle 11g (here).

·         For bulk updates and deletes the timing points remain unchanged. Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only fire at the start and the end of the the whole bulk operation, rather than for each row of the collection

DBMS_PIPE in Oracle -- Inter-Session Communication

The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.

There are two types of pipes:

• Implicit Pipes - These are created automatically when a message is sent with an unknown pipename using theSEND_MESSAGE function. Implicit pipes disappear when they are empty.
• Explicit Pipes - These are created using the CREATE_PIPE function. Explicitly created pipes must be removed using theREMOVE_PIPE function.

There are two levels of security for pipes:

• Public Pipes - These are accessible by any user with EXECUTE permission on the DBMS_PIPE package. Implicit pipes are always public, but they can also be created explicitly by calling the CREATE_PIPE function with the private parameter set to FALSE.
• Private Pipes - These are only accessible by sessions with the same ownerid as the pipe creator, stored programs owned by the pipe creator or by users connected as SYSDBA.

Oracle Points - Part-1

1.   Aggregate functions like Min, max, count, avg ignores null values in the columns

select count( department_id) from hr.employees

Gives 106 records where as we actually have 107 records.

2.   On external table we can’t perform the following.

a.   DML operations

b.   Index, Constraints, Trigger creations

Error “ORA-30657: operation not supported on external organized table”

3.   Single row functions

a.   They can be used in SELECT, WHERE, and ORDER BY clauses.

b.   They can modify the data type of the argument that is referenced.

c.    They can accept a column name, expression, variable name, or a user-supplied constant as arguments.

4.   Error - ORA-25154: column part of USING clause cannot have qualifier

5.   Correlated Sub Query

a.   The nested query executes after the outer query returns the row.

b.   Each row returned by the outer query is evaluated for the results returned by the inner query.

6.   GROUPING function is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.

7.   In the same GRANT statement we can’t put more than one table

8.   Flashback version queries provide a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.

1.   ROLLUP produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

2.   Error - ORA-02261: such unique or primary key already exists in the table occurs when we try to create primary key as well as unique key constraint on the same table column

SQL Statement Execution Flow

Suppose we have a following SQL statement.

select  employee_id, first_name

from hr.employees

where employee_id = 120

order by first_name

This statement will be validated in the following order

• Table list in the FROM clause
• Order BY clause columns, expressions
• Where clause conditions
• Finally Select list

Similarly for the following SQL

select employee_id, first_name, sum(salary)

 from hr.emp_data

where salary <> 0

group by employee_id, first_name

having sum(salary) > 0

order by employee_id, first_name

• Table list in the FROM clause
• Order BY clause columns, expressions
• Having Clause
• Group By Clause
• Where clause conditions
• Finally Select list

PARALLEL hint in Oracle

PARALLEL hint allows to create multiple slaves process in the background to perform the single task.

To completely read a table, optimizer uses FULL table scans.

By Using PARALLEL hint in case of FULL table scans we will get better query performance.

 Check for the number of CPU's available for your DB instance using the following

select value from
v$parameter
where name = 'cpu_count'

say output is 4

Example:

Create a table

create table parallel_test (id number, des varchar2(100));

insert sample data

insert into parallel_test (select level, 'desc-'|| level from dual connect by level <=500000);
commit;

Now if you compare the explain plans shown in the below image we can see the performance difference with and with out PARALLEL hint