Part 1: Types of optimizer
a. Choose B. Rule (Based on rules) c. COST (Based on cost)
Choose:
This is the default. The optimizer can use both cost-based optimization methods (CBO), or use rule-based optimization methods (RBOs), which are determined to have a statistical information available.
If the database's optimizer mode is set to selectability, the actual optimizer mode will be related to whether the analysis command is running. If table has been over, the optimizer mode will automatically become CBO, but in turn, the database will use Rule Form optimizer.
By default, Oracle uses a Choose optimizer, in order to avoid Full Table Scan, you must try to avoid using the Choose optimizer without using a rule or cost-based optimizer.
Rule:
Regardless of whether there is statistical information, the optimizer uses rule-based methods.
Cost:
To use a cost-based optimizer (CBO, COST-BASED OPTIMIZER), you must regularly run the Analyze command to increase the accuracy of object statistics in the database.
CBO determines which execution plan of the current SQL is the most efficient or cost-effective in accordance with the statistics of the available access paths and tables, indexes, and other objects. The CBO will also consider HINTS recommendations.
CBO Perform the following steps:
a. The optimizer generates a set of potential execution plans based on the available access paths and HINTS for SQL statements.
b. The optimizer evaluates the cost of each plan according to the statistics of the data dictionary. The cost is an evaluation value, which is proportional to the SQL statement to perform the computer resources consumed by a plan. The optimizer calculates the cost of the access path and connection order based on the evaluation of computer resources (I / O, CPU, memory).
C. The optimizer compares the cost of the program, thereby choosing a cost-off execution plan.
By default, CBO is targeted by the best throughput, which means that Oracle uses as few resources to process all rows that are accessed by the statement; of course, CBO can also optimize SQL with the fastest response speed, which means Oracle uses as few resources to deal with the first row or a few lines accessed by the statement, of course, this situation may consume more resources for the entire statement.
The implementation plan generated by the optimizer will vary depending on the "optimizer target". If the best throughput is targeted, the result is more inclined to use full-table scanning instead of index scan, or use sorted merge connections instead of nested loop connection; if the fastest response speed is target, the result is usually tendented Use the index scan and nest loop connection.
For example, if you have a statement that can run in a nested loop connection and run in a sorted merge connection, ordering the merge connection to return all query results, and the nested loop can quickly return to the first line or a few lines result. If you are to increase throughput as an optimizer target, the optimizer will tend to select a sort merge connection; if your optimizer target is to increase the response speed, the optimizer tends to select a nested loop connection.
Choosing an optimizer goal is based on your application, the general rules are:
a. For batch applications, the optimal goal is optimized for optimal throughput. For example, Oracle report applications.
b. For interactive applications, it is good to optimize the optimization target with the fastest response speed. For example, SQLPlus queries.
The main factors affecting optimizer optimization goals are:
a. Optimizer_Mode initialization parameters.
b. CBO statistics in the data dictionary.
C. HINTS used to change CBO optimization target.
Part II: How to Set Optimizer
Set the default optimizer, you can pass the various declarations of the Optimizer_Mode parameter in the init.ora file, such as Rule, Cost, Choose, All_Rows, First_ROWS.
Of course, you can also override them in the SQL sentence level or a session. You can change CBO optimization targets in the session: Alter Session Set Optimizer_Mode. For example: 1. Add the following statement to the initialization parameter file, you can change the CBO optimization target at instance level:
Optimizer_mode = first_ROWS_1
2, the following statement can change the current session CBO optimization target:
ALTER session set optimizer_mode = first_ROWS_1
3, you can change the Hints of CBO optimization targets
With the following HINTS, you can specify the CBO optimization target separately, and Hints in the SQL statement can override the Optimizer_Mode initialization parameters.
Part III: Optimizer_Mode
Value
Description
Choose
This is the default. The optimizer can use both cost-based optimization methods (CBO), or use rule-based optimization methods (RBOs), which are determined to have a statistical information available.
1. If in the visited table, at least one table is available in the data dictionary, the optimizer uses a cost-based method.
2. If in the visited table, only partial tables have available statistics in the data dictionary, the optimizer still uses the cost-based method, but the optimizer must use some internal information to try other internal information without statistical information. Statistics, such as the number of data blocks assigned to these tables, etc., which may result in an unmembacted execution plan.
3. If in the visited table, there is no table with statistics in the data dictionary, the optimizer uses a rule-based approach.
All_Rows
Regardless of whether there is statistical information, the optimizer uses a cost-based approach and is optimized for optimal throughput.
First_ROWS_N
Regardless of whether there is statistical information, the optimizer uses a cost-based approach and returns the first N row data set with the fastest speed, and N can be 1, 10, 100, 1000.
First_ROWS
Optimizer uses the cost of mixing with the test method, looking for an execution plan that can return the first few rows in front.
Note: CBO uses the implementation plan generated by the test method, and the cost may be larger than the non-use. First_ROWS can be used for backward compatibility and planning stability.
Rule
Regardless of whether there is statistical information, the optimizer uses rule-based methods.
Part IV: Implementation Plan
Oracle is used to run a statement, which is called an Execution Plan, and the execution plan contains the access path and connection order of each table involved in the statement.
Using the Explain PLAN statement to view the execution plan selected by the Optimizer, first create a Plan_Table to store the description of the execution plan:
@ $ Oracle_home / rdbms / admin / uTLXPLAN.SQL
Oracle recommends that you delete Plan_TABLE after doing database version upgrades, because fields may vary, which may cause scripts to fail or TKPROF failed.
This allows the execution plan of the SQL statement to view the SQL statement directly in the PL / SQL Developese and other tools.
Explain Plan is a good tool for analyzing the SQL statement. It can even analyze the statement without performing SQL. Through analysis, we can know how Oracle connection table, what way to scan the table (index scan or full Table scanning) and the name of the index.
You need to interpret the results of the analysis from the order of the upward order. The result of the EXPLAIN PLAN analysis is used in the indent format, the inside of the internal operation will be interpreted first, if the two operations are in the same layer In, there is a minimum operand will be executed first.
NESTED LOOP is a small number of operations that do not process the above rules. The correct execution path is to check the data for NESTED LOOP, where the number of operating numbers will be processed. Part 5: Full table scan
Related concepts:
I / O
It is for data blocks instead of
Oracle's I / O is for data blocks, so the percentage of data blocks accessed will affect whether the CBO is selected to scan. Generally, a plurality of records are stored in a block, and the requested record is either gather in a few blocks or disperse in a large number of data blocks.
HWM (HIGH WATER MARK)
HWM is a tag of full-scale scanning range, and each full menu scan is read to the HWM location. You can find HWM after DBA_TABLES.BLOCKS after Table Analyze, and HWM will be reset when the table is Drop, Truncate or Move. It should be noted that after a table is deleted in a large number of records, the large number of data blocks under the HWM is empty. When this table is scanned, Oracle will still read the HWM position, which will be scanned for full menu. Produces extremely bad effects.
Full table scanning (Full Table Scans)
Full Table Scan will read all data blocks under HWM, access all rows in the table, each row, to determine if the retrieval condition is met by the WHERE clause. When Oracle performs full mesh scan, read each block in order, so if you can read multiple data blocks at a time, you can improve the scan efficiency, and initialize the parameter DB_FILE_MULTIBLOCK_READ_COUNT to be set in one I / O to read Take the maximum number of data blocks.
When the optimizer will use full mete scan
1, no index available
Such as described below:
SELECT LAST_NAME, First_name
From Employees
Where upper (last_name) = 'Tom'
The Last_name field has an index, but the function is used in the query, so the query will not use the index. If you want this query to take an index, you need to create a function index create index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index index. It is important to note that implicit conversion, such as the COLX field is VARCHAR2 type but stored Number: where colx = 123456, hidden conversion to_Number (colx), at which point the index on colx will also be invalid.
2, a lot of data
If the optimizer considers that the query will access most of the data blocks in the table, even if the index is available, full mete scans will be used.
3, small table
If a data block under a table HWM is less than db_file_multiblock_read_count, only I / O can be sweep, and the full-table scan is lower than the cost of the index, and a full table scan is used.
If there is such a small number of accesses, it is usually fixed to the memory in memory for the alter Table Table_name Storage (Buffer_Pool Keep).
4, parallel
If a higher parallelism is set at the table level, such as ALTER TABLE TABLE_NAME Parallel (Degree 10), the CBO error is usually scanned. It is usually not recommended to set up parallelism in the table level.
Parallel queries can usually improve the performance of full surface scans. It is recommended to implement parallel, such as / * full_name * /.
5, full table scan hints
If you want to force an optimizer to use a full table scan to use a prompt full.
Section 6: RowID Scan
RowID is a location recorded in the data block. Since it specifies the accurate position recorded in the database, RowID is the fastest way to retrieve a single record. If you access the table through RowID, Oracle first needs to get ROWID, Oracle can get ROWID in the WHERE clause, but more is obtained by index scanning, and then Oracle is based on RowID to locate each record. .
Optimizer When is using RowID
Not each index scan is accompanied by RowID access, if all fields accessed are included in the index, no longer need to access the table through RowID.
note:
RowID is Oracle represents the internal method of data storage, which may change due to the change of the version. It is not recommended to access data by specifying RowID in WHER, because row migration and row links cause RowID changes, EXP / IMP will also change RowID.
Part 7: Index
Index scan
Index not only contains the value of the index field, but also contains the location of the bank in the table, if the statement only retrieves the index field, Oracle reads the value directly from the index without accessing the table, if the statement retrieves other field values, Oracle is recorded in the ROWID access table.
Index Scan Type:
Index unique scan (index unique scans)
Index range scanning (Index Range Scans)
Index Range Scans Descending
Index Jump Scan (Index Skip Scans)
Full index scan (Full Scans)
Fast full index scan (Fast Full Index Scans)
Index Joins
Bitmap Joins
1, index unique scanning
This scan typically occurs when a single-line record is accessed when a list of primary keys or fields containing unique constraints is accessed.
2, index range scanning
Index range Scan is a common way of retrieving data, and the returned data returns to the indexed line ascending order, and the field value is identified as a RowID ascending order. If the Order By sentence is specified in the statement, and the sort field is the index field, oracle will ignore the Order By clause.
E.g:
SQL> SELECT * FROM T;
Colx coly
-----------------------------
1 3
1 2
1 1
1 0
SQL> CREATE INDEX IND_T ON T (COLY);
SQL> Set autotrace on
SQL> SELECT * from T where color> 0;
Colx coly
-----------------------------
1 1
1 2
1 3
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by Index Rowid) of 'T'
2 1 Index (Range Scan) of 'Ind_T' (Non-Unique) No ORDER BY result set is already arranged in ascending order in COLY.
SQL> Set Autotrace Traceonly
SQL> Select * from T where color> 0 ORDER BY COLY
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by Index Rowid) of 'T'
2 1 Index (Range Scan) of 'Ind_T' (Non-Unique)
It can be seen that there is no Sort step in the execution plan, indicating that Oracle ignores the Order By clause.
3, index descending range scan
If an index is specified in the ORDER BY is designed, or the index_desc prompt is used, Oracle may use the index descending range to scan.
E.g:
SQL> SELECT / * INDEX_DESC (T Ind_t) * / Colx, Coly from T Where Coly <3;
Colx coly
-----------------------------
1 2
1 1
1 0
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 1 Card = 4 BYtes = 104)
1 0 Table Access (By Index Rowid) of 'T' (COST = 1 Card = 4 BYtes = 104)
2 1 Index (Range Scan Descending) of 'Ind_t' (COST = 2 Card = 1)
4, index jumping scan
Jumping Scanning occurs in a composite index, which is logically separated into a smaller sub-index. When a field of the composite index is not specified in the query, it will be skipped, thereby increasing the efficiency of the index scan. You can use the INDEX_SS prompt to force the jump scan.
for example:
SQL> Select * from Employees;
SEX Employee_id Address
---- ----------------------------------
F 98 ABC
F 100 ABC
F 102 ABC
F 104 ABC
M 101 ABC
M 103 ABC
M 105 ABC
SQL> CREATE INDEX IND_SEX_EMPID ON EMPLOYEES (SEX, EMPLOYEE_ID);
The index structure is shown below:
SQL> Set Autotrace Traceonly
SQL> SELECT / * INDEX_SS (Employees Ind_Sex_empid) * / * from Employees Where Employee_ID = 101; Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 3 Card = 1 BYTES = 11)
1 0 Table Access (by index rowid) of 'Employees' (COST = 3 card = 1 bytes = 11)
2 1 index (Skip scan) of 'Ind_sex_empid' (Non-Unique) (COST = 2 CARD = 1)
5, full index scanning
If you want to use a full index scan to meet two conditions, one is that the fields involved in the query are included in the index, and the second is that at least one index field has non-empty properties. Since the data of the index key is ordered, the full index scan can be operated by elimination. The full index scan only needs one I / O.
Select Empno, Ename from Big_Emp Order by Empno, ENAME
Execution Plan
-------------------------------------------------- ------------------------------
0 Select Statement Optimizer = Choose (COST = 3 Card = 1 BYTES = 11)
1 0 Index (Full Scan) of 'BE_IX' (COST = 2 Card = 1)
6, fast total index scanning
Fast full compute scan only accesss the index itself, without accessing the table, so the Fast full index scan is only included in the field involved in the index. If you want to use the field involved in the Query query, you must be included in the index, and at least one field in the index has non-empty properties. After satisfying the condition, you can use the index_ffs prompt to force the fast full index scan, and the fast full composite scan is only available for CBO.
Fast full index scan does not eliminate sort operations because the data in the index key is not sorted. Unlike the full index scan, the fast full index scan is to read the entire index through multi-block read, and can set parallel.
7, index connection
Only all fields involved in the query are included in the index, and the index connection is used, and all required data can be obtained only by accessing the index without accessing the table. The index connection is only available for CBO and cannot be eliminated.
Index connections can be enforced by index_join tips.
8, bitmap connection
The bitmap connection uses a bitmap as the key, and then converts the bit into RowID through the mapping function. Only Oracle9i Enterprise Edition supports bitmap indexes and bitmap index connections.
Sample Table Scans
Sample Table Scan is the data in the random search table. When you have a SAMPLE or SAMPLE BLOCK clause, Sample Table Scan is executed.
Such as: Select * from Employees Sample Block (1);
How does CBO choose access path?
CBO first checks the conditions in the WHERE clause and the FROM clause, and determine which access paths are available. The CBO then generates a set of possible execution plans using this access path, and then evaluates the cost of each plan by indexing, the statistics of the table, and the last optimizer selects the lowest cost.
example 1:
SELECT *
From Employees
WHERE LAST_NAME = 'Jackson'; if the last_name has a unique constraint or primary key constraint, the optimizer learns that only one line of data is returned, and in this case, the query has strong selectivity, optimization is likely to walk the unique index scan.
Example 2:
Or the above statement, if the Last_name does not have a unique constraint or primary key constraint, the optimizer uses user_tab_columns.num_distinct and user_tables.num_rows to evaluate the selectability of the query, estimating that last_name is the proportion of the Employees table.
Example 3:
SELECT *
From Employees
WHERE EMPLOYEE_ID <7500;
Evaluate this query Selective Time Optimizer Using the boundary value of 7500 and EMPLOYEE_ID field in the WHERE clause, user_tab_columns.low_value, user_tab_columns.low_value, the optimizer assumes that EMPLOYEE_ID between the minimum and the maximum is average distribution, the optimizer determines The value is less than 7500 percentage, and then this value is used as the selectivity of this query.
Example 4:
SELECT *
From Employees
WHERE EMPLOYEE_ID <: E1;
The optimizer does not know the value of E1, the value of the bound variable may be different, so the optimizer cannot use the previous method to evaluate the selectivity of the query containing the binding variable, in which case the optimizer will use The internal default value is specifically estimated a selectivity.
Example 5:
SELECT *
From Employees
WHERE EMPLOYEE_ID BETWEEN: low_e and: high_e;
The optimizer will rewrite this sentence as:
EMPLOYEE_ID> =: low_e
EMPLOYEE_ID <=: high_e
The optimizer is then used to use an internal default value to simultaneously evaluate a selectivity.
Example 6:
SELECT *
From Employees
WHERE EMPLOYEE_ID BETWEEN 7500 and 7800;
The optimizer will be rewritten as:
EMPLOYEE_ID> = 7500
EMPLOYEE_ID <= 7800
The optimizer evaluates selectivity (S1 and S2) for each condition, and then calculates the selectivity of BetWeen with the following formula: s = ABS (S1 S2-1)
Part 8: Connection
Understand
How does CBO run connection statement?
Select an execution plan for a connection statement, the optimizer must make the following decisions:
1, access path
The optimizer must select a path that can be used to retrieve data to each table in the connection statement.
2, connection method
Oracle must perform connection operations for each pair of line sources, and the methods of connection include nested cycles, sorting, hash connectivity, Cartesian, etc.
3, connection order
If you need to connect more than two, Oracle first connect two tables and connect the result of its connection with the next table until all tables are connected.
How does CBO select connection method?
The optimizer evaluates the cost of each connection method and then selects the lowest cost. If a multi-line connection is returned, the optimizer will consider the following three factors:
When returning a large number of result sets (greater than 10,000 rows), nested loop connections are very efficient, and the optimizer may not select it. The cost of nested cycles is mainly in the process of matching all selected rows and internal tables in the appearance, and the connection order of CBO can be changed by order prompt. Cost Calculation Formula for Nested Cycling Connections:
COST = Access Cost of A (Access Cost of B * Number of Rows from a) If you use CBO, use hash linkage efficiency when returning a large number of result is very high. Cost Calculation Formula for Hatched Connections:
COST = (Access Cost of A * Number of Hash Partitions Of B) Access Cost of B
If you use RBO, when returning a large number of result is higher than the efficiency of the merge connection. The cost of sorting merge connections is primarily to read all line sources into memory, and the process of sorting, multi-block read pair sorting connections will help. Calculation formula for sorting the combined connection:
COST = Access Cost of A Access Cost of B (Sort Cost of A Sort Cost of B)
When the data is presented, the two sorting costs are 0.
How CBO runs Anti-Joins
SELECT * from Employees
Where department_id not in
(Select Department_ID from Departments
WHERE LOCATION_ID = 1700);
The optimizer defaults to process anti-joins with a nested loop, but if merge_aj, haveh_aj, nl_aj prompts are used, Not in can be converted into a sorted merge or haveh anti-join.
How CBO runs Semi-Joins
Select * from Departments
WHERE EXISTS
(SELECT * from Employees
Where divartments.Department_id = Employees.Department_ID
And Employees.salary> 2500);
The optimizer default is also used in the nested loop to perform EXISTS, and it can also be adjusted by merge_sj, hash_sj, nl_sj prompt. NOT INSTS is generally recommended to rewrite with Exists, but cannot be converted to Exists when the OR branch is included in the NOT IN subquery.
Nested loop connection
The nesting loop connection is a good choice for the case where the connected data subsets is smaller. In the nested loop, the internal table is driven by the outer table, and each line returned by the appearance must be retrieved in the internal table, so the result set returned throughout the query is not too large (more than 10,000 is not suitable), Take a subset of returns as a small table (CBO default appearance is driven), and must have an index on the connected field of the inner table. Of course, you can also use the Ordered prompt to change the CBO default drive table, using USE_NL (Table_name1 Table_Name2), but forced CBO to perform a nested loop connection.
Haveh connection
Hash connection is a common manner when CBO is a big data set, and the optimizer uses a smaller table (or data source) in two tables to create a hash table in memory, then scan a larger table and detect scattered. List, find rows that match the hash table.
This method is suitable for smaller tables that can be fully placed in memory, and the total cost is the sum of the cost of accessing two tables. However, in the case of a large table, it cannot be completely placed in memory. At this time, the optimizer will split it into several different partitions, and the partition is written to the disgunded time period of the partition in the part of the memory. Larger temporary segments thus increase the performance of I / O.
It is also possible to enforce the hash connection with Use_hash (Table_name1 Table_name2) prompt. If you use a hash connection hash_Area_size initialization parameter must be enough, if it is 9i, Oracle recommends automatic management of the SQL workspace, set Workarea_Size_Policy to Auto, then adjust the PGA_AGGREGATE_TARGET. Sort merge connection
Typically, the effect of the hash connection is better than the sorted merge connection, however, if the source of the line has been acted, no longer sorting when the sorting merge connection is performed, and the performance of the sorting the combined connection is better than the hash connection. . You can use Use_merge1 Table_Name2 to force to use sorted merge connections.
The following is Oracle may choose to use the sort merge connection:
l Two tables do non-equivalent connection
l Optimizer_Mode is set to Rule
l Hash_join_ENABLE is set to false
l It has been accepted in advance, and the optimizer considers that the cost of using the sorted merge connection is lower than the hash connection.
l Hash_Area_size and sort_area_size settings are too small, and the optimizer thinks that hash connection costs are too high.
External connection
Whether it is a nesting loop or has a hash connection, the CBO does not select the connection order based on the cost, and the driven table always contains ( ).
SQL> SELECT / * ORDERED USE_NL (T1 T2) * / T1.MSISDN, T2.MSISDN from T1, T2 WHERE T1.MSISDN ( ) = T2.MSISDN;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 100 Card = 99 BYtes = 2178)
1 0 NESTED LOOPS (OUTER) (COST = 100 Card = 99 BYtes = 2178)
2 1 Index (Full Scan) of 'Ind_t2' (Non-Unique) (COST = 1 Card = 99 BYtes = 1089)
3 1 Index (Range Scan) of 'Ind_t1' (Non-Unique) (COST = 1 Card = 1 BYTES = 11)
Although the Ordered prompt is used, trying to use T1 as a drive table, but since it is an external connection, it is still a driving table as a T2.
It is also the same as a hash connection:
SQL> SELECT / * ORDERED USE_HASH (T1 T2) * / T1.MSISDN, T2.MSISDN from T1, T2 WHERE T1.MSISDN ( ) = t2.msdn;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 4 Card = 99 BYtes = 2178)
1 0 Hash Join (Outer) (COST = 4 Card = 99 bytes = 2178)
2 1 INDEX (FULL SCAN) of 'Ind_t2' (COST = 1 Card = 99 BYtes = 1089) 3 1 Index (Fast Full Scan) of 'Ind_t1' (Non-Unique) (COST = 2 Card = 999 BYtes = 10989)
At this time, the appearance is T1, the inner table is T2, the connection retains the T1 table and the T2 do not match the t2, and then use T1 to construct a lague table, and finally the T2 table is detected in T1.
Full Outer Joins
SELECT T1.MSISDN, T2.MSISDN
From t1
Full Outer Join T2
On T1.MSISDN = T2.MSISDN
ORDER by t2.msdn
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 15 Card = 1000 Bytes = 36000)
1 0 Sort (ORDER BY) (COST = 15 Card = 1000 Bytes = 36000)
2 1 view (cost = 6 card = 1000 bytes = 36000)
3 2 Union-All
4 3 Nested Loops (Outer) (COST = 2 Card = 999 BYtes = 21978)
5 4 Index (Fast Full Scan) of 'Ind_t1' (Non-Unique) (COST = 2 Card = 999 BYtes = 10989)
6 4 Index (Range Scan) of 'Ind_t2' (Non-Unique)
7 3 hash Join (Anti) (COST = 4 card = 1 Bytes = 22)
8 7 Index (Full Scan) of 'Ind_t2' (Non-Unique) (COST = 1 Card = 99 BYtes = 1089)
9 7 Index (Fast Full Scan) of 'Ind_t1' (Non-Unique) (COST = 2 Card = 999 BYtes = 10989)
From the execution plan, it is actually two external connections, one is t1.msdn = t2.msdn ( ) takes the nested loop, one is t1.msdn ( ) = t2.msdn walking, then Union-all two rows.
The result is like this:
T1.MSISDN T2.MSISDN
-----------------------
10 10
20 201
20 202
30 114
30 115
30 116
...
270
280
178
207
Ninth Part: Some and optimizer related initialization parameters
1, Optimizer_features_enable
Each version of Oracle optimizer is different, especially after making a version upgrade, you must modify this parameter before you can use the optimizer characteristics supported by this version. It can give it a value such as: 9.2.0, 9.0.2, 9.0.1, 8.1.7, 8.1.6 and so on.
2, Cursor_SHARING
This parameter replaces the constant in the SQL statement with variables, and there is a large amount of constant OLTP systems to consider enabling this parameter. However, it is necessary to understand that the bound variable can reuse a large amount of SQL, reduce the analysis time, but when the data distribution changes, the execution plan generated by the CBO is not optimal (not considering the specific variable) value). Usually the OLTP system is suitable for binding variables, the OLTP system features, SQL runs frequently and relatively short, SQL analysis time is large. If the SQL run is long, the analysis time is negligible, and the good execution plan is the most important, so the DSS system bind variable should be considered separately.
3, hash_area_size
This is the storage area of the hash connection, if the parameter value is used with a hash connection, if the hash table cannot be fully stored in memory, the has a large impact on the hash connection performance. If it is 9i recommended to start the workspace auto management, then set the PGA_AGGREGATE_TARGET.
4, sort_area_size
The size of the memory sort zone is not enough to write if the memory area will be written when sorted. 9i also recommends starting the work area automatic management and then sets the PGA_AGGREGATE_TARGET.
5, hash_join_ENABLED
If this parameter is enabled, the CBO will consider a hash connection when considering the connection method.
6, Optimizer_index_caching
This parameter represents the percentage of the cached index block, the range of optional values is 0-100. This value affects the nested loop connection, if this value is set, the CBO will more incline to use the nested loop.
7, Optimizer_index_cost_adj
The optimizer uses this parameter (which is a percentage) converts the cost of the index scan to the cost of the equivalent full mete scan, and then compares the cost of the full surface scan. Default 100, indicating that the index scan cost is equivalent to full-scale scanning costs. The optional value range is 0-10000.
8, Optimizer_Max_Permutations
This initial parameter is used to set the optimizer to consider how many connection sequences, and the optimizer is constantly generating the arrangement of possible tables, until the number of arguments reaches parameter Optimizer_max_Permutations. Once the optimizer stops generating a new possible connection arrangement, it will select the minimum cost of the cost.
9, DB_FILE_MULTIBLOCK_READ_COUNT
This parameter represents the number of consecutive data blocks read at the I / O reading at the full-scale scan or index fast full scan (block # continuous, and I / O can not exceed extent).
10, Optimizer_Mode
Optimizer mode is also an optimized target of the optimizer. The value is: rule, choose, all_rows, first_rows_n, first_rows.
11, partition_view_enabled
If set to True, the optimizer will skip the unregistered partition in the partition view, which can change the cost-based optimization program calculate the partition view statistics from the base table statistics.
12, query_rewrite_enable
If set to True, the optimizer will override SQL with the available materialized view.
