In the past I have used Excel to graph things related to Oracle database performance. I am trying out Python and the Pyplot library as an alternative to Excel. I took a graph that I had done in Excel and rewrote it in Python. The graph shows the CPU usage within the database by category. For example, I labeled the database CPU used by a group of web servers “WEBFARM1” on the graph.
Here is an example graph:
You can find most of this code in the Python section of my GitHub repository. Here is the code that I used to create the example graph above using some made up data: zip
To make this graph in Excel I was running a sqlplus script and cutting and pasting the output into a text file that I imported into Excel. Very manual. No doubt there are ways that I could have automated what I was doing in Excel. But I have studied Python as part of the edX classes I took so I thought I would give it a try.
Python let me write a program to run the graph from an icon on my desktop. I used the cx_Oracle package to pull the data from the database and Pyplot for the graph.
I’m running the Windows 32 bit version of Canopy Express for my Python development environment. This environment comes with Pylot so I just had to install cx_Oracle to have all the packages I needed to make the graph.
I think both Excel and Python/Pyplot still have value. Excel still seems easier for quick and dirty graphing. But I used Python to automate a report that I run every day with fewer manual steps. Probably could have done the same thing in Excel but I have recently studied Python so I was able to apply what I learned in my classes without a lot more effort.
Bobby
Bobby, look at dimStat utility. It is not clean at first, but it is very powerful at last. I’ve used it at our last benchmark testing at Oracle OSC, UK. It replaced EM12c, sar, iostat, ASM stats and more… And it’s free )) You don’t have to script any, just setup and configure templates( bookings) to have saved filters, agregation, set of params.
Thanks for the recommendation.
can you confirm how were you plotting in excel?which sql’s were you running?
I was running a sqlplus script, copying and pasting the output into a text file, and importing that text file into an Excel spreadsheet. I ran a slightly different query each week day. That’s why I wanted to put this in a program. I’m sure I could have done the same thing in Excel if I studied up on its features. Here is an edited version of the query that I ran to get Friday’s information. I’ve edited the real query to remove our real production server names.
select * from
(
select
CASE
WHEN MACHINE like ‘xyz%’ THEN ‘WEB’
WHEN MACHINE like ‘pdq%’ THEN ‘BATCH’
ELSE ‘OTHER’ END catagory,
to_char(sample_time,’YYYY-MM-DD’) friday_date,
(count(*)*10)/(10*3600*12) percent_cpu
from DBA_HIST_ACTIVE_SESS_HISTORY
where
to_char(SAMPLE_TIME,’DAY’)=’FRIDAY ‘ and
to_number(to_char(SAMPLE_TIME,’HH24′)) between 8 and 17 and
SESSION_STATE=’ON CPU’
group by
CASE
WHEN MACHINE like ‘xyz%’ THEN ‘WEB’
WHEN MACHINE like ‘pdq%’ THEN ‘BATCH’
ELSE ‘OTHER’ END,
to_char(sample_time,’YYYY-MM-DD’)
)
pivot
(
sum(percent_cpu)
for catagory in (
‘WEB’,
‘BATCH’,
‘OTHER’
)) order by friday_date;
xyz and pdq would be prefixes to the names of a group of machines like a farm of web servers.