OraNA :: Oracle News Aggregator

I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL.

This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child operations including an INLIST operator and other children which then (more...)

From a reply I gave on an OTN forum thread, how to see all queries currently in memory which use dynamic sampling?

Dynamic sampling is an attribute in V$SQL_PLAN.OTHER_XML.

In 11gR1, it says “yes”.
In 11gR2, it gives the level.

Using EXTRACTVALUE which is deprecated in 11.2:

select p.sql_id,  extractvalue(h.column_value,'/info') lvl
from   v$sql_plan p
,      table(xmlsequence(extract(xmltype(p.other_xml),'/other_xml/info'))) h
where  p.other_xml is not null
and    extractvalue(h.column_value,'/info/@type') = 'dynamic_sampling';

Another approach using one of the prefered alternatives – XMLTABLE:

select p.sql_id, t.val
from   v$sql_plan p
,      xmltable('for $i in /other_xml/info
                 where $i/@type eq  (more...)

Short on examples, long on words…

Last week I did a quick post about a couple of the more obscure implications of using distributed transactions, in particular:

• The current impact on subquery materialisations
• The incompatibility with MVs
• The unusual situation of transactions with locks but no sessions

Anyway, earlier today I was having a closer look at some “DFS lock handle” wait events in an 11gR1 RAC database (not because there was an obvious problem but because I saw a few of them an wanted to have a closer look).

On a DFS lock handle wait, you have to (more...)

Or what happens when you don’t have node affinity.

Here’s an old, short post that was lying around unpublished.

SQL executed on the 14th on node 2, on 15th on node 1.

Timings in seconds.

32625 seconds is just over 9 hours, of which 32175 is cluster wait time.

Database services and application datasource config have now been reconfigured to the extent that there is node affinity and the data required for the workload on node 1 is completely segregated (more...)

Three items within:
Part 1 – Subquery materialisation not possible
Part 2 – Materialised views
Part 3 – Lock scripts

Part 1 – Subquery materialisation not possible
Yesterday I was reminder of a familiar issue, officially listed as a bug, that can occur when combining subquery materialisation with distributed transactions.

I had previously tuned a query for a colleague, the best approach for which was using implicit materialised subquery factoring (implicit because it wasn’t hinted as /*+ materalize */ but was referenced multiple times in the main query) and I had provided such a solution, without even a thought (more...)

Of course, one of the first places you should go is Oracle Support, but if you, like me, put off doing that and scratch your head for a while wondering why ELAPSED_TIME in V$SQL might be significantly lower than CPU_TIME when there are no obvious other explanations, then note these two bugs in 11.1.0.7:

BUG 7168259 – Millisecond elapsed_time shows as 0 : Elapsed_time for SQL statements which execute in under one millisecond can show up as 0

BUG 7561762 – Elapsed_time is under-reported : Elapsed_time can be 0 or significantly less than V$SQL.CPU_TIME if (more...)

Today I have been looking at an application problem.

Last night important client processing missed its SLA with the application reporting ORA-02049: timeout: distributed timeout waiting for lock.

This post isn’t about the distributed lock timing out.

But consider the evidence that further investigation into these distributed locks threw up.

1. The parameter distributed_lock_timeout is set to the default of 60 (seconds) – this means that any distributed transaction that waits for an enqueue for 60 seconds will be picked off by a sniper, similar to the standard deadlock detection after 3 seconds.
2. The 1-in-10 second ASH samples in DBA_HIST_ACTIVE_SESS_HISTORY show (more...)

A quick example about things to consider when considering rewriting a SQL OR condition to a UNION/UNION ALL, as pointed out to me by Jonathan Lewis referencing this post on this recent OTN forum thread.

At the heart of the issue are:

• how to deal with the possibility of duplicates
• the potential impact of NULLS

The impact of NULLS is best described by Jonathan in the article already mentioned so I’m going to use a noddy example to illustrate:

• the problem of duplicates
• why suggesting a UNION is not an appropriate generic approach (although it may work for specific examples)
• (more...)

Here’s a little thing about plan_hash_2 that’s come via a pointer from Jonathan Lewis to bug 10162430.

From querying DBA_HIST_SQLSTAT I happened to notice that over a period of 65 days a particular query had 63 different plans (PLAN_HASH_VALUE).

I found that the differences were down done to the different internal temporary table names used for a materialized subquery, i.e. the plans were essentially the same but for the name of SYS_TEMP_0FD9D6786_B6EF87D2, SYS_TEMP_0FD9D6825_BE8671F, SYS_TEMP_0FD9D6684_EAD00827, etc.

Exactly what bug 10162430 addresses is unclear because it is listed as fixed in 11.2.0.3 and my different plan hashes (more...)

Stated requirement from the forum:
To have a foreign key to a parent table only when the status in the child table is a certain value whilst allowing for rubbish and NULLs in the child’s fk column when the status is not ACTIVE.

We can do this with a virtual column.

Here’s how.

Setup:

SQL> create table t1
  2  (col1 number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> create table t2
  2  (col1    number primary key
  3  ,status  varchar2(12) not null
  4  ,col2    number
  5  ,col2v   number generated always as (case when status  (more...)