Currently browsing author

Alberto Dell'Era, Page 2

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.…

Third International NoCOUG SQL & NoSQL Challenge!

For anyone into using their SQL skills creatively, and getting out of the boring SQL-coding daily routine … here is a puzzle that is both entertaining and challenging, and with a real prize for the winner!

Official abstract:
“In this challenge (see page 25), the Wicked Witch of the West needs help in creating a magic spell to ensure that the Third Annual Witching & Wizarding Ball is a grand success.…

Tom Kyte in Italia – 5 Aprile 2011

[Note: I’m writing in Italian since this post is about a local event]
Anche quest’anno Thomas “Tom” Kyte, il “Tom dietro” e autore di diversi libri, è tornato in Italia per tenere una delle sue conferenze ricorrenti più popolari (ecco le slides) – quella sulle features più significative della versione corrente di Oracle (quindi 11gR2 al momento).…

Xtrace: an Oracle session trace browser – exec flow

Tracing a session is extremely useful when you need to investigate how a client interacts with the database – the client could be an application of yours, a third-party application, or an Oracle module such as dbms_stats or dbms_mview. To get the perfect picture of the client-server dialogue, you “simply” need to consider all EXEC lines in the trace file, and associate to each line the executed statement and the bind variable values; a very tedious and error-prone task when done manually, that Xtrace can make for you (and for free).…

Xtrace: an Oracle session trace browser (introduction)

Xtrace is a graphical tool that can navigate Oracle trace files, manipulate them, and optionally get them back as a text file. It actually makes (much) more, but in this first post we are going to focus on its basic browsing capabilities.

Let’s see the tool in action on the trace file produced by this simple PL/SQL block:
for r in (select * from t) loop
end loop;

The resulting trace file is
[text wraplines=”false” gutter=”false”]
WAIT #2: nam=’SQL*Net message from client’ ela= 61126 driver id=1413697536 #bytes=1 p3=0 obj#=76357 tim=5789636384898
PARSING IN CURSOR #26 len=66 dep=0 uid=73 oct=47 lid=73 tim=5789636385129 hv=3421439103 ad=’aeb809c8′
for r in (select * from t) loop
end loop;
PARSE #26:c=0,e=153,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5789636385122
BINDS #26:
PARSING IN CURSOR #28 len=15 dep=1 uid=73 oct=3 lid=73 tim=5789636386184 hv=1406298530 ad=’a0503300′
PARSE #28:c=0,e=804,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=5789636386181
BINDS #28:
EXEC #28:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=5789636386284
WAIT #28: nam=’db file sequential read’ ela= 19 file#=4 block#=59 blocks=1 obj#=76357 tim=5789636386383
WAIT #28: nam=’db file sequential read’ ela= 11 file#=4 block#=60 blocks=1 obj#=76357 tim=5789636386457
FETCH #28:c=0,e=243,p=2,cr=3,cu=0,mis=0,r=100,dep=1,og=1,tim=5789636386566
FETCH #28:c=0,e=54,p=0,cr=1,cu=0,mis=0,r=100,dep=1,og=1,tim=5789636386663
FETCH #28:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=5789636386693
EXEC #26:c=0,e=1543,p=2,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=5789636386746
WAIT #26: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=76357 tim=5789636387057
WAIT #26: nam=’SQL*Net message from client’ ela= 42743 driver id=1413697536 #bytes=1 p3=0 obj#=76357 tim=5789636429824
STAT #28 id=1 cnt=200 pid=0 pos=1 obj=76357 op=’TABLE ACCESS FULL T (cr=4 pr=2 pw=0 time=363 us)’
Even for this artificially simple trace file, it takes a lot of effort to read and understand it; for example, it takes a while to associate the recursive SQL lines to the execution of the PL/SQL blocks (the “EXEC #26” line).…

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…

tweet …

For people that have asked – I’m not blogging simply because I’m feverishly working on a tool of mine that I’m very fond of 🙂

Guess what it does from the following next two screenshots …



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.…