Oracle data analysis and dynamic sampling

Oracle Database Performance tuning the most important and frequently encountered is the efficiency of SQL execution, and the most intuitive tool for SQL efficiency is the CBO's execution plan, so how to make the CBO produce the most accurate and efficient execution plan is the subject we need to study now. The same statement, a good execution plan can bring about the same speed, bad execution plan makes us miserable, below we from principle to practice to how to produce efficient planning methods to teach everyone.

A CBO introduction

The CBO is called Cost Based optimization is a mathematical model, and the cost of the same SQL statement in different versions of Oracle is not the same because the design structure of each version of the CBO optimizer is very different, It's not perfect yet, it's very intelligent and human, so we can't rely entirely on it to help us.

How to generate an accurate execution plan: Formula data +cbo= Execution plan, the more accurate the data incoming CBO gets, the more accurate the results are, the more accurate the input data is, and the accurate execution plan is calculated with accurate data.

The second example shows a more accurate execution plan than dynamic sampling after a table analysis

Dynamic sampling: As the name implies is the Oracle Automatic for you preliminary data analysis, because it is random on the table to take some data, so it is not guaranteed that the implementation plan is very accurate, only as a means of auxiliary analysis, in the last resort to analyze data, there are certain limitations.

Scenario: When the table does not parse information, Oracle uses the dynamic sampling technique, and the dynamic sampling occurs at the time of SQL hard parsing, and the incoming->CBO parameter-> generates the execution plan.

Level: Oracle 10g Oracle 11g The default dynamic sampling level is 2, it has level1-10, the higher the level of data collection, the more accurate the results, the longer the running time, level10 the sampling analysis of all the data.

Experiment

leo1@leo1> drop table Leo1 Purge; cleaning up the environment

Table dropped.

leo1@leo1> drop table Leo2 purge;

Table dropped.

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects;

Table created.

Leo1@leo1> CREATE TABLE Leo2 as SELECT * from leo1; creating Leo2 table, using LEO1 data and structure

Table created.

Leo1@leo1> Col segment_name for A10

Leo1@leo1> Select Segment_name,extents,blocks from Dba_segments where segment_name in (' LEO1 ', ' LEO2 ');

Segment_na extents BLOCKS

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

LEO1 24 1152

LEO2 24 1152

Query Leo1 and Leo2 table These two segment object storage parameters, are occupied 24 areas, 1152 blocks, 2 tables exactly the same

Leo1@leo1> COL table_name for A10

Leo1@leo1> Select Table_name,num_rows,blocks,status from Dba_tables wheretable_name in (' LEO1 ', ' LEO2 ');

TABLE_NAME Num_rows BLOCKS STATUS

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

LEO1 VALID

LEO2 VALID

In this data dictionary only display table name and current state (valid), no line information and block information, this is for God horse, well from the above operation can be seen, we just set up a table, but did not analyze table statistics, now we analyze the effect of looking at

Leo1@leo1> Execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO1 '); statistical analysis of LEO1 tables

Pl/sql procedure successfully completed.

Leo1@leo1> Select Table_name,num_rows,blocks,status from Dba_tables wheretable_name in (' LEO1 ', ' LEO2 ');

TABLE_NAME Num_rows BLOCKS STATUS

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

LEO1 71968 1051 VALID

LEO2 VALID

Now the Leo1 table already has row information and block information, Leo2 because there is no table analysis, there is nothing now.

leo1@leo1> Set Autotrace trace exp

Leo1@leo1> SELECT * from Leo1 where object_id=10000;

Execution Plan

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

Plan Hash value:2716644435

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

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

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