sqlmdx

Just another WordPress.com site

Is a row locked?

Posted by sqlmdx on December 22, 2015

Let’s consider “hypothetical” case when it’s required to check if row is blocked without attempting to lock it.
And maybe we’ll find a way to see uncommitted data in Oracle. :)
Read the rest of this entry »

Posted in Oracle | Tagged: | Leave a Comment »

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 »

Context switches may not be an issue any longer

Posted by sqlmdx on December 13, 2015

I’ve described how to measure context switches some time ago in this post.

Given that with_clause allows to declare PL/SQL functions in 12c it’s interesting to analyze overhead for context switches 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 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 »

dbms_comparison

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 »

 
Follow

Get every new post delivered to your Inbox.