Keyboard Warriors : Unite and do something positive with your lives

I follow a few non-Oracle channels on Youtube and I sometimes check out the comments, which seem to be a constant stream of keyboard warriors posting their negative rubbish. Whatever people do, the commenters claim to be able to do it better, or claim they know someone who can. If it is sport or fitness related, it will be followed by accusations of using performance enhancing drugs, which although it may be true, does not negate the thousands of hours of training and dieting people put in to get where they are.

The technology press makes your average Youtube channel comments section seem like a playground spat. Lots of articles by people who are totally ignorant about the technology they are “reporting”. In many cases, spending 5 minutes reading Wikipedia would be enough to dispel most of the garbage that is written, but unfortunately, those with an axe to grind don’t even do that. They read a headline and run with it. The language is straight out of conspiracy theory. Phrases like, “some experts believe”, and, “an insider told us”, are used like they are going out of fashion.

Imagine a world where all the time spent writing derogatory comments and downright lies was spent on doing something productive. Maybe producing informative articles and videos to help people, or contributing to open source projects. The press and TV has long been fuelled by fear and hate. Social media is one giant hate crime. If people aren’t being directly mean, they are trying to convince you that their life is better than yours, or their children are prettier or more intelligent that yours. It is all so terribly sad and boring. I’m not claiming I am above all this. I have been guilty of the same thing on numerous occasions.

So next time you do *anything* ask yourself, “Will what I am about to do have a positive impact?” If the answer is no, you probably shouldn’t do it.

Surprisingly, this post was not initiated as a result of something that was said or done to me. I have just witnessed several things this week that have made me really angry. What really irks me is I allowed those people to have that power over me. I allowed their actions to make me angry.

Enough of this hippy bullshit. I’m going to play with 12c…



Keyboard Warriors : Unite and do something positive with your lives was first posted on January 31, 2015 at 2:56 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Working With AWR Reports From EM12c

I’ve had a recent request to write an update on working with AWR reports, so as promised, here it is!

The Automatic Workload Repository

The Automatic Workload Repository, (AWR) was one of the best enhancements to Oracle back in release 10g.  There was quite a goal put in front of the development group when they were asked to develop a product that:

1.  Provided significant performance recommendation and wait event data enhancements over its predecessor statspack.

2.  Was always on, meaning that the data would continually collect without manual intervention from the database administrator.

3.  Wouldn’t impact the current processing, having its own background processes and memory buffer, designated tablespace, (SYSAUX).

4.  The memory buffer would write in the opposite direction vs. direction the user reads, eliminating concurrency issues.

Along with many other requirements, all of the above was offered with the Automatic Workload Repository and we end up with architecture that looks something like this:



Using AWR Data

The AWR data is identified by the DBID, (Database Identifier) and a SNAP_ID, (snapshot identifier, which has an begin_interval_time and end_interval_time to isolate the date and time of the data collection.) and information about what is currently retained in the database can be queried from the DBA_HIST_SNAPSHOT.  AWR data also contains ASH, (Active Session History) samples along with the snapshot data, by default, about 1 out of every 10 samples.

The goal to using AWR data effectively really has to do with the following:

1.  Have you identified a true performance issue as part of a performance review?

2.  Has there been a user complaint or a request to investigate a performance degradation?

3.  Is there a business challenge or question that needs to be answered that AWR can offer an answer to?  (we’ll go when to use AWR vs. other features…)

Performance Review

A performance review is where you have either identified a problem or have been assigned to investigate the environment for performance issues to solve.  I have a couple Enterprise Manager environments available to me, but I chose to go out to one in particular and cross my fingers hoping I would have some heavy processing to fit the requirements of this post.

The quickest way to see workload in your database environment from EM12c, click on Targets –> Databases.  Choose to view by load map and you will then view databases by workload.  Upon going to a specific Enterprise Manager environment, I found out it was my lucky day!

pt1I really don’t know who Kurt is that has a database monitored on this EM12c cloud control environment, but boy, is he my favorite person today! :)

Hovering my cursor over the database name, (kurt) you can view the workload he has running on his test database currently:pt2


Boy, is Kurt my favorite person today!

EM12c Database Home Page

Logging into the database, I can see the significant IO and resource usage for the database and host from the database home page:



If we move to Top Activity, (Performance menu, Top Activity) I begin to view more details about the processing and different wait events:



Kurt is doing all KINDS of inserts, (seen by the different SQL_IDs, by SQL Type “INSERT”.  I can drill down into the individual statements and investigate this, but really, there are a TON of statements and SQL_ID’s here, wouldn’t it just be easier to view the workload with an AWR report?

Running the AWR Report

I choose to click on Performance, AWR, AWR Report.  Now I have a choice.  I could request a new snapshot to be performed immediately or I could wait till the top of the hour, as the interval is set hourly in this database.  I chose the latter for this demonstration, but if you wanted to create a snapshot immediately, you can do this easily from EM12c or request a snapshot by executing the following from SQLPlus with a user with execute privileges on the DBMS_WORKLOAD_REPOSITORY:


For this example, I simply waited, as there was no hurry or concern here and requested the report for the previous hour and latest snapshot:



I always start at the Top Ten Foreground Events and commonly look at those with high wait percentages:



Direct Path Write, that’s it.  Nothing else to see here… :)

Direct path write involves the following:  inserts/updates, objects being written to, tablespaces being written to and those datafiles that make up the tablespace(s).

It’s also IO, which we quickly verify down in the Foreground Wait Class:



Looking at the Top SQL by Elapsed Time confirms that we are dealing with a workload consisting of all inserts:



Clicking on the SQL ID, takes me to the Complete List of SQL Text and shows me just what Bad Boy Kurt is doing to produce his testing workload:


Wow, that Kurt is quite the rebel, eh? :)

Insert in a loop into one table from the same table, rollback and then end the loop, thanks for playing. He’s kicking some tires and doing it with angst!  Don’t worry people, like I said, Kurt is doing his job, using a module called “Load Generator”.  I’d be a fool not to recognize this as anything other than what it is-  generating workload to test something.  I just get the added benefit of having a workload to do a blog post on using AWR data… :)

Now, if this was a real issue and I was trying to find out what this type of performance impact this type of insert was creating on the environment, where to go next in the AWR report?  The top SQL by elapsed time is important as it should be where you focus your efforts.  Other sections broken down by SQL is nice to have, but always remember, “If you aren’t tuning for time, you’re wasting time.”  Nothing can come of an optimization exercise if no time savings is seen after you’ve completed the work.  So by taking first the Top SQL by Elapsed Time, then looking at the statement, we now can see what objects are part of the statement, (large_block149, 191, 194, 145).

We also know that the problem is IO, so we should jump down from the SQL detailed information and go to the object level information.  These sections are identified by Segments by xxx.  

  • Segments by Logical Reads
  • Segments by Physical Reads
  • Segments by Read Requests
  • Segments by Table Scans

so on and so forth….

These all show a very similar pattern and percentage for the objects we see in our top SQL. Remember, Kurt was reading each of these tables, then inserting those same rows back into the table again, then rolling back.  As this is a workload scenario, unlike most performance issues I see, there is no outstanding object showing with an over 10% impact in any area.


As this is an Exadata, there is a ton of information to help you understand offloading, (smart scans) flash cache, etc. that will assist in relaying the information you need to make sure you are achieving the performance you desire with an engineered system, but I’d like to save that for another post and just touch on a few of the IO reports, as we were performing table scans, so we do want to make sure those were being offloaded to the cell nodes, (smart scans) vs. being performed on a database node.

We can start by looking at Top Database IO Throughput:


And then view the Top Database Requests Per Cell Throughput, (sans the Cell node names) to see how they compare:



Then we add to it this last graph that tells us, are we offloading enough of the workload to each of the cell nodes, Top IO Reasons by Request?



This has been one scenario trip through an AWR Report.  Hopefully it’s been interesting and next time I’ll search for a different load type to take you through!

Happy Friday!






Tags:  , ,

Copyright © DBA Kevlar [Working With AWR Reports From EM12c], All Right Reserved. 2015.

Data Center of the Future – now



photo by youflavio

In a recent blog post Eric D. Brown defined an Agile Data Center as

An Agile Data Center is one that allows organizations to efficiently and effectively add, remove and change services at the speed of the business, not the speed of technology - Eric D. Brown

In follow up post he said that a Agile Data Center could be implemented by Software Defined Data Center (SDDC) for example using machine virtualization to spin environments up and down.

With SDDC, it is possible for companies to replace their data center’s infrastructure with a virtualized environment and then deliver services and software as a service - Eric D. Brown

The question arrises, what technologies constitute and agile SDDC? What software should be leveraged to succeed at having an agile data center, a SDDC? The most important software to look at is software that addresses the top constraints in the data center. As the theory of constraints says, any improvement not made at the constraint is an illusion. So what are the top constraints in the data center. The top constraint, as found after working with 100s of companies and surveying 1000s of companies, is provisioning environments for development and QA. Why is that? It’s because almost every industry now is finding itself to be more and more a software industry from stock trading to booksellers to taxi companies to hotels. The competitive advantage is more and more about the software used to provide and sell the service. To build that software requires development and QA and thus development and QA environments. The hard part of an environment to provision is no longer the machine thanks to machine virtualization. The hardest part of the environment to provision is the data. Data that represents the production system is required to develop applications that use, display and manage that data. Data is typically kept in large complex databases such as Oracle, SQL Server, Sybase, Postgres and DB2.

Provisioning development and QA environments that rely on databases  can be an expensive, slow endeavor. But like machine virtualization there is a new technology data virtualization that instead of making full physical copies, instead makes one copy of each unique data block on the source including a stream of changes blocks. With this “time flow” of unique blocks from the source database, data virtualization can provide copies in minutes not by actually making copies but by providing pointers back to the existing blocks. These existing blocks are read/writeable thanks to a technology of redirect on write which saves modified blocks in a different location than the original. It all sound a bit complex but when that’s the beauty of data virtualization solutions. They take the complexity, wrap it up into automated software stack and provide simple interface and APIs to provision full developer environments from the binaries, to the code files to the most complex and difficult part of the environment provisioning which is provisioning full running copies of the data. Included in most data virtualization solutions is masking as well since sensitive data is often required to be masked in development environments. The software defined data centers (SDDC) depend on machine virtualization and data virtualization.

What other technologies are also required?

Log Buffer #408, A Carnival of the Vanities for DBAs

This Log Buffer Edition covers various innovative blog posts from various fields of Oracle, MySQL and SQL Server. Enjoy!!!


A user reported an ORA-01114 and an ORA-27069 in a 3rd party application running against an Oracle 11.1 database.

Oracle SOA Suite 12c: Multithreaded instance purging with the Java API.

Oracle GoldenGate for Oracle Database has introduced several features in Release

Upgrade to 12c and Plugin – one fast way to move into the world of Oracle Multitenant.

The Oracle Database Resource Manager (the Resource Manager) is an infrastructure that provides granular control of database resources allocated to users, applications, and services. The Oracle Database Resource Manager (RM) enables you to manage multiple workloads that are contending for system and database resources.

SQL Server:

Database ownership is an old topic for SQL Server pro’s.

Using T-SQL to Perform Z-Score Column Normalization in SQL Server.

The APPLY operator allows you to join a record set with a function, and apply the function to every qualifying row of the table (or view).

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA’s troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions.

Grant Fritchey reviews Midnight DBA’s Minion Reindex, a highly customizable set of scripts that take on the task of rebuilding and reorganizing your indexes.


It’s A New Year – Take Advantage of What MySQL Has To Offer.

MySQL High Availability and Disaster Recovery.

MariaDB Galera Cluster 10.0.16 now available.

Multi-threaded replication with MySQL 5.6: Use GTIDs!

MySQL and the GHOST: glibc gethostbyname buffer overflow.

From 0 to Cassandra – An Exhaustive Approach to Installing Cassandra

All around the Internet you can find lots of guides on how to install Cassandra on almost every Linux distro around. But normally all of this information is based on the packaged versions and omit some parts that are deemed essential for proper Cassandra functioning.

Note: If you are adding a machine to an existing Cluster please approach this guide with caution and replace the configurations here recommended by the ones you already have on your cluster, specially the Cassandra configuration.

Without further conversation lets start!


Start your machine and install the following:

  • ntp (Packages are normally ntp, ntpdata and ntp-doc)
  • wget (Unless you have your packages copied over via other means)
  • vim (Or your favorite text editor)

Retrieve the following packages


Set up NTP

This can be more or less dependent of your system, but the following commands should do it (You can check this guide also):

~$ chkconfig ntpd on
~$ ntpdate
~$ service ntpd start

Set up Java (Let’s assume we are doing this in /opt)

Extract Java and install it:

~$ tar xzf [java_file].tar.gz
~$ update-alternatives --install /usr/bin/java java /opt/java/bin/java 1 

Check that is installed:

~$ java -version
java version '1.7.0_75'
Java(TM) SE Runtime Environment (build 1.7.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.75-b04, mixed mode)

Let’s put JNA into place

~$ mv jna-VERSION.jar /opt/java/lib

Set up Cassandra (Let’s assume we are doing this in /opt)


Extract Cassandra:

~$ tar xzf [cassandra_file].tar.gz

Create Cassandra Directories:

~$ mkdir /var/lib/cassandra
~$ mkdir /var/lib/cassandra/commitlog
~$ mkdir /var/lib/cassandra/data
~$ mkdir /var/lib/cassandra/saved_caches
~$ mkdir /var/log/cassandra


 Linux configuration

~$ vim /etc/security/limits.conf

Add the following:

root soft memlock unlimited
root hard memlock unlimited
root – nofile 100000
root – nproc 32768
root – as unlimited

CentOS, RHEL, OEL, set in the following in /etc/security/limits.d/90-nproc.conf:

* – nproc 32768

Add the following to the sysctl file:

~$ vim /etc/sysctl.conf
vm.max_map_count = 131072

Finally (Reboot also works):

~$ sysctl -p

Firewall, the following ports must be open:

# Internode Ports
7000    Cassandra inter-node cluster communication.
7001    Cassandra SSL inter-node cluster communication.
7199    Cassandra JMX monitoring port.
# Client Ports
9042    Cassandra client port (Native).
9160    Cassandra client port (Thrift).

Note: Some/Most guides tell you to disable swap, I think of swap as an acrobat’s safety net, it should never have to be put to use, but in need it exists. As such, I never disable it and I put a low swappiness (around 10). You can read more about it here and here.

 Cassandra configuration

Note: Cassandra has a LOT of settings, these are the ones you should always set if you are going live. Lots of them depend on hardware and/or workload. Maybe I’ll write a post about them in the near future. In the meantime, you can read about them here.

~$ vim /opt/cassandra/conf/cassandra.yaml

Edit the following fields:

cluster_name: <Whatever you would like to call it>
data_file_directories: /var/lib/cassandra/data
commitlog_directory: /var/lib/cassandra/commitlog

saved_caches_directory: /var/lib/cassandra/saved_caches

# Assuming this is your first node, this should be reachable by other nodes
seeds: “<IP>”

# This is where you listen for intra node communication
listen_address: <IP>

# This is where you listen to incoming client connections
rpc_address: <IP>

endpoint_snitch: GossipingPropertyFileSnitch

Edit the snitch property file:

~$ vim  /opt/cassandra/conf/

Add the DC and the RACK the server is in. Ex:


Finally make sure your logs go to /var/log/cassandra:

~$ vim /opt/cassandra/conf/logback.xml


Start Cassandra

~$ service cassandra start

You should see no error here, wait a bit then:

~$ grep  JNA /var/log/cassandra/system.log
INFO  HH:MM:SS JNA mlockall successful

Then check the status of the ring:

~$ nodetool status
Datacenter: DC1
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Owns   Host ID                               Token                                    Rack
UN  140.59 KB  100.0%  5c3c697f-8bfd-4fb2-a081-7af1358b313f  0                                        RAC

Creating a keyspace a table and inserting some data:

~$ cqlsh xxx.yy.zz.ww

cqlsh- CREATE KEYSPACE sandbox WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', DC1 : 1};
Should give no errors
cqlsh- USE sandbox;
cqlsh:sandbox- CREATE TABLE data (id uuid, data text, PRIMARY KEY (id));
cqlsh:sandbox- INSERT INTO data (id, data) values (c37d661d-7e61-49ea-96a5-68c34e83db3a, 'testing');
cqlsh:sandbox- SELECT * FROM data;

id                                   | data
c37d661d-7e61-49ea-96a5-68c34e83db3a | testing

(1 rows)

And we are done, you can start using your Cassandra node!

Preparing architecture for APEX 5.0 upgrade

I doubted to set the title of this post to "Running APEX 4.2 and 5.0 in the same Oracle instance", but decided not to do that, but that is basically what I will do. Before going into details, I'll share my architecture.

In December 2013 I wrote it was time to update your APEX environment and I gave a quick overview of the architecture we're using. I thought it's time to review that post, so below you find how my preferred APEX architecture is today and tomorrow (once APEX 5 is production).

I'm using Apache as a reverse proxy in front of Tomcat. I'm not going in too much detail about which version to take: Apache v2.2 vs 2.4 and Tomcat v7 vs 8. There're many threads on the internet about that and I guess it depends your environment and your personal feeling. I've been using both versions but currently I'm on Apache v2.2 because it comes as a default with RHEL / OEL6 and SELinux is configured out of the box to protect the webserver. And for Tomcat I'm using v8 as that's the basis for the future versions of Tomcat (v9) and when you want to do Websockets for example, v8 has a more improved spec.

A few years ago we had the discussion about mod_plsql vs APEX Listener (now Oracle REST Data Services - ORDS). I think today, it's clear ORDS is the way to go as it gives you much more features and is proven technology.

For APEX I'm always on the latest version as fast as I can, as with every new release there're great improvements and fixes.

And finally the Oracle Database I'm on 12cR1 because I like the pluggable database concept and the other features it brings. I guess most people will go to 12c very soon, as 11.2 premier support ends this month. You can read more about when what is still supported in this doc.

But in this post I want to show you how easy it is to prepare your environment for APEX 5 and to test the upgrade with an architecture as above. I basically want to run APEX 4.2 and 5.0 next to each other. I'll clone my PDB and apply the APEX 5 installation on the new PDB. Next I'll configure ORDS so it knows to which database it needs to point to depending the url I'll call.

Step 1. Clone the PDB 

sqlplus / as sysdba

create pluggable database APEX50_PDB from APEX42_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX42_PDB','/u01/app/oracle/oradata/cdb/APEX50_PDB');

Step 2. Open the PDB and install APEX 5.0

alter pluggable database apex50_pdb open;

So now we have a new database which is a copy of our existing database open and ready to be used. Next we will need to install APEX 5.0. As the time of writing APEX 5.0 is not available yet, but it will probably be - connect to the new PDB and run @apexins... (follow the installation guide of APEX 5.0 once available!)

Step 3. Configure ORDS

With SQL Developer you can configure ORDS and add the connection to the new PDB.
In SQL Developer 4.1, first setup a connection to your ORDS (Tools > Manage REST Data Services Connections > Add Connection). Next open the ORDS Administration window (View > REST Data Services > Administration). Right click on the REST Data Services and Connect to ORDS:

You'll see the current configuration.

In order to connect to the new database we need to add a Database. Right click in Database Settings and add a new database. Before writing it back (the icon with the green up arrow - click the Test Settings button first (the icon with a v) to make sure everything is fine.

Final step is to let ORDS know that if we put in our url /apex50 we want to connect to the new database. You can do that by adding an entry in URL Mapping:

That's it...

Note: sometimes I've issues with adding the database and URL Mapping in SQL Developer, but it's as fast to do it command line too. The doc has a great example which commands to run:

Step 4. Test

When you navigate to your normal url e.g. http://localhost/ords/f?p=ABC you will see your APEX 4.2 instance, but if you navigate to http://localhost/ords/apex50/f?p=ABC you'll see the APEX 5.0 instance.

You can play a bit more with making it nicer urls or do some redirects in Apache, but I hope you get the idea how to start testing APEX 5.0 while still running APEX 4.2 too.

Restrict the query optimizer through OPTIMIZER_FEATURES_ENABLE and Top-Frequency/Hybrid Histograms

To make upgrades easier, I regularly see people considering disabling query optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to a non-default value. My general opinion about this “habit” is summarized in TOP with the following two sentences:

Changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter is only a short-term workaround. Sooner or later the application should be adapted (optimized) for the new database version.

The issue is that not all new features are disabled by the OPTIMIZER_FEATURES_ENABLE initialization parameter. For example, if you set it to in version, you will not get exactly the query optimizer. A particular case to be considered in 12.1 is related to the new types of histograms (top-frequency and hybrid). In fact, even though you set the OPTIMIZER_FEATURES_ENABLE initialization parameter to, the DBMS_STATS package can produce top-frequency and hybrid histograms. As a result, the estimations of the query optimizer can change. An example is provided by this script.

Note that also setting the COMPATIBLE initialization parameter to, for example, 11.0.0 with the binaries does not prevent the DBMS_STATS package to gather top-frequency and hybrid histograms.

So, if you are really planning to upgrade to 12.1 and changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter, in my opinion you should make sure that the DBMS_STATS package does not create top-frequency and hybrid histograms. Unfortunately, with the METHOD_OPT parameter, it is not possible to specify which type of histograms the DBMS_STATS package can create. The only input parameter of the GATHER_*_STATS procedures that affects this decision is ESTIMATE_PERCENT. When it is set to a numeric value greater than 0, the DBMS_STATS package does not create top-frequency and hybrid histograms. In other words, the DBMS_STATS package considers the creation of the new types of histograms only if the ESTIMATE_PERCENT parameter is set to the DBMS_STATS.AUTO_SAMPLE_SIZE constant (0).

Another possibility to prevent that the DBMS_STATS package creates top-frequency and hybrid histograms is to set the undocumented preferences ENABLE_TOP_FREQ_HISTOGRAMS and ENABLE_HYBRID_HISTOGRAMS. For example, to set them globally, you can use the following PL/SQL block:

  dbms_stats.set_global_prefs(pname => 'ENABLE_HYBRID_HISTOGRAMS', pvalue => 0);
  dbms_stats.set_global_prefs(pname => 'ENABLE_TOP_FREQ_HISTOGRAMS', pvalue => 0);

WARNING: Even though according to my tests the preferences do what I expect (i.e. disable top-frequency and hybrid histograms), I never used them in a real case and I did not ask Oracle about their usage. Hence, if you want to use them, I would not only test them carefully, but also ask Oracle Support whether their utilization can lead to known problems.

Before setting them, I also adivse you to get their (default) value with a query like the following one:

SELECT dbms_stats.get_prefs('ENABLE_HYBRID_HISTOGRAMS') AS hybrid,
       dbms_stats.get_prefs('ENABLE_TOP_FREQ_HISTOGRAMS') AS top_freq
FROM dual

Stories Are the Best, Plus News on Nest!

Friend of the ‘Lab, Kathy (@usableapps), has been using Storify for a while now to compile easy-to-consume, erm, stories about the exploits of Oracle Applications User Experience.

You might remember Storify from past stories such as the In the U.K.: Special events and Apps 14 with UKOUG and Our OpenWorld 2014 Journey.

Anyway, Kathy has a new story, The Internet of Things and the Oracle user experience, which just so happens to feature some of our content. If you read here with any regularity or know Noel (@noelportugal), you’ll know we love our internet-connect things.

So, check out Kathy’s story to get the bigger picture, and hey, why not read all the stories on the Usableapps Storify page.

And bonus content on IoT!

Google keeps making the Nest smarter and marginally, depending on your perspective, more useful. In December, a Google Now integration rolled out, pairing a couple of my favorite products.

More gimmick than useful feature, at least for me, I ran into issues with the NLP on commands, as you can see:


Saying “set the temperature to 70 degrees” frequently results in an interpretation of 270 degrees. Works fine if you don’t say “to” there. Google Now becomes a more effective assistant, this integration will be more useful, I’ve no doubt.

Then, at CES, Nest announced partnerships that form a loose alliance of household appliances. It may take a big player like Nest (ahem, Google) to standardize the home IoT ecosystem.

And just this week, Misfit announced a partnership with Nest to allow their fitness tracker, the one I used to wear, to control the Nest. I’m tempted to give the Shine another go, but I’m worried about falling back into a streak-spiral.

Thoughts on IoT? Nest? Ad-supported world domination? You know what to do.Possibly Related Posts: by Clive Bostock


This is a reposting of an old blog post that was on but is no longer accessible

More trace file analyzer tools at

Related blog post: Oracle “Physical I/O” ? not always physical with a 10046 parser specifically for I/O and readme

Often when I have a 10046 trace file, especially when looking at I/O issues, I want a histogram of I/O response time.  To get I/O response time I’ve hacked out incomple awk scripts from time to time, always meaning to write a more complete one, well now I don’t have to. It’s already been done!

Here is a cool perl script from Clive Bostock: README.TXT

(also checkout orasrp which produces a more indepth report in HTML. I like both. I like as a short  easy portable script that I can modify, whereas orasrp is a binary and only works on some ports)

For example, if I trace a session with 10046, and retrieve the tracefile, then I can run:

$ -t  mytrace.trc

and it will output  a header and three sections


  • Summary of all events for tracefile
  • Events by object summary
  • Events by object histogram

This looks like


Trace file mytrace.trc
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/oracle
System name:    SunOS
Node name:      toto
Release:        5.10
Version:        Generic_142900-12
Machine:        sun4u
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 177
Unix process pid: 16553, image: oracle@toto
Trace input file : mytrace.trc

Wait summary

Wait Event              Count Elapsed(ms)   Avg Ela (ms)  %Total
~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~
db file sequential read  2715      11593              4    3.74
       direct path read  4484       4506              1    1.45
 db file scattered read   141        898              6    0.29
          log file sync     3          8              2    0.00
             Total Elapsed:       309821

Wait Summary by object

Object Id  : Wait Event                  Count Tot Ela (ms) %Total Avg Ela (ms)
~~~~~~~~~~ : ~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~ ~~~~~~~~~~~~ ~~~~~ ~~~~~~~~~~~~~
28581      : direct path read            4484         4506   1.45            1
1756433    : db file sequential read      725         1891   0.61            2
764699     : db file sequential read      332         1762   0.57            5
37840      : db file sequential read      200         1044   0.34            5
38018      : db file sequential read      108         1009   0.33            9
81596      : db file scattered read       140          887   0.29            6

wait histogram by object

This section splits the event counts into elapsed time
buckets so that we can see if there are any suspiciousn
or anomalous response time / frequency patterns.
Object Id : Wait Event              <1ms <2ms <4ms <8ms <16ms <32ms <64ms <128ms <256ms <512ms >=1024ms
~~~~~~~~~ : ~~~~~~~~~~~~~~~~~~~~~~~ ~~~~ ~~~~ ~~~~ ~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~~~
28581     : direct path read        7680   87  148  221  144    40     4     0      0       0        0
1756433   : db file sequential read  606  268   45   35   66     6     2     0      0       0        0
764699    : db file sequential read   74  119   11   78   78     9     0     0      0       0        0
37840     : db file sequential read   50   72    6   45   47     5     0     0      0       0        0
38018     : db file sequential read   12   38    7   10   30    12     5     0      0       0        0
81596     : db file scattered read    64    4   13   62   18     8     3     0      0       0        0
41995     : db file sequential read   20   39    0    7   16     8     4     0      0       0        0
108718    : db file sequential read   74   54    5   12   24     4     0     0      0       0        0
33490     : db file sequential read    0    5   11   25   19     4     0     0      0       0        0

Enterprise Manager and Services, Part I

The term “Service” is used to represent numerous objects within Enterprise Manager 12c.  Although the most common reference to the term is related to the Oracle Management Service, (OMS), in this blog post, we are going to drill down and refer to all services identified in the drop down menu from Targets, Management Services and Repository.

The two main services we are going to cover here are set up as part of the Oracle Enterprise Manager installation and shouldn’t be edited or reconfigured, but I think it’s good to know where everything is if you should ever need to investigate it or are working with Oracle Support.

By default the following services will display in the EM12c environment-

  • EM Console Service
  • EM Job Service

These two services are essential to successful Enterprise Manager task completion.  The EM Console Service is the service that keeps the console active and accessible and the EM Job Service controls not just the EM Jobs viewable in the EM Job Activity console, but also a ton of background processing for the Enterprise Manager.

The Main Console View

Once you’ve accessed the services from the target menu, as these are both monitored targets in the Enterprise Manager, you’ll see the following information:



Along with the two, default services we expect to see for a standard Enterprise Manager installation, we also can see the status, if there are any open incidents and what system the services belong to.  You can create and remove services from here, but unless you know what you’re doing, this is mostly the view to quickly filter service information.

Creating services from this console offers you three, two generic service types and one advanced service option called an Aggregate service.


Using our existing services as examples, it may give you a bit of an idea of how a System Based service is different than a Test Based one.

A System based service monitors one single system vs. a Test based one, which monitors a one or more test based systems.  Notice that the EM Jobs Service does ONE thing-  it monitors the Enterprise Manager Job Service.  The EM Console Service has to monitor EVERYTHING that is involved in the console, which is much more complex and requires a constant test to ensure all aspects are available.

And Aggregate Service is a combination of sub-services.  If one of the many sub-services is experiencing an issue, the entire Aggregate service is dependent upon that one failure.

There are a number of other services that Enterprise Manager 12c supports:

  • Business Application
  • Service Entry Point
  • Oracle Database Service
  • Oracle Applications Service
  • Siebel Services
  • Beehive Services
  • EM Service
  • And others….

You can create, manage and remove services via EM CLI commands, too.  The following commands will support the management of each:

  • create_service
  • assign_test_to_target
  • change_service_system_assoc
  • remove_service_test_assoc
  • set_availability
  • set_properties
  • And many, many others….

To the right of the console list of services, are Key Components incident information.  This can assist the administrator when they are experiencing a problem, but the culprit isn’t displayed in the service, a related target may be the issue:



The Key Tests also show current status and any monitoring beacons that are attached to the service.

EM Console Service

If we click on the EM Console Service, we are taken to the home page for the service.


Centering in on the upper, left section of the home page, you notice that there are a number of tabs:

  • Home
  • Charts
  • Test Performance
  • System
  • Monitoring Configuration
  • Topology

For any generic service, you are going to see these tabs, but we’ll go through each one of the tabs, (outside of the home tab) for the EM Console Service so that you get a good feel for what is available in each and what tab has control over the configuration of the service.


Upon clicking the charts menu, you’ll see the basic information about page response per millisecond and page hits per minute:


Unless there is a serious performance issue in the console, I commonly don’t find a lot of useful data from the default view of “last 24 hrs” and recommend changing the view to [at least] the last 7 days.  You’ll be surprised how different the data will look when you take into account the nightly maintenance jobs that keep EM12c clean and effective:


Test Performance

The next tab is for test performance.  All services have tests involved that check and see if a service is actively running and passes either one or more tests that verify it is properly functioning and available.


The value for the last EM Console Service Test is shown above in the graph, but the real information lies lower in the performance timestamp.  The test isn’t run very often, (it is a performance impact to the system, so full testing of the service is only run once every 24 hours.)  You can see the Collection Timestamp in the right hand window of 6:55am, which will also match the extended Web Transaction used to verify that the console is accessible if you click on the EM Console Service Test at the bottom.


The System tab displays the latest information about the status and incidents of the components connected to the service.  service7

As you can see from the screen shot above, a recent test of a non-released patch has caused some grief, (this is why we test these things… :)) and there are a few applications components that were impacted and need to be reviewed and addressed.  Each of the components are linked on this page, so they can be easily accessed and the incident investigated.  As this is the EM Console Service, there are a number of related components, (16 total, as shown in the right hand totals) and you can change to the next page to display the rest of the components involved.

Monitoring Configuration

The Monitoring Configuration requires a section to itself, as this has some pretty impressive links in this tab.  We are going to go through each one of these, so you can get a solid understanding of what is available:


System Association

First thing you need to remember, DON’T CLICK ON THE “REMOVE SYSTEM” BUTTON.  Although I’ve never seen anyone attempt this from the console, I can’t imagine the damage that would be done if you chose to remove a key component from the EM Console Service unless instructed by Oracle Support, so just don’t attempt it to see what happens… :)


As discussed above, each component associated to the EM Console Service is displayed, along with the designation as a key component with a check mark.

Root Cause Analysis Configuration

By setting the analysis mode to manual, less stress is put on the system resources to collect root cause data at any issue, letting you control when the analysis is performed.  The recommendation is to leave this set to manual mode for analysis collections and only to change it [again] with the blessing of Oracle Support.

service11If any root cause analysis has been performed, you would view it to the right of the list of components and types, as the bottom section is reporting info only, listing each component and any test counts.

Service Tests and Beacons

This is how cloud control knows that the console service and key components are up and available.  A set of tests are run on a regular interval in the form of a web transaction.  This means that the test logs in, verifies a result from the URL for cloud control and verifies each key component is responsive.  If all tests result in a positive result, then the status is ‘up’.


A beacon is a target used to monitor service tests, primarily to measure performance of the service. One beacon belongs to at least each service test and is used to check connectivity between the client and the OMS host or the OMS host and the OMR host, etc.  Adding multiple beacons for one service adds no value, so if for some reason, you see multiple beacons assigned to a service, there’s a problem right there.

Availability Definition

This console looks very similar to the one above, but it covers test information about the EM Console Service Tests and EM Management Beacon:


By default, both of these require ALL KEY SERVICE TESTS must be SUCCESSFUL to be marked successful and return a status of ‘up’.  As you can see, the EM Management Beacon has not done so, which results in an unavailable status.  The next step in trouble-shooting this would be to click on the Beacon name link and investigate what  didn’t return successfully.

Performance Metrics

Performance metric data is where you set your thresholds for EM Console Service response, (and the readers say “Aha!  That’s another location I can get to the valuable metric threshold setting!”)


The metric value settings for the threshold are in milliseconds and if you are receiving notifications that this is beyond your response time, look at network connection, network connection between data centers, etc. and if it’s something you can’t correct, bump up the warning and critical threshold values to provide more value.

Usage Metrics

This setting by default isn’t set, but it’s one area, you can configure if you would like.  If you are concerned about page performance and want to report on this metric data, set up the with a logical value to start.  For a small web server, 12000 page hits per minute would be a pretty good warning value with 20000 page hits per minute critical.  Use this as a beginning base, test, rinse and repeat.


Now we’ve completed a high level review of the Monitoring Configuration, we can return to the last tab, Configuration Topology.


This last tab is for those of you that love topology charts.  This is the ultimate in configuration topology, allowing you to interact with all data, component dependency and connections to the EM Console Service.



This is also where you come to realize how dependent everything is on your EM Console Service-  no website, no Enterprise Manager access.  By hovering my cursor over the service, you can quickly see the dependent HA servers, database instance, management agent and other essential hardware and software components that are involved with the EM12c hardware and framework.  To the right is filtering options to drill down into a more manageable view:


High level information about each of the targets you want to drill down on are displayed and you can use this section to create a much easier to read topology:



The topology makes even more sense if you can view all the information on each of the targets vs. me hiding host/database names… :)

Hopefully this review of services helps explain a little bit more about this very, very important key component to Enterprise Manager 12c.  Next post on Services, I’ll go over the all important, EM Job Service.


Tags:  , ,

Copyright © DBA Kevlar [Enterprise Manager and Services, Part I], All Right Reserved. 2015.