Performance Tuning and optimization principles for Oracle Decision support systems

The characteristics of a DSS system are to produce meaningful reports from a large number of data. DSS applications may often be used with OLTP, but because their design requirements vary widely, it is not a good idea to use OLTP systems for decision support. OLTP users are generally many, while the DSS system has fewer users. Examples of decision support systems are cash flow forecasting tools that work with the order entry system (OLTP system), which helps determine how much cash reserves are needed. Another example of decision support is the customer requirements analysis tool, which identifies which products a geographic customer buys the most.

The main features of decision support systems are:

Read large volumes of data and often use full table scans as a way to access data.

Update data in very small quantities. In general, data from OLTP systems (and possibly other data sources) are streamed to the DDS system in batches, and users rarely update the DSS data themselves.

The following figure reflects the characteristics of the DSS system:

The DSS system is running with some of the following requirements:

Reasonable response time.

The results are accurate.

Can be used during the day.

In order to meet the above requirements, we should consider adjusting the DSS application system from the following aspects.

1. In the use of application logic and declarative constraints to maintain integrity, it is worth remembering that the cost of declaring integrity constraints is small. In DSS system, correlation integrity constraint and table CHECK constraint are the main constraint forms used.

2. Try to make the code shared by stored procedure objects.

3. Even if a SQL statement has different values for the bundle (bind variable) under different operating environments, Oracle considers them to be the same SQL statement. Therefore, to minimize the effort to parse SQL statements, you should use bundle variables instead of putting these different values directly into the SQL statement (using literal) (if you do, Oracle thinks they are different SQL and needs to be parsed). However, this can be done with the following loss: The optimizer cannot know the selectivity of the column. The fully written SQL statement (using literal) enables the cost-based Oracle optimizer to use histogram statistics (histogram).