Shulou(Shulou.com)06/02 Report--

In the New year, it says:

At the beginning of the new year, 2012 passed, we embarked on 2012, looking back on 2013, we paid a lot, hard work and sweat in exchange for knowledge and friendship, when our technology growth, I found a long way to be honorable. A hard work and a harvest, taking the road of technology is hard, lonely and long. Here, I would like to talk about my experience to the young men who have just started. Be psychologically prepared, you may embark on this road for a little vanity and life, but you also have to walk freely and steadily. When you look back on the road when you stand at the first milestone, you will find that your harvest is worthwhile. Your efforts are interesting, and you can have the courage to continue. I would like to thank Alantany tigerfish, Mr. Zhang, Mr. Pi, Mr. Yin, Chief dingjun, mm Beibei, and many people who have helped me. I can't grow up without your encouragement. After crying, let's start to add practical information:)

ORACLE deeply parses the 10053 event

This time we mainly talk about the oracle 10053 incident and experiments, many friends may not be very familiar with this event, because it is not used very much in daily operation and maintenance. Oracle 10046 and 10053 are unofficial trace sql methods, and the relevant information is not found in the official documentation, but can be found on MOS. Sql_trace is an officially recommended method of trace sql, which can be looked up in official documents.

10053 event: used to describe the process of how oracle chooses the execution plan, and then outputs it to the trace file for our reference, because we often see how the execution plan is executed and which resources are consumed, rather than how the execution plan is selected.

10053 scenario: when the SQL statement is executed in the wrong execution plan, and the reason can not be found, use 10053 to analyze the reason.

10053 Features:

(1) you can only understand the selection process of oracle execution plan.

(2) it is impossible to know the formula for calculating the cost, because it is a trade secret within oracle, and the calculation formula of the optimizer of each oracle version is different, and the cost of different versions of the same statement is also different. The optimizer is not very mature and needs to be improved.

(3) the key thing we need to understand in this is 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 implementation cost of sql.

(5) oracle 8i cost equivalent IO resource consumption after 9i cost equivalent IO+CPU+ network + wait events + other costs

Generally speaking, the weight of IO resources is larger than that of CPU resources.

10053 content:

Parameter area: initialization parameters, implicit parameters, these parameters can control the way oracle works

SQL area: the SQL statement executed, whether binding variables are used, and whether the conversion operation has been performed

System information area: operating system statistics cpu main frequency CPU execution time IO addressing time single block read time multi-block read time

Object Statistics area:

Data access method: the method of calculating the cost is also different if the access method is different, and the cost of multi-table association is different when the whole table is scanned.

Association query: combine each table as a driver table, and choose the association mode with the lowest "cost", regardless of which table comes first.

Final revision of the cost: oracle will make a final correction to the selected price to make it more accurate and reasonable

Choose the final execution plan: the process is so fast that it can be done in milliseconds.

Experimental environment

LEO1@LEO1 > select * from 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 cost calculation formula of the full 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$; view operating system statistics

SNAME PNAME PVAL1 PVAL2

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

Description

Aux_stats$ is a sys administrator user under a base table suffix of $, you must write schema to query, the so-called base table is to provide data for the dynamic performance view of the original table, because the base table is very important, oracle provisions do not allow direct access to and modify the base table, if you know these then another. Operating system statistics are recorded in this table. Oracle uses operating system statistics to correct the cost of the execution plan, which means that this information is one of the factors that affect cost calculation.

Note: if oracle collects operating system statistics, CBO uses the workload statistics model to calculate the cost

If oracle does not collect operating system statistics, then CBO uses non-workload statistical mode to calculate the cost. If there is no parameter value for MBRC above, it means that operating system statistics have not been collected.

The formulas for calculating the cost of the two modes are different.

SNAME: refers to operating system statistics

PNAME:parameter name parameter name

PVAL1: parameter valu

PVAL2: parameter valu

Parameter interpretation

FLAGS: Fla

CPUSPEEDNW: CPU main frequency in non-workload statistics mode, directly from hardware

IOSEEKTIM:IO addressing time (milliseconds), directly from the hardware

IOTFRSPEED:IO transfer rate (bytes / millisecond)

SREADTIM: average time to read a single block of data

MREADTIM: average time to read multiple data blocks

CPUSPEED: CPU main frequency in workload statistics mode, and a reasonable value is evaluated according to the current workload.

After collecting statistics, MBRC:oracle evaluates that a multi-block read can read several data blocks db_file_multiblock_read_count.

MAXTHR: maximum IO throughput (bytes / second)

SLAVETHR: average IO throughput (bytes / second)

What is the use of the last six parameters that can only be obtained after oracle has collected statistics? Let me explain.

When CBO calculates the cost of SQL statements, it needs to use object statistics such as database objects such as table indexes, and operating system statistics such as CPU cycles, IO speed, data block read time, etc., and choose the execution plan that takes the least time as the best execution plan.

Oracle uses dbms_stats.gather_system_stats stored procedures to collect operating system statistics, and the collected data is stored in the sys.aux_stats$ table. If we do the collection operation, there will be statistics. If we do not do it, there will be no statistics. The two methods of calculating the cost are different, which will be discussed later.

Dbms_stats.gather_system_stats syntax

Execute dbms_stats.gather_system_stats (

Gathering_mode varchar2 default 'noworkload'

Interval integer default null

Stattab varchar2 default null

Statid varchar2 default null

Statown varchar2 default null)

explain

Gathering_mode parameter, the default value is "noworkload", and can also be set to the meaning of "workload"

Noworkload: non-workload statistics mode. The data collected are all from hardware.

Workload: workload statistics mode. The collected data need to be counted within a specific database load interval, so that the data can truly reflect the operating system parameters of the database (need to perform sql evaluation)

Interval: you can specify the time interval for collecting statistics, for example, 5 collecting statistics for 5 minutes

Command: execute dbms_stats.gather_system_stats ('noworkload',5)

The START and STOP keywords decide when to start and when to end collecting statistics.

Command: execute dbms_stats.gather_system_stats ('start')

The upper and lower instructions are executed at an interval of 3 minutes, and then the statistics of these 3 minutes are written into the sys.aux_stats$ table

Execute dbms_stats.gather_system_stats ('stop')

Note: there is a MBRC parameter above that I would like to talk about. It is the abbreviated Chinese translation of initialization parameter db_file_multiblock_read_count: "how many data blocks can be read at a time?" or can read several data blocks at a time. If statistics are collected, CBO will use MBRC to calculate the cost. If statistics are not collected, CBO will use this initialization parameter db_file_multiblock_read_count to calculate the cost.

LEO1@LEO1 > show parameter db_file_multiblock_read_count this is the default value of the parameter on my machine

NAME TYPE VALUE

-

Db_file_multiblock_read_count integer 79

LEO1@LEO1 > show parameter db_block_size one of our chunks is 8k

NAME TYPE VALUE

Db_block_size integer 8192

The value of this parameter is not infinite, and the oracle on most platforms is 128. General oracle block size = 8k

If there are other restrictions to be deducted from this 1m, the maximum value of the initialization parameter db_file_multiblock_read_count is set at 128m, which means that 1m is the maximum IO limit for most operating systems.

79*8k=632K

test

LEO1@LEO1 > drop table leo1 purge; clear the environment

Table dropped.

LEO1@LEO1 > create table leo1 as select * from dba_objects; create leo1 table

Table created.

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (collect statistics for tables

Wnname= > 'leo1', user name

Tabname= > 'leo1', table name

Cascade= > true, cascading operation

Estimate_percent= > null, full table sampling

Method_opt= > 'for all columns size 1'); do not do histogram analysis to reduce the impact of cost calculation

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1 > show parameter db_file_multiblock_read_count

NAME TYPE VALUE

-

Db_file_multiblock_read_count integer 79

LEO1@LEO1 > alter session set db_file_multiblock_read_count=16; modify multi-block read parameters to 16 to facilitate calculation

Session altered.

LEO1@LEO1 > show parameter db_file_multiblock_read_count

NAME TYPE VALUE

-

Db_file_multiblock_read_count integer 16

LEO1@LEO1 > select * from sys.aux_stats$; does not collect operating system statistics

SNAME PNAME PVAL1 PVAL2

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

We do not collect operating system statistics, so CBO uses the non-workload statistics model (noworkload) to calculate the cost

The total data block of LEO1@LEO1 > select blocks from user_tables where table_name='LEO1'; LEO1 table is 1051

BLOCKS

-

1051

LEO1@LEO1 > set autotrace trace explain

LEO1@LEO1 > select * from leo1

Execution Plan

Plan hash value: 2716644435

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 71968 | 6817K | 233 (1) | 00:00:03 |

| | 1 | TABLE ACCESS FULL | LEO1 | 71968 | 6817K | 233 (1) | 00:00:03 |

The cost of the full table scan is equal to 233, of which the CPU cost accounts for 1% of the total weight.

#

The formula for calculating the cost is as follows:

Cost = (

# SRds * sreadtim +

# MRds * mreadtim +

CPUCycles / cpuspeed

) / sreadtime

# SRds-number of number of single block reads single block reads

# MRds-number of number of multi block reads multi-block reads

# CPUCyles-number of CPU cycles one CPU cycle

Average time for sreadtim-single block read time to read a single block of data

Average time for mreadtim-multi block read time to read multiple data blocks

Cpuspeed-CPU cycles per second CPU cycles per second

Note: if oracle collects operating system statistics, CBO uses the workload statistics model to calculate the cost

If oracle does not collect operating system statistics, then CBO uses non-workload statistical mode to calculate the cost. We are now in "non-workload statistical mode".

# SRds=0, because it is a full table scan, and a single block is read as 0, all of which use multiple blocks

# number of blocks / multi-block read parameter of MRds= table = 1051Universe 65.6875

Mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42

Sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles equals CPU_COST in PLAN_TABLE

LEO1@LEO1 > explain plan for select * from leo1

Explained.

LEO1@LEO1 > select cpu_cost from plan_table

CPU_COST

-

38430873

Cpuspeed equals CPUSPEEDNW= 2657.0122

COST=65.6875*42/12+38430873/2657.0122/12/1000 (milliseconds converted into seconds) = 229.90625 "1.20532" 231.11157

229.90625 is the price of IO.

1.20532 is the price of CPU.

The COST calculated by hand is equal to 232 by rounding, which is different from that seen by us, which is caused by the implicit parameter _ table_scan_cost_plus_one parameter.

LEO1@LEO1 > conn / as sysdba switch to sys to view implicit parameters

SYS@LEO1 > SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi Xreksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm LIKE'% _ table_scan_cost_plus_one%'; 2 3 4 5

NAME VALUE DESCRIB

- --

_ table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one

According to the description of this parameter, in the case of table full scan and index fast full scan, the cost+1 will be 232mm-1m-233

Let's disable the _ table_scan_cost_plus_one parameter to see the cost change.

SYS@LEO1 > alter session set "_ table_scan_cost_plus_one" = false; disabled

Session altered.

SYS@LEO1 > SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi Xreksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm LIKE'% _ table_scan_cost_plus_one%'; 2 3 4 5 effective

NAME VALUE DESCRIB

- --

_ table_scan_cost_plus_one FALSE bump estimated full table scan and index ffs cost by one

SYS@LEO1 > select * from leo1.leo1

Execution Plan

Plan hash value: 2716644435

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 71968 | 6817K | 232 (1) | 00:00:03 |

| | 1 | TABLE ACCESS FULL | LEO1 | 71968 | 6817K | 232 (1) | 00:00:03 |

The COST obtained this time is equal to 232, which matches the calculated value exactly, which is the result of disabling the implicit parameter.

SYS@LEO1 > alter session set db_file_multiblock_read_count=32;. Let's modify the multi-block read parameters.

Session altered.

SYS@LEO1 > select * from leo1.leo1

Execution Plan

Plan hash value: 2716644435

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 71968 | 6817K | 204K (1) | 00:00:03 |

| | 1 | TABLE ACCESS FULL | LEO1 | 71968 | 6817K | 204K (1) | 00:00:03 |

# SRds=0, because it is a full table scan, and a single block is read as 0, all of which use multiple blocks

# number of blocks / multi-block read parameter of MRds= table = 1051 user 32 blocks 32.84375

Mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+32*8192/4096=74

Sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles=38430873

Cpuspeed equals CPUSPEEDNW= 2657.0122

COST=32.84375*74/12+38430873/2657.0122/12/1000 (milliseconds converted into seconds) = 202.53645 seconds 1.20532 seconds 203.74177

Rounding equals 204, consistent with the COST=204 in the implementation plan.

Summary: it can be concluded from the experiment that in oracle 11gR2, the calculation formula of COST in the non-workload statistical mode of full table scan is still the same as that of 9i/10g. At the same time, we also see that the IO cost accounts for a large part of the total cost weight, which is the main factor affecting the efficiency of SQL, which needs our more attention.

two。 The cost calculation formula of B-tree index Unique scan is given, and the execution plan and calculation formula are posted.

The cost calculation formulas for various types of CBO are as follows:

Full table scan

Full table scan cost= HWM/dbf_mbrc

Index unique scan

Unique scan cost = blevel + 1

Index fast full scan

Fast Full Scan cost=leaf_blocks/adj_mbrc

Access only the index, not the original table scan

Index-only cost = Blevel + effective index selectivity * leaf_blocks

Index range scan

Range Cost = Blevel + effectivity index selectivity* leaf_blocks

+ effective table selectivity * clustering_factor

Nested loop association

Nested loop join cost = outer access cost + (inner access cost * outer cardinality)

Sort merge Association

Sort merge join cost = outer access cost + inner access cost + sort costs

Hash correlation

Hash join cost = (outer access cost * # of hash partitions) + inner access cost

Experiment

LEO1@LEO1 > drop table leo2 purge; cleanup the environment

Table dropped.

LEO1@LEO1 > create table leo2 as select * from dba_objects; create leo2 table

Table created.

LEO1@LEO1 > create index idx_leo2 on leo2 (object_id); create idx_leo2

Index created.

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (collect statistics for tables

Wnname= > 'leo1', user name

Tabname= > 'leo2', table name

Cascade= > true, cascading operation

Estimate_percent= > null, full table sampling

Method_opt= > 'for all columns size 1'); do not do histogram analysis to reduce the impact of cost calculation

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

Analysis must be done. If there is no analysis in the table, the following statistics will be lost.

LEO1@LEO1 > select index_name,blevel,leaf_blocks,clustering_factor,num_rows,distinct_keys from dba_indexes where index_name='IDX_LEO2'

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS

-

IDX_LEO2 1 159 1076 71968 71968

BLEVEL: the number of index layers 1 means only 1 layer

LEAF_BLOCKS: the number of leaf blocks of the index tree is 159

CLUSTERING_FACTOR: index clustering factor

NUM_ROWS: the number of indexed rows 71968 matches the number of data rows

DISTINCT_KEYS: different index key values of 71968

LEO1@LEO1 > select count (*) from leo2

COUNT (*)

-

71968

LEO1@LEO1 > select * from leo2 where object_id=10000

Execution Plan

Plan hash value: 2495991774

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 97 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | LEO2 | 1 | 97 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | IDX_LEO2 | 1 | | 1 (0) | 00:00:01 |

-

COST=2, where the CPU cost = 0, the equivalent query has nothing to do with the number of indexes, and the consumption of CPU resources can be ignored

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 10000)

Formula

Unique scan cost = blevel + 1

The COST=1 of INDEX UNIQUE SCAN is blevel,CBO to see how many layers of indexes need to be recursive, which is consistent with the blevel in the statistics.

The cost of TABLE ACCESS BY INDEX ROWID's COST=1 accessing the table through the index rowid

So eventually COST=1+1=2

3. To analyze the generation process of a SQL execution plan through the 10053 event, it is necessary to post the relevant information in the trace and the necessary text description.

test

LEO1@LEO1 > drop table leo3 purge; cleanup the environment

Table dropped.

LEO1@LEO1 > drop table leo4 purge

Table dropped.

LEO1@LEO1 > create table leo3 as select * from dba_objects; create leo3 table

Table created.

LEO1@LEO1 > create table leo4 as select * from leo3 where rownum select count (*) from leo4; this is a small table

COUNT (*)

-

ninety-nine

LEO1@LEO1 > create index idx_leo3 on leo3 (object_id); index created

Index created.

LEO1@LEO1 > create index idx_leo4 on leo4 (object_id); ditto

Index created.

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (leo3 table for statistical analysis)

Wnname= > 'leo1'

Tabname= > 'leo3'

Cascade= > true

Estimate_percent= > null

Method_opt= > 'for all columns size 1')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (leo4 table for statistical analysis)

Wnname= > 'leo1'

Tabname= > 'leo4'

Cascade= > true

Estimate_percent= > null

Method_opt= > 'for all columns size 1')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1 > alter session set events' 10053 trace name context forever,level 1; start the 10053 event

There are 2 level,1 and 2 level,1 in 10053 events. Level 1 is much more detailed than level 2.

Session altered.

LEO1@LEO1 > select count (*) from leo3,leo4 where leo3.object_id=leo4.object_id; execute SQL

COUNT (*)

-

ninety-nine

LEO1@LEO1 > alter session set events' 10053 trace name context off'; shutdown 10053 event

Session altered.

LEO1@LEO1 > select value from v$diag_info where name='Default Trace File'; the trace written by the current session

VALUE

-

/ u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc

Let's take a look at the relevant information in the trace file.

The parameter area contains initialization parameters and implicit parameters, etc.

* *

-Current SQL Statement for this session (sql_id=fh7dku2xy52rc)-the SQL_ID of this session

Select count (*) from leo3,leo4 where leo3.object_id=leo4.object_id

* * *

The following abbreviations for Legend are trace identifiers used by the optimizer.

The following abbreviations are used by optimizer trace.

CBQT-cost-based query transformation

JPPD-join predicate push-down

OJPPD-old-style. (non-cost-based) JPPD

FPD-filter push-down

PM-predicate move-around

CVM-complex view merging

SPJ-select-project-join

... .

Compilation Environment Dump

Optimizer_mode_hinted = false

Optimizer_features_hinted = 0.0.0

Parallel_execution_enabled = true

Parallel_query_forced_dop = 0

Parallel_dml_forced_dop = 0

Parallel_ddl_forced_degree = 0

These are the default values of parameters.

...

* *

Column Usage Monitoring is ON: tracking level = 1 identifies the time level1 level used by the 10053 event

* *

SQL area SQL query conversion merge block count statistics

* *

Query transformations (QT)

* *

****************

QUERY BLOCK TEXT query block text, which SQL statement is executed

****************

Select count (*) from leo3,leo4 where leo3.object_id=leo4.object_id

Operating system statistics area

-

SYSTEM STATISTICS INFORMATION

-

Using NOWORKLOAD Stats is based on non-workload statistical model

CPUSPEEDNW: 2657 millions instructions/sec (default is 100) CPU main frequency in non-workload statistics mode

IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IO transfer rate (bytes / millisecond)

IOSEEKTIM: 10 milliseconds (default is 10) IO addressing time (milliseconds)

MBRC:-1 blocks (default is 8) several blocks can be read at a time

Basic statistics (object-level statistics) OLAP system with object-level statistics is sufficient

* *

BASE STATISTICAL INFORMATION, these statistics come from the view.

***********************

Table Stats:: from the user_tables view

Table: LEO4 Alias: LEO4

# Rows: 99 # Blks: 5 AvgRowLen: 75.00

Average length of number of rows and blocks

Index Stats:: from the user_indexes view

Index: IDX_LEO4 Col#: 4

LVLS: 0 # LB: 1 # DK: 99 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00

How many layers of index leaf blocks, how many unique key values, how many leaf blocks, how many data block clustering factors are there?

***********************

Table Stats::

Table: LEO3 Alias: LEO3

# Rows: 71969 # Blks: 1051 AvgRowLen: 97.00

Average length of number of rows and blocks

Index Stats::

Index: IDX_LEO3 Col#: 4

LVLS: 1 # LB: 159 # DK: 71969 LB/K: 1.00 DB/K: 1.00 CLUF: 1078.00

How many layers of index leaf blocks, how many unique key values, how many leaf blocks, how many data block clustering factors are there?

Different costs of Access path analysis for LEO3 LEO3 table access path

* *

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for LEO3 [LEO3]

Table: LEO3 Alias: LEO3

Card: Original: 71969.000000 Rounded: 71969 Computed: 71969.00 Non Adjusted: 71969.00

Original row number approximate exact value non-correction value

Access Path: TableScan full table scan cost

Cost: 286.71 Resp: 286.71 Degree: 0 Total cost = 286.71

Cost_io: 286.00 Cost_cpu: 22598123 Total cost = IO cost + CPU cost

Resp_io: 286.00 Resp_cpu: 22598123 parallel access cost

Access Path: index (index (FFS)) index fast full scan

Index: IDX_LEO3

Resc_io: 45.00 resc_cpu: 9768589 Serial access cost = 45 (because the index is serially stored)

Ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/71969=0.000013 index selection

Ix_sel_with_filters index selection rate with filter conditions

Access Path: index (FFS)

Cost: 45.31 Resp: 45.31 Degree: 1 Index parallel access cost = 45.31 > 45 (Serial access cost)

Cost_io: 45.00 Cost_cpu: 9768589, so choose serial access

Resp_io: 45.00 Resp_cpu: 9768589 parallelism = 1

Access Path: index (FullScan) index full scan

Index: IDX_LEO3

Resc_io: 160.00 resc_cpu: 15533230 serial access cost = 160. this is relatively high.

Ix_sel: 1.000000 ix_sel_with_filters: 1.000000

Cost: 160.49 Resp: 160.49 Degree: 1 parallelism = 1

Best:: AccessPath: IndexFFS

Index: IDX_LEO3

Cost: 45.31 Degree: 1 Resp: 45.31 Card: 71969.00 Bytes: 0

#

Different costs of Access path analysis for LEO4 LEO4 table access path

* *

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for LEO4 [LEO4]

Table: LEO4 Alias: LEO4

Card: Original: 99.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00

Original row number approximate exact value non-correction value

Access Path: TableScan full table scan cost

Cost: 3.00 Resp: 3.00 Degree: 0 Total cost = 3

Cost_io: 3.00 Cost_cpu: 56397 IO cost + CPU cost

Resp_io: 3.00 Resp_cpu: 56397 parallel access cost

Access Path: index (index (FFS)) index fast full scan

Index: IDX_LEO4

Resc_io: 2.00 resc_cpu: 19001 Serial access cost = 2

Ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/99=0.01 index selection

Ix_sel_with_filters index selection rate with filter conditions

Access Path: index (FFS)

Cost: 2.00 Resp: 2.00 Degree: 1 Index parallel access cost = 2, parallelism = 1

Cost_io: 2.00 Cost_cpu: 19001

Resp_io: 2.00 Resp_cpu: 19001

Access Path: index (FullScan) index full scan

Index: IDX_LEO4

Resc_io: 1.00 resc_cpu: 26921 Serial access cost = 1, this is the lowest, this is it

Ix_sel: 1.000000 ix_sel_with_filters: 1.000000

Cost: 1.00 Resp: 1.00 Degree: 1

Best:: AccessPath: IndexRange

Index: IDX_LEO4

Cost: 1.00 Degree: 1 Resp: 1.00 Card: 99.00 Bytes: 0

Associated query-selection of driven tables

Statistics and calculation of OPTIMIZER STATISTICS AND COMPUTATIONS Optimizer

* *

GENERAL PLANS chooses execution plan

* *

Considering cardinality-based initial join order.

Permutations for Starting Table: 0

Join order [1]: objects associated with LEO4 [LEO4] # 0 LEO3 [LEO3] # 1

***************

Now joining: LEO3 [LEO3] # 1 now use leo4 small table to associate leo3 large table, and leo4 as driver table

***************

There are 99 items in the NL Join nested loop associated leo4 table, and the small table is the driven table.

Driver table Outer table: Card: 99.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 3

Access path analysis for LEO3

Inner table: LEO3 Alias: LEO3

Access Path: TableScan full table scan-nested loop associated COST=28253.17

NL Join: Cost: 28253.17 Resp: 28253.17 Degree: 1

Cost_io: 28183.00 Cost_cpu: 2237241142

Resp_io: 28183.00 Resp_cpu: 2237241142 parallel access cost

Access Path: index (index (FFS)) index fast full scan

Index: IDX_LEO3

Resc_io: 43.08 resc_cpu: 9768589 serial access cost

Ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Inner table: LEO3 Alias: LEO3

Access Path: index (FFS)

NL Join: Cost: 4296.33 Resp: 4296.33 Degree: 1 parallel access

Cost_io: 4266.00 Cost_cpu: 967117228

Resp_io: 4266.00 Resp_cpu: 967117228

Access Path: index (AllEqJoinGuess)

Index: IDX_LEO3

Resc_io: 1.00 resc_cpu: 8171

Ix_sel: 0.000014 ix_sel_with_filters: 0.000014

NL Join (ordered): Cost: 100.03 Resp: 100.03 Degree: 1

Cost_io: 100.00 Cost_cpu: 835894

Resp_io: 100.00 Resp_cpu: 835894

Best NL cost: 100.03 leo4 is the driver table, small table is the driver table, the final cost is 100.03

Resc: 100.03 resc_io: 100.00 resc_cpu: 835894 Serial cost IO cost + CPU cost

Resp: 100.03 resp_io: 100.00 resc_cpu: 835894 cost of parallelism

Outer table: LEO4 Alias: LEO4

SM Join sorts and then merges associations

SM cost: 268.06 cost 268.06

Resc: 268.06 resc_io: 265.00 resc_cpu: 97470464

Resp: 268.06 resp_io: 265.00 resp_cpu: 97470464

HA Join hash association

HA cost: 47.03.The cost is 47.03.It is best to hash at the lowest cost.

Resc: 47.03 resc_io: 46.00 resc_cpu: 32949334

Resp: 47.03 resp_io: 46.00 resp_cpu: 32949334

Best:: JoinMethod: Hash final association method: hash hash

Cost: 47.03Degree: 1 Resp: 47.03Card: 99.00Bytes: 8 returns the number of records + bytes

***************

Now joining: LEO4 [LEO4] # 0 now use leo3 large table to associate leo4 small table, and leo3 as driver table

***************

There are 71969 items in the NL Join nested loop associated leo3 table, and the large table is the driver table.

Outer table: Card: 71969.00 Cost: 45.31 Resp: 45.31 Degree: 1 Bytes: 5

Access path analysis for LEO4

Inner table: LEO4 Alias: LEO4

Access Path: TableScan

NL Join: Cost: 97632.61 Resp: 97632.61 Degree: 1

Cost_io: 97505.00 Cost_cpu: 4068618676

Resp_io: 97505.00 Resp_cpu: 4068618676

Access Path: index (index (FFS))

Index: IDX_LEO4

Resc_io: 0.27 resc_cpu: 19001

Ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Inner table: LEO4 Alias: LEO4

Access Path: index (FFS)

NL Join: Cost: 19581.20 Resp: 19581.20 Degree: 1

Cost_io: 19538.00 Cost_cpu: 1377283224

Resp_io: 19538.00 Resp_cpu: 1377283224

Access Path: index (AllEqJoinGuess)

Index: IDX_LEO4

Resc_io: 0.00 resc_cpu: 1050

Ix_sel: 0.010101 ix_sel_with_filters: 0.010101

NL Join (ordered): Cost: 47.68Resp: 47.68Degree: 1

Cost_io: 45.00 Cost_cpu: 85336039

Resp_io: 45.00 Resp_cpu: 85336039

Best NL cost: 47.68Last cost of nested loop association 47.68

Resc: 47.68 resc_io: 45.00 resc_cpu: 85336039

Resp: 47.68 resp_io: 45.00 resc_cpu: 85336039

SM Join sorts and then merges associations

SM cost: 269.06 cost 269.06

Resc: 269.06 resc_io: 265.00 resc_cpu: 129384180

Resp: 269.06 resp_io: 265.00 resp_cpu: 129384180

Hash join: Resc: 106.17 Resp: 106.17 [multiMatchCost=0.00] Hash correlation, cost = 106.17

Final cost for query block SEL$1 (# 0)-All Rows Plan:

The final cost of Best join order: 1 is 47.0334, and the table is driven by leo4 small table.

Cost: 47.0334 Degree: 1 Card: 99.0000 Bytes: 792

Resc: 47.0334 Resc_io: 46.0000 Resc_cpu: 32949334

Resp: 47.0334 Resp_io: 46.0000 Resc_cpu: 32949334

The Choice of SQL execution Plan

=

Plan Table

=

-- +

| | Id | Operation | Name | Rows | Bytes | Cost | Time | |

-- +

| | 0 | SELECT STATEMENT | 47 | | |

| | 1 | SORT AGGREGATE | | 1 | 8 |

| | 2 | HASH JOIN | | 99 | 792 | 47 | 00:00:01 |

| | 3 | INDEX FULL SCAN | IDX_LEO4 | 99 | 297 | 1 | 00:00:01 |

| | 4 | INDEX FAST FULL SCAN | IDX_LEO3 | 70K | 351k | 45 | 00:00:01 |

-- +

Predicate Information:

--

2-access ("LEO3". "OBJECT_ID" = "LEO4". "OBJECT_ID")

The selected execution plan matches the results of the above analysis

Let's take a look at our real implementation of the plan.

LEO1@LEO1 > set autotrace trace exp

LEO1@LEO1 > select count (*) from leo3,leo4 where leo3.object_id=leo4.object_id

Execution Plan

Plan hash value: 172281424

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 8 | 47 (3) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 8 |

| | * 2 | HASH JOIN | | 99 | 792 | 47 (3) | 00:00:01 |

| | 3 | INDEX FULL SCAN | IDX_LEO4 | 99 | 297 | 1 (0) | 00:00:01 |

| | 4 | INDEX FAST FULL SCAN | IDX_LEO3 | 71969 | 351k | 45 (0) | 00:00:01 |

Summary: exactly the same, right? this shows that the execution plan chosen by our optimizer is the best after comparing different costs. if we encounter the wrong execution plan in the actual work, we can make a detailed analysis through the 10053 event.

4. CBO may produce an incorrect execution plan when the statistics are inaccurate. Please give an example of finding the location of the CBO error in 10053 trace and giving the necessary text description.

LEO1@LEO1 > drop table leo5 purge; clear the environment

Table dropped.

LEO1@LEO1 > create table leo5 as select * from dba_objects; create leo5 table

Table created.

LEO1@LEO1 > create index idx_leo5 on leo5 (object_id); create B-tree index

Index created.

In order to make CBO produce an incorrect execution plan, I skewed the distribution of leo5 data a little bit.

The total number of LEO1@LEO1 > select count (*) from leo5; records is 72010

COUNT (*)

-

72010

LEO1@LEO1 > update leo5 set object_id=1 where object_id commit; submission

LEO1@LEO1 > update leo5 set object_id=2 where object_id > 1

3170 rows updated.

There are 68840 LEO1@LEO1 > select count (*) from leo5 where object_id=1; object_id equal to 1.

COUNT (*)

-

68840

3170 of LEO1@LEO1 > select count (*) from leo5 where object_id=2; object_id equals 2

COUNT (*)

-

3170

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (table analysis of leo5

Wnname= > 'leo1'

Tabname= > 'leo5'

Cascade= > true

Estimate_percent= > null

Method_opt= > 'for all columns size 254')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1 > select count (object_name) from leo5 where object_id=1; to view execution plan information

Execution Plan

Plan hash value: 2750404108

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 28 | 287 (1) | 00:00:04 |

| | 1 | SORT AGGREGATE | | 1 | 28 | |

| | * 2 | TABLE ACCESS FULL | LEO5 | 68840 | 1882k | 287K (1) | 00:00:04 |

A full table scan of 68840 is relatively accurate, indicating that the table analysis is in effect.

LEO1@LEO1 > select count (object_name) from leo5 where object_id=2

Execution Plan

Plan hash value: 2542459021

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 28 | 57 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 28 | |

| | 2 | TABLE ACCESS BY INDEX ROWID | LEO5 | 3170 | 88760 | 57 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | IDX_LEO5 | 3170 | | 11 (0) | 00:00:01 |

-

It is also true for fewer walking indexes.

The value of LEO1@LEO1 > update leo5 set object_id=3 where rownum select count (*) from leo5 where object_id=1; object_id has changed from 68840 to 8857.

COUNT (*)

-

8857

LEO1@LEO1 > commit; submission

Commit complete.

LEO1@LEO1 > select count (object_name) from leo5 where object_id=1

Execution Plan

Plan hash value: 2750404108

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 28 | 287 (1) | 00:00:04 |

| | 1 | SORT AGGREGATE | | 1 | 28 | |

| | * 2 | TABLE ACCESS FULL | LEO5 | 68840 | 1882k | 287K (1) | 00:00:04 |

At this time, CBO still chooses to scan the full table. From the changes in the number of records, we can know that indexing is more efficient, just like object_id=2 's execution plan, INDEX RANGE SCAN costs less, why did CBO choose the wrong execution plan? This is because although we have modified the record value, we did not update the object statistics of the leo5 table in time, and CBO still uses the earliest statistics, so we still think that the cost of full table scan is the best when calculating COST. Let's re-count the object statistics and get the latest cost list for filtering.

LEO1@LEO1 > begin

Dbms_stats.gather_table_stats (

Wnname= > 'leo1'

Tabname= > 'leo5'

Cascade= > true

Estimate_percent= > null

Method_opt= > 'for all columns size 254')

End

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

LEO1@LEO1 > alter session set events' 10053 trace name context forever,level 1; start the 10053 event

Session altered.

LEO1@LEO1 > select count (object_name) from leo5 where object_id=1; executes SQL statement

COUNT (OBJECT_NAME)

-

8857

LEO1@LEO1 > alter session set events' 10053 trace name context off'; shutdown 10053 event

Session altered.

LEO1@LEO1 > select value from v$diag_info where name='Default Trace File'; view trace file

VALUE

- --

/ u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc

[oracle@leonarding1 trace] $vim LEO1_ora_22298.trc to view the contents of the generated trace file

Table Stats::

Table: LEO5 Alias: LEO5

# Rows: 72010 # Blks: 1051 AvgRowLen: 75.00

Index Stats::

Index: IDX_ LEO5 Col#: 1

LVLS: 0 # LB: 1 # DK: 3 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00

Access path analysis for LEO5

* *

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for LEO5 [LEO5]

Table: LEO5 Alias: LEO5

Card: Original: 72010.000000 Rounded: 72010 Computed: 72010.00 Non Adjusted: 72010.00

Access Path: TableScan

Cost: 287.55 Resp: 287.55 Degree: 0

Cost_io: 287.00 Cost_cpu: 22598123

Resp_io: 287.00 Resp_cpu: 22598123

Access Path: index (AllEqRange)

Index: IDX_LEO5

Resc_io: 31.00 resc_cpu: 12862199

Ix_sel: 0.333333 ix_sel_with_filters: 0.333333

Cost: 31.33 Resp: 31.33 Degree: 1

LEO1@LEO1 > select count (object_name) from leo5 where object_id=1

Execution Plan

Plan hash value: 2542459021

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 28 | 158 (0) | 00:00:02 |

| | 1 | SORT AGGREGATE | | 1 | 28 | |

| | 2 | TABLE ACCESS BY INDEX ROWID | LEO5 | 8857 | 242k | 158 (0) | 00:00:02 |

| | * 3 | INDEX RANGE SCAN | IDX_LEO5 | 8857 | | 31 (0) | 00:00:01 |

-

Summary: after comparison, CBO finally chooses the index. When we update the statistics, CBO chooses the correct execution plan.

10053 cost execution Plan CBO calculation Formula 10046 Select execution Plan

Leonarding

2013.2.24

Tianjin & winter

Share the technology ~ realize the dream

Blog:www.leonarding.com

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.