Oracle 11g

Oracle database 11g! For me it is still hype. Not because I have not liked it but because I does not know what is so special about it. I heard about its numerous features but do not know how they can be useful. Oh, then I am talking about Oracle database who already had many, many salient features even before 11g. And I don’t know them all. It has been quite some time that Oracle database 11g has been released and I feel myself somewhat incompetent to know that I have not gone through it yet. But upon further stress I can figure out two problems relating that. First is because of less time I have due to my job and the second is I can’t find a book about it here in Pakistan. It’s hard to find any good book here and especially oracle related.

Well I have installed oracle many and I mean it many times. So as for as installation goes I don’t need anything no matter what version it is. And with the introduction of Basic Installation method since 10g life is even easier. But like most installations the first attempt was unsuccessful. Every thing went well except EM which refused to configure; pointing out some listener specific error. Well quite frankly I was unable to figure out what the problem was because I tried every possible thing that I know to configure my database with EM but to no avail. Lastly I thought that as 10g was not uninstalled properly it may be because of that. So I cleaned my Computer from everything containing the word Oracle; even the poor JIniator got hammered out. I made a fresh install and everything went good enough and installation was successful. I took a deep breathe and cajoled myself that now I will start exploring Oracle 11g because I got it installed and running.

Bind Variables

When a SQL statement is executed, it passes through three phases. Those three phases are:

1. Parse
2. Execution Plan
3. Fetching

By parsing means to check whether the syntax is correct. If that is done then Oracle generates execution plans, determines the cost of each plan and finally decides which plan the query will follow. Remember here that the actual fetching is not done here. The only decision made here is the semantics of the query. The exact logic is determined and plan is generated. Before this Oracle computes the hash value of the query based on some predefined formula and checks whether the query with the same hash values is present or not. If it is present then Oracle skips the second phase and goes to the third phase.

Bind variables can play a vital role in this scenario. Let’s clarify it how? Suppose you run a query:

SQL>select * from emp where empno=7349;

When you run this query for the first time Oracle performs all the three phases and returns the result back. Now immediately after that you gave the following command:

SQL> select * from emp where empno=7548;

This query is very similar to the first one, but only for us. For Oracle it is a different query for which all the three phases are performed. By using bind variables instead of hard coded values you can make Oracle treat both queries similar and thus skip the second phase. With bind variable the above queries will look like:

SQL> select * from emp where empno=:v_emp;

The fact that the values of the bind variables are determined at run time rather then at the time of generating execution plan helps us here. And Oracle effectively treats both the queries similar and skips the second phase and hence improves response time.

Variable Scope and Visibility

Among the most difficult things that I perceived in the very beginning of my career was the variable scope and visibility. So I decide to write about this topic last night. As I previously mentioned I am reading PL/SQL in my free time now a days. Talking about free time, by the way it’s really hard for me now days. Back to the topic, PL/SQL has a very compact and tight syntax. I mean for every Begin there must be an End. That is really impressive. And the optional parts of variable declaration and exception handling make your code very handsome looking. And if the code is written in Caps then it just kills. Try it out.

Variable scope means the boundaries in which a specific variable can be used. For example if you declare a variable then the variable will only be visible in the specific block you declared the variable in. This means is that the variable’s scope is limited to that block only. You cannot use the variable outside that block because when the block will end that variable will also end and the memory and other resources are returned back to the OS. So referring that variable outside the block will result in an error. If you have nested blocks (blocks inside other blocks) and you have declared a variable in the outer block then that variable can also be used inside the inner block, it is possible because inner block lies within the outer block and is a part of it.

Sometime it happens that a variable is in scope but is not visible. This can happen if you have declared a variable with the same name in both the outer and inner blocks of a nested blocks structure. Now if you try to use that variable in the inner block then the variable declared in the inner block will be used. The variable in the outer block will be hidden. In this type of situation we can say that the variable declared in the outer block is in scope but is not visible. Now if you want to use the variable of the outer block then you can use Labels. Labels are identifiers that identify the variables of specific blocks. They are written as:

<>,

Include this line in the declaration section of the outer block and you will be able to access the variables of outer block using this label anywhere no matter if the names are same in the inner blocks. To use this you will write:

Outer block.variable_name,

This will give the value of variable declared in the outer block.

PL/SQL v/s JAVA

The more I love Oracle the more I hate Java despite the fact that Oracle all the time supports Java. Usually I used to show respect everything related to Computer and especially Oracle, but I am not been able to control my behavior in this matter. By the way one more thing I dislike about Oracle is their insincerity towards their products especially Application Server. Well talking about java I only used it during my courses in BS-IT. I started it as enthusiastically as I can be. But soon I started to get annoying with its complex syntax and lengthy coding. The thing you can do in one line in C, you had to write 6 to 7 lines of java to do that same thing.

One thing I would like to say to all those who may be reading this and are java developers is that my purpose is not dishearten anybody but rather I am just describing my own feelings which can be perfectly wrong. While there is another name in the topic heading i.e. PL/SQL. I like PL/SQL not because it has more features then any damn language in the world but because it is more then what it seems to be. It seems to be an extension of Oracle SQL but it is a lot more then that. Just go there and read and practice. You will be amazed of what can be done with it. Starting from Oracle 9i release of database PL/SQL and SQL share the same parser. That adds something really extra in terms of performance to an already highly Oracle Database integrated language.

When Oracle Database started with built in support for java rumors were there that PL/SQL is leaving. In reality this is not the case. Oracle is certainly not doing anything in terms of enhancing Oracle Forms and Reports Developer but still one thing is for sure that PL/SQL will be there. It seems to be very much true especially if you look at Oracle’s dedication to the language. Because they are adding new and new features to the language for every now and then. And yes we already know that it has support for Objects and object related technologies. As for as java is concerned I have no plans of using it. I never wanted to be an application developer all the time and even if I had to I will choose C# .Net instead of Java.

Starting With PL/SQL

The more I hesitate about a thing the more it proves to be useful for me. This is the philosophy that I have developed about myself over the years. Although in professional life you should not hesitate in doing something and I try my best to follow this approach. But there is a thing about how you conceive a thing. From the start I always hesitated to work with Oracle. But when I did it, then it was more then just a work. It was an experience of lifetime. Going through Oracle I always found it difficult to conceive PL/SQL. I must say that I under estimated it until and unless I started some real series application development. Well I must point out here that I am not good in development. I am just starting with it. I always did DBA tasks more then development tasks and feel more confident in doing them. But I have started thinking about it seriously.

One of the reasons is PL/SQL. I started liking PL/SQL when I heard and read about its features in different articles and blogs. The other motivation was that Oracle continues to empower it as a full fledge programming language rather then just an extension to SQL. Now one may wonder that what is the origin of PL/SQL and why the word SQL is included in naming it. While I will talk about the history of PL/SQL in a separate post, here is why this language was introduced at all.

Oracle is an object relational database management system, the first ever RDBMS in history. Like any other RDBMS the primary interface to the Oracle database is through SQL. And believe me you can retrieve almost all kind of data stored in a database through SQL. But despite all the enhancements of Oracle in ANSI SQL, the fact remains is that SQL is still a data retrieval mechanism rather then a programming language. It still lacks the features of a programming language like looping through records processing one at a time, encrypting data for security, storing code on server rather then on client side and unable to handle arrays, collections and other similar data types. So this was the primary motive behind PL/SQL. To enable the developer to use programming language features with tight integration of database. And believe me; I am saying it after thinking for a moment that PL/SQL has become a very powerful programming language for applications that use Oracle as back end database.

High Water Mark

It is a marker that shows you the point when you last inserted the data in the table. Let us simplify it with an example. Suppose you created a new table initially with no value. The high water mark will be at initial position say at 0. You made an insertion. The HWM will rise by one and will reach at position 1. Similarly as you will make more and more insertions the HWM will also rise; suppose you reached at a position where the value of HWM was 100. Now you started deleting values from the table. The value of HWM will not fall with the deletion of values from the table. So you can easily say that HWM shows the highest level of insertion you have ever done in that particular table.

Every time you give the command of select to fetch values from a table the oracle searches the table to HWM. It does not matter whether your last value is stored on position 10 or 90; if your HWM is 100 then oracle will search up to 100 positions. Normally for deleting values from the table you use the command of delete. This does not affect your HWM even if you have deleted all the values from the table. To bring the HWM to the initial position you can use the command:

SQL>truncate table table_name;

The high water mark is only meaningful in the table segment.

Row Structure

The way Oracle stores a row of a table is known as row structure. A row consists of two parts; one is header and the second is data. In the header part all the control information of that particular row is stored. Control information of a row consists of three parts. First is the links of row migration and row chaining. Secondly is the lock information. Locks are used so that no two transactions should update the row simultaneously. And the last thing is how many columns are there in that row. Every row in a table has same number of columns; so why there is a need for storing this information separately in each row. This is one of the features that only oracle provides. We will see the advantage of this in just a moment.

Next is the data portion. Data portion contains the actual data stored. Each entry of data has two parts. One is known as column length (CL) and the other is column value (CV). Oracle does not store the null values. This is the feature I talked about earlier. Suppose for a particular row there are five columns in which the last three has null values. Oracle does not store the null values altogether. Then how it knows that there is a null value? As I mentioned that oracle stores the number of columns for each row. So the value stored in the header will be 5 for that row but the actual values that are present are two. Oracle will realize here that the last three values are null. Real world databases do have a lot of null values. So oracle wastes a little space for storing the number of columns information in the header of every row but on the other hand it saves a lot of space by not storing the null values.

LOB Introduction

Char and Varchar2 data types are used to store text columns in an Oracle database. While for the normal requirements they are OK, but they are not as suitable when it comes to store large objects. By large objects I mean storing of entire PDF documents for example or storing of an image. Traditionally Oracle provided two data types for these situations namely as RAW and Long RAW. But the use of these data types was inherent to some limitations. The first one was that you cannot have more one column of RAW or Long RAW data types in one table. The storage capacity was limited to 2GB. And last you cannot perform SQL operations on the subset of data stored as RAW or Long RAW.

In Oracle 10g Release 1 and above (not sure of 9i) Oracle introduced Large Objects or LOB for short. The introduction of LOB data types culminates all the restrictions that were present in RAW and Long RAW data types. LOB itself is not a data type. Rather it is a category of four data types which are divided into two distinct categories. The categorization of LOB data types is based on their storage. The two categories are:

1. Internal

2. External

Internally stored LOB objects are those which are stored inside database. They are treated just like any other object inside database. And all SQL operations are valid for them that are valid for other data types. There are three data types in this category:

1. CLOB: It is used to store large documents and stands for Character Large Objects.
2. NCLOB: It is also used to store large documents which are in character format. But it stores these documents using a different format namely as National Character Set.
3. BLOB: This data type is used to store large objects which are in binary format. For example an image.

Externally stored large objects are those objects which are stored outside database but whose reference is maintained inside database. The storage of these objects is inherent to the limitations of Operating System files. Normal SQL operations performed on other internally stored objects will not be applicable to these externally stored objects. The only data type in this category is BFILE. The column of BFILE data type will be used only to store the location of large object file.

Oracle Data Types

Data types in oracle can be divided in two general groups. First are the user defined data types. Oracle database allows you to create your own data types and use them if you find it necessary. Furthermore Oracle is an object relational database management system; that allows you to create objects and store them in the database. Any thing that has data and some functions in it is known as object.

The second group of data types is known as built in data types. These are also known as Oracle data types. These data types can be divided into three more groups. First is scalar data type. Scalar data types are those types that can store only one value in their variables. This group includes many data types that are used to store different types of data. For example char and varchar2 are used to store the characters or string values. However there is a difference and that is: suppose you have declared a variable as char (10) and have stored only one character in it. The char data type will reserve space for 10 characters despite the fact that you have stored only one. Other 9 characters space will be wasted. However if you have declared the same variable with varchar2 (10), the space will not be reserved for the number of characters that you have specified (10) rather only for the characters that you actually stored i.e. 1. The number data type is used to store numbers. It stores whole numbers as well as numbers with decimal points. Date is used for storing dates. Well you can also store dates in varchar2 but that will not allow you to use Oracle’s built in functions for dates. Timestamp is used to store date with time. BLOB stands for Binary Large Objects and it is used to store large objects e.g. images, audio data, video data etc. CLOB stands for Character Large Objects and it is used for storing large objects which are in the form of text e.g. a huge document.

The second group in the built in data types is collection. Collection data types are those data types that can store more then one values in their variables. Suppose if you want to store an array in any field of a table you can use the varray data type. And if you want to store a two dimensional array or table in any field of the table then you can use the table data type. This is used when you want to store a table inside another table.

The third and last group in the built in data types of oracle is relationship. There is only one data type in this group i.e. ref. If you want to store a value inside a field which is not in the oracle database; and resides in some other form of application then you can use the ref data type to give the address of that value. In very simple words this data type works solely as a pointer.

Rollback Segments

The primary purpose of rollback segments is to hold old values. Means that when you update a value the updated value is stored in the table and the old value is stored in the rollback segment. So whenever that old value is needed again it is restored from rollback segment. There are three main purposes of storing old values and thus of rollback segments:

1. Transaction Rollback: Suppose you are working in a table. And you updated the name column value of a particular row from Scott to James. And then suddenly you realized that change you made was not right so you give the command of rollback. The original value i.e. Scott will be restored again. How did database come to know that the old value was Scott? The answer is through rollback segment because it’s the rollback segment that stores the old values.
2. Transaction Recovery: The second purpose is transaction recovery. Suppose you have made a change and have not committed it. But another user working on the same database gave the command of commit. LGWR will write because the commit command is given. And when LGWR writes it writes all the changes from redo buffer cache to on-line redo logs. But remember along other things redo logs also store the SCN of transaction and also whether it is committed by its user or not. Some how your DBWR also fires and writes the uncommitted change to data files. At this point in time your power supply goes off. Next time when instance will startup the SMON process will see that which transactions are stored correctly and also which are committed by their own users and which are not. If any transaction is not committed by its own user just like one above it will restore its old value; and it will take its old value from rollback segment.
3. Read Consistency: Read consistency is that something that every good database must posses. It is a sort of yard stick through which the maturity of a RDBMS is measured. In oracle this feature is implemented by making use of rollback segments. Suppose a user has given the query to fetch the first five rows from a table. The query has only fetched first three rows that another query came and updated the last two rows. Now the first query should get the old values because it was given first then the query who updated the rows. Surely it will get the old values but not from the table but from rollback segment. However any other query given after the update command will get the new updated values. In this example two features of oracle came in front of us. They are consistency and concurrency. By the way consistency must always be given the higher priority.

Rollback segments are maintained in a circular fashion. Usually more then one transaction can write in one rollback segment. Up till 8i it was not possible for multiple transactions to write in one extent. But in later versions of 8i and in above all versions it is possible. But still it is not possible for multiple transactions to write in one data block. Once one extent is full it is switched to the other. This is known as Wrap. When all the extents are full then instead of over writing the first one a new extent is created at run time. It is called Extend.

Rollback segments are automatically freed up when the data in them is no longer required. Suppose your rollback segment was full up to 100M and then automatically freed up to 4M. In this case what will happen of extra space? Is it wasted? No it is not wasted instead oracle shrinks the rollback segment up to a value stored in the optimal parameter. Normally the value of optimal parameter is 4M.

With these considerations we have a legacy problem. Consider again the example given in the Read Consistency. First command wants to fetch the first five rows of a table; it has only fetched the first three that the second command updated the last two rows. At this point in time suppose oracle has shrunk the rollback segment; where the first command will get the old values? The answer is from no where. Till 8i it was a huge problem and it is famous too. It is known as “snapshot too old” and the oracle message number for this error is ora-15500. In 9i and above versions it is automatically controlled by oracle itself.

To get information about the rollback segments you can use v$rollname, v$rollstat data dictionary views. To get information about your current active transactions you can use the view named as v$transaction.