Happy Diwali to all

Dear All,

I wish you all a very happy, safe, cracking, sparkling and colourful Diwali.  Have a great year ahead !!!
 
India breaks away
India breaks away
 
 
 
 
Indian cricket has already gifted the Diwali gift with cracking clean sweep over England 🙂

 

 

 

Advertisements

Protect your database with VPD

Hi All,

Database security is an important aspect of database management and administration. Several database security solutions have been designed to prevent database from corruption, hacking threats, and unauthorized data access.

 Fine Grained Access Control (FGAC) is one of the security approaches which allow the logical and virtual partition of a database. In a multi user database environment, it becomes cumbersome activity to segregate schema and thus the data, based on user roles. Therefore, a security policy was required which can apply row level security and allows the current user to access only authorized data. For example, in an EMPLOYEE database, all managers can access only Managerial data, all HRs can view only HR related data and so on.

FGAC is also known as Virtual Private Database. It is because fine grained access can only be achieved after the database has been ‘virtually privatized’ for a user.

Thanks to Exforsys for publishing the paper. Read the complete story in the below link

Implement VPD in your database

Do post your comments/queries/feedback.

Saurabh

Result Caching feature in Oracle 11g

Caching has always been the pick of the programmers world. These days applications formulate and maintain different strategy for Cache Manager. Database presents an efficient cache manager which is capable of caching the SQL query results, PL/SQL functions results and client OCI result cache.

In earlier versions of Oracle, caching mechanism was limited up to architectural concepts, but Oracle 11g makes it a programmer’s tool to explicitly govern the application performance.

My obligations to Exforsys to publish my write up on result cache feature in Oracle 11g

Read Result Cache in Oracle 11g

I ‘ll be back with some more new findings.

Bye, SBH

Enable code optimization technique using PLSQL_OPTIMIZE_LEVEL

Attribution: This article is a complimentary excerpt from Oracle Advanced PL/SQL Developer Professional Guide

Oracle initialization parameter PLSQL_OPTIMIZE_LEVEL

The parameter was introduced in Oracle 10g to decide the level of optimization applicable for a program unit during compilation. The optimization during compilation means the removal of dead code, code movements to optimize iterative constructs, inline the calls if required, and choosing the best philosophy to optimally compile the program.

It is set by the DBA at system, session and object level. It is the object compilation parameter which is retained by the object library unit. It can be queried in USER_PLSQL_OBJECT_SETTINGS view for a given object.

Prior to 11g release, the parameter could accommodate only three valid values i.e. 0, 1, and 2. Oracle 11g introduced an additional optimization level 3. By default, the parameter value is 2. The compiler’s effort is directly proportional to the parameter value i.e. more the value, more the compiler’s effort.

It can be set using ALTER [SYSTEM | SESSION] command as shown below

/*To view the current parameter setting*/

Conn / as SYSDBA
Connected.

SQL> show parameter plsql_optimize_level

NAME TYPE VALUE
———————————— ———– —————–
plsql_optimize_level integer 2

/*Modify the setting for the current session*/
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1
/

Session altered.

The description for each value is as below

Case 1: PLSQL_OPTIMIZE_LEVEL = 0

This level represents ‘No Optimization’ mode of the compiler. The compiler only maintains the code evaluation order and executes it without any efforts on code optimization.

Let us write a small program and compare the same with other optimization levels also

Scenario: In your application, you have to give insert a delay of approx 1 second during some user operations. You create a program which runs a large loop to delay the control transfer.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:19294

PL/SQL procedure successfully completed.

Case 2: PLSQL_OPTIMIZE_LEVEL = 1

This level represents ‘Elementary and eliminative Optimization’ level by maintaining the same code order. The compiler optimizes the code by eliminating the code of irrelevant logic.

We shall re execute the previous PL/SQL block and check the difference.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:7112

PL/SQL procedure successfully completed.

Note that the execution time has reduced by 60% during this optimization level.

Case 3: PLSQL_OPTIMIZE_LEVEL = 2

This is the standard level of optimization where the compiler intelligently does the code refactoring and uses advanced optimization techniques to modify the code structure in order to achieve the best.

Re executing the previous PL/SQL block to compare the ‘Execution time’ results.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:0

PL/SQL procedure successfully completed.

Just observe the incredible intelligence of the PL/SQL compiler. The complete code has been re-structured. Analytically, the code inside the FOR loop is not relevant to the loop. So it has been moved outside the loop and now, the loop is completely ignored by the compiler. Therefore, the execution time comes out to be zero.

Case 4: PLSQL_OPTIMIZE_LEVEL = 3

This is the latest optimization level introduced in Oracle 11g. It is a sort of ‘Forced’ optimization level, which Oracle terms as ‘High Priority’ optimization. The code optimizer does the code refactoring and automatically inlines all program calls in the program unit.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Session altered.

As mentioned earlier, the parameter is treated as the object compilation factors. Hence it is stored in the object library unit and can be queried in
[ALL | DBA | USER]_PLSQL_OBJECT_SETTINGS.

Refer Oracle 11g Documentation on the topic

My RMOUG abstract (Inheritance in Oracle)

Duke, the Java Mascot, in the waving pose. Duk...

 

Inheritance in Oracle: Exploring the Descendents

“The Abstract Object model of database is an under implemented and commercially unexplored flavor of database. In the late 1980s, relational model of Oracle faced several deficiencies, which forced the evolution of Object model. Undoubtedly, relational model did all well for flat data storage, maintenance of integral data, and information security but could not offer concrete approach to accommodate complex data structures of nested, hierarchical, and extensible type. These subtle restrictions led the adoption of OOPs concepts in database systems.

Object oriented model of database carries the essence of object oriented concepts like Inheritance, data instantiation and abstraction, and establishment of relationships among types. It ensures persistent data management, effective compatibility and integration with OOPs supported languages like C++, and JAVA, which are the best market flavors of application development.

The article shall be a fair effort to explore the Object model of Oracle. The scope of the study shall include the evolutionary background, comparative drive with RDBMS, commercial study and a lot more details. I shall present a sample case study to highlight the structure building implementation and demonstrate the working of common data tasks.”

I shall post the link once the paper gets published at RMOUG journal.

Bye for now, SBH