Welcome!

Weblogic Authors: Yeshim Deniz, Elizabeth White, Michael Meiner, Michael Bushong, Avi Rosenthal

Related Topics: Recurring Revenue, Java IoT, Machine Learning

Recurring Revenue: Article

How to Monitor Oracle Database Performance

What metrics are we interested in?

An Oracle database provides several v$ views to query information about the database instance, including statistical information that can be used for monitoring and problem analysis purposes. Rene Nyffenegger wrote a nice Summary on Oracle’s v$ views that gives an overview of all available views.

The following illustration shows a dashboard with key metrics that we pull out from an Oracle database when doing performance management with our clients:

dynaTrace Dashboard showing key performance metrics queried from an Oracle Database via v$ tables

dynaTrace Dashboard showing key performance metrics queried from an Oracle Database via v$ tables

In this article I provide a quick overview on what these metrics tell us and how to get them. If you run dynaTrace in your environment you can also download the [DL:Oracle Monitor Plugin] from our Community Portal .

I also appreciate feedback from all Oracle experts out there. I am sure there are many more metrics that are interesting and important for performance analysis, so please add those here.

What metrics are we interested in?
Every time you start up an Oracle instance the system allocates memory for its System Global Area (SGA) (Read more on Oracle SGA Concepts). A very interesting area involves internal data buffers. These buffers hold data in memory and are first searched when a request comes in before fetching data from disk. Buffering obviously saves on I/O and speeds up database requests. There is a great article that explains the different buffer pools, how they can be configured and it also gives recommendations on sizing: Using the dynamic SGA Features of Oracle 9i.

Buffer, Execution and I/O metrics
There are several great blogs that explain metrics such as Buffer Cache Hit Ratio, In Memory Sort Ratio,Parse to Execute Ratio or talk about I/O metrics and how to tune your database based on metrics retrieved from v$buffer_pool_statistics.

The metrics that we query therefore are Buffer Cache Hit Ratio, In Memory Sort Ratio, Parse to Execute Ratio, SQL Area Get Ratio, Buffer Busy Wait, Free Buffer Waits, Write Complete Wait, Consistent Gets, DB Block Gets and Physical Reads.

Connection and User Count
The number of connections and user sessions are key system and performance indicators. The number of connections is usually configurable via connection pool settings on the application server. The more connections the more resources you need on the database - on the other hand you can serve more concurrent users that request data from the database. The blog v$license view tipsgives a good overview of values exposed by this view.

The metrics that we query from this view are Maximum Concurrent User Sessions, Current Concurrent User Sessions, Highest Concurrent User Sessions and Maximum Named Users

Connection Time
Additionally to the metrics that we query from the system tables, we also monitor how long it takes to actually establish a physical connection to the database. Our monitors use the Oracle Database driver and measure the time it takes to get a connection. This metric gives us a good indication on how good the database can deal with new incoming connection requests.

How to query them
Querying these values is pretty straight forward. [Our monitor] is implemented in Java. We load the Oracle JDBC Driver and establish a connection to the Oracle Database Instance when we initially launch the monitor. Check out the following blog with an example on How To connect to Oracle with JDBC. Then we simply execute the SQL statements to retrieve the actual measures on a scheduled interval (e.g.: every 30 seconds).

Most of the statements we execute are explained in the blogs I linked to earlier in this article, e.g.: Buffer Cache Hit Ratio, In Memory Sort Ratio,Parse to Execute Ratio

How to run a monitor and how to read the values
We run our monitors on the dynaTrace APM Platform . This means that these monitors are executed on a scheduled interval, e.g.: every 30 seconds. A monitor itself is a Java OSGI plugin with an execute method that queries and returns the metrics from the database views. The monitored values can then be displayed in a dashboard (as seen in the screenshot in the top) or can trigger alerts, e.g.: notify my admins in case Buffer Cache Ratio drops under a defined threshold.

If you implement your own monitor you need to figure out how granular you need the data and you have to figure out how to display and process the individual measure points.

How to read these values?
The most important thing to understand is to not only look at individual metrics but really look at your system - and with that I mean looking at measures from all application components that are involved to process individual transactions. A low Buffer Cache Hit Ratio doesn't mean that your DBA's should run off right away trying to increase this value by tweaking the db_cache_size. The business function of the application largely dictates the way it interacts with its backend datastore.  For example, if the application is more Decision Support oriented, effective use of the Buffer Cache may be impractical or impossible due to the large data sets that are being analyzed.  On the other hand, if the application is OLTP oriented, the Buffer Cache should be highly utilized and the behaviour of the Hit Ratio with respect to transaction mixes, peak concurrencies, etc ... becomes a critical measure to understand.  Nonetheless, even in these cases, you need to look at overall Transaction Response Times and see if you actually have a problem that affects the end-user.

If that is the case you need to figure out whether the time is spent in the Application Layer (bad performing code), the Network (too much data being sent between components) or the Database (inefficient settings or non optimized indices, ...). If it turns out that the problem is mainly caused by the Database Layer you can check whether your connection pools are exhausted. If that is the case you actually want to talk with the application developers and see whether they can optimize the usage of connections. If you have too much traffic on the network because too much data is requested from the database you want to look into optimizing the application code to really only query the data that is needed. If it then turns out that the problem is on the database you have to observe the metrics discussed in this article. There are ways to tweak database settings to improve buffer usage. I am not the expert on Oracle but if you follow the links that I've posted throughout you will find many good recommendations on what to do in various scenarios.

The database is most often not the root cause of transactions that perform poorly from and end-user’s perspective. Too often it is the application that is e.g.: keeping connections open for too long or is querying more data than needed. I recommend reading Top 10 Performance Problems taken from Zappos, Monster, Thomson and Co. Problem Patterns #1, #4 and #7 often lead people to point to the DBA's first instead of identifying these problems in the application layer. That being said - there are of course scenarios where it really is the database that slows down transaction response times. And in these cases it is important to have enough information available to analyze the root cause of the problem. Correlating the database metrics with other metrics from the underlying operating system, network, application server, web server, ... will make it easier to find a solution to your performance problems.

What's your strategy on database monitoring?
This is one example on monitoring your database. I am interested in getting your feedback on which tools and approaches you use to monitor your Oracle, SQL-Server, MySql, ... databases. Let us know (via comments - login required) which measures you monitor and you consider important. Thanks

Related reading:

  1. Lessons Learned: How we Monitor our Community Portal from the Cloud Our dynaTrace Community Portal is our gateway to our users....
  2. Apache Web Server Status Monitoring with a dynaTrace Plugin provided by MCG Systems The extensible plugin architecture of dynaTrace opens many doors for...
  3. Automated Performance Analysis: What’s going on in my ASP.NET or ASP.NET MVC Application? I’ve spent some time in the last weeks playing with different...

More Stories By Andreas Grabner

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

@ThingsExpo Stories
SYS-CON Events announced today that TidalScale, a leading provider of systems and services, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale has been involved in shaping the computing landscape. They've designed, developed and deployed some of the most important and successful systems and services in the history of the computing industry - internet, Ethernet, operating s...
Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, will discuss how from store operations...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, will lead you through the exciting evolution of the cloud. He'll look at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering ...
Infoblox delivers Actionable Network Intelligence to enterprise, government, and service provider customers around the world. They are the industry leader in DNS, DHCP, and IP address management, the category known as DDI. We empower thousands of organizations to control and secure their networks from the core-enabling them to increase efficiency and visibility, improve customer service, and meet compliance requirements.
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant tha...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Recently, REAN Cloud built a digital concierge for a North Carolina hospital that had observed that most patient call button questions were repetitive. In addition, the paper-based process used to measure patient health metrics was laborious, not in real-time and sometimes error-prone. In their session at 21st Cloud Expo, Sean Finnerty, Executive Director, Practice Lead, Health Care & Life Science at REAN Cloud, and Dr. S.P.T. Krishnan, Principal Architect at REAN Cloud, will discuss how they b...
SYS-CON Events announced today that mruby Forum will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. mruby is the lightweight implementation of the Ruby language. We introduce mruby and the mruby IoT framework that enhances development productivity. For more information, visit http://forum.mruby.org/.
Digital transformation is changing the face of business. The IDC predicts that enterprises will commit to a massive new scale of digital transformation, to stake out leadership positions in the "digital transformation economy." Accordingly, attendees at the upcoming Cloud Expo | @ThingsExpo at the Santa Clara Convention Center in Santa Clara, CA, Oct 31-Nov 2, will find fresh new content in a new track called Enterprise Cloud & Digital Transformation.
SYS-CON Events announced today that NetApp has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. NetApp is the data authority for hybrid cloud. NetApp provides a full range of hybrid cloud data services that simplify management of applications and data across cloud and on-premises environments to accelerate digital transformation. Together with their partners, NetApp emp...
SYS-CON Events announced today that Avere Systems, a leading provider of enterprise storage for the hybrid cloud, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Avere delivers a more modern architectural approach to storage that doesn't require the overprovisioning of storage capacity to achieve performance, overspending on expensive storage media for inactive data or the overbui...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software. They hope to capture value from emerging technologies such as IoT, SDN, and AI. Ultimately, irrespective of the vertical, it is about deriving value from independent software applications participating in an ecosystem as one comprehensive solution. In his session at @ThingsExpo, Kausik Sridhar, founder and CTO of Pulzze Systems, will discuss how given the magnitude of today's applicati...
Smart cities have the potential to change our lives at so many levels for citizens: less pollution, reduced parking obstacles, better health, education and more energy savings. Real-time data streaming and the Internet of Things (IoT) possess the power to turn this vision into a reality. However, most organizations today are building their data infrastructure to focus solely on addressing immediate business needs vs. a platform capable of quickly adapting emerging technologies to address future ...
SYS-CON Events announced today that Taica will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TAZMO technology and development capabilities in the semiconductor and LCD-related manufacturing fields are among the best worldwide. For more information, visit https://www.tazmo.co.jp/en/.
SYS-CON Events announced today that Avere Systems, a leading provider of hybrid cloud enablement solutions, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Avere Systems was created by file systems experts determined to reinvent storage by changing the way enterprises thought about and bought storage resources. With decades of experience behind the company’s founders, Avere got its ...
SYS-CON Events announced today that TidalScale will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale is the leading provider of Software-Defined Servers that bring flexibility to modern data centers by right-sizing servers on the fly to fit any data set or workload. TidalScale’s award-winning inverse hypervisor technology combines multiple commodity servers (including their ass...
As hybrid cloud becomes the de-facto standard mode of operation for most enterprises, new challenges arise on how to efficiently and economically share data across environments. In his session at 21st Cloud Expo, Dr. Allon Cohen, VP of Product at Elastifile, will explore new techniques and best practices that help enterprise IT benefit from the advantages of hybrid cloud environments by enabling data availability for both legacy enterprise and cloud-native mission critical applications. By rev...