Oracle case: Analysis of 10053 tracking files

First, we will introduce some parameter descriptions related to the CBO computing cost and how to calculate the cost of CBO in queries. Next, we will provide a general method for analyzing 10053 trace files.

First, we will introduce some parameter descriptions related to the CBO computing cost and how to calculate the cost of CBO in queries. Next, we will provide a general method for analyzing 10053 trace files.

First, we will introduce some parameter descriptions related to the CBO computing cost and how to calculate the cost of CBO in queries. Next, we will provide a general method for analyzing 10053 trace files. Finally, we will discuss how to analyze 10053 trace files in detail by combining a specific trace file.

1. Oracle Case Study

Oracle case study is mainly a tool that provides information and knowledge about complex events, processes, processes, and a series of related events. Each case is written based on experience in solving practical problems.

Each Learning case contains a certain skill level. Skill Level refers to the skill level that readers of the document should possess before learning this case.

There are several levels:

Expert Level: rich experience in related subject fields

Intermediate: experience in related topic Fields

Entry level: no experience in related topic Fields

Recommended level in this case: Expert Level

2. Case study summary

This case uses the tracking document to describe the method system for analyzing the 10053 tracking document. Note: The 10053 event tracking file is mainly used to assist oracle developers and technical support in diagnosing optimizer problems. It varies with the new version or patch set. The main purpose of this article is not to provide a comprehensive reference for the 10053 tracking file, but to introduce how oracle engineers use this tracking file.

At the same time, we will look into how the CBO calculates the cost in the query and how the CBO finally obtains the execution plan.

It should be noted that the CBO algorithm varies with the version changes when estimating the cost.

Here we will analyze a bad execution plan and determine how the CBO calculates the cost and leads to a bad plan. We will keep track of 10053 in different places, but the main focus is on how the cost is calculated for this bad plan. A good execution plan is generally shorter than a bad execution plan (where indexes are not considered.

The reason for checking 10053 is generally to figure out why the CBO chooses this method. 10053 can help us answer questions such as "Why the index is not used. "Why does CBO select full table scan ?" . Generally, 10053 is not the first method to handle performance problems-in this regard, you can view the execution plan and use the tkprof tool to get better information. 10053 go deep into the analysis of the reasons for selection by CBO.

3. Case History

It takes nine hours to execute an SQL statement that is not prompted (a select statement containing three connections). If you use the "NO_INDEX" prompt, the execution will be completed within four minutes. A table uses partitioned tables and uses parallel queries. In addition, the user sets "OPTIMIZER_INDEX_CACHING" to 70 (I wonder why the user did this, we guess it may be because they didn't get the desired execution plan at the time ). This parameter reduces the I/O of a single index by 70%.

Use the 10053 trace to get the execution plan with no prompt ("bad") and the prompt ("good. The main difference between the two execution plans is that the execution plans that are not prompted use the nested loop connection method, and the inner layer connection uses the index full scan (not the index fast full scan) operation to act as the inner row set. The execution plan with the prompt is connected by hash, and the result set of the index fast full scan (IFF) operation is used as the inner row set.

Note: In this case, the use of the trace file generated by 10053 in the new version may change significantly.

In general, the new version of the tracking file is easier to read, and less work needs to be done in the "pre-analysis" section.