Just another WordPress.com site

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 introductory article regarding 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 it doesn’t advise any workarounds.
Read the rest of this entry »

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

Measuring context switches

Posted by sqlmdx on September 30, 2014

Almost everyone who has some experience with Oracle knows about notorious notion called “context switches” between SQL and PL/SQL engines.
You can find great explanation of it by Tom Kyte here.

Even though it’s quite simple to measure overhead introduced by context switches, not so many developers are aware of how to calculate exact number of context switches and moreover how to check whether they occurred or not in some cases.
Read the rest of this entry »

Posted in Oracle | Tagged: | Leave a Comment »


Posted by sqlmdx on September 27, 2014

dbms_comparison was introduced in 11g as a powerful tool to compare and converge table data.
Even though it’s quite flexible it has some limitations:
1. Objects are supposed to be in different databases. So if you want to compare objects in the same database you should create fake db link.
2. Objects must contain unique indexes.

Another consequence from the flexibility is that converge is being applied in two steps (merge & delete) even though it’s possible to merge data using single merge statement.
Moreover those statements are based not only on tables to be synchronized but on auxiliary data populated after compare.
Read the rest of this entry »

Posted in Oracle | Tagged: | Leave a Comment »

Amending statistics list for autotrace

Posted by sqlmdx on May 24, 2014

I wonder why Oracle has not added possibility to change the list of statistics for autotrace but unfortunately that’s a fact.

You cannot change the default format of the statistics report.

However there are at lest three different ways to view extended list of statistics for given session

  1. Use dbms_session.set_identifier and dbms_monitor.client_id_stat_enable/dbms_monitor.client_id_stat_disable.
  2. First drawback is that enabling and disabling of stats collecting must be executed in another session and second drawback is that it collects only 27 statistics.
    Advantage is the ease of monitoring several sessions with the same client_id.

  3. Use queries on v$sesstat, v$statname in another session and find difference between executions.
  4. Disadvantage is the necessity to run service queries in second session to avoid side affects and positive aspect is that all existed statistics can be reported.
    It’s not mandatory to create another session manually though. It’s possible to implicitly execute some statements in another session, for example using dbms_job/dbms_scheduler or autonomous transactions.

  5. Use widely known package runstats_pkg by Tom Kyte.
  6. The drawback is the same as for previous approach: either you have to run runstats in another session or results are not absolutely precise.

Even though documentation says that we cannot change the default format of the statistics report, it’s possible to amend it.
The only restriction is number of statistics cannot exceed 11.
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 execution of some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not so obvious how execution statistics for cursors are being aggregated.
Read the rest of this entry »

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

Query transformations in Oracle: join elimination

Posted by sqlmdx on December 14, 2013

Join elimination appeared in Oracle version 10gR2.
Read the rest of this entry »

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

Query transformations in Oracle: or-expansion

Posted by sqlmdx on December 8, 2013

As you may know there are two types of query transformations in Oracle: heuristic-based and cost-based.
Heuristic-based transformations are supposed to produce better plan all the time so it’s being applied without cost comparison while cost-based ones not always lead to better plan so they are being applied only if the cost of transformed query block is less then the cost of original one.

I’m not sure whether full list of transformations is publicly available but some brief classification could be find in a white paper Query Optimization in Oracle Database 10g Release 2.

It’s obvious that some transformations such as “outer join to inner join conversion” always lead to better plan while another like “join factorization” can either improve or degrade it thus former is treated as heuristic-based transformation while latter as cost-based.
Read the rest of this entry »

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

Query transformations in Oracle: view merging

Posted by sqlmdx on November 14, 2013

Transformations in Oracle is a part of query optimization.
In short, query optimization can be divided into two parts
1. Logical optimization: query transformation
2. Physical optimization: searching for optimal access paths, join methods, join orders.
Additional details can be found in a presentation Query Transformations by Joze Senegacnik.

In terms of Oracle SQL Engine, logical optimization is performed by Query Transformer block while Plan Generator is responsible for physical optimization. Third part of a query optimizer is Cost Estimator which makes possible to compare different plans and choose one with the lowest cost.
Additional details regarding Oracle SQL Engine can be found in a VLDB paper Closing the Query Processing Loop in Oracle 11g.

In older Oracle versions there were only so called heuristic-based transformations. They have been applied without taking cost into account and with an assumption that they produce faster plan most of the time. Since version 10gR1 Oracle introduced cost-based transformations which are being applied only in case when transformed query has the lower cost than non-transformed. So in this case Plan Generator makes a loopback to Query Transformer which leads to many more strategies to find the most efficient plan.
Read the rest of this entry »

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


Get every new post delivered to your Inbox.