Currently browsing category

performance tuning

Xplan: now with “self” measures for row source operations

One of the most useful information that the Oracle kernel attaches to plans in the library cache are measures of various resource consumption figures, such as elapsed time, consistent and current gets, disk reads, etcetera. These can be made available for each plan line (aka “row source operation”).

These figures are always cumulative, that is, include both the resource consumed by the line itself and all of its progeny.…

fast refresh of join-only MVs: _mv_refresh_use_stats and locking log stats

A devastating performance degradation of materialized view fast refreshes can happen in versions after 9i – and can be healed rather easily by simply setting the hidden parameter _mv_refresh_use_stats or, a bit surprisingly, by locking statistics on the logs. The problem can manifest at least in the currently-latest patchsets of 10g, 11gR1 and 11gR2 (, and, seems to hit a lot of people, and its root cause are the utilization of wrong hints by the Oracle refresh engine.…

xplan: dbms_metadata.get_ddl for tables referenced by the plan

As a minor but useful new feature, xplan is now able to integrate into its report the DDL of tables (and indexes) referenced by the plan, calling dbms_metadata.get_ddl transparently.

This is mostly useful to get more details about referenced tables’ constraints and partitions definition – to complement their CBO-related statistics that xplan reports about.

This feature can be activated by specifing dbms_metadata=y or dbms_metadata=all (check xplan.sql…

11gR2: new algorithm for fast refresh of on-commit materialized views

This post investigates the improvements that have been made in 11gR2 to the fast refresh engine of materialized views (MVs) that are set to be automatically refreshed at commit time. We speak about join-only materialized views only in this post, as always with the help of a test case.

As noted in the post of mine “11gR2: materialized view logs changes“, in 11gR2 a new column, xid$$, is now part of materialized view logs; this column records the id of the transaction that logged the changes of the base table which the log is defined on.…

CBO: the “non-empty result set” assumption

The CBO assumes that SELECT statements are always going to retrieve at least one row – even if this is not necessarily the case, of course. Understanding why this is done is both useful and fascinating.

We must start from the very beginning and remember that one of the most important tasks of the CBO is estimating the statement cardinality, that is, to make a guess about the number of rows that will be fetched.…

Xplan 2.0

A lot of new features have been added in version 2.0 of xplan, the sqlplus script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here’s a brief description.

wait profile (from ASH)

For each statement, its wait profile is calculated fetching wait information from Active Session History:
|ash event |cnt |% |
|enq: HW – contention |2606|61.0|
|enq: TX – row lock contention| 875|20.5|
|db file sequential read | 344| 8.0|
|enq: TX – index contention | 158| 3.7|
|gc current grant busy | 152| 3.6|
|cpu | 56| 1.3|
|gc current block 2-way | 34| 0.8|
|gc current block busy | 13| 0.3|
|gc buffer busy | 10| 0.2|
|gc cr block 2-way | 7| 0.2|
|gc current grant 2-way | 5| 0.1|
|read by other session | 5| 0.1|
|direct path write | 3| 0.1|
|gc cr block busy | 3| 0.1|
|gc cr grant 2-way | 1| 0.0|
|SQL*Net more data from client| 1| 0.0|
|cr request retry | 1| 0.0|

By default this feature is on in 10g+ and inspects a window of ash_profile_mins=15 minutes from v$active_session_history.…

Tuning Oracle for Siebel – SQL template

The time has come to write down some of the most relevant discoveries I’ve made so far while being part of a team that is tuning a huge Siebel installation for a leading Italian company (“huge” especially because of the user base dimension and secondarily because of the hardware deployed, a three-node RAC on pretty powerful SMP machines).

This blog entry is about the structure of the Siebel queries and the effect of the settings of some CBO-related parameters – settings made by the Siebel clients by altering the session at connect time, or required as mandatory in the Siebel installation notes.…

Optimizing SQL statements with xplan

Xplan is a utility to simplify and automate the first part of every SQL statement tuning effort, that is, collecting the real plan of the statement, its execution statistics (number of executions, number of buffer gets performed, etc), getting the definition of all the accessed tables (and their indexes), and, last but not least, the CBO-related statistics of the accessed tables (and their indexes and columns) stored in the data dictionary by dbms_stats or ANALYZE.…