Summary of optimization methods for Oracle block (primarily SQL)

Oracle optimization is itself a difficult thing to do and everything involved. Let's talk about my optimization experience (for beginners only):

Many of the optimization experiences mentioned in the book include indexes, table structures, scalar queries, and database-level optimizations, but 80% of optimizations can be statement-level optimizations. The optimized objects include: procedure, function, and SQL.

For people who are familiar with Oracle databases, optimizations do not need to be done with any tools. But below I say two tools for SQL optimization: Dbms_profile and advisor two tools.

Of course, you can also optimize by executing the plan.

In the end, we will briefly describe several areas where the ADDM report needs to focus.

1, Dbms_profile This tool is not installed before 10g, need to manually perform $oracle_home/rdbms/admin/proftab.sql initialization. This tool is used to collect procedure and function based on the time spent on each statement, is the approximate amount.

2, the Advisor tool is for each SQL statement, through the Oracle built-in optimizer to give optimization recommendations.

Then for two tools can be used together, using Dbms_profile to filter the object process statements, through the Advisior for statement-level optimization.

First, the use of dbms_profile not to explain, after the detailed.

Ii. Examples of Advisior use

1) Defining Task tasks

Declare
My_task_name VARCHAR2 (30);
My_sqltext Clob;
Begin
my_sqltext:= ' select A.*,b.deptno from emp a,dept b where A.deptno=b.deptno ';
My_task_name:=dbms_sqltune.create_tuning_task
(Sql_text = My_sqltext,---optimized statement
user_name = ' SCOTT ',---optimized users
Scope = ' comprehensive ',---optimization range (comprehensive or limited)
Time_limit,---optimized time limit
Task_name = ' tuning_sql_text ',---task Name
Description = ' task to '); ---task description
End

2) Perform Tasks for optimization analysis

Begin Dbms_sqltune.execute_tuning_task (' Tuning_sql_text '); End

3) See if results have been obtained
SELECT * from User_advisor_tasks where task_name= ' tuning_sql_text ';

4) View Optimization results
Select Dbms_sqltune.report_tuning_task (' Tuning_sql_text ') from dual;

As a result of the optimization results, the optimizer does not give the result because the statements are relatively simple.

Iii. brief description of the ADDM report

1) Generate ADDM Report

There are two methods, one is generated through the EM tool, and the other is the ADDM report generated by the command. The following is a report generated through the EM tool.

The results are as follows:

2) optimization needs to consider several points are a, Memory Target, B, I/O, C, Wait stats, in the ADDM report is the following points

Some of the value anomalies need to be observed.

Iv. using the execution plan for optimization

There are many ways to optimize SQL using an execution plan. Can be through Dbms_xplan, plsql in the plan or CMD in the set autotrace on and so on.

Using an execution plan for SQL optimization requires more knowledge:

1) Understand the difference between bitmap index, hash index and B-tree index and the scope of application

2) Of course range partitioning, hash partitioning, list partitioning, and complex composite partitioning features

3) SQL statement Parsing order, parsing range of the index

4) Understanding and application of hint

5) Establishment of column index in table and planning of index space and data space, etc.

To better optimize SQL through an execution plan.

Optimization is a very complex but also is a relatively simple thing, which involves a lot of knowledge, this article simply lists several commonly used optimization methods, for SQL optimization sometimes need to be based on experience optimization.

For reference only.

This article is original, prohibit reprint!

Summary of optimization methods for Oracle block (primarily SQL)