Posts
-
"ASH math" of time_waited explained with pictures and simulation
As explained by John Beresniewicz, Graham Wood and Uri Shaft in their excellent overview ASH architecture and advanced usage, avg( v$active_session_history.time_waited ) is not a correct estimate of the average latency (the “true average”) esperienced by a wait event, the reason being that short events are less likely to be sampled. In order to correct this, the authors propose a formula that gives an unbiased estimate of the “true average”.
-
Fast refresh of aggregate-only materialized views with MAX - algorithm
In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the MAX aggregate function:
-
Fast refresh of aggregate-only materialized views with SUM - algorithm
In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the SUM aggregate function:
-
Fast refresh of aggregate-only materialized views - introduction
This post introduces a series about the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only an aggregate:
-
"alter session force parallel query", and indexes
This post is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL). The latter ways are the most popular in my experience, but I have noticed that their popularity is actually due, quite frequently, more to imperfect understanding rather than informed decision - and that’s a pity since “alter session force parallel query” can really save everyone a lot of tedious work and improve maintainability a great deal.
-
fast refresh of outer-join-only materialized views - algorithm, part 2
In this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner table without a unique constraint on the joined column(s).
-
fast refresh of outer-join-only materialized views - algorithm, part 1
In this series of posts we will discuss how Oracle refreshes materialized views (MV) containing only OUTER joins, covering only 11.2.0.3. We will use the very same scenario (MV log configuration, DML type, etc) as in the inner_join case, “just” turning the inner join into an outer join:
-
OLTP compression: migrated rows are compressed
In his articles Compression in Oracle – Part 2: Read-Only Data and Compression in Oracle – Part 3: OLTP Compression, Jonathan Lewis has shown that block (re)compression is never attempted on updates - it is attempted only on inserts (and, of course, only if the used space crosses the PCTFREE threshold).
-
overlapping ranges with priority
A customer of ours (a leading Italian consumer goods retailer) has asked us to solve the following problem, that occurs quite frequently and that is not trivial to solve efficiently - and that is very interesting to design and fun to blog about!
-
refresh "fast" of materialized views optimized by Oracle as "complete"
In my current “big” project, I am building a network of nested materialized views to transform rows of one schema into rows of another (very different) schema. The former is used by the old (but still live) version of an application of ours, the latter by the new version; our idea is to incrementally (aka “fast”) refresh the network daily in order to have the new schema ready when the new version goes live. We need this nework because we have only a few hours of allowed downtime, and the transformations are very complex: the MV network is going to be composed of at least 200+ MVs, each containing tens of millions of rows.
-
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”).
-
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!
-
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 asktom.oracle.com” 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).
-
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 (10.2.0.4, 11.1.0.7 and 11.2.0.1), 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.
-
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.
-
11gR2: materialized view logs changes
In this post we are going to discuss some 11gR2 changes to materialized view logs that are aimed at increasing the performance of the fast-refresh engine of materialized views (MVs), especially the on-commit variant.
-
CBO: NewDensity for Frequency Histograms,11g-10.2.0.4 (densities part IV)
As we have seen in the previous posts of this series, in 11g a new figure named “NewDensity” has been introduced as a replacement for the “density” column statistic for columns whose histogram has been collected; this change has been backported in 10.2.0.4 also.
-
CBO: "NewDensity" replaces "density" in 11g, 10.2.0.4 (densities part III)
In this post we are going to explore and explain the rationale for the formula used by the CBO to compute the “NewDensity” figure that replaces, from 10.2.0.4 onwards, the “density” column statistic in the cardinality estimation formulae for columns with height-balanced (HB) histograms defined.
-
CBO: the formula for the "density" column statistic (densities part II)
In this post we are going to explore and explain the rationale for the formula used by dbms_stats to compute the “density” column statistic, used by the CBO in versions less than 10.2.0.4 to estimate the cardinality of a class of SQL statements. In the next post, we will speak about its replacement, named “NewDensity” in 10053 trace files.
-
CBO: about the statistical definition of "cardinality" (densities part I)
Let’s explore the concept of cardinality from the point of view of the statistician; this is both to get a clearer vision of the matter (i.e. for fun) and to path the way for understanding the rationale for the “density” statistics as calculated by dbms_stats (the topic of an upcoming post).
-
An interview with Mark Townsend
While attending the 11gR2 launch event in Milan last Thursday, I had the distinguished opportunity (invited, as a blogger, by the Oracle team that was organizing the event) to meet Mark Townsend and exchange a few words about the new features of 11gR2 and the Oracle database in general as well.
-
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.
-
NoCOUG's "First international SQL challenge"
Just a short note to tell my friends that I have been bestowed the August Order of the Wooden Pretzel, that is, that I won the NoCOUG’s “First international SQL challenge” with this solution.
-
fast refresh of single-table materialized views - algorithm summary
Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single master table, containing no aggregate but, at most, filter predicates and additional column definitions:
create materialized view test_mv build immediate refresh fast on demand with rowid -- with primary key as select test_t1.*, x1+x2 as x1x2 from test_t1 where x1 != 0.42;This kind of MVs might be considered a degenerate case of a join-only MV, a topic that we investigated on an earlier post, and one could expect the same algorithm. But that is not the case: the test case shows that the algorithm used is very different.
-
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.
-
fast refresh of join-only materialized views - algorithm summary
This post investigates how Oracle fast refreshes materialized views containing only joins of master tables:
create materialized view test_mv build immediate refresh fast on demand as select test_t1.*, test_t1.rowid as test_t1_rowid, test_t2.*, test_t2.rowid as test_t2_rowid, test_t3.*, test_t3.rowid as test_t3_rowid from test_t1, test_t2, test_t3 where test_t1.j1_2 = test_t2.j2_1 and test_t2.j2_3 = test_t3.j3_2 ;The fast refresh algorithm is simple and very easy to understand - so trivial in fact that once examined and understood, the possible tuning techniques follow naturally.
-
Bind Variables Checker for Oracle - now install-free
I’ve finally managed to implement an install-free version of my utility to check for bind variables usage. The new script is named bvc_check.sql and when run, it examines the SQL statements stored in the library cache (through gv$sql) and dumps the ones that would be the same if the literals were replaced by bind variables.
-
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).
-
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.
-
Order of keys inside index blocks
In this post we are going to illustrate how index keys are ordered inside a leaf block of an Oracle B+tree index.
-
Why blogging ?
Mainly because I like to write, and people usually like my writings. So, why not ?
subscribe via RSS