Large Memory Allocations due to Query Parsing
Troubleshooting high PGA memory allocations which escalated in ORA-04036 error. High memory consuption was related to a parsing problem. Continue Reading
Oracle
Index Organized Table vs. Clustered Index
Comparing efficiency of IOT in Oracle with Clustered Index in SQL Server. Continue Reading
Rescuing Data from Corrupted Tables
This blog post describes how to partially rescue data from corrupted table segments if you find yourself in a miserable position of not having a valid backup at your disposal. Continue Reading
Tables Containing “Current” and “Historical” Rows
If your data model forsees storing “current” and “historical” rows in the same table, there is a chance that there will be queries doing self-joins drived by the “current” rows. This model can give rise to wrong join cardinality estimates applied on skewed data distributions. The more emphasized the skew is, the larger is the error. Table partitioning turns out to be a good solution in such cases. Continue Reading
Density Calculation with Hybrid Histograms
This blog post describes how the calculation of new density has modified with the introduction of hybrid histograms in Oracle 12c. Continue Reading
Gathering Statistics with Option ‘gather auto’
In 12c the parameter options was introduced in gather_table_stats. Unfortunately, it doesn’t work as expected. Continue Reading
Disjunctive Subquery Optimization
Disjunctive subqueries are the subqueries which are applied to the or operator. Alternatively, these queries can be rewritten to use the union all operator which may be a more efficient way to retrieve the records from the database. This blog post compares the efficiency of SQL Server and Oracle optimizer when dealing with the transformations of disjunctive subqueries.
Continue Reading
Nested Loop Join Heuristics
Not always the cheapest execution plan gets chosen. This blog post describes such an edge case in SQL Server and Oracle. Continue Reading
Expect – Alternative to Supplying Password to Command Line
The Expect framework can be used as an alternative to passing passwords to the command line. Continue Reading
Simultaneous patching not possible with datapatch
Unfortunately… Since the datapatch utility has been introduced it is not longer possible to patch more databases at the same time. Consequently, if you try to patch two databases simultaneously one of them will fail. The process The (oversimplified) flow… Continue Reading