Oracle Performance and Engineering

A few months back, I was investigating a slow query for a client that had a huge IN-list (over 1100 items in IN-list). The IN-listed column was a unique key and yet the query was not using an index (complicated query fired on a UNION ALL-ed view -- each part of the UNION ALL - separately fired - would use the index however). I was trying to play with statistics and asked for generating statistics with histogram across the schema. Or, asked to fire the following -

exec dbms_stats.gather_schema_stats(ownname => 'my_owner',method => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

I *thought* that the "AUTO" will decide which columns to collect histograms on. Though the strategy had solved the problem and confirmed my wrong assumption - today I learnt that "AUTO" option will only work - if at all - on a database that's being used in full production capacity for a while; or in other words, has all typical SQL statements fired by the application in the cache.

Given AUTO option, Oracle actually queries SYS.COL_USAGE$ table (that stores details of columns used in WHERE clause) and determines what columns should it collect histograms on. This column usage monitoring is apparently *not* governed by "alter table monitoring on;" (as given wrongly in some documents - including this one on Oracle website). SYS.COL_USAGE$ population depends on an undocumented parameter _COLUMN_TRACKING_LEVEL.

Thus, to collect histograms on the columns with most skewed data - sepcially after upgrade, db restart, migration etc. - we should really use "SKEWONLY" option. While this is time consuming, this one looks like the best option. For subsequent execution - we can always use "REPEAT" (thereby not repeating same calculation to see which columns actually have skewed enough value to warrant a histogram).

I also learnt about a new Oracle 10g-R2 (10.2) optimizer parameter called optimizer_secure_view_merging

Wasn't this achieved with MERGE / NO_MERGE and PUSH_PRED / NO_PUSH_PRED hints? Have to do more testing on this parameter.

posted by Nilendu  # 4:44 PM