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.

Temporary Segments

As its name suggest that this segment is used to hold temporary data. The prime example of this is sorting. So you can say that temporary segments are used when the database does any kind of sorting. When a database does sorting? There are a few events when a database needs to sort values.

1. Order by: Every time you use the command which includes the order by clause the database does sorting and intimately uses temporary segment. The example of an order by command is:

SQL>select * from emp order by ename;

This command will order the results by Employee Name in ascending order. If you want it to order it descending then you will give the command:

SQL>select * from emp order by ename desc;

2. Group by: When you use the clause of group by the database also performs the sorting. The group by clause is used to take aggregate values on a column. The example of group by clause is:

SQL>select dept no, count(*) from emp group by dept no;

3. Distinct: Distinct keyword is used to avoid the multiple show of a single value in a result. If you use command which includes this keyword the database does the sorting. The example of this is:

SQL>select distinct dept no from emp;

4. Creation of an Index: Every time you create an index the database does the sorting. Indexes are created for easy retrieval of data.
5. Union: Union operator is used when you need to merge the outputs of two commands. The use of union operator also involves the sorting process. The example of this is:

SQL>select * from emp union select * from employee;

These are the five situations when the database does the process of sorting. And when it does it stores some values for some time and it stores those values in the temporary segment.

Temporary segments are created inside the temporary tablespace. If you check the V$tablespace data dictionary view you will see the temporary tablespace named as temp. Usually these segments are created one per transaction. And also they are created at run time. Oracle is very clever it creates the temporary segments in advance for the maximum load. In this manner it avoids the creation of these segments at run time and intimately improves performance. The corresponding data file for the temporary tablespace is temp file and it can be seen on the operating system level. If you want more information about the temporary segments on your database then check the data dictionary view named as v$sort_segment.

Oracle Data Block (Continued)

We are down to the most granular unit of storage in an Oracle system i.e Oracle block. If we continue to break up then it is evident that an extent consists of many Oracle blocks. Further more an Oracle block maps to the one or more physical Operating System blocks. And also it is a good practice to have the size of your Oracle block equal to the OS block size.

There are three main parts of an oracle block. These are header, free space and the data part. The header contains the control information. This control information includes the full address of the oracle block itself, information of the addresses of the rows stored in the data block known as row directory, and information of inittrans and maxtrans. The inittrans parameter defines that how many transactions can initially access the data block. And maxtrans defines that how many transactions are allowed to access the data block. Suppose that your inittrans is 3 and your maxtrans is 5. It means that 3 transactions are currently registered in the header and are accessing the data block and total of 5 are allowed to access the Oracle block. To allow two more transactions to be registered and access the data block the header will take the space from free space and allow them to access the data block.

The free space is reserved for the future growth. The data portion grows upwards while the header portion grows downwards. One point worth mentioning here is that free space is used for update command not for the insert command. It means that when an existing row grows in size then the free space is used; otherwise if you want to insert a new row and there is no free space left then a new data block will be used.

Data portion is used for the actual storage of the data. Suppose you have an extent in which data block 1,2,4,7 are free while all other are full. When new data needs to inserted oracle checks for the free data blocks. One way is look up the entire extent and find out which data blocks are used and which are free. But this is a very cumbersome method. What oracle does is it keeps a list of free data blocks in the form of link list. This list is known as free list.

Two thresholds are used to control the data in the data block named as PCT free and PCT used. When the data in the data block reaches the PCT used limit it means that data block has no more space to hold the data. Oracle looks in the free list and picks another data block. Now suppose a user has started deleting data from the data block. In this process if it gets to the PCT free limit then that block is included in the free list. Normally this limit is 40% of the data block. It means that if a data block is full but not up to 40% then it is kept in the free list.

Let us end it with two more very important concepts. Suppose you have a row with a null value for some column. Now you have updated the row with some value; that value is so big that space in the data block is not enough for it. As you are updating the row space will be available for you from free space. But suppose it is not enough also; in this case a new data block will be created for you and the all of row’s data is stored in it. This is known as Row Migration. In other situation suppose a user is inserting data for a particular row and it is so big that space in the data block is not enough for it. In this case you are inserting data not updating therefore no space will be available for you from free space. Instead a new data block is created for you and remaining data is stored in it. It is called Row Chaining. And the individual pieces of data in different blocks for a single row are known as Row Pieces.

If you want some information about the data blocks and free space then you can use the following views: dba_extents, dba_free_space. And also the following command will give you useful information about data blocks. It will tell you the sum of all data blocks in each tablespace of your database.

SQL>select tablespace_name, count (*), max (blocks), sum (blocks)

from dba_free_space group by tablespace_name;