This is the second blog entry for a series of posts related to the topic of “sizing.”
This time we are going to chart the cpu usage and calculate the average (mean), median, minimum, and maximum from the ASH data we standardized in previous post “Sizing post #1: How to standardize ASH data for sizing.”
In our previous post we ended up with a table called CPU_STANDARD with two columns SAMPLE_TIME and CPU_DATA. The SAMPLE_TIME contains 10 seconds equidistant times and the CPU_DATA column contains the number of active sessions for each sample time.
DESC cpu_standard Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_TIME DATE CPU_DATA NUMBER SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS'), cpu_data FROM cpu_standard ORDER BY 1; SAMPLE_TI CPU_DATA --------- ----------- ... ... 2016-09-14T16:48:50 1 2016-09-14T16:49:00 1 2016-09-14T16:49:10 4 2016-09-14T16:49:20 1 2016-09-14T16:49:30 10 2016-09-14T16:49:40 1 2016-09-14T16:49:50 3 2016-09-14T16:50:00 1 2016-09-14T16:50:10 1 ... ... 69120 rows selected.
The data in table CPU_STANDARD was collected from a Workload Repository with a retention period of only 8 days.
We can figure out the amount of days by two methods.
1. Numer of rows.
There are 69,120 rows in the CPU_STANDARD table. Each row represents a 10 second period because we have one sample every 10 seconds. There are 3,600 seconds in one hour which means there are 360 samples (one sample every 10 seconds) in each hour. Divide 69,120 by 360 to get the number of hours 69,120 / 360 = 192. Divide 192 by 24 hours to get the number of days. 192 / 24 = 8. The retention period was only 8 days.
2. Min and Max sample time.
SELECT MIN(sample_time) min_st, MAX(sample_time) max_st FROM cpu_standard; MIN_ST MAX_ST --------- --------- 06-SEP-16 14-SEP-16
Our cpu data is from September 6th to September 14th which is a period of 8 days.
I recommend to have 45 days retention period or at least 30 days to make sure we capture the activitiy that cycles every month, for example a monthly backup or the closing month accounting processes, however, for the purposes of this blog post we can work with the 8 days cpu data.
How about we get a chart for our cpu data to visualize how much our utilization of cpu our database is consuming. How can we chart 69,120 data points? Trying to chart 69,120 points in excel may not work and most likely we will get “Application not responding” message. In fact a chart with so many data points may not be as useful as we expect.
In order to have an idea on the workload of our database we can generate a chart but instead of using every 10 second sample we can generate one data point for every 360 samples (one data point per hour).
How do we summarize 360 data points into just one? We could generate an average (mean) or get the median or get the maximium value.
Lets use the maximum value of each hour to represent each hour in our chart. The following query truncates the date column to the hour, and gets the maximum value of each hour:
SELECT TO_CHAR(TRUNC(sample_time,'HH24'), 'YYYY-MM-DD"T"HH24') THE_HOUR, MAX(cpu_data) FROM cpu_standard GROUP BY TRUNC(sample_time,'HH24') ORDER BY 1; ... ... 2016-09-14T12 8 2016-09-14T13 27 2016-09-14T14 15 2016-09-14T15 12 2016-09-14T16 10 2016-09-14T17 7 2016-09-14T18 1 192 rows selected.
Now we have a more decent size case for our chart. We can chart 192 data points easily in excel.
We are going to first create a table with the hourly data
CREATE TABLE cpu_standard_hourly AS SELECT TO_CHAR(TRUNC(sample_time,'HH24'), 'YYYY-MM-DD"T"HH24') THE_HOUR, MAX(cpu_data) CPU_DATA FROM cpu_standard GROUP BY TRUNC(sample_time,'HH24') ORDER BY 1;
Once we have our table with only 192 data points in Sql Developer we export the data to excel format. If you haven’t exported table data in Sql Developer you will be surprised how easy it is, review documentation Database Express Edition 2 Day DBA 11.2 chapter 10 Exporting and Importing Metadata and Data.
When we open the exported data in excel we have two columns of data, the column titled THE_HOUR and the CPU_DATA column. This is the data we are going to use for our chart. One very simple example on how to create a line chart is shown in this page: MS Excel 2016: How to Create a Line Chart.
From the following chart we can observe the minimum utilization at 1 and the maximum utilization at 27.
We can also get those values with queries
SELECT MIN(cpu_data) MIN_CPU, MAX(cpu_data) MAX_CPU FROM cpu_standard_hourly; MIN_CPU MAX_CPU ----------- ----------- 1 27 SELECT * FROM ( SELECT the_hour, cpu_data FROM cpu_standard_hourly ORDER BY 2 ) WHERE rownum <=1; THE_HOUR CPU_DATA ------------- ----------- 2016-09-14T18 1 SELECT * FROM ( SELECT the_hour, cpu_data FROM cpu_standard_hourly ORDER BY 2 DESC ) WHERE rownum <=1; THE_HOUR CPU_DATA ------------- ----------- 2016-09-14T13 27
Remember that the 27 is the maximum utilization within that hour but not necessarily the cpu was at 27 active sessions the whole hour. It is just that the maximum sampled cpu data reached 27. Because the hourly data hides the detail of what happens within the hour we can chart data for the 10 second samples in the CPU_STANDARD table not for the whole 8 days retention period but only for smaller time periods of interest for example one hour.
Lets chart 360 points of that one hour “2016-09-14T13” that reached the maximum of 27 active sessions. The following query gets the data points
SELECT TO_CHAR(TRUNC(sample_time,'HH24'),'YYYY-MM-DD"T"HH24') sample_time, cpu_data FROM cpu_standard WHERE TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24') = '2016-09-14T13' ORDER BY 1; ... ... 2016-09-14T13 1 2016-09-14T13 1 2016-09-14T13 1 2016-09-14T13 1 2016-09-14T13 1 2016-09-14T13 1 2016-09-14T13 1 360 rows selected.
Lets create a table to hold exactly that data
CREATE TABLE cpu_standard_14T13 AS SELECT sample_time, cpu_data FROM cpu_standard WHERE TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24') = '2016-09-14T13' ORDER BY 1;
Exporting to excel and charting we get
The spike of 27 active sessions happened only for one sample, the rest is 1s and 2s
Scrolling to the right we can see other periods of time
There are some 3s, 4s, 6s, and 8s and 6s and the majority of the samples are 1s.
So far, we have created charts for data points representing the maximum sample of every hour and we also created a chart with 360 data points for one single hour to observe the details of what happens regarding cpu utilization within the hour.
Lets get some other statistics
The average (mean) and the median utilization of the database:
SELECT AVG(cpu_data) FROM cpu_standard; AVG(CPU_DATA) ------------- 2.452748843 SELECT MEDIAN(cpu_data) FROM cpu_standard; MEDIAN(CPU_DATA) ---------------- 2
Question is: How can we use this information to know what is the number of cpus that I really need to support the load of my database if some times it uses 27 and some times it uses 1 with an average of 2.45 and a median of 2?
Using the average of 2.45 or the median of 2 cpus as the requirement for this database is not realistic because the database will go above 2 cpus and will generate a lot of waits at those specific times. Using the maximum of 27 won’t be realistic either because I am wasting resources as those 27 were reached only once meaning it was only for a very short period of time in a logon storm fashion but the utilization went down again really quick.
Well, the answer is, we don’t know yet, the not so good mean and the bad median with ugly minimum and maximum don’t give me the answer yet but at least we have some knowledge about how our database behaves and how many cpus are active at specific times. We can say we got a profile of our workload. This profile can vary from database to database in the organization depending on what applications are connecting to the database, in fact the entity that defines the workload is the application or set of applications making use of the database.
Stay tuned for the next blog post on sizing matters.