Oracle developers analyze SQL Execution Plan concerns

Complicated and complex execution plans, developer eyes may be lost, resulting in a sexy and silly SQL statement running on the server.

There are a few important parts here, as for others, of course, not to be polite ,,

(I) Number of returned rows

The Oracle optimizer is a major indicator of cost-based and cost-effective evaluation, that is, the number of rows queried.

Generally, the return value is 50% or the return value of a large table exceeds of the total number of records, and the space for optimization is very small.

Marked:

(Ii) ratio of returned rows to logical reads

Experience: the logical read overhead of five or less lines is acceptable.

Marked:

Each row requires 7 logical reads.

(Iii) Aggregate Query

Note the following two points for this type of query:

① The returned row should be the number of rows in the scan table instead of 1

② General optimization tips: The index is treated as a thin Table without returning the Table (the return Table is marked as Table Access By index rowid)

Marked:

(Iv) accuracy of prediction rows

The Execution Plan contains the following columns: Rows, which are the Rows returned by Oracle predictions, and may not be prepared by TEMA in some cases.

Remember to compare the value with the actually returned row. If it is inaccurate, you should find the cause, such as the Statistical Information, histogram, high water level...

Marked:

(V) predicate Information

Predicate Information has two values: filter and access, where access is generally index read or hash join.

Focus on this bird. The most important thing is to check whether there is any data type conversion, which suppresses the index and increases the overhead.

Marked:

(Vi) Dynamic sampling

The table statistics have expired or deteriorated, or the table has not been analyzed at all.

Marked:

Okay. Write so much first, and make up later.

By David Lin

2013-06-

Always be a first-rate version of yourself, instead of a second-rate version of someone else