CBO learning --- Chapter 1 -- What do You Mean by Cost

CBO learning --- Chapter 1 -- What do You Mean by Cost Chapter 2 meaning of Cost (What do You Mean by Cost ?) Two meanings of Cost (Cost/Cost): (1) the magic number generated by the explain plan tool (2) the actual resource consumption of SQL Execution is actually the result of the Cost being executed by the optimizer. this book mainly studies: How the optimizer generates Cost. 1.1 The three variants (varians) of the optimizer_mode optimizer option CBO (cost-based optimizer_mode) are controlled to view and modify the parameters:

[sql] SQL> show parameter optimizer_mode            NAME                                 TYPE                   VALUE    ------------------------------------ ---------------------- ----------    optimizer_mode                       string                 ALL_ROWS            SQL> alter session set optimizer_mode=first_rows;    SQL> show parameter optimizer_mode            NAME                                 TYPE                   VALUE    ------------------------------------ ---------------------- ----------    optimizer_mode                       string                 FIRST_ROWS  

 

Available value of the optimizer_mode parameter:

[SQL] all_rows -- execute the statement execution plan first_rows_N -- N = 1, 10, 100, and 1000 in the shortest time, thoroughly analyze the first join order, estimate the total number of returned rows, and re-optimize the statement, the first N rows are returned before execution. In first_rows -- 9i, the first row is returned quickly. There are multiple high-level constraints: Avoid merge join and hash jion, unless you can only perform nested loop connection with the second table scanned in the full table. Rule -- obsolete. Some internal statements include hint/* + rule */choose -- between role and all_rows.

 

The optimizer_dynamic_sampling parameter controls whether dynamic sampling is performed for tables without statistical information. 2 is (10 Gb default), 1 is not (9i Default) # script analysis, cost Analysis on optimizer_mode = first_rows in the code package attachment, under \ ch_01_cost \

[sql] first_rows.sql  first_rows_08.txt  first_rows_09.txt  first_rows_10.txt  

 

A 10000-row table is constructed in the Code. The table has a primary key and a B-tree index of a common column. In optimizer_mode = first_rows, six types of queries are executed, reflecting different versions, different conditions have different Cost values for the optimizer. in the code, hide the parameter "_ sort_elimination_cost_ratio" -- ratio control parameter between cost that does not take sort and cost that uses sort. The specific control is as follows: if the sort cost is not taken or the sort cost> _ sort_elimination_cost_ratio is taken, the execution plan is sorted. if the sort cost is not taken or the sort cost <_ sort_elimination_cost_ratio is taken, the execution plan is not sorted. _ optimizer_cost_model = choose, 10g will automatically activate the cpu costing mode, that is, when the system statistics is not collected, the default noworkload system statistics 1.2Cost definition Sometimes CBO will produce the wrong Cost, cause: (1) Some Cost model assumptions are not applicable (2) data statistics are available, but misleading (3) data statistics are unavailable (4) Lack of knowledge about hardware performance (5) I do not understand the current workload (6) code bug CBO evolution process: (1) 8i opt only calculates the number of IO (the # of IO requests) (2) 9i introduces CPU costing

[SQL] CPU costing model: Cost = (# SRds * sreadtim + # MRds * mreadtim + # CPUCycles/cpuspeed) /sreadtim [SQL] Explain [SQL] # SRDs-number of single block reads # MRDs-number of multi block reads # CPUCycles-number of CPU Cycles sreadtim-single block read time in milliseconds mreadtim-multi block read time in milliseconds cpuspeed-CPU cycles per second in MHz

 

(3) 10 Gb introduces offline optimizer to store critical statistical information in profile form, and helps online optimizer to enhance query of 1.3 variants and costing SQL variants through prompt: before cost calculation, the SQL statement is equivalent to deformation. Deformation Rules: (1) SQL cannot be changed. (2) if it can be changed, it can be changed. (3) if it can be changed, but the cost is too large, the constant invisible parameter "_ complex_view_merging" will be changed ", false (8i), true (9i), control whether to read the view definition into SQL when a view exists in SQL, and then perform equivalent variants and optimization. (This parameter is set to true in 8i, and sometimes cannot be controlled) # script analysis, path \ ch_01_cost \ In the code package attachment

[sql] view_merge_01.sql  view_merge_01_08.trc  view_merge_01_08.txt  view_merge_01_09.trc  view_merge_01_09.txt  view_merge_01_10.trc  view_merge_01_10.txt  

 

Modify the parameter "_ complex_view_merging" and add hint to control the execution plan of the SQL statement. before optimization, perform the following operations: predicate pushing ): create a predicate by passing a closure (transitive closure) and generate a predicate from a constraint non-nested subquery (subquery unnesting) star variant (star transformation) Attachment: http://up.2cto.com/2013/0719/20130719095917100.rar