Tuning The Result Cache

, , , , , , , , ,

The result cache stores the results of database queries in memory for reuse, improving performance by reducing execution time for repeated queries.
There are two types of result caches: the server result cache, which resides in the Shared Global Area (SGA), and the client result cache, located within a client process.
Using result caches significantly enhances performance for OLAP applications and queries returning a few rows from numerous accessed rows, such as in data warehouses.
Oracle database initialization parameters control the configuration and management of the server result cache, while client configuration files can override client result cache settings.
Efficient use of result caching requires understanding read consistency, query parameter requirements, and restrictions, while monitoring can be done through specific database views and tables.

For more info, see:https://arsalandehghanis.medium.com/tuning-the-result-cache-d7bb0402f826

/0 Comments/by

ORA-00600 [qeselupdpre_20]

, , , ,

This error is triggered when trying to update a column in a table that is part of a correlated subquery involving the same table. This issue was encountered in Oracle databases of versions equal to or greater than 12.1 but below 12.2, with 12.1.0.2 being the confirmed affected version.

Description:
The error was caused by a regression that prevented the correct marking of the compare column in cases where there is a correlation column from the same table but a different view. This regression led to the ORA-00600 [qeselupdpre_20] error when executing certain update statements within the specified version range of Oracle databases.

Version Affected:
The problem affects Oracle database versions from 12.1 to 12.2, with the specific version 12.1.0.2 (Server Patch Set) confirmed to experience this issue. It’s crucial to note that this problem does not extend to versions 12.2 and above.

Workaround:
Unfortunately, there is no available workaround for the ORA-00600 [qeselupdpre_20] error in the affected versions of the Oracle database. Users encountering this issue are advised to proceed directly to the fix provided by Oracle.

Fixed:
The fix for the bug causing the ORA-00600 [qeselupdpre_20] error is first included in Oracle database version 12.2.0.1 (Base Release). Users experiencing this problem should consider upgrading their Oracle database to version 12.2.0.1 or later to resolve this issue.

/0 Comments/by

My lectures in 2021

, , , , ,
Oracle Groundbreakers EMEA 2021
Michigan Oracle Users Summit 2021 –
/by

Avoid SQL Performance Problems

, , , ,

Would you please download and install the one-off patch for your database version and OS for bugs not included in RU yet? If you do not find a patch for your specific version and OS from MOS, please open a Service Request with details on the patch needed. In addition, please include a list of patches already applied (opatch lsinventory -detail) and any other patches you intend to apply.

Some of the Optimizer bug fixes (not all) are controlled by the “_FIX_CONTROL” setting and are DISABLED by default in 19c when installed either through one-off backport or through RUs. Please refer to the corresponding Patch Readme carefully for one-off patches and enable the fixed control to activate the fix.

Example:

SQL> alter system set “_FIX_CONTROL”=”29302565:1”;
/0 Comments/by

Transitivity

, , , ,

Oracle CBO performs transitivity as the first step in optimization. Transitivity means generating additional predicates based on existing predicates. The main advantage is the inclusion of extra access methods. Transitivity is not done for join predicates.

Assume A, B, and C are columns. If A=5 and A=B, you can conclude that B=5. This can be applied to queries as shown on the following page. Note that A=5 contains a constant.
However, in terms of joins, if A=B and B=C, it is not concluded that A=C; in other words, the Oracle server does not generate transitive closures for join predicates.
For more details about transitivity, see Doc ID 68979.1

 

/0 Comments/by