Almost everyone who has some experience with Oracle knows about notorious notion called “context switches” between SQL and PL/SQL engines.
You can find a great explanation of context switches by Tom Kyte here.
Even though it’s quite simple to measure the overhead introduced by context switches, not so many developers know how to calculate exact number of context switches and moreover how to check whether they occurred or not in some cases.
dbms_comparison was introduced in 11g as a powerful tool to compare and synchronize table data in different tables.
Even though it’s quite flexible it has some limitations:
1. The tables are supposed to be in different databases. Therefore if you want to compare tables in the same database you will need to create fake db link.
2. The tables must have unique indexes.
Another consequence from the flexibility is that synchronizing is being applied in two steps (merge & delete) even though it could have been done using single merge statement.
Moreover those statements are based not only on tables that are synchronized but also on auxiliary data populated after comparison.
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 least three different ways to view the extended list of statistics for given session
- Use dbms_session.set_identifier and dbms_monitor.client_id_stat_enable/dbms_monitor.client_id_stat_disable.
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.
- Use queries on v$sesstat, v$statname in another session and find difference between executions.
The disadvantage is the need 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 necessary to create another session manually though. It’s possible to implicitly execute some statements in another session, for example by using dbms_job/dbms_scheduler.
- Use widely known package runstats_pkg by Tom Kyte.
The drawbacks are 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 in fact possible to amend it.
The only restriction is that number of statistics cannot exceed 11.
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.
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 they are applied without cost comparison while cost-based transformations not always lead to better plans so they are applied only if the cost of transformed query block is less than the cost of the 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 others such as “join factorization” can either improve the plan or make it worse thus former is treated as heuristic-based transformation while latter as a cost-based.
Query transformations in Oracle is a part of query optimization.
In short, query optimization can be divided into three parts
1. Logical optimization: query transformation
2. Physical optimization: searching for optimal access paths, join methods, join orders.
3. Cost Estimator: choosing the optimal plan.
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 physical optimization is done by Plan Generator.
Plan costing, comparison and choosing the one with the lowest cost is done by the Cost Estimator.
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 were applied without taking cost into account and with an assumption that they produce faster plan most of time. Since version 10gR1 Oracle introduced cost-based transformations which are applied only in case when transformed query has the lower cost than non-transformed. So in this case Plan Generator loops back to Query Transformer which leads to many more strategies evaluated to find the most efficient plan.