Questions about CBO Optimization

How can I use the CostBased optimizer to Optimize Query operations?

Oracle provides cost-based (CostBased) and rule-based (RuleBased) optimizers (CBO and RBO) for determining the execution plan of query operations. The CostBased optimizer calculates the overhead of various execution plans and then selects the execution plan with the lowest cost. You can use CBO in the following ways:

1. Set the OPTIMIZER_MODE = choose parameter in the INIT. ORA file;

2. Set OPTIMIZER_GOAL = FIRST_ROWS or ALL_ROWS at the Session level.

3. Use Hint in the query statement, including CHOOSE, ALL_ROWS, and FIRST_ROWS.

Why is my execution plan not the best?

CBO depends on some statistical information of the table to select the execution plan with the lowest cost. When the statistical information is inaccurate, the generated plan may not be the best. Therefore, use the ANALYZE command to ANALYZE and count tables in a timely manner.

My query performance last week is very good. Why is the query speed very slow now?

This is because the execution plan is changed. The following factors will change the execution plan:

1. The OPTIMIZER_MODE parameter in the INIT. ORA file is changed;

2. The table defines or changes the parallel query degree;

3. The ANALYZE command is used to re-ANALYZE the table, and the ESTIMATE method is used. different percentages can be selected to produce different analysis results;

4. The DB_FILE_MULTIBLOCK_READ_COUNT parameter is modified; 5. The SORT_AREA_SIZE parameter is modified.