Monitoring Oracle Database Performance with Grafana – Part II

This the second part of a blog post on using the Grafana graphing tool to create an Oracle database performance dashboard for monitoring host and database performance in real time.

In the previous blog post I created a process to read key metrics from Oracle and insert them into a PostgreSQL database. This intermediate step was necessary as the free edition of Grafana does not include the connector to read directly from Oracle.

Once the metrics were loaded into a PostgreSQL table, a second process calculated deltas for those Oracle metrics that are cumulative. The results were then read by Grafana to show host performance and also report on the number of connected Swingbench users.

In this blog post I am going to expand on that and add IO metrics to track read and write IOPs and also throughput.

There is a video for this blog

As before, all code is available from my GitHub repository.

If you have not read Part I, you can find it here

Monitoring Oracle Database Performance with Grafana – Part I

In a previous blog post I explored how we can use Oracle’s rich V$ views to monitor CPU utilization on the host on which our database is running, as well as the IO generated by all nodes of the database.

Numbers are great, but it would be even better to see the loads represented graphically on a dashboard for an immediate read on how the database and the underlying infrastructure is handling the workload.

In this blog post I will use the Grafana visualization package to create a simple performance dashboard for our Oracle database.

There is a video for this blog

All code is available on GitHub

If you are looking for Part II, you can find it here

Calculating IOPS and Throughput from GV$FILESTAT and GV$IOSTAT_FILE

In a recent blog post I explored how to monitor the database hosts’ CPU utilization from inside of the Oracle database using the V$OSSTAT view. Although not as comprehensive as native OS tools, the approach offered a quick and simple method for Oracle DBAs to leverage the existing software installed and established network connectivity to overlay a simple host monitoring layer that could track CPU utilization rates, and possibly identify CPU starvation risk, or over-provisioned database hosts in a manner that is largely platform neutral and works regardless of any hypervisor.

In this post I am going to explore how to use how to monitor database IOPS and storage throughput in close-to-real-time, again using the databases’ interval views. Once again our approach will likely not be as comprehensive as native OS tools, but will offer Oracle DBAs a quick and simple additional method to track the storage performance of their databases. In addition, since the Oracle internal views track only the database IO, and not IO generated from other applications, our DBAs can be confident that the numbers represent what the database has generated and is experiencing, undiluted by peripheral activities.

Calculating Host Utilization using V$OSSTAT

Since the move to all flash storage solutions, about ten years ago, CPU utilization and bottlenecks have frequently become the primary cause of performance concerns for Oracle DBAs. Whereas in the past the top wait events seen in a Statspack or AWR report might be “db file scattered read” or “db file sequential read”, our contemporary DBA is far more likely to see “CPU” atop the list.

It is important to remember that seeing “CPU” atop the wait events in an AWR report might indicate that the host CPUs are saturated, or it might indicate that there are single threaded processes within the database that are waiting on a single core to complete a compute operation.

And there is a trade off between more cores and clock speed. Generally speaking, within each generation of CPU, more cores means a slower clock. A denser processor with more cores will be able to handle a larger number of simultaneous compute operations – ideal of parallel query processing, analytics, AI and consolidation, whereas a lower core count with a higher clock speed is generally better suited for transaction processing applications, as well as being dramatically less expensive to license for the Oracle software.

Keeping an eye on host utilization then is an important part of an operational DBAs task list, and there are many excellent tools to do this across a large enterprise. In this post we explore a simple method to monitor host utilization from within the database, meaning that our DBA can use SQL*Plus access via TNS to databases to keep track of the underlying hosts.

Delete a storage snapshot of an Oracle 19c RAC database using ASM diskgroups with Dell PowerMax.

In this post I am going to explore deleting a storage snapshot of an Oracle 19c RAC database created on a Dell PowerMax storage array.

This post is a companion to the video Dell PowerMax – Refresh a storage snapshot of Oracle RAC database. and a follow on from the blog post Create a storage snapshot of an Oracle 19c RAC database using ASM diskgroups with Dell PowerMax.

This blog post assumes some basic knowledge of PowerMax storage concepts. If you need a basic introduction please check out my PowerMax basics video.

These examples will use the Solutions Enabler command line method of managing the PowerMax, unlike the video which uses the Unisphere graphical interface.

Refresh a storage snapshot of an Oracle 19c RAC database using ASM diskgroups with Dell PowerMax.

In this post I am going to explore refreshing a storage snapshot of an Oracle 19c RAC database created on a Dell PowerMax storage array.

This post is a companion to the video Dell PowerMax – Refresh a storage snapshot of Oracle RAC database. and a follow on from the blog post Create a storage snapshot of an Oracle 19c RAC database using ASM diskgroups with Dell PowerMax.

This blog post assumes some basic knowledge of PowerMax storage concepts. If you need a basic introduction please check out my PowerMax basics video.

These examples will use the Solutions Enabler command line method of managing the PowerMax, unlike the video which uses the Unisphere graphical interface.

Create a storage snapshot of an Oracle 19c RAC database using ASM diskgroups with Dell PowerMax.

In this post I am going to explore creating a storage snapshot of an Oracle 19c RAC database using ASM disks from PowerMax storage.

This post is a companion to the video Dell PowerMax – Create a snapshot of an Oracle RAC database.

This blog post assumes some basic knowledge of PowerMax storage concepts. If you need a basic introduction please check out my PowerMax basics video.

These examples will use the Solutions Enabler command line method of managing the PowerMax, unlike the video which uses the Unisphere graphical interface.

Create Oracle ASM diskgroups with Dell PowerMax and PowerPath

In this post I am going to explore adding a new ASM diskgroup to an Oracle 19c RAC using PowerMax storage.

We will follow best practices as laid out in H17390 Deployment Best Practices Guide for Oracle with Powermax. This post is a companion to the video Create Oracle ASM diskgroups with Dell PowerMax and PowerPath.

This blog post assumes some basic knowledge of PowerMax storage concepts. If you need a basic introduction please check out my PowerMax basics video.

These examples will use the Solutions Enabler command line method of managing the PowerMax, unlike the video which uses the Unisphere graphical interface.

ORA-12637: Packet receive failed

I recently installed the Oracle Instant Client 21c on a Linux host.

I then tried to access an Oracle 19c RAC database on another host:

connect soe/soe@racn1.rrcsc.pmax.local:1521/metrodb.rrcsc.pmax.local
ORA-12637: Packet receive failed

Numerous blog posts on this error, but in this case I needed to add the directive DISABLE_OOB=ON to my SQLNET.ORA.

Normally this would be in $ORACLE_HOME/network/admin, but on the Instant Client, it is located in:

[root@MYLINUX01 ~]# cat /lib/oracle/21/client64/lib/network/admin/sqlnet.ora

DISABLE_OOB=ON

Powerpath will not remove paths after ASM volumes are removed

You’ve unmounted the ASM diskgroup, disabled it, removed the volumes from the masking view and you’ve rescanned the SCSI bus.. yet Powerpath still will not remove the paths…