SQLMDX

Eventually it's all about data

Posts Tagged ‘performance tuning’

fetch first … percent

Posted by sqlmdx on December 19, 2015

New syntax for so called “Top N” queries was introduced in Oracle 12c.

It can be used to return either fixed number of rows or specified percent of rows in a table.
Obviously latter requires to know the total number of rows to return the result so let’s check what is happening under the hood in this case.
Read the rest of this entry »

Posted in Oracle | Tagged: , | Leave a Comment »

Yet another approach to overcome bloom filter bug

Posted by sqlmdx on October 4, 2014

Bloom filter may help to dramatically reduce amount of data to be read from partitioned table by eliminating unneeded partitions.
Here is a nice introduction into bloom filters.

Unfortunately bloom filter doesn’t work with DML which causes a lot of challenges in 11gR2.
This bug is documented on metalink Bug 13801198 : BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS however that note doesn’t suggest any workarounds.
Read the rest of this entry »

Posted in Oracle | Tagged: , , | Leave a Comment »

Recursive calls and statistics

Posted by sqlmdx on May 11, 2014

There are many cases when executing some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not at all obvious how execution statistics for cursors are aggregated.
Read the rest of this entry »

Posted in Oracle | Tagged: , | Leave a Comment »