How to Test Oracle Database

Oracle Database Testing – This shall be one of the 3 parts of the Oracle Database testing series.

We will cover the following 3 parts in this database testing series:

#1) Introduction to Oracle Database Testing
#2) Testing the database with respect to Memory, Space and CPU processing
#3) Oracle Real Database Application Testing
Bonus Part #4) How to Test and Validate Your Oracle Database Backup and Recovery?

Let’s start with the Introduction to Oracle Database Testing:

Quickly Learn How To Test Oracle Database

What is a Database?

Also, read => All about Database Testing

A database is a collection of data structured methodically which helps manage, manipulate and retrieve data as and when required.

A database is managed by making use of database software named the Database Management System (DBMS) which provides an interface for end users to access the data stored in the database. We access, manipulate this data by SQL statements (Structured Query Language) which were designed for this specific purpose.

For a layman who isn’t aware of the term “database” consider this – We have a desktop/laptop assuming most of us have windows software installed on it, we open a Microsoft Excel sheet, store a couple of names and addresses of individuals, save and close it. This data is stored on the hard disk of your desktop/laptop.

Next time we want to look up the address of a person which we have in the excel sheet, we open this excel sheet and look it up. Consider this excel sheet as the “database” and the Windows operating system as the “DBMS” software which knows how to access and open the file so that the requested piece of information is available to us.

The DBMS software is much more complex, evolved and integrated and is responsible for performing various tasks and activities to ensure smooth functioning of the database.

Why Oracle?

Oracle is the corporation that has built the software to manage a database. Oracle database is one of the most sophisticated, robust, scalable, secure databases available in the market due to the exquisite features all bundled and available to us.

Data in this database is termed relational due to the fact that data is stored and sorted in tables and each of these tables has a relationship established among them. Hence the name- Relational Database Management System (RDBMS).

Real example: Consider a class that consists of 50 students and each of them having a residential address, a class that they have opted, their phone numbers.

Each student’s address could be grouped and stored in one table, phone numbers in another table. If I have to query details of a particular student I look up each of these tables and get the respective information since each of these tables seems “related”.

Managing data in Oracle is particularly favoured due to simplicity in managing and creating them. Since today’s competitive world demands uninterrupted availability, the Oracle database helps deliver it at lower costs.

You could skip the first two sections discussed above if you already have a faint idea about databases, relational models and Oracle.

Now, Comes The Interesting Part!

Need to Test a Database

Data is one of the most integral constituents of an organization that is deemed extremely sensitive and we demand the database which manages them to make sure they are highly impregnable.

A Database can be considered as an application that runs among various other applications running on your server. The database does have to be installed just like any other application and interacts with the hardware through the operations system (windows, Unix) that runs on your server.

We have to make sure we get the optimal performance and benefits out of the Oracle database. The database will not manage itself and perform if you aren’t aware of its capability.

We have various methods to test a database in terms of load, stress, I/O, performance.

The functioning of a database depends on several parameters such as the memory, CPU processing speed, I/O subsystem, filesystem, hardware and applications that connect to the database.

So, to make sure the database performs optimally and avoids the risk of instabilities we need to make sure all the above parameters are well-tuned. Most importantly tune them with respect to your environment. The database in every organization is different – handled, managed and backed up differently.

Telecom industry will want a database which is highly responsive and accurate since millions of user might be making use of the database. A database for a chemical factory comparatively wouldn’t need such a high responsive database since they would focus mostly on reporting and retrieve data as they would have much lesser traffic to handle.

---

Recommended Tool

#1) SolarWinds Database Performance Analyzer for Oracle

SolarWinds provides a Database Performance Analyzer for Oracle. It is a database monitoring solution for Oracle that will help you to find out the root cause of poor performance. It can perform a blocking analysis.

Its anomaly detection is powered by Machine Learning. It supports real-time as well as historic monitoring. It has workload and index advisors. The solution can provide you the details about the wait times, plans, queries, resources, etc. for your Oracle database.

It can be used to optimize all your production databases like Azure, Amazon AWS, etc. It can work with the cloud as well as on-premises databases. The solution will help you to ensure the top performance for Oracle SE, EE, RAC, Exadata, E-Business, and PDBs.

---

How Do You Test Your Database?

We usually have the following environments in an organization:

1. UAT (User Acceptance Testing)
2. DEV (Development Environments)
3. Pre-Prod or TEST environments
4. Production

We usually perform testing on a considerable amount of data which we perceive to be eventually moved to production. This testing is performed in the pre-prod, test or UAT environments.

Before deploying a production database we need to validate the data to make sure that the production is not hampered by any adverse effects.

We need to make sure that the database is tested with full workload just as you expect it to be run against the production database and monitor to ensure the operational and functional requirements are met. We need to test each of the SQL statements thoroughly before they would be deployed in production.

Set a benchmark that you feel is acceptable; this again involves several cycles of testing. If there are any third-party applications that might be part of the organizational infrastructure, make sure the third-party vendors are involved and obtain their feedback regarding the licensing, functioning of their application with the database.

If they do have certain parameters that need to be set to handle a particular amount of workload, set them and make sure they are accepted by the organization and the vendor.

If they do have certain tools to test their application performance make sure they are validated since most databases, including Oracle, do not support third-party applications and fixing an issue which creeps up after having the application deployed in production would be very tedious and could have a huge impact on the organization at which the database server resides.

Points to Consider for Testing Oracle DB:

• Consider a number of users
• SQL statements that they might execute
• The Memory used for each of the users for each of the statements
• The number of transactions that a user would normally execute to retrieve the desired information from the database
• Test if users are receiving the desired response from the database within the expected stipulated time.

1) Memory Test

In terms of memory, it depends on the number of processes that are running in the database as the database system constitutes a user, server and background processes.

User processes are the processes that service user requests from the application. Server processes interpret the request from the application users and process the SQL statements sent from the user.

Background processes help with the functioning of the database and perform various tasks during the life of the database. The advantage of Oracle is that the memory can be automatically managed by a database and it can tune it according to the load.

But, despite that, we need to make sure we have enough memory on the server and a certain amount of memory is always dedicated to the Oracle database.

2) Space Test

In terms of Space, we need to consider the amount of data that would be worked on daily, weekly, monthly, yearly and how much of it would you want to be stored permanently or for a longer period of time.

We should get this valuable piece of information from the Business intelligence who has designed the infrastructure. Another crucial piece of information is to know if we need to store unlimited amounts of data. Define “unlimited” should be your first question.

We can have terabytes of data stored as long as you have enough resources to support it. We would have to manage them with data requirements that have been ever-growing.

3) Processing Test

In terms of Processing, how many CPU cores would we need? Remember a core is a physical entity and with the advent of CPU subsystem technology, we have various methods to employ and utilize multiple CPU cores and multiple threads per core.

Suppose you have 8 CPUs at your disposal, are we sure we are utilizing all of them. Do we really need 8 CPUs? Can we manage them with 4 or 6? Estimating a number of CPU cores that you would require has become more and more imperative to understand processor requirements.

4) Application Testing

In terms of Application testing, we need to understand the type of application – Is it a Pro*C, OCI, JDBC application.

The mechanism of connection depends on the type of client software used and is connection pooling employed. An approximate number of processes and sessions that are required to accommodate the users connecting to the database simultaneously.

For versions higher than Oracle 11g we also have a very good feature called the Oracle Real Application testing with several enhancements which makes use of SQL Performance Analyzer, Database replay, and Test Data management which helps you diagnose the changes to data and manage them. More details on this in the next part of this series.

Conclusion

To conclude, we have dealt with the definition of a database, introduction to Oracle database, the need and aspects of testing oracle database.

In the next part of this series, we shall discuss more on each of the aspects of Oracle Database testing to make sure the design and functioning of the database are broadly met.

Till then put your all questions related to Oracle database testing, in the comments below.

About the author: This is a guest post by Suntrupth Yadav. He is an Oracle Certified Professional in 10g and 11g with over 8.5 years of total experience in the software industry. He is an expert handling all kinds of Oracle database issues.