Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL, Cassandra & much more … \,,/

  • Likes

    • 256,760
  • Archives

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘performance’

How to monitor your PostgreSQL databases using EDB PEM – Setup, Config, benchmarking and much more …

Posted by FatDBA on March 26, 2021

Hi Everyone,

Today’s post will be all about monitoring your PostgreSQL database clusters using EDB PostgreSQL Enterprise Manager (PEM). Postgres Enterprise Manager is a comprehensive, customizable solution providing an interface to control and optimize your PostgreSQL deployment.

I will be doing the installation, configuration, adding servers to the console and will perform a live monitoring of the database while I will be generating some synthetic load on the database host. I am doing this on a standalone RHEL 7 64 Bit server which I will be using it both as a PEM server and local instance. Alright, so without further ado, lets start. So, first you need to download EDB’s official repository and install following package.

Below is a complete list of packages available with name ‘edb-pem’, you need to install version: edb-pem-8.0.1-1.rhel7.x86_64

[root@canttowin repo]# yum search edb-pem
Loaded plugins: langpacks, ulninfo

=================================================================== N/S matched: edb-pem ====================================================================
edb-pem-debuginfo.x86_64 : Debug information for package edb-pem
edb-pem.x86_64 : PostgreSQL Enterprise Manager
edb-pem-agent.x86_64 : Postgres Enterprise Manager Agent
edb-pem-docs.x86_64 : Documentation for Postgres Enterprise Manager
edb-pem-server.x86_64 : PEM Server Components

Once installation is completed, go to the default installation directory, it’s /usr/edb in my case, and go to pem/bin folder.

[root@canttowin ~]# cd /usr/edb/
[root@canttowin edb]# ls
as12 bart efm-4.1 jdbc migrationtoolkit pem pgbouncer1.15 pgpool4.2
[root@canttowin ~]# cd /usr/edb/pem/bin/
[root@canttowin bin]# ls
configure-pem-server.sh configure-selinux.sh

We see two configuration shell scripts are present, we will be using the configuration script – configure-pem-server.sh
Here I will be choosing option 1 which means I will be installing web services and databases all on one host, next you need to input installation path (/usr/edb/as12 in my case), followed by super user name, port numbers and IP Address of the server.

Before I call the config script, let me quickly reset the default superuser’s password.

postgres=# alter user postgres with password 'dixit';
ALTER ROLE

Now, let’s call the configuration scipt and pass all discussed values.

[root@canttowin bin]# ./configure-pem-server.sh

 -----------------------------------------------------
 EDB Postgres Enterprise Manager
 -----------------------------------------------------
Install type: 1:Web Services and Database, 2:Web Services 3: Database [ ] :1
Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.) [ ] :/usr/edb/as12
Enter database super user name [ ] :enterprisedb
Enter database server port number [ ] :5444
Enter database super user password [ ] :
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24 [ 0.0.0.0/0 ] :10.0.0.153/32
Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.) [ ] :edb-as-12
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists) [ ~/.pem/ ] :
CREATE EXTENSION
[Info] Configuring database server.
CREATE DATABASE
CREATE ROLE
CREATE ...
..
..
..
CREATE EXTENSION
-->  [Info] -->  [Info] Configuring database server.
-->  [Info] -->  [Info] creating role pem
-->  [Info] -->  [Info] Generating certificates
-->  [Info] -->  [Info] Executing systemctl stop edb-as-12
-->  [Info] -->  [Info] Skipping - configurations for /var/lib/edb/as12/data/pg_hba.conf and /var/lib/edb/as12/data/postgresql.conf file
-->  [Info] -->  [Info] Executing systemctl start edb-as-12
-->  [Info] -->  [Info] Enable pemagent service.
-->  [Info] -->  [Info] Executing systemctl enable pemagent
-->  [Info] -->  [Info] Stop pemagent service
-->  [Info] -->  [Info] Executing systemctl stop pemagent
-->  [Info] -->  [Info] Start pemagent service.
-->  [Info] -->  [Info] Executing systemctl start pemagent
-->  [Info] -->  [Info] Configuring httpd server
-->  [Info] -->  [Info] Executing systemctl stop httpd
-->  [Info] -->  [Info] Taking backup of /usr/edb/pem/web/pem.wsgi
-->  [Info] -->  [Info] Creating /usr/edb/pem/web/pem.wsgi
-->  [Info] -->  [Info] Taking backup of /usr/edb/pem/web/config_local.py.
-->  [Info] -->  [Info] Generating PEM Cookie Name.
-->  [Info] -->  [Info] Creating /usr/edb/pem/web/config_local.py
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-pem.conf
-->  [Info] -->  [Info] Creating /etc/httpd/conf.d/edb-pem.conf
-->  [Info] -->  [Info] Configuring httpd server sslconf
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-ssl-pem.conf
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-ssl-pem.conf
-->  [Info] -->  [Info] Executing /usr/edb/pem/web/setup.py
Postgres Enterprise Manager - Application Initialisation
========================================================
-->  [Info] -->  [Info] Check and Configure SELinux security policy for PEM
 getenforce found, now executing 'getenforce' command
 Configure the httpd to work with the SELinux
 Allow the httpd to connect the database (httpd_can_network_connect_db = on)
 Allow the httpd to connect the network (httpd_can_network_connect = on)
 Allow the httpd to work with cgi (httpd_enable_cgi = on)
 Allow to read & write permission on the 'pem' user home directory
 SELinux policy is configured for PEM
-->  [Info] -->  [Info] Executing systemctl start httpd
-->  [Info] -->  [Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.
-->  [Info] -->  [Info] PEM server can be accessed at https://127.0.0.1:8443/pem at your browser

It’s completed, and at the very end it has provided URL to access the PEM GUI.

Now next step is to install PEM Agents to the server, you need to install it on all servers which you want to monitor, I am leaving the PEMAgents configuration that you do in agent.cfg file.

[root@canttowin bin]# yum install edb-pem-agent

Let’s check the PEM GUI now.

Here on the left panel you will notice there’s already one database present under ‘PEM Server Directory’ folder, this is the same database which we have configured/used PEM server, hence it will be automatically added to the server list. We will manually add one more database cluster to explain how to do it explicitly.

Let’s check the dashboard for the same (PEM Server) database for session, TPS, IO related details.

Now, let’s add another database to the monitoring console. I will be adding a community PostgreSQL 12 database to it. Go to ‘PEM Server Directory’ folder right click on it, choose option create-> server.

Next, fill connection wizard with all details i.e, username, password, IP, port and security related details for the new database and click save at the end.

And you are done!

Now, let’s see the default landing page of PEM GUI and here you see details of all added hosts and agents with their status.

Next I will create some new databases to see how that data reflects in PEM GUI.
postgres=#
postgres=# create database dixit;
CREATE DATABASE
postgres=# create database kartikey;
CREATE DATABASE

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size
-----------+----------+----------+-------------+-------------+-----------------------+--------
dixit | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8049 kB
kartikey | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8049 kB
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8193 kB

(3 rows)

All good! now let’s do some performance test to see how useful PEM can be in case of performance issues. In order to mimic or simulate the situation, I will generating some synthetic load using PostgreSQL’s default utility Pgbench.

Reference:
-c number of clients
-j 2 number of threads
-t amount of transactions

These values are 10000 transactions per client. So : 10 x 10000 = 100,000 transactions

[postgres@canttowin bin]$ ./pgbench -U postgres -p 5432 -c 10 -j 2 -t 10000 postgres
starting vacuum…end.

Let’s see how the changes are captured and presented in PEM.

Okay, we can see the peaks are recorded and presented.

The load is still running and we can clearly see that from the below graph.

[postgres@canttowin bin]$ ./pgbench -U postgres -p 5432 -c 10 -j 2 -t 10000 postgres
starting vacuum…end.transaction type:
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 18.217 ms
tps = 548.940142 (including connections establishing)
tps = 548.970173 (excluding connections establishing)

Alright, so the load run has ended, let see how the graph now looks like.

So to conclude, PEM is a great tool which can fulfil all your monitoring needs, it has got some cool features too i.e. performance dashboards, tuning wizards, advisories and other graphs.

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , | 1 Comment »

How to monitor your PostgreSQL database using Grafana, Prometheus & postgres_exporter.

Posted by FatDBA on March 24, 2021

Hi Everyone,

I am back with the another post, this time it’s for monitoring PostgreSQL database using one of the popular interactive visualization platform Grafana. I have recently implemented Grafana + Prometheus and created few really cool performance charts and database metric dashboards using one of the popular PostgreSQL metric exporter ‘postgres_exporter‘ to monitor a EDB 12 PostgreSQL database cluster.

I have divided everything in to three parts – Grafana Installation & configuration, Prometheus Installation and Configuration and final postgres_exporter installation and configuration.

let’s first start with Grafana installation and configuration on Linux server (this is EL 7).

Grafana Installation:

1. Disable SELinux

Change SELINUX=enforcing to SELINUX=disabled and Reboot.

vi /etc/sysconfig/selinux

2. Now we need to create Grafana YUM repository

vi /etc/yum.repos.d/grafana.repo

and add following lines to it

[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

3. Install Grafana now using YUM

yum install grafana

This will install all binaries to location /usr/sbin/grafana-server, copies init.d file to /etc/init.d/grafana-server and default log location would be /var/log/grafana/grafana.log.

4. Install additional font packages

yum install fontconfig
yum install freetype*
yum install urw-fonts

5. Now, enable grafana service

Enable Grafana service on system boot
systemctl enable grafana-server.service

Start it using the following command:
systemctl start grafana-server

6. Check Grafana web interface

http://IPADDRESSorHOSTNAME:3000/

Note: Default username and password is ‘admin‘, and once you login, it will prompt you to set a new password.

Add PostgreSQL as Data Source:

Next we now going to add PostgreSQL as a data source in Grafana.

1. Login to Grafana and go to ‘configuration‘ tab on the left of the console.

2. Add PostgreSQL as a data source, populate all details like hostname, port number, database name, DB user and password, PostgreSQL version and some optional entries like connection limits, SSL details etc. Once done, click on button with name save and test to see if all details are fine.

3. Now you will start seeing PostgreSQL as a data source under data source tab.

Prometheus Installation & Config

All good, now next is to install and configure ‘Prometheus’, this is a time-series database that is optimized for storing and serving time series through associated pairs of time(s) and value(s). You can also use InfluxDB or Graphite as a time series database for Grafana.

1. Download and untar the Prometheus file for your respective OS.
Example:
curl -LO url -LO https://github.com/prometheus/prometheus/releases/download/v2.22.0/prometheus-2.22.0.linux-amd64.tar.gz
tar -xvf prometheus-2.22.0.linux-amd64.tar.gz
mv prometheus-2.22.0.linux-amd64 prometheus-files

2. Create prometheus user, directories and make that account owner for all the files and folders.

sudo useradd --no-create-home --shell /bin/false prometheus
sudo mkdir /etc/prometheus
sudo mkdir /var/lib/prometheus
sudo chown prometheus:prometheus /etc/prometheus
sudo chown prometheus:prometheus /var/lib/prometheus

3. Copy prometheus and promtool binaries from prometheus-files to /usr/local/bin and change ownership

sudo cp prometheus-files/prometheus /usr/local/bin/
sudo cp prometheus-files/promtool /usr/local/bin/
sudo chown prometheus:prometheus /usr/local/bin/prometheus
sudo chown prometheus:prometheus /usr/local/bin/promtool

4. Move consoles and console_libraries directories from prometheus-files to /etc/prometheus folder and change ownership

sudo cp -r prometheus-files/consoles /etc/prometheus
sudo cp -r prometheus-files/console_libraries /etc/prometheus
sudo chown -R prometheus:prometheus /etc/prometheus/consoles
sudo chown -R prometheus:prometheus /etc/prometheus/console_libraries

5. Now time to configure. Create prometheus.yml file

vi /etc/prometheus/prometheus.yml

copy below entries to the YAML file

global:
  scrape_interval: 10s

scrape_configs:
  - job_name: 'prometheus'
    scrape_interval: 5s
    static_configs:
      - targets: ['10.0.0.153:9090']

6. Change ownership of this file

sudo chown prometheus:prometheus /etc/prometheus/prometheus.yml

7. Create prometheus service file

vi /etc/systemd/system/prometheus.service

copy below entries to the file.

[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus \
    --config.file /etc/prometheus/prometheus.yml \
    --storage.tsdb.path /var/lib/prometheus/ \
    --web.console.templates=/etc/prometheus/consoles \
    --web.console.libraries=/etc/prometheus/console_libraries

[Install]
WantedBy=multi-user.target

8. Register systemd service to register prometheus service and start it.

sudo systemctl daemon-reload
sudo systemctl enable prometheus
sudo systemctl start prometheus

and check the status of the service

sudo systemctl status prometheus

[root@canttowin edb]# systemctl status prometheus
● prometheus.service - Prometheus
   Loaded: loaded (/etc/systemd/system/prometheus.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-03-24 05:55:53 EDT; 4s ago
 Main PID: 17641 (prometheus)
    Tasks: 7
   CGroup: /system.slice/prometheus.service
           └─17641 /usr/local/bin/prometheus --config.file /etc/prometheus/prometheus.yml --storage.tsdb.path /var/lib/prometheus/ --web.console.templates...

Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.172Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.358Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.455Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.456Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.456Z caller=head.go:719 component=tsdb msg="WAL replay c...173255ms
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:732 fs_type=XFS_SUPER_MAGIC
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:735 msg="TSDB started"
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:861 msg="Loading configuration file"...heus.yml
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.481Z caller=main.go:892 msg="Completed loading of configuration …µs
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.481Z caller=main.go:684 msg="Server is ready to receive ...quests."
Hint: Some lines were ellipsized, use -l to show in full.

9. If no issues till here, you are all good, time to check Prometheus WEB GUI, you can access its using below URL.

http://10.0.0.153:9090/graph

10. You can check other stats and other available metrics via GUI console.

11. Now you will start seeing ‘Prometheus’ in the list of data sources along with PostgreSQL which we have added at the first step.

Postgres_Exporter Installation and configuration

Now, when Grafana and Prometheus is all set, time to install and configure postgres_exporter. Postgres_exporter is a popular PostgreSQL metric exporter for Prometheus.

1. Lets create few required directories first.

mkdir /opt/postgres_exporter
cd /opt/postgres_exporter

2. Download and untar the file.

wget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.5.1/postgres_exporter_v0.5.1_linux-amd64.tar.gz
tar -xzvf postgres_exporter_v0.5.1_linux-amd64.tar.gz
cd postgres_exporter_v0.5.1_linux-amd64

3. Copy core file ‘postgres_exporter’ file to /usr/local/bin directory

cp postgres_exporter /usr/local/bin

4. Next, lets create the configuration file for postres_exporter

cd /opt/postgres_exporter
sudo vi postgres_exporter.env

I want to visualize my EDB 12 PostgreSQL stats, so will use below data source details. This is for all database, you can also monitor any specific database.

[root@canttowin edb]# more /opt/postgres_exporter/postgres_exporter.env
DATA_SOURCE_NAME="postgresql://enterprisedb:oracle@10.0.0.153:5444/?sslmode=disable"

5. Next, create the service for postgres_exporter

vi /etc/systemd/system/postgres_exporter.service

put below lines to the service file

[Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target
[Service]
User=postgres
Group=postgres
WorkingDirectory=/opt/postgres_exporter
EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter
Restart=always
[Install]
WantedBy=multi-user.target

6. Next enable service and check status

sudo systemctl daemon-reload
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter

[root@canttowin edb]# systemctl status postgres_exporter
● postgres_exporter.service - Prometheus exporter for Postgresql
   Loaded: loaded (/etc/systemd/system/postgres_exporter.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-03-24 05:52:17 EDT; 2s ago
 Main PID: 16984 (postgres_export)
    Tasks: 3
   CGroup: /system.slice/postgres_exporter.service
           └─16984 /usr/local/bin/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics

Mar 24 05:52:17 canttowin.ontadomain systemd[1]: Started Prometheus exporter for Postgresql.
Mar 24 05:52:17 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:17-04:00" level=info msg="Established new database connection...go:777"
Mar 24 05:52:17 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:17-04:00" level=info msg="Semantic Version Changed on \"10.0....o:1229"
Mar 24 05:52:18 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:18-04:00" level=info msg="Starting Server: :9187" source="pos...o:1437"
Hint: Some lines were ellipsized, use -l to show in full.

7. Now we can check status if postgres_exporter (as a target) state information and other details. This we can check it through Prometheus web GUI.

As we have configured postgres_exporter and mapped it with Prometheus, we can also look for many of the expressions that it has created. This you can access on ‘graph’ page under Prometheus GUI console.

You can check it’s immediate results in both graphical or in console itself.

Alright, we have installed and configured Grafana and have added PostgreSQL and Prometheus as a data source and have configured postgres_exporter metric collector too. Time to generate some graphs. Well, you can do it by creating manual queries under new dashboards or else you can use any of prebuild Grafana dashboard templates for PostgreSQL i.e. 6742 or even 9628 (there are many more on Grafana labs repo).

1. Lets Import dashboard with ID 6742. Go to option with + sign on the left panel and choose last option ‘import’.

2. In next screen, pass dashboard ID 6742 and press button Load. You can also use JSON file entries instead of ID, I will use ID here in this sample configuration. Here select ‘Prometheus’ from the drop down list and click ‘Import’ and it’s all set!

3. Now this will bring the final dashboard showing lot’s of PostgreSQL metrics and their current values.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , | Leave a Comment »

Thanks Percona for this great product called PMM (Percona Monitoring & Management Server)

Posted by FatDBA on December 2, 2020

Hi Guys,

For one of our customer we recently moved from on-prem to AWS cloud and we moved our entire infrastructure along with our MySQL & PostgreSQL Clusters. The very next question that started bothering us is to look out for any solid, stable and reliable monitoring and performance management tool, and then we learned about Percona’s Monitoring & Management Server (PMM).

After trying the setup we are pretty satisfied with it’s performance as it fulfils all our expectations from any monitoring tool to monitor our applications and databases. We found the product best in terms of Cost (comes with an hourly price of only $ 0.10/hr), highly secure (with SSL encryption) and offers some really cool and smart features.
There are many features what it offers, but being a long time Performance Consultant (DBA) I personally found the ‘Query Analytics’ section more useful and impressive. It has got everything what you need to know for your real-time workload, it provides an in-depth analysis of your queries i.e. execution plan, latency information, query fingerprints etc. This can provide very useful at the time when you have any problem in hand and want to deep dive in and want to know what all is going on with your system at the statement level.

It has got great integration with Grafana platform too, great data visualizations in the form of dashboards etc.

I will recommend this tool for people who are looking for any good stable monitoring platform along with performance administration tool. Not only for PostgreSQL & MySQL but it’s available for other platforms too like Amazon RDS MySQL, Amazon Aurora MySQL, MongoDB, Percona XtraDB Cluster and ProxySQL.

It’s available on both AWS and Azure markets.

Link to AWS Marketplace : https://aws.amazon.com/marketplace/pp/B077J7FYGX?qid=1605533229523&sr=0-1&ref_=srh_res_product_title

Link to Azure Marketplace: https://azuremarketplace.microsoft.com/en-us/marketplace/apps/percona.pmm_2?tab=Overview

 

Hope It Helped!

Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

High Latch Free waits due to Result Cache: RC Latch contention

Posted by FatDBA on June 16, 2020

Hi Everyone,

This is one of the latest issue that I will be discussing next where in one of the 12c (12.1.0.2.0) Non-RAC production box we suddenly started seeing a huge spike in database workload (or AAS). As usual I started with some live monitoring using scripts, OEM Performance page and tools and saw huge numbers of sessions waiting on ‘Latch Free’ (Others classed event) waits. Next I saw the event wait were contributing more than 87% of the total DBTime % with exceptionally high latch wait times of 54 ms (Yes, that’s too much for latch gets) and what else caught my eye was ‘enq: RC – Result Cache: Contention’ with average wait times of 19.31 ms.

But lets not jump directly to the conclusion, next I checked SQLs with excessive buffer scans (SQL Ordered by Gets) and saw the source statement that caused the mess with exceptionally high number of buffer scan requests and as you must be aware that each scan that you do in memory buffers (db buffer cache) you will have to acquire a latch for your scan. This one SQL was responsible for around 85% of total buffer reads or gets and interestingly within three hours of AWR report not even a single execution was completed.

Alright now next task was to identify the type of latch which was causing ‘latch free’ or latch shortage in the database. And for that I checked ‘Latch Statistics’ section and ‘Latch Sleep Breakdown’ subsection to see what latch was frequently missed to get and with more sleep requests, and I have got the culprit, ‘Result Cache: RC Latch’ was coming with huge Miss and sleep ratio. The latch was sleeping mainly for ‘Result Cache: Serialization12’ and little bit on ‘Result Cache: Serialization01’.

Next just to be double sure, I checked for P2 value for ‘Latch Free’ event which was coming as 559. So I queried V$Latchname to see if it’s matching the same what we identified so far. And yes, it was same RC Latch!


> SELECT latch#,name FROM gv$latchname WHERE latch#=559;

    LATCH# NAME
---------- ----------------------------------------------------------------
       559 Result Cache: RC Latch
 

You can do more deep down analysis and troubleshooting on such issues, Tanel Poder has written a great presentation for all who want to dig deep and want to pin point the addresses, code etc.
Download the copy of the document, click next –> Oracle_Latch_And_Mutex_Contention_Troubleshooting

Alright, now when the latch name, SQL is identified lets check more about the RESULT CACHE, and it’s set to MANUAL (parameter result_cache_mode) and this is strange. Next we saw that the SQL using the RC contains DS_SVC hints and are part of dynamic statistics activity.
Then I thought to look out for some official documentation on the same, and luckily found one official note from Oracle Doc ID 2002089.1 which is matching almost same with my problem.

And as per the document the workload we can apply is to disable the Adaptive Dynamic Statistics mechanism.


alter system set "_optimizer_ads_use_result_cache" = FALSE;
 

or else One-off Patch 26436717 can be requested and applied to fix the issue for a permanent fix.

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

Oracle Real Application Testing (RAT) – Part 2: What is Capture & how to do it ?

Posted by FatDBA on February 10, 2020

Hi Folks,

Continuing the same subject/topic what I have started in my last post – Real Application Testing (RAT). This post is all about the ‘Capture‘ part what happens on the source database where we captures the workload which will later on replayed on the target database.

I have break it in to few easy steps to understand.

Step 1:
First we need to verify if the RAT option is installed and working fine. This you only need to verify upto 10Gr2 as all later versions comes with all features enabled by himself during the installation process, until you didn’t de-selected any specific feature during customized installation.
In case of 10g you need to check using v$option dynamic view for RAT parameter and there is a need to enable the parameter ‘pre_11g_enable_capture’.

Please see below the steps of verification!

Step 2:
Creating exclusion FILTERS (If Required) for capture.
This is the step where we create filters to exclude few of the system usernames and few of other schemas like SYSMAN, SYS and all of such schemas where you don’t want to capture load.
Please see the screenshot use to do the same.

Step 3:
Creation of RAT specific OS based directory.
This is the place where all CAPTURE files will be saved and should be created like below.

Step 4:
Next, we can now start the CAPTURE process, this should be done using the main RAT specific procedure DBMS_WORKLOAD_CAPTURE and its function START_CAPTURE. Here the main parameter to pass is name (Name of the capture you want to name), dir (directory which will hold all workload files, the same what I have created above).
There are few parameters which I have intentionally not used i.e. DURATION as there is a BUG in 10Gr2 database which causes the capture not to stop even after specific time and had to manually stop the process. So in below example I will be capturing XXXXX minutes of load from this database and will stop it explicitly.

This being a staging setup I am taking around 30 minutes of workload but in real time this could be anything between 10 or 15 minutes of peak hours.

You can monitor the progress using DBA_WORKLOAD_CAPTURES view. See below.

Next, you can get more details about this ongoing capture activity. See below.

Step 5:
Next when we are done with the capturing of load for the specified time, we can go and stop it now. In my case on RS staging I left that running and capturing workload for ~ 32 Minutes.

Next, you can verify the contents by going to the RAT directory. There you will see .rec (recording) files, .wmd and a special report (TEXT/HTML format) will generate and is specific to CAPTURE process only.

Now we have BEGIN SNAP Id and END SNAP (time duration for the capture runtime) we can generate the AWR report as well.
The same report can be fetched via text method as well.

Step 6:
Next we will export the AWR data. This will be later on used to generate the comparison report from REPLAY side. This will create two more files under the capture directory wcr_ca.log and wcr_ca.dmp

Next steps starts at the target end or the host and deserves a separate post.
I will soon be writing about Replay process in my next post. Till that time keep learning!

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

Oracle Real Application Testing (RAT) – Part 1: What it is ?

Posted by FatDBA on January 31, 2020

Hi Guys,

As committed I am back with the first edition or the post on Oracle RAT (Real Application Testing) and there be couple more follow up chapters on the same in next few days or weeks.

Alright, recently during one of our mission-critical production database migration we reached a point where we had to perform the Load Test before pushing the real-time workload on to this new system. I was asked to prepare the strategy and to pick the best possible tool to access the performance of the performance of this new system and how it will respond to the current traffic.

Received lot’s of suggestions from rest of the team, i.e. Swingbench, Loadrunner, Orion etc. but most of them are with a predefined set of Supplied Benchmarks though few are customized but are more related to the server performance and bechmarking but not at the Database or SQL level. And considering the notorious behavior of many of the custom code and legacy application modules I was more leaned towards picking a tool which covers both Database and SQL, and we finally agreed on Oracle RAT.

Oracle Real Application Testing, an option that comes with Oracle Enterprise Edition. Oracle Real Application Testing helps you to test the real-life workload after changes on the database such as database upgrades, OS upgrades, parameter changes, hardware replacement, etc. So, in short the Oracle RAT will be system stress test tool to simulate production load. Introduced in Oracle 11g Release 1. But yes, it’s not free and comes with additional cost and licenses.

There are two features “Database Replay” and “SQL Performance Analyzer” will help fine-tuning on the database before passing production.
I will cover more about the ‘Database Replay’ feature here and might cover the ‘SQL Performance Analyzer’ feature later.

When can you use RAT – “Database Replay” feature?
System Changes
– Hardware replacement such as CPU, RAM, etc.
– Database and OS upgrades
– Storage changes (OCFS2 – ASM)
– OS changes (Windows – Linux)
Configuration Changes
– Single Instance – RAC– Patch installation– Database parameter change

Which database versions are supported?
The workload capture process is supported on the Oracle Database 10g R2 (10.2.0.4) and above versions. The worload replay process is supported on the Oracle Database 11g R1 and above versions.

How to do it, where to start and all ?
Well there are two different ways you can perform the RAT (DB Replay) testing
– Using Oracle Enterprise Manager (OEM) : This option is entirely GUI based where you select your source and target systems and by doing all those clicks performs this stress/load testing on the system.
– Using command line way (My preferred way of doing this, yes I am ‘old school’) using DBMS_WORKLOAD_CAPTURE & DBMS_WORKLOAD_REPLAY procedures.

Some High Level Steps:
– Capture workload into capture files (In the form of .rec files, are flat files)
– Copy files to test system and preprocess them (to make them machine understandable)
– Replay files on test system (play the recorded files)
– Perform detailed analysis of workload capture and replay using reports generated by Database Replay. (Reporting for bench markings)

ON SOURCE System:
dbms_workload_capture.start_capture 
dbms_workload_capture.finish_capture; 

Copy the workload files to the client system. For example: – /home/oracle/rat/test1

On TARGET System:
1. dbms_workload_replay.process_capture 
2. dbms_workload_replay.initialize_replay 
3. dbms_workload_replay.prepare_replay 
4. Run the workload client to calibrate the replay. The calibration process (mode=CALIBRATE) recommends the number of client processes required to perform the replay
5. Replay the workload using below command.
dbms_workload_replay.start_replay; 

Components: The ARCHITECTURE (Simplified)

DB REPLAY “The Big Picture”

What is a Workload Client ?
The REPLAY uses wrc clients – Which are multi-threaded JAVA clients and can be started on the same machine/host or on separate hosts.
Will cover about them more in depth in future posts.


[oracle@PDIXIT:RAT]$ wrc system/XXXX mode=calibrate replaydir=/DBCapture/RAT/RAT_13DEC15_19_17
 Workload Replay Client: Release 11.2.0.4.0 - Production on Sat Dec 16 05:50:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
Report for Workload in: /DBCapture/RAT/RAT_13DEC16_19_17
-----------------------
Recommendation:
Consider using at least 13 clients divided among 4 CPU(s)
You will need at least 168 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
 
Workload Characteristics:
- max concurrency: 575 sessions
- total number of sessions: 1729
 
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
 

Now how to compare/benchmark ?
At the end of both CAPTURE & REPLAY methods you need to generate few process specific report.
Few of the important files that help in benchmarking are:
AWR Reports: Generate the AWR reports for the same time interval when we have any of the two process were in progress. The BEGIN AND END Snaps can be collected from DBA_WORKLOAD_CAPTURES & DBA_WORKLOAD_REPLAYS
CAPTURE/REPLAY Reports: These reports are specific to workload capture and playing on target.
Capture Vs Replay reports.

Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , | Leave a Comment »

RAT Reporting Error: ORA-06502: numeric or value error: character string buffer too small

Posted by FatDBA on December 16, 2019

Hi All,

Today’s topic of discussion is to handle/fix one of the issues that I’d faced while generating RAT (real application testing) reports on 10gR2 database. I know many of us are not yet aware about the tool, it’s purpose and functionality. Very soon I will be writing about this great product from Oracle for database load testing using real/genuine workload and is quite helpful to forecast your DB performance before you migrate.

Alright, coming back to the point – I was trying to generate the RAT Capture report (on target of course) to see what all was there in the capture, its observations, highlights and rest and that’s when we’ve encountered an error (pasted below)



DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 81,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/ 2 3 4 5 6 7
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 7446
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 8591
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 8521
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 486
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 1214
ORA-06512: at line 4


There are two solutions to this problem:

1. First to drop the common (shared by capture and replay) schemas and their infrastructure tables using below two scripts.
That firstscript below drop schema tables shared by capture and replay and second drops the Capture infrastructure tables.
catwrr.sql – Catalog script for Workload Capture and Replay — this script then rebuilds all the capture and replay related tables.


@@?/rdbms/admin/catnowrr.sql
@@?/rdbms/admin/catwrr.sql
exec prvt_report_registry.register_clients(TRUE); --- This one registers clients 

Note: In that case you might loss all of your previous capture ID details from the system as it simply washes or wipes everything there related with RAT tables. Hence this is kind of a crude and a raw method to fix this issue. And I recommend to always connect with Oracle Support before going to run these scripts on your database!

2. I tried of another approach to avoid this error and generate the RAT capture report from the target instead of Source where we were getting the error.
Is that possible ?? — Yes, you can. After further analysis I found the issue is with the 10gR2 capture reporting code which sometimes throws this error.

So, the second way turned ut to be a better approach here as we have all of our previous stats and data untouched and nothing has been wiped out in this case, as we simply ran the reporting procedure from the target (12c R2 in our case) and that’s how avoided the issue.


Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: | Leave a Comment »

Collecting Exadata Cell Performance Statistics using ‘Cellperfdiag.sql’

Posted by FatDBA on July 22, 2018

Hi Guys,

Last week we finished migration for one of the customer who recently purchased the Oracle Exadata Machines (X6-2), after that complex data movement is successfully completed we observed some great performance improvements without actually making any changes and exadata features in action.

But its not that the Exa machines are ‘In-frangible’ Or Unbreakable. There are many of the times when you actually have to understand the Cell/Computer Nodes or system wide performance and output from tools like Sundiag, Exawatcher, Exachk, TFA, SOS report etc. were not sufficient.

For the purpose of ‘Cell Performance DIAG info’ Oracle has provided a script to collect Exadata Cell Performance statistics which you can use to interpret the issues.

Output of the script is distributed into several sections
1. Cell specific parameters
2. Top CELL waits with wait times (Worst Times)
3. Cell Configuration details

Script to Collect Exadata Cell Performance Information (cellperfdiag.sql) (Doc ID 2038493.1)
Below is the output from one of the Cell Node of a lower environment.


CELLPERFDIAG DATA FOR DixitLab_July19_0257

IMPORTANT PARAMETERS RELATING TO CELL PERFORMANCE:

INST_ID NAME                                     VALUE
------- ---------------------------------------- ----------------------------------------
      1 cell_offload_compaction                  ADAPTIVE
      1 cell_offload_decryption                  TRUE
      1 cell_offload_plan_display                PDTO
      1 cell_offload_processing                  TRUE
      2 cell_offload_compaction                  ADAPTIVE
      2 cell_offload_decryption                  TRUE
      2 cell_offload_plan_display                PDTO
      2 cell_offload_processing                  TRUE

TOP 20 CURRENT CELL WAITS

This is to look at current cell waits, July not return any data.

ASH CELL PERFORMANCE SUMMARY

This query will look at the average cell wait times for each cell in ASH

CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------------------------ --------------- --------------
192.199.66.19;192.199.66.20       1185605081.0        96124.9
192.199.66.21;192.199.66.22       1148823479.0        88371.0
192.199.66.17;192.199.66.18       1048776677.0        82115.3
192.199.66.23;192.199.66.24        927836650.0        76604.7

20 WORST CELL PERFORMANCE MINUTES IN ASH:

APPROACH: These are the minutes where the avg cell perf time
was the highest.  See which cell had the longest waits and
during what minute.

MINUTE       CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------ ------------------------------ --------------- --------------
July18_1308   192.199.66.21;192.199.66.22         28445866.0       536714.5
July18_1158   192.199.66.19;192.199.66.20         25685919.0       524202.4
July18_1218   192.199.66.23;192.199.66.24           496960.0       496960.0
July18_2008   192.199.66.21;192.199.66.22         21481465.0       488215.1
July18_1618   192.199.66.19;192.199.66.20         56796413.0       485439.4
July18_1638   192.199.66.17;192.199.66.18         23823342.0       467124.4
July18_2004   192.199.66.21;192.199.66.22         28935643.0       466703.9
July18_1207   192.199.66.17;192.199.66.18          1779234.0       444808.5
July19_0042   192.199.66.19;192.199.66.20           888334.0       444167.0
July18_1654   192.199.66.23;192.199.66.24          2619836.0       436639.3
July18_2008   192.199.66.19;192.199.66.20         11634865.0       430920.9
July18_1324   192.199.66.19;192.199.66.20         66537869.0       423808.1
July18_1157   192.199.66.21;192.199.66.22         22725628.0       420845.0
July19_0028   192.199.66.21;192.199.66.22           841081.0       420540.5
July18_1323   192.199.66.19;192.199.66.20         76790471.0       419620.1
July18_1157   192.199.66.19;192.199.66.20         28103203.0       401474.3
July18_1159   192.199.66.17;192.199.66.18         14050389.0       401439.7
July18_1158   192.199.66.23;192.199.66.24         10054891.0       386726.6
July18_1639   192.199.66.17;192.199.66.18         79265611.0       386661.5
July18_2009   192.199.66.21;192.199.66.22         20335679.0       383692.1

50 LONGEST CELL WAITS IN ASH ORDERED BY WAIT TIME

APPROACH: These are the top 50 individual cell waits in ASH
in wait time order.

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
19/JULY/18 08:04:19.964 PM 192.199.66.21;192.199.66.22          2 cell smart incremental backup            2990700133          0          0     5560085
19/JULY/18 08:04:18.964 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4112515153      32768     3333391
19/JULY/18 04:38:52.666 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2653079
19/JULY/18 04:38:51.656 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2486538
19/JULY/18 01:23:19.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2461036
19/JULY/18 04:38:51.656 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2447901
19/JULY/18 11:59:13.149 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2443358
19/JULY/18 11:57:14.811 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3718979034      32768     2433342
19/JULY/18 02:53:14.279 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440   24718082      32768     2431871
19/JULY/18 01:24:36.080 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2430882
19/JULY/18 01:36:57.174 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3409456992      32768     2429672
19/JULY/18 01:36:58.527 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2427505
19/JULY/18 01:36:56.527 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     2427165
19/JULY/18 11:59:13.149 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2426099
19/JULY/18 01:24:40.011 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131  931391314      32768     2417661
19/JULY/18 01:24:40.090 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2416163
19/JULY/18 01:24:47.100 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2396291
19/JULY/18 08:04:36.984 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     2388370
19/JULY/18 01:23:36.971 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2370848
19/JULY/18 04:48:26.040 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213  842883291      32768     2359311
19/JULY/18 03:35:36.076 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2367494631      32768     2344972
19/JULY/18 01:36:56.527 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2330945
19/JULY/18 11:57:52.881 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2310010
19/JULY/18 04:38:55.395 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2304803
19/JULY/18 01:37:01.537 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2284322
19/JULY/18 01:23:19.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2275197
19/JULY/18 01:36:58.527 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2250389
19/JULY/18 11:58:11.911 AM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2256514768      32768     2176213
19/JULY/18 01:23:46.981 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2149317
19/JULY/18 01:24:54.380 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 1783777120      32768     2138193
19/JULY/18 01:24:14.051 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     2107128
19/JULY/18 08:07:04.178 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1233700156      32768     2102692
19/JULY/18 08:04:28.984 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     2098081
19/JULY/18 02:43:04.647 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2367494631      32768     2090428
19/JULY/18 01:23:47.921 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2077418
19/JULY/18 01:24:48.100 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2056411
19/JULY/18 01:24:48.021 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131  931391314      32768     2043911
19/JULY/18 11:57:57.891 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2033684
19/JULY/18 08:03:38.870 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 2251483371      32768     2032547
19/JULY/18 01:37:01.174 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2031799
19/JULY/18 08:03:39.874 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  474454945      32768     2024898
19/JULY/18 11:58:31.931 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  474454945      32768     2013268
19/JULY/18 03:53:00.757 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2701596410      32768     2004020
19/JULY/18 04:39:03.405 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     1997819
19/JULY/18 04:18:40.331 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     1994866
19/JULY/18 01:23:34.861 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1467922874      32768     1988301
19/JULY/18 04:38:57.666 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440   24718082      32768     1985840
19/JULY/18 01:23:17.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     1983376
19/JULY/18 01:08:56.066 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     1965134
19/JULY/18 04:39:39.763 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     1956491

100 LONGEST CELL WAITS IN ASH ORDERED BY SAMPLE TIME

APPROACH: These are the top 50 individual cell waits in ASH
in sample time order.

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
19/JULY/18 11:57:14.811 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3718979034      32768     2433342
19/JULY/18 11:57:27.821 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  552535141      32768     1627154
19/JULY/18 11:57:49.881 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     1871961
19/JULY/18 11:57:52.881 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     1634342
19/JULY/18 11:57:52.881 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2310010
19/JULY/18 11:57:57.891 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2033684
19/JULY/18 11:58:11.911 AM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2256514768      32768     2176213
19/JULY/18 11:58:31.931 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  474454945      32768     2013268
19/JULY/18 11:58:56.001 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1433462179      32768     1665222
19/JULY/18 11:59:11.149 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 3958490174      32768     1796201
19/JULY/18 11:59:13.149 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2426099
19/JULY/18 11:59:13.149 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2443358
19/JULY/18 12:02:25.487 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 1783777120      32768     1667222
19/JULY/18 12:36:22.441 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 2891233531      32768     1817473
19/JULY/18 01:08:11.966 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     1824501
19/JULY/18 01:08:53.056 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  758773684      32768     1633915
19/JULY/18 01:08:56.066 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     1965134
19/JULY/18 01:22:18.849 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     1692084
19/JULY/18 01:22:35.760 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1404181515      32768     1927461
19/JULY/18 01:22:35.760 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1404181515      32768     1892900
19/JULY/18 01:22:35.871 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     1817924
19/JULY/18 01:23:08.830 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     1798727
19/JULY/18 01:23:17.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     1983376
19/JULY/18 01:23:19.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2461036
19/JULY/18 01:23:19.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2275197
19/JULY/18 01:23:19.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1340519488      32768     1600315
19/JULY/18 01:23:21.951 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     1806676
19/JULY/18 01:23:32.971 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     1916487
19/JULY/18 01:23:34.861 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1467922874      32768     1988301
19/JULY/18 01:23:36.971 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2370848
19/JULY/18 01:23:46.981 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2149317
19/JULY/18 01:23:47.921 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2077418
19/JULY/18 01:24:09.051 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     1716730
19/JULY/18 01:24:14.051 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     2107128
19/JULY/18 01:24:36.080 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2430882
19/JULY/18 01:24:40.011 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131  931391314      32768     2417661
19/JULY/18 01:24:40.090 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2416163
19/JULY/18 01:24:47.100 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2396291
19/JULY/18 01:24:48.021 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131  931391314      32768     2043911
19/JULY/18 01:24:48.100 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2056411
19/JULY/18 01:24:54.380 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 1783777120      32768     2138193
19/JULY/18 01:35:40.244 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2381222600      32768     1670138
19/JULY/18 01:36:56.527 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2330945
19/JULY/18 01:36:56.527 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     2427165
19/JULY/18 01:36:57.174 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3409456992      32768     1872516
19/JULY/18 01:36:57.174 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3409456992      32768     2429672
19/JULY/18 01:36:58.527 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2250389
19/JULY/18 01:36:58.527 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2427505
19/JULY/18 01:37:01.174 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2031799
19/JULY/18 01:37:01.174 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     1782826
19/JULY/18 01:37:01.537 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3530077886      32768     2284322
19/JULY/18 01:37:04.587 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     1609551
19/JULY/18 02:43:01.717 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     1705580
19/JULY/18 02:43:04.647 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2367494631      32768     2090428
19/JULY/18 02:43:04.757 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     1667922
19/JULY/18 02:43:11.657 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  552535141      32768     1700677
19/JULY/18 02:53:14.279 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440   24718082      32768     2431871

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
19/JULY/18 03:09:17.091 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440   24718082      32768     1718584
19/JULY/18 03:09:22.101 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1404181515      32768     1924659
19/JULY/18 03:09:23.820 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     1609377
19/JULY/18 03:35:36.076 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2367494631      32768     2344972
19/JULY/18 03:42:53.196 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     1705247
19/JULY/18 03:43:09.256 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1340519488      32768     1621901
19/JULY/18 03:53:00.757 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2701596410      32768     2004020
19/JULY/18 03:53:03.356 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2701596410      32768     1951623
19/JULY/18 03:53:04.767 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2701596410      32768     1911016
19/JULY/18 04:02:42.355 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 1122005524      32768     1600296
19/JULY/18 04:18:40.331 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     1994866
19/JULY/18 04:18:41.331 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     1680223
19/JULY/18 04:18:47.341 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     1676801
19/JULY/18 04:38:51.656 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2447901
19/JULY/18 04:38:51.656 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2486538
19/JULY/18 04:38:52.666 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2653079
19/JULY/18 04:38:55.395 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2304803
19/JULY/18 04:38:57.666 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440   24718082      32768     1985840
19/JULY/18 04:39:03.405 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     1997819
19/JULY/18 04:39:26.743 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 1433462179      32768     1686065
19/JULY/18 04:39:39.455 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     1770436
19/JULY/18 04:39:39.763 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     1956491
19/JULY/18 04:39:40.763 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1122005524      32768     1719451
19/JULY/18 04:39:45.763 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131   91591739      32768     1669777
19/JULY/18 04:39:53.783 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     1606474
19/JULY/18 04:47:20.930 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 1467922874      32768     1598316
19/JULY/18 04:47:21.101 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1467922874      32768     1723089
19/JULY/18 04:48:26.040 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213  842883291      32768     2359311
19/JULY/18 05:17:36.317 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  125724152      32768     1743703
19/JULY/18 05:23:11.293 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  552535141      32768     1631948
19/JULY/18 05:23:19.727 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     1741381
19/JULY/18 06:23:57.158 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1374369774      32768     1769798
19/JULY/18 08:03:38.870 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2701596410      32768     1622627
19/JULY/18 08:03:38.870 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 2251483371      32768     2032547
19/JULY/18 08:03:39.874 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  474454945      32768     2024898
19/JULY/18 08:03:39.874 PM 192.199.66.23;192.199.66.24          2 cell smart incremental backup            3802949213          0          0     1775912
19/JULY/18 08:04:18.964 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4112515153      32768     3333391
19/JULY/18 08:04:19.964 PM 192.199.66.21;192.199.66.22          2 cell smart incremental backup            2990700133          0          0     5560085
19/JULY/18 08:04:28.984 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     2098081
19/JULY/18 08:04:36.984 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     2388370
19/JULY/18 08:07:04.178 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1233700156      32768     2102692
19/JULY/18 08:09:11.705 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  870191132      32768     1753415
19/JULY/18 10:13:12.946 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     1777250

ASH HISTORY CELL PERFORMANCE SUMMARY

This query will look at the average cell wait times for each cell in ASH

CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------------------------ --------------- --------------
192.199.66.19;192.199.66.20       4320370317.0       144803.9
192.199.66.17;192.199.66.18       3720276327.0       126342.3
192.199.66.21;192.199.66.22       3302364789.0       116198.6
192.199.66.23;192.199.66.24       2214253857.0        86457.1

20 WORST CELL PERFORMANCE MINUTES IN ASH HISTORY:

APPROACH: These are the minutes where the avg cell perf time
was the highest.  See which cell had the longest waits and
during what time minute.

MINUTE       CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------ ------------------------------ --------------- --------------
July17_0458   192.199.66.21;192.199.66.22          2403872.0      2403872.0
July17_0454   192.199.66.23;192.199.66.24          2150347.0      2150347.0
July09_1416   192.199.66.17;192.199.66.18          1887469.0      1887469.0
July08_2209   192.199.66.21;192.199.66.22          1693688.0      1693688.0
July17_0200   192.199.66.23;192.199.66.24          1597102.0      1597102.0
July17_1655   192.199.66.19;192.199.66.20          1567615.0      1567615.0
July11_0400   192.199.66.19;192.199.66.20          1373801.0      1373801.0
July16_1343   192.199.66.17;192.199.66.18          1356319.0      1356319.0
July07_2001   192.199.66.21;192.199.66.22          6774340.0      1354868.0
July10_2006   192.199.66.23;192.199.66.24         10787174.0      1348396.8
July04_1431   192.199.66.19;192.199.66.20          5180228.0      1295057.0
July17_0454   192.199.66.19;192.199.66.20          2579203.0      1289601.5
July09_1901   192.199.66.19;192.199.66.20          1260900.0      1260900.0
July11_0400   192.199.66.23;192.199.66.24          1255291.0      1255291.0
July08_1437   192.199.66.19;192.199.66.20          2439786.0      1219893.0
July17_0457   192.199.66.23;192.199.66.24          1177614.0      1177614.0
July04_0906   192.199.66.23;192.199.66.24          1153969.0      1153969.0
July18_0123   192.199.66.17;192.199.66.18          1153542.0      1153542.0
July17_1957   192.199.66.21;192.199.66.22          1112349.0      1112349.0
July11_0958   192.199.66.23;192.199.66.24          4421561.0      1105390.3

50 LONGEST CELL WAITS IN ASH HISTORY ORDERED BY WAIT TIME

APPROACH: These are the top 50 individual cell waits in ASH
history in wait time order.

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
07/July/18 08:01:45.575 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 2891233531      32768     4381405
16/July/18 02:55:09.946 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 2279513079      32768     3489839
19/JULY/18 08:24:30.057 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     3027146
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2994219
14/July/18 11:07:27.489 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2583166
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2572340
09/July/18 10:55:35.129 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2524157
09/July/18 10:30:37.201 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2511210
09/July/18 07:04:57.612 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     2508541
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2504764
15/July/18 02:23:59.797 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     2498919
09/July/18 07:15:15.200 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1122005524      32768     2496073
16/July/18 10:01:28.060 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2481597
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2481323
09/July/18 11:37:22.166 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2478192
16/July/18 12:18:57.851 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 2984361190      32768     2476915
16/July/18 03:00:25.122 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2474114
16/July/18 07:54:01.716 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  758773684      32768     2473662
16/July/18 08:15:35.001 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2473365
15/July/18 01:42:56.817 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2256514768      32768     2472463
08/July/18 05:38:29.082 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 3704102872      32768     2466691
17/July/18 02:28:27.460 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2464447
09/July/18 11:07:27.164 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 1122005524      32768     2463370
17/July/18 02:43:03.640 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2460091
16/July/18 03:00:45.182 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2459368
16/July/18 07:37:59.702 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2457007
14/July/18 11:07:25.301 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4016617326      32768     2453596
17/July/18 02:40:02.464 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131   91591739      32768     2449781
09/July/18 02:20:37.918 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2449762
11/July/18 04:09:24.827 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3409456992      32768     2449147
14/July/18 09:45:22.608 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     2447554
16/July/18 10:52:07.321 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 3958490174      32768     2444207
14/July/18 04:49:22.691 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2441217
04/July/18 02:31:23.455 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2441112
14/July/18 10:03:34.449 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     2440465
04/July/18 03:07:31.342 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2438825
14/July/18 03:32:54.840 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2437829
17/July/18 02:38:52.304 AM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2263141931      32768     2437640
16/July/18 02:29:45.206 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2437155
16/July/18 08:15:35.001 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2367494631      32768     2436056
16/July/18 10:38:49.813 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2435774
09/July/18 08:12:07.171 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  185394440      32768     2435748
15/July/18 02:44:02.345 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1233700156      32768     2435230
16/July/18 07:38:55.929 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440   24718082      32768     2431597
16/July/18 10:39:39.873 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2430666
10/July/18 03:01:42.766 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2430197
15/July/18 06:11:20.764 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2429871
16/July/18 10:16:11.295 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1019816052      32768     2429717
08/July/18 05:38:19.072 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2263141931      32768     2429692
16/July/18 10:01:38.100 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2428275

100 LONGEST CELL WAITS IN ASH HISTORY ORDERED BY SAMPLE TIME

APPROACH: These are the top 100 individual cell waits in ASH
history in sample time order.

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
04/July/18 02:30:43.598 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1374369774      32768     2424167
04/July/18 03:07:31.342 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2438825
04/July/18 10:29:07.175 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2417864
04/July/18 10:29:47.246 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 1233700156      32768     2339031
04/July/18 02:31:23.455 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2441112
07/July/18 08:01:45.575 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 2891233531      32768     4381405
08/July/18 01:36:44.865 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2420689
08/July/18 01:36:44.865 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2330502
08/July/18 01:36:53.062 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 1783777120      32768     2420230
08/July/18 04:23:25.390 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     2410974
08/July/18 04:34:13.379 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2256514768      32768     2414668
08/July/18 05:17:46.103 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 1783777120      32768     2319802
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2481323
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2504764
08/July/18 05:38:19.072 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2263141931      32768     2429692
08/July/18 05:38:29.082 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 3704102872      32768     2466691
08/July/18 05:38:29.082 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 3616317741      32768     2325172
09/July/18 10:13:18.007 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2418070
09/July/18 10:13:18.007 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1404181515      32768     2293017
09/July/18 10:13:23.693 AM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2263141931      32768     2355873
09/July/18 10:13:28.017 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  931391314      32768     2359674
09/July/18 10:30:37.201 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2511210
09/July/18 10:55:35.129 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2524157
09/July/18 10:55:35.129 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2383155
09/July/18 11:07:27.164 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 1122005524      32768     2463370
09/July/18 11:37:22.166 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2478192
09/July/18 01:57:19.921 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1467922874      32768     2337878
09/July/18 02:20:37.918 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2449762
09/July/18 06:51:04.415 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 3266942716      32768     2303697
09/July/18 07:04:57.612 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     2508541
09/July/18 07:15:15.200 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1122005524      32768     2496073
09/July/18 08:12:07.171 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  185394440      32768     2435748
10/July/18 03:01:42.766 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2430197
10/July/18 01:38:20.572 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1122206058      32768     2396284
10/July/18 08:06:28.912 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 3616317741      32768     2425901
10/July/18 08:06:37.777 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 3704102872      32768     2423546
11/July/18 04:09:24.827 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 3409456992      32768     2449147
14/July/18 09:45:22.608 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1374369774      32768     2447554
14/July/18 10:03:34.449 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     2440465
14/July/18 10:03:54.460 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     2401321
14/July/18 11:07:25.301 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4016617326      32768     2453596
14/July/18 11:07:27.489 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2583166
14/July/18 01:03:23.248 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3958490174      32768     2314095
14/July/18 03:32:54.840 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2437829
14/July/18 04:49:22.691 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2441217
15/July/18 02:23:59.797 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     2498919
15/July/18 02:28:01.245 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 2984361190      32768     2418245
15/July/18 01:42:26.787 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2263141931      32768     2418862
15/July/18 01:42:56.817 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2256514768      32768     2472463
15/July/18 02:44:02.345 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1233700156      32768     2435230
15/July/18 05:23:22.759 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1404181515      32768     2424663
15/July/18 06:11:20.764 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2429871
15/July/18 06:12:20.859 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1340519488      32768     2299402
16/July/18 02:29:45.206 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2437155
16/July/18 02:31:14.605 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  744236512      32768     2425509
16/July/18 02:35:59.882 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3958490174      32768     2424458
16/July/18 02:55:09.946 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 2279513079      32768     2417036

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
16/July/18 02:55:09.946 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 2279513079      32768     3489839
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2994219
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2572340
16/July/18 03:00:25.122 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2474114
16/July/18 03:00:45.182 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2459368
16/July/18 03:01:07.779 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  870191132      32768     2338236
16/July/18 09:36:02.169 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1122005524      32768     2395522
16/July/18 10:01:28.060 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2481597
16/July/18 10:01:38.100 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2428275
16/July/18 10:16:11.295 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1019816052      32768     2429717
16/July/18 10:38:49.813 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 2342661109      32768     2435774
16/July/18 10:39:39.873 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2430666
16/July/18 10:39:39.873 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2414021
16/July/18 10:52:07.321 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 3958490174      32768     2444207
16/July/18 11:47:58.536 AM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2381222600      32768     2422526
16/July/18 12:18:57.851 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 2984361190      32768     2476915
16/July/18 05:01:01.612 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440   24718082      32768     2351090
16/July/18 06:55:13.320 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2375834
16/July/18 06:55:13.320 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2414035
16/July/18 06:56:23.443 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3718979034      32768     2322248
16/July/18 07:37:59.702 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2457007
16/July/18 07:37:59.702 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2417497
16/July/18 07:38:55.929 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440   24718082      32768     2431597
16/July/18 07:54:01.716 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  758773684      32768     2473662
16/July/18 08:15:35.001 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2419493
16/July/18 08:15:35.001 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2367494631      32768     2436056
16/July/18 08:15:35.001 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131  280877291      32768     2473365
17/July/18 02:03:05.401 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133  744236512      32768     2425350
17/July/18 02:38:22.270 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2344538
17/July/18 02:38:49.166 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     2423420
17/July/18 02:38:49.166 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3256856773      32768     2419184
17/July/18 02:38:52.304 AM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213 2263141931      32768     2437640
17/July/18 02:38:59.176 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1433462179      32768     2415119
17/July/18 02:40:02.464 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131   91591739      32768     2449781
17/July/18 02:43:03.640 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2460091
17/July/18 04:58:17.697 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4112515153      32768     2403872
17/July/18 02:24:05.496 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2381222600      32768     2420104
17/July/18 02:28:27.460 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2464447
17/July/18 04:39:42.871 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131   91591739      32768     2297049
19/JULY/18 08:24:30.057 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     3027146
19/JULY/18 09:55:04.470 AM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1340519488      32768     2363513
19/JULY/18 04:38:55.395 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1317632796      32768     2304803
19/JULY/18 08:04:36.984 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     2388370

AWR CELL DISK UTILIZATION

APPROACH: This query only works in 12.1 and above.  This is looking
in the AWR history tables to look at cell disk utilization for some
of the worst periods.  Top 100 disk utils.
DISK_UTILIZATION_SUM: Sum of the per-minute disk utilization metrics.
IO_REQUESTS_SUM: Sum of the per-minute IOPs.
IO_MB_SUM: Sum of the per-minute I/O metrics, in megabytes per second.

   SNAP_ID BEGIN               END                 CELL_NAME                      DISK_NAME                      DISK_UTILIZATION_SUM IO_REQUESTS_SUM  IO_MB_SUM
---------- ------------------- ------------------- ------------------------------ ------------------------------ -------------------- --------------- ----------
     22669 July13_1300          July13_1330          192.199.66.17;192.199.66.18    /dev/sda                                       1977           11302       1938
     22669 July13_1300          July13_1330          192.199.66.17;192.199.66.18    /dev/sdb                                       1977           11302       1938
     22669 July13_1300          July13_1330          192.199.66.17;192.199.66.18    /dev/sdm                                       1977           11302       1938
     22669 July13_1300          July13_1330          192.199.66.19;192.199.66.20    /dev/sda                                       1926           11343       1979
     22669 July13_1300          July13_1330          192.199.66.19;192.199.66.20    /dev/sdb                                       1926           11343       1979
     22669 July13_1300          July13_1330          192.199.66.19;192.199.66.20    /dev/sdm                                       1926           11343       1979
     22669 July13_1300          July13_1330          192.199.66.21;192.199.66.22    /dev/sda                                       1966           11227       1945
     22669 July13_1300          July13_1330          192.199.66.21;192.199.66.22    /dev/sdb                                       1966           11227       1945
     22669 July13_1300          July13_1330          192.199.66.21;192.199.66.22    /dev/sdm                                       1966           11227       1945
     22669 July13_1300          July13_1330          192.199.66.23;192.199.66.24    /dev/sda                                       1888           11353       1920
     22669 July13_1300          July13_1330          192.199.66.23;192.199.66.24    /dev/sdb                                       1888           11353       1920
     22669 July13_1300          July13_1330          192.199.66.23;192.199.66.24    /dev/sdm                                       1888           11353       1920
     22827 July16_2000          July16_2030          192.199.66.17;192.199.66.18    /dev/sda                                       2068           11683       1428
     22827 July16_2000          July16_2030          192.199.66.17;192.199.66.18    /dev/sdb                                       2068           11683       1428
     22827 July16_2000          July16_2030          192.199.66.17;192.199.66.18    /dev/sdm                                       2068           11683       1428
     22827 July16_2000          July16_2030          192.199.66.19;192.199.66.20    /dev/sda                                       2089           11588       1443
     22827 July16_2000          July16_2030          192.199.66.19;192.199.66.20    /dev/sdb                                       2089           11588       1443
     22827 July16_2000          July16_2030          192.199.66.19;192.199.66.20    /dev/sdm                                       2089           11588       1443
     22827 July16_2000          July16_2030          192.199.66.21;192.199.66.22    /dev/sda                                       1975           11095       1419
     22827 July16_2000          July16_2030          192.199.66.21;192.199.66.22    /dev/sdb                                       1975           11095       1419
     22827 July16_2000          July16_2030          192.199.66.21;192.199.66.22    /dev/sdm                                       1975           11095       1419
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    /dev/sda                                       3808            9676       3421
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    /dev/sdb                                       3808            9676       3421
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    /dev/sdm                                       3808            9676       3421
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_00_monkeynode11_adm                        1929            4811       1713
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_01_monkeynode11_adm                        1879            4623       1703
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_02_monkeynode11_adm                        1793            5186       1674
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_03_monkeynode11_adm                        1808            4887       1633
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_04_monkeynode11_adm                        1923            4612       1719
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_05_monkeynode11_adm                        1898            4812       1750
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_06_monkeynode11_adm                        1887            4748       1677
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_07_monkeynode11_adm                        1905            4917       1720
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_08_monkeynode11_adm                        1792            4997       1687
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_09_monkeynode11_adm                        1623            4926       1501
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_10_monkeynode11_adm                        1921            4466       1739
     22875 July17_2000          July17_2030          192.199.66.17;192.199.66.18    CD_11_monkeynode11_adm                        1949            4469       1723
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    /dev/sda                                       4075           11529       5240
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    /dev/sdb                                       4075           11529       5240
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    /dev/sdm                                       4075           11529       5240
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_00_monkeynode12_adm                        2033            5673       2614
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_01_monkeynode12_adm                        2040            5626       2622
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_02_monkeynode12_adm                        1942            5636       2452
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_03_monkeynode12_adm                        1897            5623       2351
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_04_monkeynode12_adm                        2051            5586       2591
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_05_monkeynode12_adm                        1862            5838       2297
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_06_monkeynode12_adm                        2058            5355       2581
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_07_monkeynode12_adm                        2063            5668       2592
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_08_monkeynode12_adm                        2076            5506       2676
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_09_monkeynode12_adm                        2066            5829       2619
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_10_monkeynode12_adm                        2046            5322       2562
     22875 July17_2000          July17_2030          192.199.66.19;192.199.66.20    CD_11_monkeynode12_adm                        1972            5429       2487
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    /dev/sda                                       3710            9738       3413
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    /dev/sdb                                       3710            9738       3413
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    /dev/sdm                                       3710            9738       3413
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_00_monkeynode13_adm                        1874            4751       1752
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_01_monkeynode13_adm                        1835            4755       1661
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_02_monkeynode13_adm                        1772            5021       1623

   SNAP_ID BEGIN               END                 CELL_NAME                      DISK_NAME                      DISK_UTILIZATION_SUM IO_REQUESTS_SUM  IO_MB_SUM
---------- ------------------- ------------------- ------------------------------ ------------------------------ -------------------- --------------- ----------
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_03_monkeynode13_adm                        1801            4860       1627
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_04_monkeynode13_adm                        1729            4834       1603
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_05_monkeynode13_adm                        2094            6325       2698
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_06_monkeynode13_adm                        1804            4935       1633
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_07_monkeynode13_adm                        1943            4356       1757
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_08_monkeynode13_adm                        1870            4385       1673
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_09_monkeynode13_adm                        1690            4848       1525
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_10_monkeynode13_adm                        1908            5668       1679
     22875 July17_2000          July17_2030          192.199.66.21;192.199.66.22    CD_11_monkeynode13_adm                        1858            4699       1681
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    /dev/sda                                       3677            9714       3381
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    /dev/sdb                                       3677            9714       3381
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    /dev/sdm                                       3677            9714       3381
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_00_monkeynode14_adm                        1853            4752       1685
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_01_monkeynode14_adm                        1822            4750       1693
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_02_monkeynode14_adm                        1874            5117       1725
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_03_monkeynode14_adm                        1813            4593       1665
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_04_monkeynode14_adm                        1810            4736       1637
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_05_monkeynode14_adm                        1869            4575       1717
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_06_monkeynode14_adm                        1789            4769       1669
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_07_monkeynode14_adm                        1840            4634       1734
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_08_monkeynode14_adm                        1838            4480       1666
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_09_monkeynode14_adm                        1753            4656       1641
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_10_monkeynode14_adm                        1729            5019       1574
     22875 July17_2000          July17_2030          192.199.66.23;192.199.66.24    CD_11_monkeynode14_adm                        1849            4579       1710
     22876 July17_2030          July17_2100          192.199.66.17;192.199.66.18    /dev/sda                                       2658           10231       2347
     22876 July17_2030          July17_2100          192.199.66.17;192.199.66.18    /dev/sdb                                       2658           10231       2347
     22876 July17_2030          July17_2100          192.199.66.17;192.199.66.18    /dev/sdm                                       2658           10231       2347
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    /dev/sda                                       3156           12112       4142
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    /dev/sdb                                       3156           12112       4142
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    /dev/sdm                                       3156           12112       4142
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_01_monkeynode12_adm                        1602            5917       2090
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_04_monkeynode12_adm                        1633            5773       2100
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_07_monkeynode12_adm                        1609            5877       2073
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_08_monkeynode12_adm                        1632            5778       2088
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_09_monkeynode12_adm                        1602            6016       2066
     22876 July17_2030          July17_2100          192.199.66.19;192.199.66.20    CD_10_monkeynode12_adm                        1594            5964       2040
     22876 July17_2030          July17_2100          192.199.66.21;192.199.66.22    /dev/sda                                       2449           10793       2281
     22876 July17_2030          July17_2100          192.199.66.21;192.199.66.22    /dev/sdb                                       2449           10793       2281
     22876 July17_2030          July17_2100          192.199.66.21;192.199.66.22    /dev/sdm                                       2449           10793       2281
     22876 July17_2030          July17_2100          192.199.66.21;192.199.66.22    CD_05_monkeynode13_adm                        1569            6433       2025
     22876 July17_2030          July17_2100          192.199.66.23;192.199.66.24    /dev/sda                                       2657           10505       2409
     22876 July17_2030          July17_2100          192.199.66.23;192.199.66.24    /dev/sdb                                       2657           10505       2409
     22876 July17_2030          July17_2100          192.199.66.23;192.199.66.24    /dev/sdm                                       2657           10505       2409

DB_ID_FOR_CURRENT_DB
--------------------
          2490671309

CELL THREAD HISTORY - LAST FEW MINUTES

This query only works in 12.1 and above.

  COUNT(*) SQL_ID        CELL_NAME                      JOB_TYPE                         DATABASE_ID INSTANCE_ID
---------- ------------- ------------------------------ -------------------------------- ----------- -----------
      1598               192.199.66.19;192.199.66.20    UnidentifiedJob                            0           0
      1555               192.199.66.23;192.199.66.24    UnidentifiedJob                            0           0
      1237               192.199.66.17;192.199.66.18    UnidentifiedJob                            0           0
      1148               192.199.66.21;192.199.66.22    UnidentifiedJob                            0           0
       834               192.199.66.21;192.199.66.22    NetworkPoll                                0           0
       824               192.199.66.17;192.199.66.18    NetworkPoll                                0           0
       804               192.199.66.19;192.199.66.20    NetworkPoll                                0           0
       804               192.199.66.23;192.199.66.24    NetworkPoll                                0           0
       417               192.199.66.21;192.199.66.22    OCL System Message Thread                  0           0
       417               192.199.66.21;192.199.66.22    Storage index eviction                     0           0
       412               192.199.66.17;192.199.66.18    OCL System Message Thread                  0           0
       412               192.199.66.17;192.199.66.18    Storage index eviction                     0           0
       402               192.199.66.19;192.199.66.20    OCL System Message Thread                  0           0
       402               192.199.66.19;192.199.66.20    Storage index eviction                     0           0
       402               192.199.66.23;192.199.66.24    OCL System Message Thread                  0           0
       402               192.199.66.23;192.199.66.24    Storage index eviction                     0           0
       332               192.199.66.23;192.199.66.24    NetworkRead                                0           0
       281               192.199.66.21;192.199.66.22    NetworkRead                                0           0
       254               192.199.66.17;192.199.66.18    NetworkRead                                0           0
       193 9ujkk29vay3bz 192.199.66.23;192.199.66.24    PredicateDiskRead                  584354840           1
       168 9ujkk29vay3bz 192.199.66.17;192.199.66.18    PredicateDiskRead                  584354840           1
       165 9ujkk29vay3bz 192.199.66.19;192.199.66.20    PredicateDiskRead                  584354840           1
       152 9ujkk29vay3bz 192.199.66.21;192.199.66.22    PredicateDiskRead                  584354840           1
       135               192.199.66.19;192.199.66.20    NetworkRead                                0           0
       112               192.199.66.17;192.199.66.18    CacheGet                                   0           0
        86               192.199.66.19;192.199.66.20    CacheGet                                   0           0
        83               192.199.66.21;192.199.66.22    CacheGet                                   0           0
        66               192.199.66.23;192.199.66.24    CacheGet                                   0           0
        55 aym6pqm5uzd90 192.199.66.17;192.199.66.18    PredicateDiskRead                  584354840           1
        52 aym6pqm5uzd90 192.199.66.21;192.199.66.22    PredicateDiskRead                  584354840           1
        51 aym6pqm5uzd90 192.199.66.23;192.199.66.24    PredicateDiskRead                  584354840           1
        50 oj2tdhpjgnvus 192.199.66.23;192.199.66.24    CacheGet                           584354840           1
        47 aym6pqm5uzd90 192.199.66.19;192.199.66.20    PredicateDiskRead                  584354840           1
        37 oj2tdhpjgnvus 192.199.66.17;192.199.66.18    CacheGet                           584354840           1
        34 oj2tdhpjgnvus 192.199.66.19;192.199.66.20    CacheGet                           584354840           1
        29 oj2tdhpjgnvus 192.199.66.21;192.199.66.22    CacheGet                           584354840           1
        18               192.199.66.17;192.199.66.18    PredicateFilter                            0           0
        17               192.199.66.23;192.199.66.24    PredicateFilter                            0           0
        16               192.199.66.19;192.199.66.20    PredicateFilter                            0           0
        16 221fz5z4guyxu 192.199.66.17;192.199.66.18    PredicateDiskRead                 1377831170           1
        15               192.199.66.21;192.199.66.22    PredicateFilter                            0           0
        15               192.199.66.23;192.199.66.24    CachePut                                   0           0
        14 c10mc2tzkhbwb 192.199.66.23;192.199.66.24    PredicateDiskRead                 1377831170           1
        13               192.199.66.23;192.199.66.24    CachePut                          1377831170           1
        12               192.199.66.21;192.199.66.22    CachePut                                   0           0
        11               192.199.66.17;192.199.66.18    CachePut                                   0           0
        10               192.199.66.19;192.199.66.20    CachePut                                   0           0
        10               192.199.66.19;192.199.66.20    PredicateCacheGet                          0           0
         9               192.199.66.21;192.199.66.22    CachePut                           584354840           1
         9 221fz5z4guyxu 192.199.66.19;192.199.66.20    PredicateDiskRead                 1377831170           1

CELL CONFIG


CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
192.199.66.17;192.199.66.18


  1.0
  1526659672886
  
  
    monkeynode11_adm
    remoteLoginEpdiled
    normal
    OSS_12.2.1.1.6_LINUX.X64_180125.1
    40/40
    7
    text
    8/8
    normal
    WriteThrough
    1614NM70A0
    2
    ib0
    ib1
    0.016666666666666666
    192.199.66.17/22
    192.199.66.18/22
    4.1.12-94.7.8.el6uek.x86_64
    off
    Oracle Corporation ORACLE SERVER X6-2L High Capacity
    126
    7
    mail
    critical,warning,clear
    
    2/2
    normal
    success
    12.2.1.1.6.180125.1
    cell-12.2.1.1.6_LINUX.X64_180125.1-1.x86_64
    27351071
    12.1.2.3.6.170713
    "PRODA Exadata Cell 01"
    monkeynode11-adm
    11.20.238.25
    pdiPD_pdiSTRATUS.droid@pdi.com.PD
    FALSE
     host=11.47.4.195,port=162,community=public,type=asr,asrmPort=16161
    online
    18.0
    normal
    22 days, 1:03
    normal
  


192.199.66.19;192.199.66.20


CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------

  1.0
  1526662533109
  
  
    monkeynode12_adm
    remoteLoginEpdiled
    normal
    OSS_12.2.1.1.6_LINUX.X64_180125.1
    40/40
    7
    text
    8/8
    normal
    WriteThrough
    1614NM705Y
    2
    ib0
    ib1
    0.0
    192.199.66.19/22
    192.199.66.20/22
    4.1.12-94.7.8.el6uek.x86_64
    off
    Oracle Corporation ORACLE SERVER X6-2L High Capacity
    126
    7
    mail
    critical,warning,clear
    
    2/2
    normal
    success
    12.2.1.1.6.180125.1
    cell-12.2.1.1.6_LINUX.X64_180125.1-1.x86_64
    27351071
    12.1.2.3.6.170713
    "PRODA Exadata Cell 02"
    monkeynode12-adm
    11.20.238.25
    pdiPD_pdiSTRATUS.droid@pdi.com.PD
     host=11.47.4.195,port=162,community=public,type=asr,asrmPort=16161
    online
    18.0
    normal
    22 days, 1:03
    normal
  


192.199.66.21;192.199.66.22


  1.0
  1526661149874

CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  
    monkeynode13_adm
    remoteLoginEpdiled
    normal
    OSS_12.2.1.1.6_LINUX.X64_180125.1
    40/40
    7
    text
    8/8
    normal
    WriteThrough
    9717PD519T
    2
    ib0
    ib1
    0.0
    192.199.66.21/22
    192.199.66.22/22
    4.1.12-94.7.8.el6uek.x86_64
    off
    Oracle Corporation ORACLE SERVER X6-2L High Capacity
    126
    7
    mail
    critical,warning,clear
    
    2/2
    normal
    success
    12.2.1.1.6.180125.1
    cell-12.2.1.1.6_LINUX.X64_180125.1-1.x86_64
    27351071
    12.1.2.3.6.170713
    "PRODA Exadata Cell 03"
    monkeynode13-adm
    11.20.238.25
    pdiPD_pdiSTRATUS.droid@pdi.com.PD
     host=11.47.4.195,port=162,community=public,type=asr,asrmPort=16161
    online
    18.0
    normal
    21 days, 23:03
    normal
  


192.199.66.23;192.199.66.24


  1.0
  1526660912516
  
  
    monkeynode14_adm

CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
    remoteLoginEpdiled
    normal
    OSS_12.2.1.1.6_LINUX.X64_180125.1
    40/40
    7
    text
    8/8
    normal
    WriteThrough
    1635NM70Y9
    2
    ib0
    ib1
    0.016666666666666666
    192.199.66.23/22
    192.199.66.24/22
    4.1.12-94.7.8.el6uek.x86_64
    off
    Oracle Corporation ORACLE SERVER X6-2L High Capacity
    126
    7
    mail
    critical,warning,clear
    
    2/2
    normal
    success
    12.2.1.1.6.180125.1
    cell-12.2.1.1.6_LINUX.X64_180125.1-1.x86_64
    27351071
    12.1.2.3.6.170713
    "PRODA Exadata Cell 04"
    monkeynode14-adm
    11.20.238.25
    pdiPD_pdiSTRATUS.droid@pdi.com.PD
     host=11.47.4.195,port=162,community=public,type=asr,asrmPort=16161
    online
    17.0
    normal
    21 days, 22:09
    normal
  



IORM CONFIG


CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
192.199.66.17;192.199.66.18


  1.0
  1526659674006
  
  
    monkeynode11_adm_IORMPLAN
    
    
    basic
    active
  


192.199.66.19;192.199.66.20


  1.0
  1526662534081
  
  
    monkeynode12_adm_IORMPLAN
    
    
    basic
    active
  


192.199.66.21;192.199.66.22


  1.0
  1526661150877
  
  
    monkeynode13_adm_IORMPLAN
    
    
    basic
    active
  


192.199.66.23;192.199.66.24


  1.0
  1526660913655
  
  
    monkeynode14_adm_IORMPLAN
    
    

CELLNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CONFVAL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
    basic
    active
  



TIME
-----------------------
July19 02:57:21
1133011440 1122005524      32768     1719451
19/JULY/18 04:39:45.763 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131   91591739      32768     1669777
19/JULY/18 04:39:53.783 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     1606474
19/JULY/18 04:47:20.930 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 1467922874      32768     1598316
19/JULY/18 04:47:21.101 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 1467922874      32768     1723089
19/JULY/18 04:48:26.040 PM 192.199.66.23;192.199.66.24          2 cell single block physical read          3802949213  842883291      32768     2359311
19/JULY/18 05:17:36.317 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  125724152      32768     1743703
19/JULY/18 05:23:11.293 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  552535141      32768     1631948
19/JULY/18 05:23:19.727 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131   91591739      32768     1741381
19/JULY/18 06:23:57.158 PM 192.199.66.19;192.199.66.20          1 cell single block physical read          1318032131 1374369774      32768     1769798
19/JULY/18 08:03:38.870 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213 2701596410      32768     1622627
19/JULY/18 08:03:38.870 PM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440 2251483371      32768     2032547
19/JULY/18 08:03:39.874 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  474454945      32768     2024898
19/JULY/18 08:03:39.874 PM 192.199.66.23;192.199.66.24          2 cell smart incremental backup            3802949213          0          0     1775912
19/JULY/18 08:04:18.964 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 4112515153      32768     3333391
19/JULY/18 08:04:19.964 PM 192.199.66.21;192.199.66.22          2 cell smart incremental backup            2990700133          0          0     5560085
19/JULY/18 08:04:28.984 PM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 3409456992      32768     2098081
19/JULY/18 08:04:36.984 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     2388370
19/JULY/18 08:07:04.178 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1233700156      32768     2102692
19/JULY/18 08:09:11.705 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133  870191132      32768     1753415
19/JULY/18 10:13:12.946 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3256856773      32768     1777250

ASH HISTORY CELL PERFORMANCE SUMMARY

This query will look at the average cell wait times for each cell in ASH

CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------------------------ --------------- --------------
192.199.66.19;192.199.66.20       4320370317.0       144803.9
192.199.66.17;192.199.66.18       3720276327.0       126342.3
192.199.66.21;192.199.66.22       3302364789.0       116198.6
192.199.66.23;192.199.66.24       2214253857.0        86457.1

20 WORST CELL PERFORMANCE MINUTES IN ASH HISTORY:

APPROACH: These are the minutes where the avg cell perf time
was the highest.  See which cell had the longest waits and
during what time minute.

MINUTE       CELL_PATH                      TOTAL_WAIT_TIME  AVG_WAIT_TIME
------------ ------------------------------ --------------- --------------
July17_0458   192.199.66.21;192.199.66.22          2403872.0      2403872.0
July17_0454   192.199.66.23;192.199.66.24          2150347.0      2150347.0
July09_1416   192.199.66.17;192.199.66.18          1887469.0      1887469.0
July08_2209   192.199.66.21;192.199.66.22          1693688.0      1693688.0
July17_0200   192.199.66.23;192.199.66.24          1597102.0      1597102.0
July17_1655   192.199.66.19;192.199.66.20          1567615.0      1567615.0
July11_0400   192.199.66.19;192.199.66.20          1373801.0      1373801.0
July16_1343   192.199.66.17;192.199.66.18          1356319.0      1356319.0
July07_2001   192.199.66.21;192.199.66.22          6774340.0      1354868.0
July10_2006   192.199.66.23;192.199.66.24         10787174.0      1348396.8
July04_1431   192.199.66.19;192.199.66.20          5180228.0      1295057.0
July17_0454   192.199.66.19;192.199.66.20          2579203.0      1289601.5
July09_1901   192.199.66.19;192.199.66.20          1260900.0      1260900.0
July11_0400   192.199.66.23;192.199.66.24          1255291.0      1255291.0
July08_1437   192.199.66.19;192.199.66.20          2439786.0      1219893.0
July17_0457   192.199.66.23;192.199.66.24          1177614.0      1177614.0
July04_0906   192.199.66.23;192.199.66.24          1153969.0      1153969.0
July18_0123   192.199.66.17;192.199.66.18          1153542.0      1153542.0
July17_1957   192.199.66.21;192.199.66.22          1112349.0      1112349.0
July11_0958   192.199.66.23;192.199.66.24          4421561.0      1105390.3

50 LONGEST CELL WAITS IN ASH HISTORY ORDERED BY WAIT TIME

APPROACH: These are the top 50 individual cell waits in ASH
history in wait time order.

SAMPLE_TIME               CELL_PATH                      INST_ID EVENT                                     CELLHASH#  DISKHASH#      BYTES TIME_WAITED
------------------------- ------------------------------ ------- ---------------------------------------- ---------- ---------- ---------- -----------
07/July/18 08:01:45.575 PM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 2891233531      32768     4381405
16/July/18 02:55:09.946 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 2279513079      32768     3489839
19/JULY/18 08:24:30.057 AM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     3027146
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2994219
14/July/18 11:07:27.489 AM 192.199.66.17;192.199.66.18          1 cell single block physical read          1133011440  185394440      32768     2583166
16/July/18 02:56:30.076 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2572340
09/July/18 10:55:35.129 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3530077886      32768     2524157
09/July/18 10:30:37.201 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1317632796      32768     2511210
09/July/18 07:04:57.612 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 3835758527      32768     2508541
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2504764
15/July/18 02:23:59.797 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3835758527      32768     2498919
09/July/18 07:15:15.200 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 1122005524      32768     2496073
16/July/18 10:01:28.060 AM 192.199.66.21;192.199.66.22          1 cell single block physical read          2990700133 3770061594      32768     2481597
08/July/18 05:17:56.113 PM 192.199.66.23;192.199.66.24          1 cell single block physical read          3802949213  842883291      32768     2481323
09/July/18 11:37:22.166 AM 192.199.66.19;192.199.66.20          2 cell single block physical read          1318032131 1019816052      32768     2478192
16/July/18 12:18:57.851 PM 192.199.66.21;192.199.66.22          2 cell single block physical read          2990700133 2984361190      32768     2476915
16/July/18 03:00:25.122 AM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440 3266942716      32768     2474114
16/July/18 07:54:01.716 PM 192.199.66.17;192.199.66.18          2 cell single block physical read          1133011440  758773684      32768     2473662
16/July/18 08:15:35.001 PM 192.199.66.19;192.199.66.20  

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 2 Comments »

DBMS_XPLAN and its different options/arguments available

Posted by FatDBA on May 10, 2018

Hi Mates,

I am back after a long time to write something that is very vital piece of information when you are about to start any troubleshooting task, yes that is the EXECUTION PLAN, well there are many ways to generate the CBO plans (i.e. AUTOTRACE, extended/debug traces, utlxpls.sql, V$SQL_PLAN, Few of the specialized SQL specific AWR reports like awrsqrpt.sql, STS etc.) but the most common and best way of doing thisng in this subecjt is to use DBMS_XPLAN & its function DISPLAY_CURSOR.

So, yes today’s topic is to understand what all options do we have to generate a more interactive, detailed, elaborative plans.

Okay will start it with very rudimentary (BASIC) styled plan and slowly will use rest of the arguments/options available. The plan includes the operation, options, and the object name (table, index, MV, etc)


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0h79fq6vx4p99',0,'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  TABLE ACCESS FULL| DIXEMP |
------------------------------------

Next is the most common way of generating the plans that’s with the DISPLAY function which allows us to display the execution plan stored in the plan table. First we explain a SQL statement.
Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.


SQL> explain plan for select * from dixemp;
Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3980174453

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DIXEMP |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Next is the ‘ALLSTATS LAST’ option for FORMAT parameter. Lets see how to use it.
ALLSTATS is a shortcut for IOSTATS and MEMSTATS (e.q. BUFFER information) information and the other keyword LAST can be specified to see only the statistics for the last execution.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |     14 |00:00:00.01 |      10 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      1 |     14 |     14 |00:00:00.01 |      10 |
--------------------------------------------------------------------------------------

But if you take a look at the above plan you’ll see that the plan doesn’t contain few of the vital columns or stats like COST and bytes processed (BYTES) as it doesn’t comes by default with that, you actually have to add few more predicates to get that info. That is +cost and +bytes with your FORMAT parameter.

Below is how you can get that missing info from the plan.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0h79fq6vx4p99',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |       |     3 (100)|     14 |00:00:00.01 |      10 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      1 |     14 |   532 |     3   (0)|     14 |00:00:00.01 |      10 |
-----------------------------------------------------------------------------------------------------------

And the ALL parameter will give you the general plan but rest all of the details like Query Block Name / Object Alias, Predicate Information, column projection details.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0h79fq6vx4p99',0,'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DIXEMP |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DIXEMP@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DIXEMP"."EMPNO"[NUMBER,22], "DIXEMP"."ENAME"[VARCHAR2,10],
       "DIXEMP"."JOB"[VARCHAR2,9], "DIXEMP"."MGR"[NUMBER,22],
       "DIXEMP"."HIREDATE"[DATE,7], "DIXEMP"."SAL"[NUMBER,22],
       "DIXEMP"."COMM"[NUMBER,22], "DIXEMP"."DEPTNO"[NUMBER,22]

ALLSTATS is a shortcut for IOSTATS and MEMSTATS (e.q. BUFFER information) with your plan.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('0h79fq6vx4p99',0,'ALLSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      7 |        |     98 |00:00:00.01 |      70 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      7 |     14 |     98 |00:00:00.01 |      70 |
--------------------------------------------------------------------------------------

Now, if you want to view additional details of your plan, for example set of hints to reproduce the statement or OUTLINE, you can use it in your format parameter.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0h79fq6vx4p99',format=>'ALLSTATS LAST +outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |     14 |00:00:00.01 |      10 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      1 |     14 |     14 |00:00:00.01 |      10 |
--------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DIXEMP"@"SEL$1")
      END_OUTLINE_DATA
  */

Adding or removing any data/stats from the plan, that’s possible using + or – signs follwed by argument.
Example: if you want t view cost and bytes information use +cost, +bytes in your plan or if you want to remove the same info in your run of dbms_xplan use -cost, -bytes.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0h79fq6vx4p99',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |       |     3 (100)|     14 |00:00:00.01 |      10 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      1 |     14 |   532 |     3   (0)|     14 |00:00:00.01 |      10 |
-----------------------------------------------------------------------------------------------------------

You can write a mix of both as below


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '0h79fq6vx4p99', cursor_child_no => 0, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  0h79fq6vx4p99, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from dixemp

Plan hash value: 3980174453

--------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      7 |        |     3 (100)|          |     98 |00:00:00.01 |      70 |
|   1 |  TABLE ACCESS FULL| DIXEMP |      7 |     14 |     3   (0)| 00:00:01 |     98 |00:00:00.01 |      70 |
--------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DIXEMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DIXEMP"@"SEL$1")
      END_OUTLINE_DATA
  */

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

Visualize your database performance statistics using Tableau.

Posted by FatDBA on January 28, 2018

Hi Everyone,

Today’s post is all about producing some interactive data visualizations your system/database statistics using one of the most popular BI tool Tableau.
There are lot’s of other tools which can be used for your data analysis i.e. Excel, Microstrategy Analytics, Domo, QlikView but i always find Tableau easiest way to do such things, specially during all my database audit, 360 health reviews and troubleshooting task-forces.

Tableau offers a suite of tools that include an online, desktop and server version. All of these versions provide a easy-to-use drag and drop interface that can help you quickly turn your data into business insights. Like many other data analytics and visualization tools, Tableau can connect to local or remote data of many different formats.

Okay now after that short introduction of the tool, time to do some tests using the tool.
I have divided process in to three step activity and are discussed below.

First: Data Collection
You can collect your AWR reports in TEXT format and which will be later on parsed to create a CSV file.
There are many tools/scripts available online to generate multiple AWR reports of your database. I see an awesome work was already done by FlashDBA (Download). You can use his script to generate batch AWR reports and yes in TEXT format ONLY!

Example:

[oracle@dixitlab AWR]$ ls -ltr
total 12852
-rw-r--r--. 1 oracle oinstall 225031 Jan 27 21:25 awrrpt_1_445_446.txt
-rw-r--r--. 1 oracle oinstall 255010 Jan 27 21:26 awrrpt_1_446_447.txt
-rw-r--r--. 1 oracle oinstall 253217 Jan 27 21:26 awrrpt_1_447_448.txt
-rw-r--r--. 1 oracle oinstall 253217 Jan 27 21:26 awrrpt_1_448_449.txt
-rw-r--r--. 1 oracle oinstall 244229 Jan 27 21:26 awrrpt_1_449_450.txt
........
.................

Second: Data Fold or Compression.
Now next you need a tool to fold your AWR reports in to a CSV. Here once again FlashDBA did a marvelous job, he wrote one fabulous script to parse your AWR text files and generate a final AWR report.
You can download the script from his Github (Download Link)

As far as the script, you need to pass the format of your files and direct output to a CSV as shown below.

[oracle@dixitlab AWR]$ ./awr-parser.sh awr*.txt > tunedbperftests.csv

Info : Parsing file awrrpt_1_445_446.txt at 2018-01-27 21:32:49
Info : Parsing file awrrpt_1_446_447.txt at 2018-01-27 21:32:53
Info : Parsing file awrrpt_1_447_448.txt at 2018-01-27 21:32:56
Info : Parsing file awrrpt_1_448_449.txt at 2018-01-27 21:33:01
Info : Parsing file awrrpt_1_449_450.txt at 2018-01-27 21:33:07
Info : Parsing file awrrpt_1_450_451.txt at 2018-01-27 21:33:15
Info : Parsing file awrrpt_1_451_452.txt at 2018-01-27 21:33:21
....
........
Info : Parsing file awrrpt_1_499_500.txt at 2018-01-27 21:36:56
Info : No more files found
Info :
Info : ______SUMMARY______
Info : Files found : 55
Info : Files processed : 55
Info : Processing errors : 0
Info :
Info : Completed with 0 errors
[oracle@dixitlab AWR]$

With that you are done with the parsing of reports and have got the final CSV which we will be using to play around within Tableau.
Contents inside the parsed file.

Filename	Database Name	Instance Number	Instance Name	Database Version	Cluster	Hostname	Host OS	Num CPUs	Server Memory (GB)	DB Block Size	Begin Snap	Begin Time	End Snap	End Time	Elapsed Time (mins)	DB Time (mins)	Average Active Sessions	Busy Flag	Logical Reads/sec
awrrpt_1_445_446.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	445	1/26/2018 21:57	446	1/26/2018 23:00	62.24	6.33	0.1	N	2629.5
awrrpt_1_446_447.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	446	1/26/2018 23:00	447	1/27/2018 0:00	60.19	12.18	0.2	N	13973.4
awrrpt_1_447_448.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	447	1/27/2018 0:00	448	1/27/2018 1:00	60.15	13.52	0.2	N	14055.8
awrrpt_1_448_449.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	448	1/27/2018 1:00	449	1/27/2018 2:00	60.15	10.13	0.1	N	11597.4
awrrpt_1_449_450.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	449	1/27/2018 2:00	450	1/27/2018 3:00	60.16	0.03	0	N	65.4
awrrpt_1_450_451.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	450	1/27/2018 3:00	451	1/27/2018 4:00	60.12	0.02	0	N	70.3
awrrpt_1_452_453.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	452	1/27/2018 5:00	453	1/27/2018 6:00	60.13	0.69	0	N	189.8
awrrpt_1_453_454.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	453	1/27/2018 6:00	454	1/27/2018 7:00	60.13	2.88	0	N	2439.1
awrrpt_1_454_455.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	454	1/27/2018 7:00	455	1/27/2018 8:00	60.14	12.57	0.2	N	14027.3
awrrpt_1_455_456.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	455	1/27/2018 8:00	456	1/27/2018 9:00	60.14	10.11	0.1	N	13916.6
awrrpt_1_456_457.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	456	1/27/2018 9:00	457	1/27/2018 10:00	60.14	10.26	0.1	N	13941.5
awrrpt_1_457_458.txt	TUNEDB	PRIMARY	tunedb	EE	1	dixitlab.localdo	Linux x86 64-bit	1	2.77	8K	457	1/27/2018 10:00	458	1/27/2018 10:50	50.25	18.67	0.3	N	14118.9

Third: Data Representation using Tableau.
Okay so here we have the final parsed CSV of all those TEXT AWR reports named ‘tunedbperftests.csv’ and we are ready to play around and learn.

Immediately after launching you will see couple of options available for Data Sources on the left. Choose TEXT as the source and browse the CSV to load.

Next you will see all rows of your data source (tunedbperftests.csv in our case) file.

Next click on Worksheet, your personal area to play.

Tableau then divides the data in two main types: dimensions and measures. Dimensions are usually those fields that cannot be aggregated; measures, as its name suggests, are those fields that can be measured, aggregated, or used for mathematical operations. Dimension fields are usually used for row or column headings; measures are usually used for plotting or giving values to the sizes of markers.

             

Next tab is for Analytical functions, i.e. If you want to add a constant, average, mean, median averages or any reference lines to your graph/chart.

All good now, so we all all set yto plot out performance charts for that DB historical information that we have collected in the form of CSV and loaded to Tableau. Lets, plot for average hard parse per/second, Average DB Time, Average Pareses, Average Transactions happened against Time (Hourly rate of BEGIN TIME as a measure).

So, using above Area Graph you have plotted the average metric usages on the database during a time period.

Next, i will visualize one of the most prominent db wait event observed in the database during the probe (data collection) period ‘DPR’ or ‘Direct Path Reads’ and will plot the Bar graph against the TIME (Hourly BEGIN TIME).

Some more stats visualisations, this time ‘top 5 waits‘ and their hourly frequency.

There are lot’s of other things that you can do with your statistics, i mean you can plot your data in the form of Square, side by side circle, polygon, pie char, polygons, gantt bar, line graph, area graphs, box-and-whisker plots, highlight tables and many more.

So, imagine and you can visualize your database statistics using Tableau!

Questions are welcome. Happy reading! 🙂 🙂

Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | Leave a Comment »

 
%d bloggers like this: