Modes Of Oracle Server

Oracle runs in two modes. One is Dedicated mode and the other is Shared mode. Before differentiating the two let us take a look at what happens when a user or client connects to Oracle Database.

When a user connects to Oracle using a client tool like SQL*PLUS two things happen: first user is connected to listener and second a user process is created for the user. The Listener connects the user to the Instance part of the Oracle Server. And as you know the Instance is mean to access the database. So Instance connects the user to the database. After the connection is established a Server process and Process Global Area (PGA) is created for the user. Now there is no need to be connected to the listener. All the requests of the user will now be carried out through the user process. The user process will put them in the PGA. The Server process will take the requests from PGA and will carry them out for the user. After the server process had got the data from the database which was requested by the user; it will put the data into the PGA. The user process will now take the data from the PGA and will hand it over to the user or client. Again remember that not even the server process is directly connected to the database. It is using the Instance (Background Processes + Shared Global Area) as a mean to access the database.

The default mode of Oracle server- is dedicated mode. In dedicated mode there is one user process, one server process and one PGA for each user. And the working described above takes place for each individual user. But when there are hundreds and even thousands of users connected to the database the dedicated mode is not appropriate. You must turn the server into shared mode. When Oracle is running in shared mode like in parallel server there is only one user process that will take requests from all the users connected to the database. There will be very few server processes (if not one than normally far less than the number of users) communicating with the database on behalf of the users. In shared mode there is only one PGA. The PGA here does not contain any requests or data; it consists of small UGAs (User Global Area) one for each user. The actual data and requests are kept inside individual UGAs.

Cursor Defined

When a SQL statement is issued against Oracle RDBMS then Oracle checks whether it is a valid SQL statement and also whether the user who has issued it has the appropriate permissions. If these two conditions are satisfied then a private SQL area is assigned to the statement. If this is the first time this statement is issued then it is parsed and its parsed copy is saved in the Shared SQL area for further use. Next time when this statement is issued then it is not parsed again but instead already parsed version residing in the Shared SQL area is used.

Both the Private and Shared SQL areas are part of Library Cache which in turn is part of Shared Pool. Private SQL area further consists of two more areas. One is called Persistent area which has static size and stores all the constants values used in the statement. The other is called runtime area and it is used to store all dynamic contents of the statement; for example Binding information. The cursor is nothing but a symbolic name or handle of the Private SQL area of a statement.

Oracle SQL Developer

More then a year ago Sir Fahd gave me an Oracle product as a New Year gift. The product was named as Oracle Raptor. The product was for developing SQL in an easy way then it is possible using SQL*PLUS. I did not use it for two reasons. First it is always hard for me to use something in place of SQL*PLUS and second was the presence of Oracle EM which can aid you to a great deal if you want your development to be fast. One more reason was the time it takes in starting.

The latest version of this product is released in this year’s February. I downloaded it just for fun but for my surprise it takes much less time to s art then its earlier version. Also it has some more and exciting new features. One of them of my great interest is its support for other databases then Oracle. For the time being it supports Microsoft SQL Server, Microsoft Access, MySQL and of course Oracle itself. In my office there is no Oracle type thing so I am trying to connect it to SQL Server. So far I am not been able to do it but still trying. Hoping some favor from it. All in all some appreciation has started to arise in my mind for it. Let us see to how far?

PL/SQL Engine

From its birth SQL lacks the procedural processing like other languages have. And the need was felt that if some way these capabilities can be added to SQL then it will be more useful then it is without them. PL/SQL is nothing but Oracle’s answer for that. If you belong to Oracle world then the learning of this language can benefit you more then you can think. Because it is used in all the development tools of Oracle and also you can use it against the Oracle Server as well. Well benefits aside I am going to talk about the PL/SQL engine and how it works.

PL/SQL is present there where PL/SQL is. Suppose you are working on Forms Developer product of Oracle then let us see how a PL/SQL procedure is compiled. The PL/SQL engine breaks the procedure in three different types of blocks of statements. All the statements that consist of procedural constructs like IF, For Loop etc are processed by Procedural Statement Executor in the Forms Developer itself. All the SQL statements are sent to the Database’s SQL Statement Executor. And all the PL/SQL statements stored in the database are sent to process in the Oracle Server’s Procedural Statement Executor. The net result is combined and is sent back to the user or caller function.

Microsoft’s .Net

Since the beginning of this year I am closely attached to .Net platform. I feel that I will be selfish if I do not talk about this revolutionary application development IDE. Well I am not a very good .Net developer and neither I can be in this short amount of time. I am just going to discuss what I feel about it in my little experience. Well in very simple words I would like to say that I like it. And I really appreciate it and love developing in it. All the promising things that you find in Microsoft documentation like ease of development, CLR features and language independence are there.
I have been mostly tightened to VB.Net. The language is great in itself especially with the introduction of Object Oriented Programming. Whether it is pure object oriented or not is an issue that is very daunting in itself but it really has some very sound OOPs implementations which I think are a lot better then its counter part Java or at least they are really simple to use as compared to Java. Another significant enhancement from previous version of VB is the introduction of proper Exception Handling methods. The old “On error resume next” was simply pathetic but that at least made Windows 98 to run despite everything that happen with it internally.
If you are a .Net developer then I would like you to share your thoughts and correct me.

Free Books

Following is a link to a very useful site of free books on almost all topics relating to computers. It also provides links to other sites of of free books.

Click Here to Follow!

Restore Points

Transaction mechanism is one of the most important aspects of any database and likewise every database implements it. Transaction is an atomic block of SQL statements that can be executed against a database. Committing a transaction means that the changes made by the transaction should be made permanent. And likewise rollback means that the changes made by transaction must not be applied to the database. Transaction mechanism works logically in the memory and not on the physical hard disc. It means that once you have committed a transaction or have rolled back then you can’t do the other. Similar for save points; save point allow you to specify specific points within a transaction so that you can perform specific operations on specific points within a transaction. It all sounds good with the obvious limitation described above. Oracle takes this concept to the next level in 10g.
The very simple but still very powerful feature for the DBA is of restore points. Restore points allow you to bring your database back to a previous state. Consider the following situation. Suppose you are working on your database and made a change in one of your tables and also committed it. You shut downed the database and went back home. There you realized that the change you made was a blunder. You can undo the change with Oracle’s flashback query but what if the changes were in hundreds. You need an entire day. No. you did not if you would have implemented just one thing. If you have created the restore points after critical updates every time doing them then you can roll back your database just with a single command. The creation of restore points is through very familiar “create” statement. You can create restore points with the following command.
create restore point restorepointname; But remember this command only works for Oracle Database 10g Release 2. So create restore points after every critical update.

OOP in PL/SQL

The name PL/SQL implies that it is a procedural language. But from 9i onwards PL/SQL started support for objects. The focus of procedural paradigm is on data structures like procedures, functions. On the other hand Object Oriented Programming (OOP) revolves around modeling the real world entities in the form of objects. Objects consist of attributes that describe the object and methods that describe the behavior of the object. Method is a design level terminology that is implemented through procedures and functions.
The implementation of OOP in PL/SQL is almost similar to other languages; of course syntax and terminology differs a bit. All the tenets of OOP like abstraction, encapsulation, inheritance and polymorphism lie there. In Java, VB.Net, C++ and other OOP languages there is a concept of grouping objects in the form of classes. The similar concept in PL/SQL is termed as Object Type. There are two types of objects in PL/SQL. Transient objects are those who get initiated with the start of the program and abolished with the termination of the program. Permanent objects are those that are stored in the database. As you know Oracle database supports objects from its version 8. In database object types are treated like any other schema objects and can be accessed and manipulated like the others.

Extproc-Multithreaded Agent

More for less is the inherent attribute of Oracle technology. Although Oracle is resource hungry software but once it is installed it can be made to use its resources optimally. One of the prime examples of this is shared server architecture. I am not going to discuss the shared server architecture anymore or how it works here. But rather I am going to point out how Oracle optimizes its resources when it sees too many external routines users are connected to the database. Well Oracle does not do this automatically but it can be made through multithreaded agent. The configuration of multithreaded agent is complex but once done then it can be shared between multiple user sessions.
The default behavior of Oracle is to fork a new Extproc agent for every new user session. The default works but consumes too many resources very quickly. Therefore it is recommended that you use multithreaded agent when too many user sessions are expected to be connected to your Oracle database. Regardless of how it is configured here it is how it works. When the multithreaded agent is configured the incoming requests first come to a monitor thread. The monitor thread puts them in a FIFO queue. Monitor thread actually maintains the load balancing information about the dispatcher threads and puts the request in appropriate queue. Here dispatcher thread picks the request and puts it in another queue. Task threads get the request from this queue and accomplish the task and return the result of the request to the appropriate user session.

Native Dynamic SQL

Native Dynamic SQL! When I first heard the term the very first confusion raised in my mind was what Dynamic SQL is. Or at least what is static SQL so that I can differentiate between the two. Here is what I understood. When you know the tables and columns that you are referencing in your SQL then the SQL you are using is static. There are many advantages of using static SQL. First all the errors and exceptions that can occur at runtime are known or can be found. And more importantly all the techniques of SQL tuning are applicable to static SQL only. So why dynamic SQL?
There is another school of thought in application development that says; “you should write robust, reusable and dynamic code modules”. This is object oriented programming. Many times in application development it happens that you don’t know the columns and tables that you will be interacting with. For that purpose and many others you must rely on dynamic SQL. Thus dynamic SQL is SQL that decides at runtime what tables to retrieve data from and what columns to retrieve.
Having said what is dynamic SQL is not enough? You must know how to generate it to be beneficial. Oracle provides two ways for the generation of dynamic SQL. One is DBSM_SQL built in package and the other is Native Dynamic SQL. Which one is better of the two is something that is dependent on your personal judgment. However NDS outperforms DBMS_SQL in performance. However you can’t ignore the DBMS_SQL because it is the legacy way and billions of lines of code have been written using this package; so in order to understand that code you must understand this package. Also NDS does not support the generation of dynamic SQL where name, number or types of arguments is not known. So you must use DBSM_SQL package for those situations.
On the other hand NDS is better then DBMS_SQL in many ways. First and I think the most prominent advantage is that its syntax is pretty similar to normal SQL syntax. Performance is also a lot better then the DBSM_SQL package. And also the NDS is the future that we will be using.