Why Use Taxonomies?

As stated, the reason for using taxonomy is to help structure information. While flagging metrics allows us to see which metrics are abnormal, the results are still a collection of seemingly unconnected metrics. By creating a taxonomy whereby metrics are grouped according to some commonality, I am essentially bundling the tens of thousands of metrics into a smaller handful of useful higher-level categories to make the analysis easier and more efficient. Doing this allows me to record and see the metric analysis in a new way.

The taxonomy you choose to impose on the metrics will have an intended purpose. With that purpose in mind, you will create categories remembering the MECE principle mentioned above. Following is a discussion of the two taxonomies I have created thus far. Creating a taxonomy for thousands of metrics is no easy task, so I consider the taxonomies I use as a work in progress, whereby I continued to refine them from time to time.

Infrastructure Taxonomy

The first taxonomic grouping I implemented was an infrastructure taxonomy. The categories I include in this taxonomy are CPU, IO, memory, SQL, DB, and network. Each of these categories also has a subcategory, some of which can be viewed in Figure 6-1. The subcategories for the infrastructure taxonomy were invented by me to provide an extra level of detail to the structure imposed by the taxonomy. The subcategories I assigned stem from personal observations of patterns in the metric names, nothing more. In one sense, the taxonomy is an arbitrary categorization; one could just as easy have another categorization scheme. In Figure 6-1, the metric count column shows how many of the implemented metrics fall into this category/subcategory.

The goal of the infrastructure taxonomy is to group metrics into functional groups that will help identify when a performance issue is related to a problem in the infrastructure and direct to the specific portion of the infrastructure. What I have done is match metrics to the area within the technology infrastructure [sometimes referred to the technology stack] that best fits the metric.

When a taxonomy is used within the DOPA process, the interpretation of the results is made much easier. For example, when lots of metrics are flagged and the taxonomy shows that they are all related to the memory category, it is obviously prudent to consider that changes to memory might be an important part of the solution. Likewise, if a lot of metrics flag up in the CPU or IO categories, one would need to check into these infrastructure components to see if they are operating efficiently. By reporting metrics along with the taxonomic grouping, these observations are more easily interpreted.

When you run the DOPA process using the infrastructure taxonomy, you will get a table that shows the taxonomic categories (or subcategories if you choose to look at this level) and the category count. The category count view shows how many metrics were flagged in that category for the time interval specified. Figure 6-2 shows the results of the DOPA process looking at the category count view using the infrastructure taxonomy for a real data set. In this example, you can see that the IO category has a significant number of flagged metrics. There are other areas with flagged metrics, but because of the overwhelming number of flagged metrics in the IO category, a closer look at those would be a good next step.

In order to build the infrastructure taxonomy, I wrote a single SQL statement that examines every single metric instrumented thus far. The SQL code searches for specific keywords in the metric name and uses them to classify the metric into one of the categories and subcategories. The search includes terms like i/o, memory, and network. If I can’t classify a metric into a high-level category, I give it the classification “any” [metrics in the any category, of course, are good candidates for refining the taxonomy when the time comes]. After running this search/categorization, I check to make sure assigned categories make sense. I use an iterative process to refine the substrings so that I can be sure each metric gets to a proper categorization. As I said, my taxonomy is a work in progress; I continue to make modifications as I see the need to change things around.

Oracle Taxonomy

The second taxonomy I created classifies according to Oracle subcomponents/subsystems. Since Oracle is a massive system with many working parts, and since many of those parts are managed independently, it is helpful to know if performance issues are affecting one or more of those parts so that we may focus our tuning efforts there. Figure 6-3 shows some of the classification groups I use for this taxonomy. You can see in this figure that the categories have subcategories as well. In the table, the first category you will see is BUFFER CACHE. BUFFER CACHE is further divided into two subcategories: ratio and time. There is one metric in each of these subcategories as indicated in the metric count column.

To create the Oracle taxonomy, I use the same SQL code I used for creating the infrastructure taxonomy, but I use different keywords that relate to the Oracle subcomponents to search and assign each metric to the appropriate category. To be clear, the categories and subcategories were created by using patterns in the metric names for the assignments.

I ran the DOPA code using the Oracle taxonomy on the same data that was used for the infrastructure taxonomy example in Figure 6-2, and those results are shown in Figure 6-4. When running the category count view using the Oracle taxonomy, many of flagged metrics currently fall into the ALL category. This is because I have not spent a great deal of time assigning metrics to the various categories. I include this to show that the taxonomies are only as useful as you make them. I intend to go back and improve this particular taxonomy as time allows because I think there is good potential for its usefulness when it is fleshed out a little better.

The Taxonomy Table

The code contains the SQL used to create the taxonomy table and populate it with data for the infrastructure and Oracle taxonomies. The result of running this SQL statement is a populated table called METRIC_TAXONOMY.

A portion of the data dictionary view of the taxonomy table is shown in Figure 6-5. The taxonomy table has five rows.

• TAXONOMY_TYPE is the name for the taxonomy. Currently I have implemented infrastructure and Oracle taxonomy types.

• STAT_SOURCE displays the name of the view that provides the data for a given metric source of the metric.

• METRIC_NAME is the name for that metric as given or as built during the normalization process.

• CATEGORY and SUB_CATEGORY specify the taxonomic groupings.

A portion of the taxonomy table is shown in Figure 6-6. In this sample listing of the metric taxonomy table, both taxonomy types are included. The table displays the category and subcategory into which each metric has been sorted. The source(s) of each metric is also given. I believe it is important to carry the metric source, because I don’t want users of the DOPA process to lose sight of the source from which each metric value came. An interesting observation here is that DB time is a metric with values drawn from two different sources.

Having a single table for both (or more) taxonomies allows you to use it in the DOPA process based on the taxonomic classification that makes the most sense for your use case (i.e., the model you are building). It also allows you to subset the model in multiple ways which can be helpful for discovering patterns and identifying particular areas of performance degradation. As I build models using the DOPA process, I have always picked one or the other of the two taxonomies.

Again, both of the taxonomies I have developed thus far are a work in progress. I am always striving to adhere to the MECE principle and want to have good/useful categorizations for the metrics. If I notice that an important metric is categorized inappropriately, I adjust the code that builds the taxonomy. I simply drop the old taxonomy table and create a new one with the new mappings of the metrics to the taxonomic categories. My infrastructure taxonomy has been the more helpful to me thus far, but that is probably because it comports well with the DOPA process model building I have been using most.

Code Snippet for Building the Taxonomies

This hard-coded approach to building the taxonomies works, but it is a bit cumbersome, but it works well enough for my purposes. In the future, I hope to explore the possibility of using machine learning techniques to build additional taxonomies.

Summary

The main purpose of this chapter was to introduce you to the concept of taxonomies. Building a taxonomy means simply creating meaningful categories into which the various metrics can be sorted/organized. The selection of which category scheme to use will be determined by your own needs/interests. For example, you could create a taxonomy to map a metric to where it can be found in the AWR report if that was important to you. Similarly, one could develop a taxonomy that maps a metric to where it appears in Oracle Enterprise Manager. Any categorization scheme is possible as long as you adhere to the MECE principle—mutually exclusive and collectively exhaustive. The instructions provided in this chapter, along with the SQL code in “AWR - flag - all metrics - taxonomy.sql,” will equip you to enhance the current taxonomies or build your own custom taxonomies to enhance your understanding of previously selected features using string pattern matching.

Another purpose of this chapter was to show you how a simple technique can be a powerful means to improve the DOPA process. The usefulness of taxonomic groupings lies to a great extent in the reporting phase of the DOPA process, and that is the topic of the next chapter where you will learn how to use the taxonomies to bring a greater depth of insight to tuning analytics.