I am collecting feedback about which topic you like to see for upcoming webcast. I am planning to conduct this sometime next month (June).
Any other feedback you like to share – please feel free to do that in the comments section …
I am collecting feedback about which topic you like to see for upcoming webcast. I am planning to conduct this sometime next month (June).
Any other feedback you like to share – please feel free to do that in the comments section …
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:
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.
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:
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.
For further differences between various database editions, refer MOS Doc ID 465455.1
Now let’s take a step further, and discuss hardware terminologies:
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:
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:
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
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.
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 :-)
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 seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') || USER || USERENV('SESSIONID'));
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.
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; RAND_NUM ---------- 634.58 8284.76 2318.97 2183.62 3702.22
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; RAND_HUNDREDS ------------- 600 8200 2300 2100 3700
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; RAND_NAMES ------------------------- 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; RAND_DATES ---------- 16-DEC-81 02-AUG-05 28-FEB-87 29-SEP-86 08-JUN-91
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; RAND_DATE --------- 26-OCT-10 21-JAN-10 26-AUG-10 31-AUG-10 07-JUL-10
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; MOD_10 ---------- 2 3 4 5 1 2 3 4 5 1
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.
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”.