Reprint: Generate Oracle Performance Excel curve using python one click

Article turned from: http://www.shsnc.cn/show-109-970-1.html

For further details, refer to: http://xlsxwriter.readthedocs.io/tutorial01.html

1. Why do you want to generate graphs

For Oracle database operations, sometimes it takes a week to see how the database is running, and the performance curve is the best representation of the performance changes in the database system. If you have EM with Oracle installed, it's not a problem to see this. But some databases are not installed, and EM is more difficult to achieve customized reporting requirements, it is difficult to send the chart to the user.

First look at the data to be generated:

There are additional requirements, such as the tool that generates the report that needs to be executed under Unix/linux, and can be easily inserted into Word by generating the Exel report directly with one click.

This article is about how to use Python to implement this process, primarily for Oracle DBAs who have a certain understanding of the programming language.

2. Why to use python

If you're talking about why you want to use Python, just a little bit about automating operations, you'll understand that Python is the king of programming in this field. The industry's most famous automated operations tools, Saltstack and Ansible, are based on Python.

3. How to do

Next, in the Linux environment where Oracle database 11g is already installed, how to achieve the final goal step-by-step.

3.1. installation python

In the majority of Linux and Unix itself will bring the python environment, but the version may be too old, may cause the later use of some of the packages are incompatible, it is recommended to make a new installation. Installation can be considered using the root account for installation, if no root access, using the Host account Oracle can also be installed.

Python version selected the latest version of 2.7.10, not selected 3.5 The main reason is that the software package under 3.x is not as rich as 2.x.

To install the Python example with an Oracle account:

It is recommended to use the source code method to install, the main is to download Python source code from http://www.python.org/ftp/python/. Select the latest version, download the. tgz file, and perform the usual configure, make, and make install steps.

Refer to the following:

The Python master installation directory set in this article is/home/oracle/opt/python2.7.10/

Switch to Oracle user and create the installation directory

Su-oracle
[Email protected] ~]$ mkdir-p/home/oracle/opt/python2.7.10/

Setting Python environment variables

[Email protected] ~]$ VI ~/.bash_profile
Join a Python path on path line
Path=/home/oracle/opt/python2.7.10/bin:/usr/sbin:/sbin:/usr/bin: $PATH: $HOME/bin:

Export PATH

Download the installation package

[Email protected] ~]$ wget https://www.python.org/ftp/python/2.7.10/Python-2.7.10.tgz--no-check-certificate

--2015-10-25 12:41:56--https://www.python.org/ftp/python/2.7.10/Python-2.7.10.tgz
Resolving www.python.org ... 103.245.222.223
Connecting to www.python.org|103.245.222.223|:443 ... Connected.
Warning:cannot Verify www.python.org ' s certificate, issued by '/c=us/o=digicert Inc/ou=www.digicert.com/cn=digicert SHA2 Extended Validation Server CA ':
Unable to locally verify the issuer ' s authority.
HTTP request sent, awaiting response ... OK
length:16768806 (16M) [Application/octet-stream]
...

Unzip the installation package and enter the directory

[Email protected] python-2.7.10]$ tar xfz python-2.7.10.tgz
[Email protected] python-2.7.10]$ CD Python-2.7.10

Execute Configure command

[Email protected] python-2.7.10]$./configure--prefix=/home/oracle/opt/python2.7.10/
Checking build system Type ... I686-pc-linux-gnu
Checking host system Type ... I686-pc-linux-gnu
Checking for--enable-universalsdk ... no
Checking for--with-universal-archs ... 32-bit
....
Config.status:pyconfig.h is unchanged
Creating Modules/setup
Creating modules/setup.local
Creating Makefile

Execute make command

[[email protected] python-2.7.10]$ make
...

Execute the Make install command

[[email protected] python-2.7.10]$ make install
...

Verifying python

[[email protected] python-2.7.10]$ Python
Python 2.7.10 (default, OCT 25 2015, 13:07:20)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "Help", "copyright", "credits" or "license" for more information.
>>>

To this Python 2.7.10 for Linux is installed complete

3.2. installation cx_oracle Module

The primary purpose of installing cx_oracle is to enable Python to connect to the Oracle database to obtain session records in the database

The prerequisites are

Oracle database software or client has been installed, version 10g or above, critical environment variable oracle_home, Ld_library_path configured correctly

The key settings for this article are as follows:

VI ~/.bash_profile
Export Oracle_base=/u01/app/oracle
Export Oracle_home= $ORACLE _base/product/11.2.0/dbhome_1
Export Ld_library_path= $ORACLE _home/lib

Refresh Environment Variables

SOURCE ~/.bash_profile

Python version recommendation is more than 2.6

Cx_oracle for https://pypi.python.org/pypi/cx_Oracle/#downloads, the most installed package for this article is cx_oracle-5.2.tar.gz

The installation and testing process is as follows:

Download

wget https://pypi.python.org/packages/source/c/cx_Oracle/cx_Oracle-5.2.tar.gz#md5= 6a49e1aa0e5b48589f8edfe5884ff5a5--no-check-certificate

Extract

Gunzip cx_oracle-5.2.tar.gz
Tar xvf Cx_oracle-5.2.tar

Performing the installation

CD cx_oracle-5.2
Python setup.py Install

Test Cx_oracle module is normal

>>> Import Cx_oracle
>>>

If the following Importerror prompt indicates that the database software is not installed properly or the critical environment changes are set incorrectly

>>> Import Cx_oracle
Traceback (most recent):
File "", Line 1, in
Importerror:libclntsh.so.11.1:cannot open Shared object file:no such file or directory

Test using Python to connect to an Oracle database

VI test_cx_oracle.py

#-*-Coding:utf-8-*-
ImportCx_oracle #引用模块cx_Oracle
Conn=cx_oracle.connect ( ' Scott/[email protected]:1521/orcl ') #连接数据库 in the format: username/password @ip: Port/Service Name
C=conn.cursor () #获取cursor
X=c.execute ( ' select * from Scott.emp ') #使用cursor进行各种操作
PrintX.fetchone () #打印一行
row = X.fetchall ()
forR inchRow: #循环打印所有行
PrintR
C.close () #关闭cursor
Conn.close () #关闭连接

Execute test Script

Python test_cx_oracle.py

A result similar to the following shows that Python is connected to the database and successfully obtains the data and prints out

[email protected] ~]$ python test_cx_oracle.py
(7369, ' SMITH ', ' clerk ', 7902, Datetime.datetime (1980, 20, 0, 0), 800.0, None;
(7499, ' ALLEN ', ' salesman ', 7698, Datetime.datetime (1981, 2, 20, 0, 0), 1600.0, 300.0, 30)
(7521, ' WARD ', ' salesman ', 7698, Datetime.datetime (1981, 2, 22, 0, 0), 1250.0, 500.0, 30)
(7566, ' JONES ', ' MANAGER ', 7839, Datetime.datetime (1981, 4, 2, 0, 0), 2975.0, None, 20)
(7654, ' MARTIN ', ' salesman ', 7698, Datetime.datetime (1981, 9, 28, 0, 0), 1250.0, 1400.0, 30)
(7698, ' BLAKE ', ' MANAGER ', 7839, Datetime.datetime (1981, 5, 1, 0, 0), 2850.0, None, 30)
(7782, ' CLARK ', ' MANAGER ', 7839, Datetime.datetime (1981, 6, 9, 0, 0), 2450.0, None, 10)
(7788, ' SCOTT ', ' ANALYST ', 7566, Datetime.datetime (1987, 4, 0, 0), 3000.0, None, 20)
(7839, ' KING ', ' president ', None, Datetime.datetime (1981, one, 0, 0), 5000.0, none, 10)
(7844, ' TURNER ', ' salesman ', 7698, Datetime.datetime (1981, 9, 8, 0, 0), 1500.0, 0.0, 30)
(7876, ' ADAMS ', ' clerk ', 7788, Datetime.datetime (1987, 5, 0, 0), 1100.0, None, 20)
(7900, ' JAMES ', ' clerk ', 7698, Datetime.datetime (1981, 3, 0, 0), 950.0, None, 30)
(7902, ' FORD ', ' ANALYST ', 7566, Datetime.datetime (1981, 3, 0, 0), 3000.0, None, 20)
(7934, ' MILLER ', ' clerk ', 7782, Datetime.datetime (1982, 1, 0, 0), 1300.0, None, 10)

In this connection, the Cx_oracle module installation test for Oracle is completed

3.3. installation Xlsxwriter Module

Why install the Xlsxwirter module, mainly used to read and write xlsx files in Python, that is, Excel files, and manipulate Excel to generate graphs

The following is the installation and testing process

: Https://pypi.python.org/pypi/XlsxWriter#downloads

Download

wget https://pypi.python.org/packages/source/X/XlsxWriter/XlsxWriter-0.7.7.tar.gz#md5= c68dca16927e2a919837c68ff63b1e5b--no-check-certificate

Extract

Gunzip xlsxwriter-0.7.7.tar.gz
Tar xvf Xlsxwriter-0.7.7.tar

Performing the installation

CD XlsxWriter-0.7.7
Python setup.py Install

Test Xlsxwriter module is normal

>>> Import Xlsxwriter
>>>

Test Write xlsx file

VI test_xlsx.py

#-*-Coding:utf-8-*-

ImportXlsxwriter

Workbook = Xlsxwriter. Workbook ( ' hello.xlsx ')
Worksheet = Workbook.add_worksheet ()

Worksheet.write ( ' A1 ', ' Hello World ')

Workbook.close ()

Download the generated file hello.xlsx, open with Excel to see, the resulting result is as follows:

As you can see, the A1 cell has been correctly written to Hello World.

To this, the Xlsxwriter module installation is complete.

3.4. write a one-click Build Script

Where does the data come from?

To generate graphs, there is always data.

The following statement allows you to query the session change data in the Oracle Database AWR report (prior authorization to the corresponding account, such as Grant Select any dictionary to Scott):

SELECT
To_char (Trunc (b.end_interval_time+10/60/24, ' hh24 '), ' yyyymmdd_hh24 ') Snap_time,
SUM (Decode (a.instance_number, 1, A.value)) "Inst_1",
SUM (Decode (A.instance_number, 2, A.value)) "Inst_2",
C.value Max_session,
D.name, a.snap_id
From Dba_hist_sysstat A, dba_hist_snapshot B, (select value from V$parameter where name= ' sessions ') C,v$database D
WHERE a.snap_id = b.snap_id
and A.instance_number = B.instance_number
and stat_name = ' logons current '
GROUP by d.name,a.snap_id, C.value, To_char (trunc (b.end_interval_time+10/60/24, ' hh24 '), ' yyyymmdd_hh24 ')
Order by snap_id;

There is no data in the inst_2 because the database for this connection is a single-instance database, so the data is empty.

Open Xlsxwriter official website, http://xlsxwriter.readthedocs.org/, to see how Excel graphs are generated.

Find the chart Examples on the homepage and go to the Example:line chart page.

Http://xlsxwriter.readthedocs.org/example_chart_line.html

The following official examples are given:

#######################################################################
#
# Example of creating Excel line charts with Python and Xlsxwriter.
#
# Copyright 2013-2015, John McNamara, [email protected]
#
ImportXlsxwriter

Workbook = Xlsxwriter. Workbook ( ' chart_line.xlsx ')
Worksheet = Workbook.add_worksheet ()
Bold = Workbook.add_format ({ ' Bold ': 1})

# ADD The worksheet data that the charts would refer to.
headings = [ ' number ', ' Batch 1 ', ' Batch 2 ']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]

Worksheet.write_row ( ' A1 ', headings, bold)
Worksheet.write_column ( ' A2 ', Data[0])
Worksheet.write_column ( ' B2 ', Data[1])
Worksheet.write_column ( ' C2 ', Data[2])

# Create a new chart object. In a embedded chart.
Chart1 = Workbook.add_chart ({ ' type ': ' line '})

# Configure the first series.
Chart1.add_series ({
' name ': ' =sheet1! $B ',
' Categories ': ' =sheet1! $A: $A $7 ',
' Values ': ' =sheet1! $B: $B $7 ',
})

# Configure second series. Note use of the alternative syntax to define ranges.
Chart1.add_series ({
' name ':       [ ' Sheet1 ', 0, 2],
' Categories ': [ ' Sheet1 ', 1, 0, 6, 0],
' Values ':     [ ' Sheet1 ', 1, 2, 6, 2],
})

# ADD a chart title and some axis labels.
Chart1.set_title ({ ' name ': ' Results of sample Analysis '})
Chart1.set_x_axis ({ ' name ': ' Test number '})
Chart1.set_y_axis ({ ' name ': ' Sample Length (mm) '})

# Set an Excel chart style. Colors with white outline and shadow.
Chart1.set_style (10)

# Insert the chart into the worksheet (with a offset).
Worksheet.insert_chart ( ' D2 ', Chart1, { ' X_offset ': 25, ' Y_offset ': 10})


Workbook.close ()

The following code can be obtained by combining the module part code of Cx_oracle with a slight modification:

#-*-Coding:utf-8-*-
#######################################################################
#
# Example of creating Excel line charts with Python and Xlsxwriter.
#
# Copyright 2013-2015, John McNamara, [email protected]
#
Import Xlsxwriter, cx_oracle, sys

Reload (SYS)
Sys.setdefaultencoding (' Utf-8 ')

conn = Cx_oracle.connect (' scott/[email PROTECTED]:1521/ORCL) # Connect to Database

c = conn.cursor () # Get cursor

x = C.execute ("'
SELECT
To_char (Trunc (b.end_interval_time+10/60/24, ' hh24 '), ' yyyymmdd_hh24 ') Snap_time,
SUM (Decode (a.instance_number, 1, A.value)) "Instance_1",
SUM (Decode (A.instance_number, 2, A.value)) "Instance_2",
C.value Max_session,
D.name, a.snap_id
From Dba_hist_sysstat A, dba_hist_snapshot B, (select value from V$parameter where name= ' sessions ') C,v$database D
WHERE a.snap_id = b.snap_id
and A.instance_number = B.instance_number
and stat_name = ' logons current '
GROUP by d.name,a.snap_id, C.value, To_char (trunc (b.end_interval_time+10/60/24, ' hh24 '), ' yyyymmdd_hh24 ')
ORDER BY snap_id
‘‘‘)
dbname = X.fetchone () [4]
row = X.fetchall ()

Workbook = Xlsxwriter. Workbook (' chart_line.xlsx ')
Worksheet = Workbook.add_worksheet ()
Bold = Workbook.add_format ({' Bold ': 1})

# ADD The worksheet data that the charts would refer to.
headings = [' Date_time ', ' inst_1 ', ' inst_2 ', ' maxsess ', ' DBNAME ', ' snap_id ',]

Worksheet.write_row (' A1 ', headings, bold)

# worksheet.write_column (' A2 ', row[0])
i = 0
For R in row:
Worksheet.write_row (' A ' + str (i + 2), Row[i])
i = i + 1

# Create a new chart object. In a embedded chart.
Chart1 = Workbook.add_chart ({' type ': ' Line '})

Chart1.add_series ({
' Name ': [' Sheet1 ', 0, 1],
' Categories ': [' Sheet1 ', 1, 0, I, 0],
' Values ': [' Sheet1 ', 1, 1, I, 1],
})

# Configure second series. Note use of the alternative syntax to define ranges.
Chart1.add_series ({
' Name ': [' Sheet1 ', 0, 2],
' Categories ': [' Sheet1 ', 1, 0, I, 0],
' Values ': [' Sheet1 ', 1, 2, I, 2],
})

# ADD a chart title and some axis labels.
Chart1.set_title ({' name ': dbname + ' number of database Sessions '})

# Set an Excel chart style. Colors with white outline and shadow.
Chart1.set_style (10)

# Insert the chart into the worksheet (with a offset).
Worksheet.insert_chart (' D2 ', Chart1, {' X_offset ': +, ' Y_offset ': 10})

C.close ()
Workbook.close ()

Save As Chart_line.py

Execute Python chart_line.py

Open the generated Excel file Chart_line.xlsx, and you'll see the resulting table

With only one line, because the connected single-instance database, if connected to the RAC database, generates a two-node chart.

4. Summary

This article in Python way from scratch to realize the Python connection Oracle database, one-click generation of Excel chart, only for the students of the operation of the reference, there are a lot of imperfect places, but also please forgive, if necessary to expand their own.

Of course, it can be implemented in Java or other ways, but Python is easy to use, with very little code, which makes it easy to implement the features we want. After all, life has many choices, choose one you like.

As for how individuals want to do this, the main reference is Liu Tians's "Python Automation operations: technology and best Practices," a step by step Baidu, Google.

Reprint: Generate Oracle Performance Excel curve using python one click