Python implementation automatically generates Oracle AWR reports

One of the most interesting aspects of planning and developing a performance automation implementation framework is how to automatically obtain Oracle AWR reports during scenario execution through the framework. Although Oracle client-provided AWRRPT.SQL scripts can provide an interactive way to generate AWR reports, they are not directly used in the automation framework, requiring at least some modification to turn the interactive patterns into silent execution.

1. A problem analysis

After the analysis of the problem, there are two basic ways to solve it:

A. Deploy the shell script on the Oracle server and use the shell command to start Oracle's Sqlplus execution Autoawr.sql, where autoawr.sql is primarily used to obtain the required parameter values, and then calls the Oracle package DBMS_ Workload_repository.awr_report_html realize the automatic generation of AWR report;

B. Implement the dbms_workload_repository.awr_report_html of the AWR report directly in the performance framework via the local Sqlplus call Oracle package;

2. Comparison of two schemes

The comparison between the two approaches:

For a, the modified shell script and SQL script need to be deployed to the Oracle server in advance, the step is out of the framework, and the generated report is on the Oracle server, the framework provides a way to obtain the AWR on the remote Oracle locally for subsequent analysis and report output;

For B, when the framework implements this function, no additional deployment is required before the framework is used, making the framework more independent, easier to use, and the ability to generate reports directly in locally specified locations;

Essentially, the two ideas are the same, but for the performance framework, which side of the feature should be implemented, whether it is a performance framework or an Oracle server.

3. realization Ideas

By comparing the implementation end, we decide to implement this function in the performance framework, and briefly describe the implementation idea.

The implementation is divided into three steps:

First: Get the 4 parameter of awr_report_html function, namely Dbid,inst_num,l_bid,l_eid;

Second: The concatenation calls Awr_report_html and uses the spool to write the result to the HTML SQL statement;

Third: Call Sqlplus Execute SQL file, generate AWR;

4. awr_report_html functions

The awr_report_html function is as follows:

Functions are generally used as follows:

SELECT output from TABLE (dbms_workload_repository. Awr_report_html (dbid, inst_num,l_bid,l_eid,0));

It is important to note that:

1. Although by calling awr_report_html can select to HTML format text, in Python implementation I did not put the return results in the iterator, through the ReadLines method written to the HTML file, because the output contains some additional information , the generated HTML does not open successfully, because of the Sqlplus parameter configuration, because the use of spool directly written to the file method;

2. When using spool, set the following sqlplus configuration in the SQL script to avoid extraneous information or formatting problems in the output, set as follows:

Set Heading off

Set Trimout on

Set Trimspool on

Set Linesize 2500

Five python source

According to the above ideas, realize the source code as follows:

Other resources:

Python Introductory Course: http://i.youku.com/weiworld521

Python implementation automatically generates Oracle AWR reports