Neeraj Bhatia's Blog

December 20, 2011

Do we need Smarter Developers or Smarter DBAs?

Filed under: Oracle - General — neerajbhatia @ 19:30
Tags: , , ,

There is an ongoing discussion about who is responsible for application performance tuning: Oracle developers or DBAs. While all Oracle database users and developers argue that it’s a DBA who is responsible for application performance tuning, DBA cries it’s a developer who write the code and so he should write it in an efficient way and later optimize it.

When it comes to application performance tuning most of the times DBA remains in a tight corner, new functionalities being added to the database are just like giving more weapons to performance firefighters when it should also empower the developers community to write and test the efficient code. Whenever I listen to any Oracle community member – the way they speak is like application performance tuning is a battle to be fought only by Oracle DBAs. Look at the Oracle database’s new features in recent versions like 3-click performance tuning in Enterprise Manager Cloud Control 12c, claim that ADDM solve all the performance issues, automatic SQL tuning advisor and Real application testing. While some of these are just marketing gimmick others are really good. No doubt Oracle has put it’s heart and soul into making the product mature and easy to use for everyone, but even after so much efforts everyone who deals with Oracle databases believe that performance tuning is a sole responsibility of an Oracle DBA and all these new features are meant solely for Oracle DBAs.

I don’t think Oracle as a company is doing anything wrong it’s just a perception which has been made since long and become so strong in last many years that performance tuning is sole responsibility of an Oracle DBA. I must say when it comes to application performance tuning, it’s a developer who can contribute more than a DBA and its a developer who should design, write and test his SQLs and PL/SQL.

Almost 7 years back I started my career as a performance tuning DBA, I struggled a lot dealing with application performance tuning issues most of which were out of my control. Think about it. How many times you have left the office on Friday evening with everything well in place and all of sudden on Monday morning everyone is complaining about database performance. You as a DBA becomes from hero to villain just over the weekend. Everyone looks at you suspiciously. You look at every corner of the database for a clue but unfortunately you don’t find any. You get back to the basics and start doing all the things you have learnt over the years – take an AWR/Statspack snapshot and compare it with the known-good time and look for the clues what has changed? Now you are surprised to see many new SQLs are coming in the reports. Later discussions and meetings reveal that actually developers changed the code during the weekend. These changes were not documented anywhere, because developers are unaware that little changes to the SQLs can change the way how CBO looks at them. Forget the ITIL processes, change approval process etc, as such a thick layer of IT processes are rarely there in small Oracle shops. Generally there is a chinese wall between large developers team and small DBAs team in any company.

Now think about some scenarios:

  • MultiColumn CBO Statistics is helpful in improving the selectivity estimates in situations where there is a relationship between the columns. But who is best placed to conclude such a relationship, developers or DBAs?
  • Partitioning Criteria: DBA can tell which tables should be partitioned but what should be the partitioning criteria, which partitioned indexes. Can he take such decisions without considering the table’s access criteria?
  • Index Design: Which columns should be indexes and what type of index? Without functional knowledge can we expect DBA prepare optimal index designs?

How many times we have find Oracle developers who do 10053 trace to investigate why CBO is behaving in a particular way, profiling their PL/SQL codes for clues of performance improvements. Rarely! All assumes that all these tools are meant for DBAs who works as performance firefighters. Yes, this is true that Oracle DBAs work as firefighters whenever there is a performance issue, but if developers leverage this rich functionality of best database technology during development and testing phase, many of the issue could be solved before deployment.

Most of the times developer is a person who just passed out from a college and got a job as an Oracle developer. Sometimes Oracle developer is a person who has development experience but in other programming language like C, Java etc. So in a normal scenario developer is unaware of Oracle architecture, different ways to write optimized SQLs. So how can we expect optimized code hitting an Oracle database?

Now think from an Oracle DBA perspective for a second, how many times during high workload or high utilization time Oracle DBA finds top sessions are coming from TOAD and Toad-like frond-end tools which are pertaining to Oracle developers who with the help of rich functionality provided by tools are executing dumb, poorly written SQLs. Best practice says – no testing should be done in production environments, developers should not have access to dynamic public views blah blah, but that is not the case in many Oracle shops.

The conclusion is – we do need smarter DBAs but more importantly we really need smarter developers. Developers who look at the performance aspects of the code during early at the software development cycle, test them properly using Oracle rich set of tools like SQL trace, 10053 trace, explain plan, tkprof etc and then ultimately release them to the production database. To start with you can have a formal process in place where before any changes developers discuss them with DBA. Some examples are – before any table creation they consider how data will be populated to the table, what will be the access pattern, index design based on where clause etc. Later as the process and people get mature, you will find fine-tuned code executing on the database.



January 17, 2011

Understanding Oracle Database Licensing Policies

Filed under: Oracle - General — neerajbhatia @ 17:03

Today I’ve chosen to blog about Oracle database licensing policy for two reasons. First, My experience with Oracle databases always revolved around the performance tuning work, so I never had to bother about licensing bits. In last few months quite number of times some friends in my network approached me about how Oracle license its products. In latest situation, at my workplace I’ve been asked to review the Oracle licenses for a large data center with virtualized environment. Secondly technology has evolved very fast in recent years. Virtualization and Cloud computing has really made the licensing policies more complex. This triggered me to dig into the details and write about it.

My intent is to discuss about Oracle policies for educational purpose and if you are planning to buy/renew Oracle license for your environment, I strongly recommend to refer official Oracle policies. Some of the links, I have mentioned at the end for your reference.

With the intention to make this post self-contained, I have discussed some additional things which are related to licensing policies like different Oracle database editions, hardware terminology, Cloud computing etc.

Without further ado, let’s start our discussion with licensing for an Oracle database running on physical servers (non-virtualized environments). Licensing requirements in this case depends on database edition, software environment and whether you want to license based on named users or number of processors. Before delving into actual details, let me quickly touch base on these criteria.

Database environment: Typically we can differentiate database environments into three categories:

  • Development Environment
  • Test Environment
  • Production Environment

You can use full database licenses for development OR optionally you can download absolutely free database software from Oracle Technology Network (OTN). In order to download an Oracle product from OTN, you have to accept the OTN Development License. According to this agreement, user can use the licensed product for development purpose and has not the right to deploy applications.  Please refer references section for further details on OTN licensing. Test and Production environments must be fully licensed and OTN option is only available for development environments.

Now a brief discussion about various Oracle database editions. Oracle database comes in five flavors or edition, each suitable for different scenario.

  • Express Edition: This is an entry-level, small-footprint database that’s free. Of course it comes with certain restrictions, It can be installed on any size host machine with any number of CPUs (one database per machine), but can store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.
  • Personal Edition: This edition of an Oracle Database is designed to provide software developers a cost effective, yet full featured Oracle Database environment without technical limits.
  • Standard Edition One:  It is an affordable, full-featured database for servers with up to two sockets.
  • Standard Edition: Standard Edition is an affordable, full-featured database for servers with up to four sockets.
  • Enterprise Edition: The Oracle Database Enterprise Edition (EE) offers industry-leading database solution without any restriction.

For further differences between various database editions, refer MOS Doc ID 465455.1
Now let’s take a step further, and discuss hardware terminologies:

  • Socket: Physical receptacles on server main boards that accommodate CPU packages. Our laptop has 1 socket.
  • CPU package: A silicon die which fit into socket.
  • CPU core: The core is a self contained execution unit on the silicon die and capable of doing processing. Multi-core CPUs have multiple execution cores on the single piece silicon die.

These days most of the processors are of multicore capability (dual and quad core, also called 2-way and 4-way respectively). This means that a single physical package has more than one execution core. Thus a server can have multiple physical sockets and a single physical socket in turn can accommodate a package with more than one execution core. This means a machine with 2 physical sockets, can accommodate 4 CPUs; and if each package is dual core, we have 8 cores of execution.

License Metrics: Oracle database can be licensed using two metrics,  Named User Plus or Processor with the exception of the Personal Edition which can only be licensed using the Named User Plus metric.

The Named User Plus metric is used in environments where users (or non-human devices accessing the database, for eg temperature monitoring device) can be easily identified and counted.  The Processor metric is used where users cannot be easily identified and counted, such as internet-based applications.  The Processor metric is also used when it is more cost effective than Named User Plus licenses.

Let’s start with Named User Plus licensing for different database editions:

  • Standard Edition One can only be licensed on servers that have a maximum capacity of 2 sockets. It requires a minimum of 5 Named User Plus licenses or the total number of actual users, whichever is greater.
  • Standard Edition can only be licensed on servers that have a maximum capacity of 4 sockets. It requires a minimum of 5 Named User Plus licenses or the total number of actual users, whichever is greater.
  • The Enterprise Edition requires a minimum of 25 Named User Plus per Processor licenses or the total number of actual users, whichever is greater.

Example:  A customer who wants to license the Database Enterprise Edition on a 4-way box will be required to license a minimum of 4 processors * 25 Named User Plus, which is equal to 100 Named User Plus.

Oracle’s processor based licensing are based on number of CPU cores but number of total CPU cores should be converted to number of processor. Oracle has a predefined core processor licensing factor which you can use for your platform.  So all cores on all multi-core chips are to be aggregated before multiplying by the appropriate core processor licensing factor and all fractions of a number are to be rounded up to the next whole number.
As explained Oracle Standard One and Standard editions are licensed based on the sockets with restriction of 2 and 4 sockets respectively, for such editions a processor is counted equivalent to a socket; however, in the case of multicore-chip modules, each core in the multi-chip module is counted as one occupied socket. It means 2-socket server each having one dual-core CPU, will be considered as 4 sockets server and is not eligible for Standard One edition installation.

Let’s take another example of a multi-core chip based server with total 6 cores and an Oracle Processor Core Factor of 0.25, which would require 2 processor licenses (6 multiplied by 0.25 equals 1.50, which is then rounded up  to the next whole number, which is 2).
Also a multicore hardware platform not specified in the Oracle Processor Core Factor Table, will have a core processor licensing factor of 1.0, means all the cores will be equivalent to a processor.

Now, let’s discuss licensing for virtualized environments. There can be two types of virtualized environments: soft-partitioning and hard-partitioning. In soft-partitioning, CPU capacity can be changed on runtime as additional resource is needed. This is a flexible way of managing resources. In hard-partitioning, a physical server is separated into distinct smaller systems, each with its own CPUs. Oracle licensing is straightforward. One has to license for all the CPUs configured on the system in case of soft partitioning and only for number of CPUs configured to that partition in case of hard partitioning. Here are few illustrations:

  • A server has 32 CPUs installed, but it is hard partitioned and only 16 CPUs are made available to run Oracle.  The customer is required to license Oracle for only 16 CPUs.
  • A server comes with 30 CPUs and Oracle is installed on this server.  If this server cannot be hard partitioned, the customer must obtain licenses for all 30 CPUs.

On cloud computing environment, Amazon Web Services (AWS) is the only cloud service vendor licensed to run Oracle database. AWS offering is termed as Amazon Elastic Compute Cloud (EC2). In the cloud environment, licensing is based on the number of virtual cores and each virtual core should be considered equivalent to a physical core.
It means for Oracle Standard Edition One or Standard Edition, EC2 instances with 4 or less virtual cores are counted as 1 socket, which is considered equivalent to a processor license. For EC2 instances with more than 4 virtual cores, every 4 virtual cores used (rounded up to the closest multiple of 4) equate to a licensing requirement of 1 socket. It means, under cloud computing, Standard Edition can only be licensed on EC2 instances up to 16 virtual cores and Standard Edition One can only be licensed on EC2 instances up to 8 virtual cores.

For Enterprise editions, virtual cores should be converted to number of processors using standard core processor licensing factor. For example, licensing Enterprise Edition on a single EC2 instance of 8 virtual cores (platform with core processor licensing factor of 0.5) would require 8 * 0.5 = 4 processor licenses.
Standard named user plus licensing policies are also applicable in cloud environment.

That’s the end of the story. Please feel free to post comments if you have questions or feedback to share.

References (Further Reading) …
1) Oracle Technology Network Developer License Terms

2) Different Oracle Database Licensing Policies

3) Oracle’s Software Investment Guide

4) Oracle Price List

5) Oracle Processor Core Factor Table

6) Online Purchase of Oracle Products

January 14, 2011

Edition-Based Redefinition: My wish once again comes true

Filed under: Oracle - General — neerajbhatia @ 16:19

Today I did random browsing of Oracle blogs and I found an interesting post about database 11gR2 new feature called Edition-Based Redefinition by Christian Antognini. Here is a link to the blog post. I recommend you to read the paper and browse documentation library for the further details.

Edition-based Redefinition

Just after I started reading the post, I realized that my one more wish came true. With this new functionality, you can have various versions of a database object in the same database and Oracle will manage the versions based on its edition.

Now If I go back into the past and recall my experience with the version 9i, developers used to create different versions of their piece of work (PL/SQL code) and save in the same production database. As you can expect, It resulted in so many packages and procedures with dates (or change request number) appended with the names in the production schema. This sometimes resulted in delay in packages compilation times and wrong packages become live etc.
Previously we could keep different versions of a same object by saving them in different schemas, but with this new functionality you can have a hierarchy of versions V1, V2 etc.

Once again, Oracle has surprised me with their development. It seems someone in the Oracle development team is hearing my wishes. This is the second instance, when one of my desired feature had lately added to the kernel. First I wished to have a OS-like recycle bin in the database itself and Oracle had introduced in the version 10g onwards.
Now I think I should better document my desired features and wait for them to become true  🙂


November 20, 2010

How to Generate Random Data for Test Cases using DBMS_RANDOM in Oracle

Filed under: Oracle - General — neerajbhatia @ 08:42
Tags: , ,

Generating random numbers is a capability of any application software system, and so of Oracle database. And that’s my today’s point of discussion. I am sure most of the readers must be already aware about the Oracle-supplied DBMS_RANDOM package and its capabilities. So this post is more for me to document my learning for future references and for the novice members of our Oracle community.

There are various applications of random numbers, one of them is to prepare data set for testing purposes. As Jonathan Lewis discussed in philosophy post, one doesn’t has to wait for the various scenarios to come, he can always construct relevant test cases and develop his knowledge. Obviously first step is to build relevant and appropriate testcases where underlying data is simulated to the actual scenario. This is where random number generation comes into the picture. In another example web-based application developers may want to generate random password strings for the new registered users and later, on first login ask for password reset.

Regular blog readers must have noticed several “innovative” ways of generating random numbers through dbms_random. dbms_random package is one of the package available in Oracle to generate random numbers, I said “one of the package” as in release 10g, much-better dbms_crypto package was introduced which can also generate random numbers. Although you can write PL/SQL code to generate random numbers but Oracle suggests that because DBMS_RANDOM calls Oracle’s internal random number generator so it’s always faster and efficient.

The dbms_random package is created as part of Oracle database installation. The script dbmsrand.sql which can be found in the built-in packages source code directory ($Oracle_Home/rdbms/admin) contains the source code for this package’s specification. This script creates sys.dbms_random and its public synonym with the same name and grant execution privilege to public.

There can be two areas of discussion w.r.t. dbms_random: Oracle’s logic of random number generation and second how to use procedures to generate random numbers in various practical scenarios. The second area recently come to my attention and I think it’s worth to discuss it. Readers are welcome to read dbmsrand.sql script for understanding of Oracle’s algorithm of random number generation; I may discuss it later.


In software application terms random number generator is a computer program designed to generate a sequence of numbers or character strings that lack any pattern, i.e. appear random. There are two principal methods used to generate random numbers. One measures some physical phenomenon that is expected to be random and then compensates for possible biases in the measurement process. The other uses computational algorithms that produce long sequences of apparently random results, which are in fact completely determined by a shorter initial value, known as a seed. The latter type is often called pseudorandom number generators. A “random number generator” based solely on this way cannot be regarded as a “true” random number generator, since its output is inherently predictable.

So any pseudo-random number formula depends on the seed value to start the sequence. If you start with the same seed, you will get the same sequence of values from the formula. To create a random and unpredictable sequence, the seed must be a truly random number. To get this truly random number for the seed, most programs use the current date and time, converted to an integer value (for example, converted to the number of seconds that have elapsed since January 1, 1970). Since this is a different number every time you start the program, it makes a good seed. Some programs generate multiple seed values through some logic and then take the average of these to generate sequence of random numbers.

Getting started with dbms_random

As discussed, there are two steps to generate the random numbers: first initialize the package and second generate the actual data. By initializing we mean providing a seed value. By default, the package is initialized with the current user name, current time down to the second, and the current session. Following is an excerpt from the “dbmsrand.sql” script, which do implicit initialization.

-- initialize if needed

IF (need_init = TRUE) THEN


If the package is seeded with the same seed and then accessed in the same way, it will produce the same results in both cases. This is important in cases when one wants to generate re-producible test case. In such a case, same seed is recommended.

Summary of dbms_random Subprograms

Following chart displaying the subprograms of DBMS_RANDOM package. The names in the ovals are procedure, while names in the rectangles are functions. Also, subprograms with shaded-boxes (i.e. Initialize, Random and Terminate) are obsolete now.



1)   Initialize Procedure: The syntax of Initialize procedure is dbms_random.initialize(val in binary_integer). It initializes the package with a specified seed value. In 10g this procedure is obsolete as it simply calls the SEED Procedure.

2)   Seed Procedure: This procedure resets the seed that is automatically set by Oracle. This is an overloaded procedure and accepts number or character string as an input.

3)   Normal Function: Returns random numbers in a normal distribution. This function returns random numbers in a standard normal distribution.

4)   Random Function: Generates a random number greater or equal to -power(2,31) and less than power(2,31). This procedure is obsolete in 10g and replaced by Value function.

5)   Value Function: Again this function is overloaded and without any arguments it returns a random number, greater than or equal to 0 and less than 1 with 38-digit precision means 38 digit to the right of the decimal . Alternatively, this function returns a random number which is greater than or equal to low and less than high values passed as arguments.

6)   String Function: This function generates a random string. It takes two parameter opt and len which are for string type to be generated and the length of the string. Following are some pre-assigned values to the opt parameter, providing any other character as an argument will result in uppercase alpha characters.

  • ‘u’ OR ‘U’ – Returns string in uppercase alphabetic characters
  • ‘l’ OR ‘L’ – Returns string in lowercase alphabetic characters
  • ‘a’ OR ‘A’ – Returns string in mixed case alphabetic characters
  • ‘x’ OR ‘X’ – Returns string in uppercase alpha-numeric characters
  • ‘p’ OR ‘P’ – Returns string in any printable characters.

7)   Terminate Procedure: When we are finished with the package we can call Terminate procedure to close the package. However, this procedure is obsolete now so it does nothing.


So far we have covered the theoritical aspects of dbms_random package. It’s time to see some examples. Let’s start our discussion with normal() function to generate random numbers which are in normal distribution. The following code will generate 100,000 numbers which are in the normal distribution.

test@ORADB10G> exec dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select trunc(dbms_random.normal * 1000) from dual connect by level <=1e5;

It’s worth to note that I’ve called seed() procedure, thus making this code reproducible. Following graph is generated after applying the truncate function on the data. This is a famous bell-curve showing that data is following normal distribution.

Numeric Data: Let’s suppose we want to generate 5 random numbers between 1 and 10,000 with 2-digits precision.

test@ORADB10G> exec dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select trunc(dbms_random.value(1,10000),2) rand_num from dual connect by level <=5;

If we want to generate random numbers less than 10,000 and divisible by 100 or in other words only numbers in hundreds. Here is the code to achieve this:

test@ORADB10G> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select trunc(dbms_random.value(1,10001),-2) Rand_Hundreds from dual connect by level <=5;

Character String: Let’s suppose we want to generate dummy names in the form of first name space last name where both fist name and last name are 4-10 characters variable-length string in proper case (the first letter of each word in uppercase, all other letters in lowercase).

test@ORADB10G> exec dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select initcap(dbms_random.string('L',dbms_random.value(4,10)))
        || ' ' || initcap(dbms_random.string('L',dbms_random.value(4,10))) Rand_Names from dual connect by level <=5;

Vgfj Lyyv
Hmtvmie Etsdpdajyg
Awzadckd Fvjji
Cridnrykc Ewpe
Umvyvpjx Xreohmnvu

Seems the names of aliens 🙂

Random Dates: Generating random values are tricky and needs understanding of Julian date formats. Oracle can store dates in the Julian formats. In this format integer offsets from January 1, 4712 B.C. in the past are stored. The format mask ‘J’ can be used with date functions (TO_DATE or TO_CHAR) to convert date data into Julian dates and similarly TO_NUMBER function can be used to use Julian dates in calculations.

Having said this, you can generate random dates in a given range by finding the integer that corresponds to your desired start date, and then adding a random integer to it. For example, suppose you want to generate random dates between 1-Jan-1980 and today (i.e., 19th Nov, 2010). Here is a sample code you can use:

test@ORADB10G> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select to_date(to_char(to_date('01/01/1980','dd/mm/yyyy'),'J') + trunc(dbms_random.value(1,11280)),'J') Rand_dates from dual connect by level <=5;

This code is adding random numbers in the range 1-11280 to the base date (1/1/80). The maxinum value 11280 is number of days between the base date and today.

Take another example where we want to generate random dates in last one year, in other words after 19th Nov 2009.

test@ORADB10G> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.

test@ORADB10G> select sysdate- trunc(dbms_random.value(1,365)) Rand_dates from dual connect by level <=5;

So far we’ve discussed to generate totally random data, however if want to have some control on the data being generated, means repeation based on some logic, that’s where modulus functions comes handy. Following code can be used if you want to generate numbers between 1 to 10. Please note that data generated in this fashion is not random, but mod() function is very useful in some cases.

test@ORADB10G> select mod(rownum,5)+1 Mod_10 from dual connect by level <=10;

That’s really it. I’ve tried to cover different scenarios to generate random numbers, however one can write complex code that suits his scenario. I hope this makes your purpose.


November 12, 2010

Everything You Want to Know About Oracle Histograms Part-1

Filed under: Performance Optimization — neerajbhatia @ 23:25

In the first part of Oracle Histograms series, I am discussing basic concepts like what is a histogram in general, why histograms are required in Oracle and how they are implemented etc.

Link to Part-1

This series may have two more parts about practical details and case studies.


October 25, 2010

Linux Memory Utilization

Filed under: Performance Optimization — neerajbhatia @ 09:08

Many times I observed that novice database/system administrators on Linux platform confuse with the actual memory utilization and utilization presented by Linux native utilities like sar, top etc. The reason behind this is, the way Linux presents memory utilization.

While performance diagnosis most of the times you’ll find 90-100% memory utilization, however that may not be the case and you can calculate “actual” memory utilization in following fashion.

Fetch memory utilization data from sar files using -r switch.
sar -r -f <filename>

It will display data in this format

04:40:01 PM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad
04:50:01 PM 258788 16372260 98.44 12696 13912224 12484624 94144 0.75 420

Information being displayed here, is somewhat misleading. According to it, at 04:50PM, 98.44% memory was utilized (As its merely calculated by the formula kbmemused/sum(kbmemused, kbmemfree)).

But in actual that was not the case. In order to get actual memory utilization, subtract kbbuffers and kbcached from kbmemused, and then used the above formula. In this case, Memory utilization = (16372260-12696-13912224)/(258788+16372260) = 14.71%

The reason behind this is Linux treats unused memory as a wasted resource and so uses as much RAM as it can to cache process/kernel information.

Here Buffers= amount of physical memory used as buffers for disk writes Cached = amount of physical memory used as cache for disk reads

Even if you use top utility to check the memory utilization, situation seems dreadful. It appears that almost all memory is being used. This is because of the way top display memory utilization information.

Here is an example:

top - 09:43:51 up 29 min,  2 users,  load average: 0.10, 0.27, 0.22

Tasks: 166 total,   2 running, 164 sleeping,   0 stopped,   0 zombie

Cpu(s):  5.9%us,  0.8%sy,  0.0%ni, 93.1%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:   2065628k total,  1195636k used,   869992k free,    59540k buffers

Swap:  2104472k total,        0k used,  2104472k free,   695492k cached

The top command reports that total 2065628k bytes (2017MB) of memory is configured on the system, 1195636k (1167MB) is used and 869992k (849MB) is free. Some of the used memory that the top command reports is actually being used for cache. In other words, it is available to be reassigned if another process requires it. To get a better picture of memory utilization, you should use free command. free is basically a c-program that reads output from /proc virtual file system.

Here is an example:

total       used       free     shared    buffers     cached

Mem:          2017       1180        836          0         59        690

-/+ buffers/cache:        430       1586

Swap:         2055          0       2055

The free command reports output in 1024-byte blocks. I used -m switch to report memory utilization in megabytes.

We can interpret free command output as follows. In the first line, it reports that this system has 2017MB memory configured, out of which 1180MB is used and 836 is free. The shared memory column is obsolete and should be ignored. 59MB reported under buffers column, being used as buffers for disk writes and 690MB used as cache for disk read operations. It should be note that memory reported under buffers and cached, is available for use. Hence in second line, they are added to free memory. Thus in actual, system has 1586MB free for use (~ =836+59+690 MB).
Thus it is important to note that, we should concentrate on second line “-/+ buffers/cache:” to get free memory in the system.
The third line reports swap utilization information. Here system has 2055MB of swap configured, swap is not being used and total swap is free for use. We can also get current memory utilization using vmstat (virtual memory statistics tool).

I hope it will help you to get better picture of memory utilization.


October 10, 2010

How to become Oracle Guru (10,000 hour rule)

Filed under: Oracle - General,Other Discussion — neerajbhatia @ 18:10

Recently there was an interesting thread on OTN forums where an Oracle DBA asked for career advice. During the discussion, it went into an interesting direction when Oracle experts Jonathan Lewis and Tom Kyte jumped into the discussion and shared the secret of their success. As per Jonathan, one needs 5 years of focused effort to become a guru like him, Tom Kyte, Cary Millsap and Tanel Poder.

This 5 years (or around 5 years) is a point to note here. There are around 1,800 days in 5 years and 6 hours per day means around 10,000 hours in total to practice and learn Oracle. While reading the thread Immediately I did these calculations in my mind and resulting 10,000 hours thing ring a bell in my mind that I’ve read this somewhere.

In Nov’2008 a book named ‘Outliers’ was released by my favorite author, and I think most influential of our time, Malcolm Gladwell. In the chapter II, Malcolm discusses how 10,000 hours rule play an important role in someone’s journey towards perfection. He took examples of Bill Joy, Bill Gates, the Beatles and also shared some study results. For the sake of giving an example, let me summarize Bill Joy.

Bill Joy is a legendary computer scientist who had written UNIX language and after graduating co-founded Sun Microsystems, which recently acquired by Oracle. Bill did his B.S. from University of Michigan. He joined Michigan in the fall of 1971, the very first year when the university’s computer center opened. Michigan was one of the first universities in the world to have time-sharing system, where hundred people could do programming simultaneously.
Bill Joy did programming eight to ten hours a day during Michigan. In 1975, he enrolled for M.S. at Berkeley. There he buried himself even deeper and did programming day and night. He used to stay up until two or three in the morning. As per Bill, he became proficient programmer by his second year of Berkeley. Bill confirmed that it was the time when he had completed his 10,000 hours of practice.

Now, let’s come back to our original discussion of how to become Oracle guru.

As per the thread, Tom took around 6 years to become an Oracle expert (Oct’94 – 2000) and Jonathan suggested one should practice for 5 years to become proficient. In Back to Basics presentation, Tanel Poder shares his knowledge curve and as per him, it took him around 6 years (1999 to 2005) to become expert and during 7th year of this learning journey, he had release his high-profile snapper. I am not sure about the practice time for other Oracle gurus like Cary Millsap and for that matter Julian Dyke, but one thing I can say that they must have completed 10,000 hours of practice.

Now the question should be, can anyone become a proficient in his/her field by practicing. Malcolm touched this thing and suggested that there is a thing called innate talent and achievement is talent plus preparation. For example, not every person reading Oracle documentation will have same set of questions in this mind, test those scenarios and eventually learn from them. Talented people have divergent thinking, inquisitiveness, skepticism which enable them to learn different and new things, which otherwise people tend to ignore.

Now other than talent and practice there is a third (and perhaps most important factor in some examples) called opportunity. Malcolm explained how opportunities helped above successes in a big way. There were stream of opportunities that came Bill Joy’s way, for example. Because of University of Michigan, he was able to practice on time-sharing systems instead of with punch cards, because the university was willing to spend money, he could stay up all night; and because he was able to put in so many hours, he was able to get the opportunity to rewrite UNIX.

I am not that much fortunate to meet others, but I had met Jonathan Lewis in Hyderabad during AIOUG meet last month and where I had asked this question and he kindly replied that he had been asked to learn Oracle by reading the manuals and he did a lot of practice and study to learn the “new” software (perhaps Oracle version 5.1). At the time when Oracle was evolving, Jonathan had something under his belt which made him ahead from others in the game. Isn’t that a great opportunity that your organization ask you to become familiar and learn new software application, which after some years become No1 in it’s domain and there are not many people around which know it better than you.

So in short, talent does matter but what mattes a lot is practice and it’s 10,000 hours of practice which makes your brain to assimilate all that it needs to know to achieve true mastery. Practice isn’t the thing you do once you’re good, it’s the thing you do that makes you good. As Frank A. Clark quoted “Continuous effort — not strength or intelligence — is the key to unlocking our potential”.

October 1, 2010

Oracle Database Vault (Part-1 Introduction)

Filed under: Database Security — neerajbhatia @ 23:09
Tags: ,

Oracle’s database vault functionality is a part of Oracle Database security framework and share space with label security, data masking, auditing etc. Before delving into the details, let me quick explain why it’s so important to have such security framework in the system.

Oracle DBAs generally have a full database access through ‘DBA’ role, which has powerful privileges like ‘select any table’ and ‘drop any table’. Everyone is fallible to some degree and so the DBAs. What if a DBA accidently drops a production table? On the other hand, DBA don’t need to have access to sensitive tables for example payroll, HR related. As databases are growing more and more valuable, database security is at the top of the minds of IT heads. Nowadays, securing database from outside world (through firewall etc) is not sufficient as insider theft and hacking from inside firewall are major areas of concern. Also, regulatory compliances (PCI, SOX,HIPAA) are driving the need to have a security framework.

I used to work for a telecom organization where a DBA accidently dropped all major production tables (cloning script mistakenly executed on production database which included popular drop table X, create table X statements). You can imagine the impact of that. To avoid such incidents in future, in Oracle 9i we had developed a framework. It had a table with columns like username, corresponding privileges, machine name, start time, end time, IP address etc. Now for every drop table like statements, a trigger first scans the table and if user doesn’t has privilege, command gets failed. A sample table entry can be, username=system, privilege=drop table, object, schema=hr, start time=, end time=, machine name=. The framework was being managed by non-DBA administrator (security admin) through a front-end and sql commands. The point to note is, it built a clear separation of duties which is the very essence of a security design.

The framework we had in-built in 9i is now part of Oracle database and known as database vault, though it’s more efficient and manageable as it’s a part of Oracle kernel itself. In this and coming posts, I am going to explore database vault.

At the very basic level, database vault restricts access to specific areas in an Oracle database from any users, including privileged users like DBAs. This provides separation of duties, only HR schema administrator is able to execute any DDL, DML queries in HR schema, thus highly secured database system.

Just to summarize, with Oracle Database Vault, you can address the most difficult security problems remaining today: protecting against insider threats, meeting regulatory compliance requirements, and enforcing separation of duty.

In my next post, I will explain components of database vault and a quick demo on how it works.

Blog at