_ Sort_elimination_cost_ratio parameter of Oracle CBO

The _ sort_elimination_cost_ratio parameter of Oracle CBO shows several optimization cases related to the implicit parameter _ sort_elimination_cost_ratio of Oracle optimizer (Refer ). If _ SORT_ELIMINATION_COST_RATIO is used as the keyword to query on Metalink, many bugs related to this parameter will be found. The error feature of the execution plan is also indexed, however, after the index full scan is performed, if 10053 Trace is performed, an annoying Recost for order by step will be found, and then it will lead to the wrong execution plan. Upgrading 9i to 10g is the most likely to encounter this problem (the original good, to 10g found that there is a problem with the execution plan ). the problematic SQL statement is usually triggered BY INDEX RANGE SCAN and an ORDER BY statement. In more cases, the optimizer mode is FIRST ROWS. In this way, Oracle will try to eliminate sorting as much as possible, sorting is considered as expensive by default. This problem can be solved by controlling the value of the implicit parameter _ SORT_ELIMINATION_COST_RATIO (0 by default:

Alter session set "_ SORT_ELIMINATION_COST_RATIO" = 5

 

Other possible solutions: the sorting in the index should be consistent with the ORDER by in the SQL statement. To put it bluntly, many Oracle implicit parameters are designed to solve bugs in specific Oracle situations. Because they are not universal, they appear as implicit parameters in some versions. In the production database, enabling implicit parameters in some cases is not impossible, as long as you understand what the corresponding implicit parameters are.