ORACLE 10053 Incident Detailed

This time we mainly explain the Oracle 10053 events and experiments, many friends may not be very familiar with this event, because in the daily transport dimension use not a lot. Oracle 10046 and 10053 are unofficial trace SQL methods that are not available in official documents, but can be found on MOS. Sql_trace is an officially recommended method for trace SQL, which can be queried in official documents.

10053 events: A process that describes how Oracle chooses to execute the plan, and then outputs it to the trace file for our reference because we often see what resources are consumed by the execution plan, rather than how the execution plan is chosen.

10,053 scene: When the SQL statement is executed by the wrong execution plan, and cannot find the reason, then please use 10053来 to analyze the reason.

10053 Features:

(1) Only understand the Oracle Execution plan selection process

(2) The calculation formula for which the cost cannot be learned, because this is an Oracle internal business secret, and each Oracle version of the optimizer calculation formula is not the same gap is quite large, different versions of the same sentence, the cost is not the same, the optimizer is not very mature, still need to improve.

(3) In this we will focus on how the "cost" is calculated, and then we can understand how the implementation plan is chosen.

(4) In 10053 you can understand which factors affect the execution cost of SQL

(5) Oracle 8i cost equivalent IO resource consumption 9i after cost equivalent io+cpu+ Network + wait event + other costs

The weight of general IO Resources is relatively large CPU weight is smaller

10053 content:

Parameter area: Initializes parameters, suppressed parameters that allow Oracle to work

SQL zone: Executed SQL statement, whether to use bound variables, whether the conversion operation

System Information Area: Operating system statistics CPU frequency CPU Execution time IO addressing time single block read time multi-block read time

Object Statistics Area:

Data access Way: Access mode is not the same as the method of calculating cost, the whole table scan Walk index multiple table correlation cost is different

Associated query: Each table as a driving table to combine, select the "cost" of the smallest association, and which table in the former has no relationship

The final revision of the cost: Oracle will make a final revision to the cost of choice, making it more accurate and more reasonable

Select the final execution plan: The process is very fast and the millisecond is done.

Experimental environment

Leo1@leo1> select * from V$version; This is my Oracle edition.

BANNER

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

Oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

1. Verify the costing formula of the whole table scan and post the execution plan and calculation formula.

Leo1@leo1> Col sname for A20

Leo1@leo1> Col pname for A20

Leo1@leo1> Col pual1 for A30

Leo1@leo1> Col Pual2 for A30

Leo1@leo1> select * from sys.aux_stats$; Viewing operating system statistics

sname pname PVAL1 PVAL2

This column more highlights: http://www.bianceng.cn/database/Oracle/

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

Sysstats_info STATUS COMPLETED

Sysstats_info DStart 08-15-2009 00:49

Sysstats_info dstop 08-15-2009 00:49

Sysstats_info FLAGS 1

Sysstats_main CPUSPEEDNW 2657.0122

Sysstats_main Ioseektim 10

Sysstats_main iotfrspeed 4096

Sysstats_main Sreadtim

Sysstats_main Mreadtim

Sysstats_main Cpuspeed

Sysstats_main MBRC

Sysstats_main Maxthr

Sysstats_main Slavethr