Selection of optimization modes in oracle cbo Mode

Generally, for a database with a single function, you can set an optimizer mode at the instance level,
For example, for OLAP systems, most of the time the database runs a report job, the execution is basically an aggregate SQL operation,
For example, set the optimizer mode to all_rows.
For some website-type databases with many paging operations, it is better to set it to first_rows.
Assume that our database runs basically an OLAP system, so the optimizer mode is set to ALL_ROWS, which is conducive to the quick completion of Report SQL.
However, the database also runs some user query services. The query method can be described as paging. Sometimes users complain about slow query,
Although we know the problem, it is difficult to solve it because these SQL statements have been written to the code by developers.
In this case, if this problem can be taken into consideration during the development phase, for the SQL statements that require paging operations, the developer converts the optimization mode to FIRST_ROWS in SQL using the Hint method, this greatly improves the data processing speed.
For example, a paging query that extracts 10 records each time:
Select * from (SELECT/* + first_rows (10) */. *, rownum rnum from (SELECT/* + first_rows (10) */id, name from t1 order by id) a Where rownum = 1;
You can use FIRST_ROWS (n) repeatedly in each subquery to improve query efficiency.
Although it is not a good idea to manually add Hint operations to SQL statements, you can consider such settings in some cases, such as when you need to consider other user operations.
However, some tests need to be done in the early stage to ensure that such settings can improve the performance without affecting the database in other aspects. This is a problem that should be carefully considered during the system design phase.

 

This article is from "one leaf in the wind"