Oracle SQL Tuning Health Check Script

Oracle SQL Tuning Health Check Script

We focus on the performance of database system, the main task of database tuning is to optimize SQL. Good data architecture design, with application system middleware and writing a beautiful SQL, is the future of the system on-line no fatal performance problems of the strong guarantee.

In the CBO era, a SQL execution plan was diverse. The factors that affect the execution plan also change from the SQL writing rules of the past Rbo era to a comprehensive factor. This provides the basis for us to generate better execution plans, and it also gives us a lot of trouble with tuning.

At present, it is common practice, through the AWR report or debugging means to find a problem with a SQL, and then from the library Cache (or AWR) to extract the execution plan. At the same time, the corresponding statistical information needs to be collected manually.

In Mos[id 1366133.1], we found an Oracle non-public script that could help us collect all the health information about the execution of SQL and output it in HTML format. This article introduces this script tool and also treats it as a generalization.

1. Environment preparation

We chose Oracle 10g as an experimental environment.

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod

PL/SQL Release 10.2.0.1.0-production

CORE 10.2.0.1.0 Production

TNS for 32-bit windows:version 10.2.0.1.0-production

Nlsrtl Version 10.2.0.1.0–Production

Constructs an experimental SQL for demonstration purposes.

Sql> Select/*+ Demo */Emp.empno, emp.ename, Dept.dname

2 from EMP, dept

3 where Emp.deptno=dept.deptno;

EMPNO ename dname

----- ---------- --------------

7369 SMITH

7934 MILLER ACCOUNTING

(length reason, result set omitted ...) )

-At this point, the SQL already exists in the library cache;

Sql> Select sql_id from V$sqlarea where Sql_text like ' select/*+ demo */% ';

sql_id

-------------

Auurp0v54vjgc

2. Call Script

we can download it from the website to the build script sqlhc.sql (SQL Tuning health-check script. [ID 1366133.1]). It is then called in Sqlplus.  

D:\test>sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Wednesday December 28 22:10:18 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Sql> Conn Sys/[email protected] as Sysdba

is connected.

sql> @sqlhc. sql

--the license has a logo;

Parameter 1:

Oracle Pack License (Tuning or diagnostics) [y| N] (required)

Enter a value of 1: N

The PL/SQL process has completed successfully.

--The sql_id number of the inspection;

Parameter 2:

Sql_id of the SQL to be analyzed (required)

Enter a value of 2: AUURP0V54VJGC

This is followed by the process of generating an HTML-formatted report, which is then notified in the form of a file name.

Sqlth file has been created:

Sqlhc_orcl_ibmvs2a1bhcns0_10.2.0.1.0_auurp0v54vjgc_20111228221121.html.

Review this file and act upon its content.

Sql>

This allows us to find the generated HTML format report file in the specified directory.

D:\test>dir

The volume in drive D is not labeled.

The serial number of the volume is ca23-1191

Directory of D:\test

2011-12-28 22:11 <DIR>.

2011-12-28 22:11 <DIR>.

2011-12-28 22:11 101,335 Sqlhc.log

2011-12-28 21:49 109,841 Sqlhc.sql

2011-12-28 22:11 10,140 sqlhc_orcl_ibmvs2a1bhcns0_10.2.0.1.0_auurp0v

54vjgc_20111228221121.html

3 Files 221,316 bytes

2 Directories 2,439,000,064 bytes available

3. Results Report Analysis

In the report, it is divided into three main parts.

First, the background environment parameter is set. This includes the optimizer used by the SQL design and the associated data table statistics selection baseline.

Next is the related design data table, the index statistic information.

Finally, the execution plan information is used.

4. Conclusion

Using the SQL Tuning Heath-check script can help us to easily extract the execution plan and statistics of the relevant SQL. As a basis for our performance tuning.

However, I emphasize that, as a tuning staff, the basic knowledge, the principle of understanding is very important. Tools, scripts are just a means to help us improve our daily productivity.

Http://dev.yesky.com/183/30968683.shtml

Oracle SQL Tuning Health Check Script