A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post includes links to video tutorials explaining what the tool is about.
The new version comes with numerous improvements and new features. The most important ones are:
From time to time I’ve mentioned the fact that the optimizer will sort indexes alphabetically by name as the last tie-breaker when two plans have the same cost. Thanks to an email that arrived a couple of days ago I’ve been alerted to event 10089 (which has been around since at least 18.104.22.168) with the description: “CBO Disable index sorting”.
Setting this event will disable the index sorting (which may result in some tied execution plans changing, of course) and may reduce contention on the row cache objects latches if you’re running 22.214.171.124 or later and doing a lot of hard parsing.
A couple of days ago I wrote an article about Oracle picking the “wrong index” after an index rebuild, and I mentioned that the sample data I had generated looked a little odd because it came from a script I had been using to investigate a completely different problem. This note describes that other problem, which appeared on the Oracle-L mailing list last month.
By my count there are 109 new v$ and gv$ dynamic performance views in 12c (so far) – and Glen Fawcett has posted a short note on a group that may be of particular benefit to anyone who finds they really have to delve into esoteric I/O problems from time to time. For the less exotic, there’s v$io_outliers and v$lgwrio_outliers which give details about any very slow I/Os – for the more exotic there’s v$kernel_io_outliers – which is the really fascinating one.
Here’s a short session capture focused on v$io_outliers:
Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be deterministic – the answer seems to be “not yet”. Here’s a simple script that you can run from end to end to check current and future releases – it compares inline (with) and standalone functions when the “deterministic” keyword has been used.
Here’s a quirky little thing I discovered about 5 minutes after installing 12c Beta 1 (cut-n-pasted from SQL*Plus):
create or replace view v$my_stats as select ms.sid, sn.statistic#, sn.name, sn.class, ms.value from v$mystat ms, v$statname sn where sn.statistic# = ms.statistic# 14 ; create or replace view v$my_stats * ERROR at line 1: ORA-00999: invalid view name
You can’t create views with names that start with V$ or GV$ in the sys schema. Presumably to eliminate the risk of someone’s clever view definition from overwriting and disabling one of the distributed dynamic performance views by accident.
I thought I’d try to spend some of today catching up on old comments – first the easier ones, then the outstanding questions on Oracle Core.
The very first one I looked at was about pushing predicates, and the specific comment prompted me to jot down this little note about the 10053 trace file (the CBO trace).
Here’s an interesting anomaly from the OTN database forum – PL/SQL track. It’s a single row delete from a table that does a huge number of db block gets and (in the first example shown) physical reads. Here’s the supplied output with autotrace enabled:
I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):
select count(*) from t1 where n1 = n2 ; create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select mod(rownum, 1000) n1, mod(rownum, 1000) n2 from generator v1, generator v2 where rownum <= 1e6 ;
If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.
Some time ago – actually a few years ago – I wrote a note about the hint /*+ gather_plan_statistics */ making some informal comments about the implementation and relevant hidden parameters. I’ve recently discovered a couple of notes from Alexander Anokhin describing the feature in far more detail and describing some of the misleading side effects of the implementaiton. There are two parts (so far): part 1 and part 2.