Oracle Performance Riddle

Can a select statement create a transaction Lock?

Recently I had to research a “enq: TM contention” wait event. I queried active session history (ASH) an found blocking session’s id.

I queried ASH again and found what the blocking session was doing right at the time when the other session was waiting on the “enq: TM contention” lock. At this time there was a long running select statement active in the blocking session.

How is that possible? Can a select create a lock? What must have happened ? Would it actually help to tune the select?

For a further hint you might wat to read Arup Nanda’s blog on transaction locks.

enq: TX row lock contention and enq:TM contention

Index Rebuild: Magic or Voodoo?

I actually don’t like to write about todays topic. However, I hope you will agree this story is worth to be told.

Via Freelist somebody asked if an Index rebuild could be occaisionally usefull. There was a case at hand, where index rebuild seemed to be usefull after a big delete. I sometimes got annoyed by the popular myth, that regular index rebuild would be helpfull. This is one of the more popular database myths as branded by Mr. Index, Richard Foote.

Thus, I could not resist the opportunity to trash the myth I don’t like and answered that there is no such case. I ignored the warning wisper in my head telling me that things are never that easy.
Of course, the unavoidable happened Jonathan Lewis corrected me stating that there are corner cases were index rebuild can be useful ( e.g. after a big delete).

As if this would not be enough shortly thereafter I came accross this little statement below. (I presenting a test case that makes use of the SH schema.)

select time_id from sales where sparse is not null;

The Exection Plan with runtime statistics looks like that:

| Id  | Operation                                  | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
|   0 | SELECT STATEMENT                           |            |      1 |        |      0 |00:00:00.04 |   41770 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES      |      1 |      1 |      0 |00:00:00.04 |   41770 |
|*  2 |   INDEX FULL SCAN                          | SPARSE_IDX |      1 |      1 |      0 |00:00:00.04 |   41770 |

Predicate Information (identified by operation id):

   2 - filter("SPARSE" IS NOT NULL)

Why did the optimizer choose a full index scan? Actually a full table scan would be much better. The estimate (E-Rows) is correct. The statistcs are current.

Can you guess what is missing from my test case? Hint: I researched the DML on this table and I found an update statement.

A Strange Condition in the Where Clause

It makes me proud to learn that Carlos Sierra is following my blog. I knew Carlos from my time at Oracle, even though we only recently met for the first time. I see Carlos as a man who acts rather than complains. One of the people that make a difference.
With my next example I will show that it is also an astute analyst.
I recently saw a condition in an execution plan that was not present in the sql statement. I wanted to know how the condition came from.
I give a simple example: Consider a table x that looking like this:

SQL> desc x
Name Null? Typ
----------------------------------------- -------- -----------------

Let us do a query on x:

select count(*) from x where z=1;

The execution plan looks like this:

Plan hash value: 989401810

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |      |       |       |   420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| X    |  1144K|    14M|   420   (2)| 00:00:01 |

Predicate Information (identified by operation id):

   2 - filter(NVL("Z",1)=1)

Why is does the execution plan show this strange condition? You will find the answer in Carlos Sierra’s blog.
One more hint: It has something to do with default values. Good luck.

Parallel Activity just drops

In one of my DWH POCs for Oracle’s Real World Performance Group we did see an unexpected drop of activity. The customer wanted to know why it happened.
This is how sql monitor’s activity tab looked:

I investigated carefully and found out that the reason was in both cases an outer join that looked liked that

ON T2.X_ID    = T1.X_ID

I found that 90% of all values in T1.X_ID were Null values. The distribution for the join was hash. Thus, the parallel slave dealing with null values got most of the work and was for quite some time the only one running. Table T1 was the biggest table, to broadcast it was out of question.
(You may also compare Randolf’s article on skew parallel distribution: Parallel Execution Skew – Demonstrating Skew).

Can you now re-code the join condition such that the join is done by all parallel slaves? (This was version 11G. In version 12 the optimizer should take care of it.)  Further assumption: table T2 has a one column primary key call T2_id.


The Collection in The Collection

We had to rewrite a commonly used PL / SQL function. The code suffered heavily from the Context Switch. He literally was called millions of times. We set ourselves the goal of having a single Bulk Collect to read all data from the database . I give here a code skeleton that I have adapted to the scott / tiger schema.

   CURSOR c1
   SELECT deptno, dname
     FROM dept;
   CURSOR c2 (p_deptno NUMBER)
       SELECT empno,ename,sal, comm
         FROM emp
        WHERE deptno= p_deptno;
   FOR c1rec IN C1
      FOR c2rec IN c2(c1rec.deptno)
      END LOOP;


In reality, of course, there was complex logic instead of null in the loop. I worked jointly with a developer of the Software vendors on the problem. We did not have enough time to understand this code. We decided to only formally convert the code and to keep the loops. As mentioned, we wanted to read all the data in a single step in a nested array.
There should be an outer department collection that contains an inner employee collection as a nested table. There are other solutions (as Bryn Llewellyn thankfully pointed out one in the comment), but the nested table solution merges smoothly with the legacy code. As in our example also in reality the amount of data per query was so small that it all would easily fit into one array .

How does the select look like?


Is the migration database slower?

My customer set up a database in a virtual environment to support a migration. Initial tests show that the migration database is many times slower than the production database. The tests focus on a particular query.

Here is an excerpt from an AWR of the migration database. You can see the relevant data from the sql command that you should investigate as part of the list “SQL ordered by elapsed time”.

apsed Time (s)


Elapsed Time per Exec (s)










For comparison, the same data from the production database:

Elapsed Time (s)


Elapsed Time per Exec (s)










What makes you curious? What working hypothesis would you choose to start your investigation and what would you check? Hint: This sql statement we investigate is a count which scans only one table. The execution plan is identical in both cases, it is in each case a full table scan.

Why is the new Hardware slow?

You buy new hardware to be faster. This is a normal expectation. But what if the new hardware is slower than the old one? The speculation about the cause went wildly. Since I had to wait for this assignment due to holiday time, the tension was high as the examination could finally begin.

A quick check showed that the new hardware was no slower than the old one. The decisive hint brought a raw trace. I show only the important part.

On the old hardware of trace looked like this:

FETCH #25:c=1154407,e=1152124,p=0,cr=102603,cu=0,mis=0,r=101,dep=0,og=1,tim=650755949521

Cursor # 25 is a large Select that runs slowly. On the new hardware trace looked as follows:

FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0,r=1,dep=0,og=1,plh=3621104505,tim=39783214696
WAIT #601010888: nam='SQL*Net message from client' ela= 171 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217398
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=39783217416
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217423
WAIT #0: nam='SQL*Net message from client' ela= 117 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217546
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217560
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=39783217567

Cursor # 601010888: corresponds cursor # 25 on the old hardware. The database on the new hardware is version 11, the database on the old hardware version 10.
Obviously except for the version there is at least one other difference between the two databases. What is it? What is effect of this difference?
Both databases are accessed via the exact same program, which is implemented using MS Visual Studio.

Why do we not need an alias?

Here’s a question from OTN. I’m curious what you have to say. (Please do not check the answer to OTN.)

select order_id, sum(quantity*unit_price)
from order_items
having sum(quantity*unit_price) > ALL
(select sum(quantity*unit_price)
from customers
join orders using (customer_id)
join order_items using (order_id )
group by order_id)
group by order_id;

The question is: Why do we not need an alias for order_id in the “group by order_id” clause in the sub query, although the column appears in two tables?
Thus, to make it a bit more difficult, I have built a small additional hurdle. STATE_PROVINCE is in the OE schema for version 12 (for other versions I have not tested) component of a type variable. The query is not running as shown here. How should we modify the query so that it will work?

Where did the downgrades come from?

In a data warehousewe we faced a increasing number of downgrades of parallelism. It seemed to be getting worse by the day. Indeed, it turned out that the number of free available parallel processes showed a constant decrease. A closer examination of PX used showed that many had Toad as source.
How was it possible that the number of parallel sessions that originated from Toad grew constantly?
On the other hand, there was still a comfortable number of PX is available. How was it possible that we were still seeing downgrades? What parameters should we check out?

Navigate many shallow hierachies in parallel

Suppose you have a large number of flat hierarchies. As an example we take the emp table from the Scott schema.
Using it is we create a table Tree_emp as shown here:

create table tree_emp as select * from emp;

alter table tree_emp modify (empno number(18));
alter table tree_emp modify (mgr number(18));

INSERT INTO tree_emp
SELECT EMPNO+(10000*step.n) empno,
MGR+(10000*step.n) mgr,
TRUNC(DBMS_RANDOM.value(low => 8, high => 50))*100 SAL,
FROM emp,
(SELECT rownum n FROM dual CONNECT BY rownum <= 1000000
) step

Show for all presidents (mgr is zero) the sum of the salaries of all subordinate . Write a pipelined table function that can navigate multiple trees simultaneously.
Use Oracle’s connect by Syntax.You can index as you like.