Archive for the ‘PL/SQL’ Category

ACCESSIBLE BY clause versus Function Based Indexes

December 14, 2015

Oracle introduced the ACCESSIBLE BY clause in 12c to improve the security of subrograms.
Basically the scenario is following:
Let’s create a tax stored function (as hr user) which will be used by the same user in his depts subprogram.
The creator wanted to allow the execution of this function for oe user in it’s total_orders procedure.

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

HR user issued the necessary grant statement:

 
GRANT EXECUTE ON tax TO oe;
/

Note again, the creator can use this function in his depts procedure, but – for example – in depts2 not!

 
CREATE OR REPLACE PROCEDURE depts(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Procedure DEPTS compiled

However, if hr user wants to create a similar procedure with different name (such as depts2), it will receive an error:

 
CREATE OR REPLACE PROCEDURE depts2(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Error(8,1): PL/SQL: Statement ignored
Error(9,54): PLS-00904: insufficient privilege to access object TAX

This is same for oe user, who wants to use hr’s tax function in total_orders procedure:

 
CREATE OR REPLACE PROCEDURE total_orders
IS
v_total number;
BEGIN
SELECT sum(order_total) INTO v_total
FROM orders;
dbms_output.put_line('total:'||v_total||' tax of it:'||hr.tax(v_total));
END;
/
Procedure TOTAL_ORDERS compiled

When oe user wants to use hr’s tax function in total_orders2 procedure she or he will receive an Oracle error:

 
CREATE OR REPLACE PROCEDURE total_orders2
IS
v_total number;
BEGIN
SELECT sum(order_total) INTO v_total
FROM orders;
dbms_output.put_line('total:'||v_total||' tax of it:'||hr.tax(v_total));
END;
/
 PL/SQL: Statement ignored
PLS-00904: insufficient privilege to access object TAX

This information well known, no suprise.
Now hr wants to create a tax_ix function based index for tax function, because he or she wants to improve the performance
for that queries which refer to this function:

 
CREATE INDEX TAX_IX ON EMPLOYEES(TAX(SALARY));
SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TAX

This behavior is also know from ORACLE-BASE.
(https://oracle-base.com/articles/12c/plsql-white-lists-using-the-accessible-by-clause-12cr1)
What to do?
Let’s modify the definition of the tax function removing the ACCESSIBLE BY clause

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
--ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

Let’s try to create and use the index again:

 
CREATE INDEX TAX_IX ON EMPLOYEES(TAX(SALARY));
Index TAX_IX created.

The execution plan
We can see that the optimizer used our index in an INDEX RANGE SCAN operation.

 

 

 

Let’s modify our tax function allowing the ACCESSIBLE BY clause:

 
CREATE OR REPLACE  FUNCTION TAX(P_AMOUNT IN NUMBER) 
RETURN NUMBER  
DETERMINISTIC
ACCESSIBLE BY (depts,oe.total_orders)
IS M  NUMBER;
BEGIN
CASE WHEN P_AMOUNT <8000 THEN  M:=0.08; ELSE  M:=0.31;  
END CASE;
RETURN P_AMOUNT*M;
END;
/
Function TAX compiled

Let’s recompile our depts procedure:

 
CREATE OR REPLACE PROCEDURE depts(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
V_max_sal number;
BEGIN
SELECT MAX(salary) INTO v_max_sal
FROM employees
where department_id=p_deptno;
dbms_output.put_line
('The tax value in department('||p_deptno||') is: '||tax(v_max_sal));
END;
/
Procedure DEPTS compiled

Now we want to execute the depts procedure as hr:

 
EXEC depts(90)

Error report -
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TAX
ORA-06512: at "HR.DEPTS", line 5

It seems to be that hr is underprivileged for executing this function, but
1. The owner of both subrogram is hr.
2. The tax function was allowed for depts procedure to use it.
What is the solution?
According my knowledge I can say
1. Do not create function based index for that function which has
ACCESSIBLE BY clause
2. If you need the function based index, please do not specify ACCESSIBLE BY clause.
Any solution or workaround are appreciated.
Many thanks.
Laszlo
laszlo.czinkoczki@webvalto.hu

Advertisements

How to avoid the persistent state of package’s components?

August 4, 2014

Generally the persistent state of package’s component is useful, we don’t want to eliminate it.
Persistency means that the lifetime of package components(variables, cursors,exception definitions, etc.)
start with the instatiation of package and finish with the end of session.
Many developers collect, for example, the necessary exception definitions in a particular package and
later everybody can use them as many times in their session as they want without reloading them into memory.
Sometimes inconvenient, that our session “remembers” the previous state of package components.
Typical scenario when a package is instantiated in one session and later we modify the code of the package in an other session. After this modification in the first session we want to refer to an arbitrary component of the changed component we will get the following error message:

 
ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package "HR.CS" has been invalidated 

Many times when we want to implement an algoritm with recursion, we need one or more “global variables”.
Let see an example:
Consider the algoritm for the classical “Tower of Hanoi”:
(http://www.mathsisfun.com/games/towerofhanoi.html)

 
CREATE OR REPLACE PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
dbms_output.put_line(first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
/

Whe want to run procedure for 3 level tower then program generates the necessary steps for moving the tower from the first (“1”) position to the second(“2”).
exec hanoi(1,2,3) — we want to move a 3 level tower from the first position to the second

 
PROCEDURE HANOI compiled
anonymous block completed
1 => 2
1 => 3
2 => 3
1 => 2
3 => 1
3 => 2
1 => 2

Let’s count the number of the steps.
Not surprisingly, the result : 7. (2**3 -1 , where the 3 is the height of the tower).
We want to see step number before printing step instruction.
Not very easy! We can not use a local variabe, we need a “global” variable.
The first attempt could be the following:
Let’s create a package , called hanoi_pkg!

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i+1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

and run the procedure:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

The result -seems to be- correct.
Please, run again:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
8. step: 1 => 2
9. step: 1 => 3
10. step: 2 => 3
11. step: 1 => 2
12. step: 3 => 1
13. step: 3 => 2
14. step: 1 => 2

The problem is that our session remembers the latest value of the counter!
The reason is that the components of the package are persistent.
The private variable “i” (see i pls_integer:=0;) is also persistent, even this is a private (not public) variable!
How to solve the problem?
Please use the SERIALLY_REUSABLE pragma ;
The modified package:

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
PRAGMA SERIALLY_REUSABLE ;
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PRAGMA SERIALLY_REUSABLE ;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i + 1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

Now we test the modification:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Run again – when the package has been instantiated already!)
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Finally we can state, that with
PRAGMA SERIALLY_REUSABLE ; statement we removed the instantiated package from the memory.
This can be very useful when we continously develop a package, while other sessions want to use it.

Thanks for visting my site!
My e-mail has changed:
laszlo.czinkoczki@webvalto.hu
but
czinkoczkilaszlo@gmail.com
is still alive.

Restricted access to PL/SQL subprograms in Oracle 12c

July 10, 2013

Prior to Oracle 12c everyone can refer to a subprogram (helper program) in an other PL/SQL program unit if that user has execute privilege for the helper object or owns it.
Now, in Oracle 12c the creator of the helper can determine that
which program units can refer to it, even the other users have execute privilege for the helper objects or they have the EXECUTE ANY PRIVILEGE system privilege.
Even the the owner of the helper object is same as the PL/SQL
subprogram’s owner, but if the dependent object is not entitled to use
the helper subprogram it can not refer to helper PL/SQL subprogram.
The new feature is that the helper PL/SQL subprogram can have
an ACCESSIBLE BY (subprogram1,subprogram2, …) clause where the creator can provide the access to the subprograms listed after the ACCESSIBLE BY keywords.
In the following example HR user who created the tax function provided access of the tax function to the depts procedure (owned by HR) and to depts2 owned by CZINK user.
Note that HR issued the suitable object privilege to czink.
Let’s see the definition of the tax function and the GRANT statement:

CREATE OR REPLACE FUNCTION tax(BASE NUMBER)
RETURN NUMBER
ACCESSIBLE BY (depts,czink.depts2)
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN
  S:= 0.10;
ELSIF BASE<20000 THEN
  S:=0.25;
ELSE
  S:=0.3;
END IF;
RETURN BASE*S;
END;
/
GRANT EXECUTE ON tax TO czink;

Now HR user created a procedure called depts and executed it:

CREATE OR REPLACE
PROCEDURE depts(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

However if HR wants to create a depts2 procedure with the
following code, Oracle produces an error message, because the depts2 procedure WAS NOT ENTITLED to refer to the tax function:

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
Error(17,14): PLS-00904: insufficient privilege to access object TAX

Now CZINK user wants to create and execute a depts2 procedure
referring to the tax function owned by HR:

(Supposed that CZINK user has it’s own employees table)

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
IF r.manager_id IS NOT NULL THEN
SELECT last_name INTO MANAGER FROM employees
WHERE employee_id=r.manager_id;
ELSE
manager:='No Manager';
END IF;
DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
' tax:'|| HR.tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts2(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

Of course, if CZINK user created a procedure
(referring to HR’s tax function) with different name than depts2
then CZINK user would get the same error message.
(The DBA role was assigned to the CZINK user in my example)

Comment and benefits of using the ACCESSIBLE BY clause:

1. You can provide access to a helper PL/SQL programs only for those
PL/SQL subprograms which are really need to refer to them.
2. The restriction made for PL/SQL subprograms not for users.
3. Even if a user has a DBA role or “just” the
EXECUTE ANY PROCEDURE the user won’t be able to use the helper
PL/SQL subprogram, unless it(the helper program) allows
to access directly to the invoker program.
4. You can specify the ACCESSIBLE BY clause on package level
(not for individual members), like this:

CREATE OR REPLACE PACKAGE taxes ACCESSIBLE BY (depts,czink.depts2)
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER;
END taxes;
/
CREATE OR REPLACE PACKAGE BODY taxes
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF    BASE<4000 THEN   S:= 0.10;
ELSIF BASE<20000 THEN   S:=0.25;
ELSE  S:=0.3;
END IF;
RETURN BASE*S;
END tax1;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN   S:= 0.10;
ELSE   S:=0.3;
END IF;
RETURN BASE*S;
END tax2;
END taxes;
/

Iterative UPDATE with SKIP LOCKED option

August 28, 2012

Many times occur that we can not execute an UPDATE or DELETE statement, because a couple of rows are locked by an other session(s).
We can generate – and handle – exceptions, but this solution sometimes not enough.
Using the SKIP LOCKED clause – officially introduced in Oracle 11g- we can try to solve the problem incrementally.
Let’s suppose that other sessions sooner or later will finish their transactions. During their transactions we can modify the “free” (not locked) rows with this option.
Let’s see a simple case study:
Our goal is to increase all employees’s salary with 20% ,
however other session can prevent us from doing this modification.
First we create a table that is copy of employees owned by HR.
We modify the structure of the new table adding a column which will indicate whether the modification is done or not.
Initially the value of this column should ‘N’:
To check the timestamp of the modification we change the data type of hire_date from DATE to TIMESTAMP:

DROP TABLE emp4;
CREATE TABLE EMP4 AS SELECT * FROM EMPLOYEES;
ALTER TABLE EMP4 ADD MODIFIED CHAR(1) DEFAULT 'N';
ALTER TABLE EMP4 MODIFY hire_date TIMESTAMP;


We have two sessions:
In the first session we issue periodically the following statement:

UPDATE emp4 
set last_name=UPPER(LAST_NAME) WHERE SALARY >&SAL;


where Sal substitution variable should be 10000, 14000
For 10000 the result:
15 rows updated
This session has not issued the COMMIT statement!

In the second session execute the following PL/SQL program:

DECLARE
CURSOR c_emp is
SELECT * FROM  EMP4
WHERE modified ='N' FOR UPDATE SKIP LOCKED ;
r  emp4%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r;
EXIT WHEN C_EMP%NOTFOUND;
r.hire_date:=SYSTIMESTAMP;
UPDATE EMP4 SET salary=salary*1.2, 
                 modified ='Y', hire_DATE=r.hire_date
WHERE CURRENT OF c_emp;
DBMS_OUTPUT.PUT_LINE
(r.employee_id||': '||r.last_name||' modified');
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||c_emp%ROWCOUNT||' modifications were');
CLOSE c_emp;
END;
/

The result :
92 modifications were
Issue a COMMIT in the first session and repeat the UPDATE with 14000
The result:
3 rows updated
Run again the PL/SQL program:
The result:
12 modifications were.
When the first session issues a COMMIT, after the second session finish it’s modification
The result:
1 row updated
It is not guaranteed that this method always successful.
We can use it, if the likelihood of the fact that other transactions will be finished on time, great enough.

Dynamic SQL with FOR UPDATE clause

August 28, 2012

Many times we can hear that the Native Dynamic SQL can not be implemented with FOR UPDATE clause in a PL/SQL program.

In this article I will proof that we can associate a dynamic SQL statement with a weak ref cursor variable using FOR UPDATE clause.

Let us consider the following – simple- case study:
We would like to modify the salary column of an employees like table
(This means that the structure of the table is identical with HR’s employees table):
We increase the salary for those employees whose salary is less than a particular value.

Let’s see the code:

CREATE OR REPLACE PROCEDURE din_sql_upd
(p_tname VARCHAR2,p_sal NUMBER)
IS
TYPE c_emp IS REF CURSOR;
c  c_emp;
r EMPLOYEES%ROWTYPE;
v_updated_rows INTEGER:=0;
BEGIN
OPEN c FOR 'SELECT * FROM '||p_tname||' WHERE salary < '
|| p_sal ||' FOR UPDATE';
LOOP
FETCH c INTO r;
EXIT WHEN C%NOTFOUND;  
DBMS_OUTPUT.PUT_LINE(RPAD(R.LAST_NAME,20)||' Salary: '||r.salary);
IF r.salary < p_sal THEN
EXECUTE IMMEDIATE
'UPDATE '||p_tname ||' SET salary =salary*2 WHERE employee_ID=:r '
USING r.employee_id;
v_updated_rows:=v_updated_rows+1;
END IF;
END LOOP;
CLOSE c;
DBMS_OUTPUT.PUT_LINE(CHR(10)||v_updated_rows||' modifications made.');
END din_sql_upd;
/
EXECUTE din_sql_upd('employees',2500)


You can observe that the UPDATE statement does not contain
the WHERE CURRENT OF clause, because it is PROHIBITED!
This program demonstrates one of a kind workaround for solving this problem:
We used simply the primary key instead of WHERE CURRENT OF.

Design Considerations – Enhanced Bulk Binding in PL/SQL

April 22, 2009

The Bulk Binding is relatively old technology,
but unfortunatelly rarely used by developers.
Basically the Bulk Binding is buffer technology.
Whenever you want to execute an SQL Statement or Fetch statement in loop,
please consider the Bulk Binding.
When you communicate withmthe SQL Engine from your PL/SQL program,
then Oracle creates a - so called - context switch.
This consumes pretty amount of time in loop.
We can reduce it applying the buffer technology.
Lets see the following example.
1. We create a relatively big table (ca.1 200 000 rows)

CREATE TABLE big_emp(empno,last_name,first_name,salary)
AS
SELECT ROWNUM,E.last_name||ROWNUM,E.first_name||ROWNUM,E.salary
FROM employees E,employees D,employees F;

2. Run this procedure, which uses the traditional technology:
CREATE OR REPLACE PROCEDURE trad_fetch_open
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC trad_fetch_open __will use more than 15 seconds CPU time!

3. We can use explicit Bulk Binding with the following procedure.
   Observe that we can control the size of the buffer with the LIMIT clause!
CREATE OR REPLACE PROCEDURE
BULK_LIMIT(tname VARCHAR2,rows NUMBER := 10)
IS
type emp_cur_type IS REF CURSOR ;
type c_type is table of  BIG_EMP%rowtype;
c1 emp_cur_type; j number:=0; emp c_type;
S NUMBER:=0;
n1 number;
n2 number;
stmt CLOB;
BEGIN
  stmt:='SELECT * FROM '||tname;
  n1:=dbms_utility.get_cpu_time;
  OPEN c1 FOR stmt;
  LOOP
      FETCH c1 BULK COLLECT INTO EMP LIMIT rows;
      EXIT WHEN c1%NOTFOUND and emp.count=0;
      FOR I IN 1..EMP.COUNT LOOP
        S:=S+ EMP(I).SALARY;
      END LOOP;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC BULK_LIMIT('BIG_EMP',1000)
We can see that with relatively small buffer size(for example with 1000)
we can achieve very good result.
If we increase the size above 1000 the performance can be worse
according to enlarged memory handling!

4. In Oracle 10g R2 and later we can use implicit Bulk Binding.
Using the traditional FOR rec_var IN cursor_name LOOP statement
we can achieve the Bulk Binding with appropriate
setting of PLSQL_OPTIMIZE_LEVEL.
Consider the following example:
a. ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =2;
   Note this is the default value of this parameter in Oracle 10g and later.
b. Create the following procedure:
CREATE OR REPLACE PROCEDURE bulk_fetch_for
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;  n1 number;   n2 number;
BEGIN
 n1:=dbms_utility.get_cpu_time;
 FOR EMP2 IN C1 LOOP
   S:=S+ EMP2.SALARY;
 END LOOP;
 n2:=dbms_utility.get_cpu_time;
 DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
c. EXEC bulk_fetch_for -- will use less than 2 seconds CPU time!
   This is called AUTOBULKIFICATION!
d. Issue the following statement:
   ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =1;
e. Recompile the bulk_fetch_for procedure!
   ALTER PROCEDURE  bulk_fetch_for COMPILE;
f. EXEC bulk_fetch_for -- will use more than 15 seconds CPU time!

g. If we use the traditional OPEN FETCH technology we will get poor performance
   regardless the value of the PLSQL_OPTIMIZE_LEVEL parameter
CREATE OR REPLACE PROCEDURE trad_fetch_open IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
h. EXEC trad_fetch_for -- will use more than 15 seconds CPU time!
The conclusion is:
- Use the explict Bulk Binding if You want specify the size of the buffer.
- Use the implicit Bulk Binding if You want to rely on the Oracle
  to rewrite your program to improve the performance.

Adaptive Cursors in Oracle 11g

March 19, 2009

Up to Oracle 1og we have an algorythm, called Bind Peeking.

Briefly, this means the following:

  • In SQL statement we use a bind variable with not null value.
  • First time, when SQL statement was executed, the Oracle will apply Hard Parsing to establish the best execusion plan for this statement, using (“peeking”) the value of the bind variable.
  • Next time,when we use the same SQL, but with differernt bind value, the same execution plan will be used(called Soft Parsing)
  • This is very good for OLTP systems, where the data is dispersed approximately evenly.

In OLAP environment the Bind Peeking typically is not optimal, because there is no guarantee, that the data-according to the bind variable-is spreaded approximately evenly.

In Oracle 11g we can ask the server to see the subsequent values of the bind variable to determine whether the previous plan was optimal or Oracle has to create a new, better execution plan using relevant statistics (typically histograms).

Let’s see the following case study.

  • First, we create a simple table with an index for the record_type column.
  • Second, we populate it with the natural numbers from 1 to 10000.
  • Third, we modifiy the even numbers for the number 2. So, the half part of the table contains the same values and the other half part of the table contains different (odd) values.

CREATE TABLE ac_test
(id NUMBER, record_type NUMBER);
CREATE INDEX ac_test_ix ON ac_test(record_type);

DECLARE
TYPE test_tab_type IS TABLE OF ac_test%ROWTYPE INDEX BY PLS_INTEGER;
test_tab test_tab_type;
BEGIN
FOR i IN 1 .. 100000 LOOP
test_tab(I).id := i;
test_tab(I).record_type := i;
END LOOP;
FORALL i IN 1 .. test_tab.COUNT
INSERT INTO ac_test VALUES test_tab(i);
UPDATE ac_test SET record_type =2 WHERE MOD(record_type ,2)=0;
COMMIT;
END;
/

  • We clear the Shared Pool and make this feature enabled.
  • Using the DBMS_STATS package we create statistics (including histograms)

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION set “_optimizer_adaptive_cursor_sharing”= TRUE;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, ‘ac_test’,
method_opt=>’for all indexed columns size skewonly’, cascade=>TRUE);
END;
/

  • Now we create and populate a bind variable with 1 and execute an SELECT statement
  • Examine the execution plan
  • Observe that that cursor is Bind Sensitive, but not Bind Aware


VARIABLE v_rec_type NUMBER;
EXEC :v_rec_type := 1
SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
COL IS_BIND_SENSITIVE FORMAT A20
COL IS_BIND_AWARE FORMAT A20
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_text =
‘SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type’;

The result is similar to the following:

-------------------------------------------------------------------------
| Id | Operation                        | Name       |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| 1 | SORT AGGREGATE
| 2 | TABLE ACCESS BY INDEX ROWID       | AC_TEST    |
|* 3 | INDEX RANGE SCAN                 | AC_TEST_IX |
-------------------------------------------------------------------------


SQL_ID           CHILD_NUMBER    IS_BIND_SENSITIVE    IS_BIND_AWARE
43ydhqbsh1t4x    0                               Y                N
  • Now we can repeat the previous process  with the value 2 of the bind variable

  • We can observe  that the execution plan is the same.

  • Repeat the process again with the value 2 of the bind variable!

  • We can observe  that the execution plan is different!  

     

    ----------------------------------------------
    | Id  | Operation                  | Name    |
    ----------------------------------------------
    |   0 | SELECT STATEMENT           |         | 
    |   1 |  SORT AGGREGATE            |         | 
    |*  2 |   TABLE ACCESS FULL        | AC_TEST |

     

    
    SQL_ID        CHILD_NUMBER       IS_BIND_SENSITIVE    IS_BIND_AWARE
    ------------- ------------        ----------------    --------------
    43ydhqbsh1t4x            0                         Y               N
    43ydhqbsh1t4x            1                         Y               Y