Using a case study to thoroughly understand 10046 trace-byte-level in-depth cracking, 10046trace --

Using a case study to thoroughly understand 10046 trace-byte-level in-depth cracking, 10046trace --

Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/37840583

PM, 10046-byte-level in-depth cracking

 

To learn more, log on to the http://www.jianfengedu.com/Discuz/detail/id/56
[Technology sharing QQ chat group] 252296815

The first class in China to become the top master of QTune
-- "I promise. I have listened carefully and understood my lessons. I have become a top-notch player in QTune step by step !!!

SQL optimization course series I CBO internal Algorithms
SQL optimization course system II transformaction.
SQL optimization course system III qtune case studies
SQL optimization course system IV various tuning tools

-- "Can be heard from the beginning, truly become a master of QTune

I only asked one question. Can someone really figure out the> choice rate? DIS is not clear.
No one knows about these things, or even oracle COE.

 

++ Let me show you something

--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    10 |   349   (1)| 00:00:05 ||*  1 |  TABLE ACCESS FULL| T1 |     1 |    10 |   349   (1)| 00:00:05 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_NAME"='00440')table name:T1column name:OBJECT_NAMEnum_rows:87417num distinct:48128num_nulls:797density:.0000115446779034865histogram:HEIGHT BALANCEDhigh_value:4435(D5) -- internal value:354151154089362000000000000000000000low_value:3030343432(00442) -- internal value:25020794088876500000000000000000000000400 internal Value : 250207940886347000000000000000000000ENDPOINT_VALUE     ENDPOINT_NUMBER BUCKET_COUNT-------------------- --------------- ------------46641  40147796  411A1  498B2  589C3  668D5  759

 

Density = (Bucket_total-Buckets_all_popular_value )/
Bucket_total/(Newcastle-popular_values.COUNT)
= (75-34)/75/(48128-4) =. 00001136

Sel =. 00001136*(87417-797)/87417*(1-(250207940888765000000000000000000000-250207940886347000000000000000000000)/(354151154089362000000000000000000000-250207940888765000000000000000000000 )))
=. 000011256
Card = 87417 *. 000011256 =. 983965752

This is: col = val and val <low_value and value> 2 * low_value-high_value
An algorithm similar to this condition.

High_value: 4435 (D5) -- internal value: 354151154089362000000000000000000000
Low_value: 3030343432 (00442) -- internal value: 250207940888765000000000000000000000

00400 internal Value: 250207940886347000000000000000000000

How are these three numbers obtained?

D5 is saved in dba_tab_columns as follows: 4435. In fact, the internal oracle computing value is 354151154089362000000000000000000000.

Density * A4Nulls * (1-(val-low_value)/(high_value-low_value) This is a formula. This formula is useless. You won't use it.

As a matter of fact, he is telling you how to COST. Instead, he wants to tell you how to study oracle algorithms.

It is actually telling you how to guess. It is also wrong.

For example, cardinality uses ceil (). In fact, oracle uses round in most cases.

 

Are you helpless in the face of slow production SQL statements? I am just an example here...
1. Serious performance problems caused by merge join cartestian.
2. Why is index skip scan required for this SQL statement? -- The problem caused by rebuild index on the patition table.
3. Why does this SQL always go through full table scan?
4. What triggers wrong results?

If you want to understand the root cause of SQL, the real algorithm, the real underlying layer, and the real success... Please join:

DSI Series 1 CBO internal algorithm-2014.8.5 startup (14 class hours, 7 class days), ￥499/person

Choice rate, base calculation formula
-------------------------
1. No histogram for a single table
1.1 (col = val) in the range
1.2. (><=>= between, within the range
1.3. out-of range
 
2. A single table has a frequency histogram.
2.1 (col = val) in the range
2.2. (><=>= between, within the range
2.3. out-of range
 
3. A single table has a hight balanced histogram.
2.1 (col = val) in the range
2.2. (><=>= between, within the range
2.3. out-of range
 
4. Join selection rate calculation

Cost Calculation formula:

1. Important Concepts
-----------------
1.1
1.2 Cost computing model.
1.3 Cost unit.
1.4 Number of reads per block (# SRds)
More than 1.5 read times (# MRds)
1.6 system statistics
1.7 Join selecti1_& Cardinality
 
2. Single Table Access (no histogram)
------------------
2.1 Cost Model Components
2.2 full table scan Cost Calculation
Cardinality and selectivity of table 2.3
 
3. Index Scanning
------------------
3.1 formula:
3.2 scan a table through B * Tree index

Join
---------
4. Nested Loop

5. Hash Join and Sort Merge Join

 

 

--------------- Recently shared activity video
Caibird2005 share the topic "Table splitting, partition tables, and parallel computing in the data warehouse environment"
Link: http://pan.baidu.com/s/1eVA14
Master Clojure shares the topic rac study from scratch
Link: http://pan.baidu.com/s/16FRIm
Jack shared the topic "Storage past, present, and future-several key technologies and virtualization of storage".
Link: http://pan.baidu.com/s/1eQrECQe
Dr. Dasight shared the topic "technical development in the big data age and challenges to IT people"
Link: http://pan.baidu.com/s/1bnenDtd
Link: http://pan.baidu.com/s/1sj0fJ5r
GuoyJoe shared the theme "BBED reporting performance: UPDATE restoration and decryption"
Share the topic "Big Data" with Dr. Dasight, http://pan.baidu.com/s/1i3mlPC5
Link: http://pan.baidu.com/s/1mgLrAuG
Link: http://pan.baidu.com/s/1c0hCsSS
Du Xing shared his topic "MySQL 5.6 features and how to transition from MySQL 5.5 to MySQL 5.6"
Link: http://pan.baidu.com/s/1eQ5oQp8
Dr. Dasight shared the topic "Analysis on applicability of Hadoop and Nosql technology".
Link: http://pan.baidu.com/s/1mg9g3Za
 
You can get the above shared videos for free when you join the VIP and share them in the future. You only need to pay 10 yuan for membership fee, as shown in the following link:
Http://item.taobao.com/item.htm? Spm = a1z10.1. w4004-6565782199.2.ul78Pt & id = 38629931035

 

My desktop has a file named start tracetxt, which is a file in 867 bytes.

Text file.
Check whether it is used. Delete it if it is useless ..
Otherwise, back up the file before deleting the file. No.