Trying Python and Pyplot for Database Performance Graphs

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:

monday

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Trying Python and Pyplot for Database Performance Graphs

  1. jimroll says:

    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.

  2. john says:

    can you confirm how were you plotting in excel?which sql’s were you running?

    • Bobby says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.