Track Oracle9i CPU and I/O usage costs

When Oracle releases each new release, the database becomes more focused, especially in its external environment. Oracle has set many important initialization parameters based on the number of CPUs on the Oracle server, and Oracle is now more aware of the CPU running cycles and the cost of using I/O operations.

The cost-based SQL Optimizer (CBO) has been raised to take account of external impacts, especially when it comes to optimizing execution for an SQL query. Because the database is not running in an environment unaffected by other factors, the CBO will be a factor that affects the external disk I/O costs and CPU cycle costs for each SQL operation. This important feature makes CBO one of the most mature software products in the world. The CBO's job is to choose the best execution plan for any SQL operation.

Depending on the Oracle documentation, the cost of I/O and CPU usage is evaluated as follows:

Use Cost = (#SRds * sreadtim + #MRds * mreadtim + #CPUCycles
-------------------------------------------------
Cpuspeed)
-------------------------------------------------
Sreadtim

Over here:

#SRDs – Number of single-block reads

#MRDs – Number of multiple reads
Number of #CPUCycles –cpu run cycles

Sreadtim-Tan Read time
mreadtim– time to read multiple chunks
Cpuspeed-Number of CPU runs per second

Please note that the cost of external use is subject to disk read estimates and the impact of estimated CPU costs associated with each internal operation. Oracle holds detailed information about the many component costs of SQL processing and uses these average costs to influence the choice of a cost-based SQL optimizer. Here are some examples:

The cost of a cluttered signal connection ──oracle know the average number of RAM memory consumed by a cluttered connection.
Classification ──oracle keep track of RAM to perform classification and centralized operations.
List Scan cost ──oracle saves information about the time it takes to execute a multiple read.
Index block access cost ──oraclet know the average time required to elicit a single block.
Please note that these usage costs are evaluated on a different basis, depending on your choice of Oracle Optimizer. If you have an OLTP system with the First_rows optimizer model, the CBO can return row operations more quickly. On the other hand, if you are using the First_rows optimizer model for the Data Warehouse, the CBO will be severely affected by these external factors because the First_rows model is designed to reduce resource consumption.

To fully understand Oracle's external usage costs, let's delve into these new external impacts and how the Oracle CBO uses the external usage costs.

CPU Cost

Now the CBO has been able to estimate the order of magnitude of the machine's operating cycle required for each operation, and to influence the cost of executing the plan calculations. The CPU usage costs associated with Oracle queries depend on the current server load. CPU costs are often not important unless the entire Oracle program uses too much CPU resources.

IO cost

The CBO can estimate the order of magnitude of the physical block read into each operation. The I/O cost is proportional to the physical data block read. However, the CBO does not have the priority right to buffer directories, nor can it distinguish between a logical read and a physical read. Because of these drawbacks, the CBO does not recognize whether the data block has been read into the RAM data buffer.

Not completely perfect.

Note that the cost of these uses is also a function of the read number and is related to the number of reads, and that the external costs do not take into account the number of data blocks located in the RAM data buffers, but the new CBO release should take this into account.

Here we can see Oracle using the CPU and I/O cost assessments of the profiling execution plan. This assessment becomes more complex than when we are dealing with similar queries, as similar queries can be handled by many concurrent programs.

In the next column of my article, I'll talk about how the CBO is affected by the number of statistics. In order to be able to make the best execution plan, the CBO must make full use of all the data project information related to the query because you have to control how the statistics are allocated, which is a key aspect of the CBO's adjustment.