Oracle Cost Based Optimizer Effect of Optimizer index cost adj Parameter
Below is a MRR and PLR article in category Computers Technology -> subcategory Software.
Oracle Cost-Based Optimizer: Impact of the `optimizer_index_cost_adj` Parameter
Overview
When Oracle processes an SQL statement, it must decide the best way to retrieve the required data. This decision is generally made using one of two methods:
- Rule-Based Optimizer (RBO): Used when there are no internal statistics available for the referenced objects. However, Oracle has deprecated this method.
- Cost-Based Optimizer (CBO): Utilized when internal statistics are present. The CBO evaluates various execution plans and selects the one with the lowest cost in terms of system resources.
A key Oracle initialization parameter, `optimizer_index_cost_adj`, can significantly influence the CBO's decisions. If set incorrectly, it can lead to suboptimal performance. This article explores the effect of this parameter using a real-world example.
Case Study: Media Client Using SAP CRM/BW
While working with a media client running SAP CRM/BW applications on an Oracle database exceeding 4 terabytes, I encountered performance issues related to the `optimizer_index_cost_adj` setting. I analyzed a poorly performing SQL query involving the view `VBAP_VAPMA`, based on the `VBAP` and `VAPMA` tables. The parameter appeared to favor inefficient index scans over full table scans.
Example SQL Query
```sql
SELECT "AEDAT", "AUART", "ERDAT", "ERNAM", "KONDM", "KUNNR", "MATKL", "MATNR", "NETWR", "POSNR", "VBELN", "VKORG", "WAERK", "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", "ZZKONDM_P"
FROM SAPR3."VBAP_VAPMA"
WHERE "MANDT" = :a0
AND "AEDAT" > :a1
AND "AUART" = :a2
AND "KONDM" = :a3
AND "VKORG" = :a4
AND "ZZCDO" >= :a5
```
Parameter Impact
- With `optimizer_index_cost_adj = 10`:
- The execution plan favors an index scan using `VBAP~Z3`.
- With `optimizer_index_cost_adj = 100` (Oracle recommended default):
- The execution plan switches to a full table scan, which proved more efficient.
Execution Cost Analysis
- Full Table Scan Cost: Approximately 484,193. This is calculated roughly as:
\[
\text{No of blocks} / \text{DB\_FILE\_MULTIBLOCK\_READCOUNT} = 3,873,549 / 8 = 484,193
\]
By increasing `DB_FILE_MULTIBLOCK_READCOUNT` to 32, and reorganizing the table, the cost could drop to approximately 82,000, improving I/O performance fivefold.
- Index Scan Cost: An adjusted value of 149,483, using the `SAPR3.VBAP~Z3` non-unique index. There are only 160 distinct values out of 15.9 million rows.
The real cost was approximated as:
\[
\text{blevel} + (\text{Avg leaf blk per key} \times (\text{num\_rows} \times \text{selectivity})) = 1,188,451
\]
When `optimizer_index_cost_adj` is set to 10, the adjusted cost becomes 118,845.1, only 10% of the actual overhead.
Conclusion
It's crucial to let Oracle's optimizer determine the best execution path rather than mandating index usage. Adhering to the default `optimizer_index_cost_adj` value should be paired with up-to-date statistics, as the cost-based optimizer heavily relies on accurate data.
For further insights, visit [Sagar Patil's Blog](http://OracleDbaSupport.co.uk), curated by an expert Oracle consultant.
---
You can find the original non-AI version of this article here: Oracle Cost Based Optimizer Effect of Optimizer index cost adj Parameter.
You can browse and read all the articles for free. If you want to use them and get PLR and MRR rights, you need to buy the pack. Learn more about this pack of over 100 000 MRR and PLR articles.