Solution: Unpivot using cross apply

Cross apply works in one way like a cartesian join. In addtion we can have a ordering of the tables in the from clause.

For that reason it is possible to reference a table inside the from clause.

In our example below we combine the well-known table emp with a virtual table we call CrossApplied. The CROSS APPLY operator makes sure the two table will be combined like a cartesian product. The point is that inside of my virtual table CrossApplied I can access columns which are in emp (aliased t).
By that way I can make rows for columns in emp.

You will find the execution plan below. For those that do wonder: yes, CROSS APPLY is somewhat similar to LATERAL.

The CROSS APPLY solution was indeed in our test quicker than the UNPIVOT clause.

SELECT  empno,
        CrossApplied.Col_name, CrossApplied.Col_value
  FROM emp t
 CROSS APPLY (Select 'Mgr' col_name, t.Mgr col_value from dual
              UNION ALL
              Select 'job', t.job  from dual
              UNION ALL
              Select 'sal', t.sal  from dual) CrossApplied

| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                 |       |       |    87 (100)|          |
|   1 |  NESTED LOOPS      |                 |    42 |  1386 |    87   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   210 |     3   (0)| 00:00:01 |
|   3 |   VIEW             | VW_LAT_C2E3294A |     3 |    54 |     6   (0)| 00:00:01 |
|   4 |    UNION-ALL       |                 |       |       |            |          |
|   5 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |

Solution: Can a select statement create a transaction Lock?

I would like to thank Andrew Sayer for the correct solution. A select statement without a for update clause can not set a transaction lock. The transaction lock must therefore come from another statement of the same session.

As long as no commit has taken place, the lock is upright. We would therefore have to go back in time using active session history to determine where the transaction lock originated.

Basically, it is a good idea to speed up the long-running Select statement. This allows the transcation lock to be held for a shorter period of time, since the commit point can be reached more quickly.

Solution:Index Rebuild: Magic or Voodoo?

Of course my readers have solved the problem. I would like to thank Jure Bratina, Andrew Sayer and Martin Berger for their contributions.
In the following you will see the entire test case in a commented form. IMHO most of it is self-explanatory.
The following little scripts represents the initial set up. The schema is, as I said, the SH example scheme.

alter table sales add  (sparse varchar2(300)); 
update sales set sparse = rpad('sometext',300, '*');
create index sparse_idx on sales (sparse);
select blocks from user_segments where segment_name ='SPARSE_IDX';

Let us now examine the size of the index segment:

select blocks from user_segments where segment_name ='SPARSE_IDX';


select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';


Here is an update similar to the one I found in the original database:

update sales set sparse=NULL;

918843 rows updated.

exec dbms_stats.gather_table_stats(user,'SALES');

And how does it affect the statistics and the segments?

select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';

select blocks from user_segments where segment_name ='SPARSE_IDX';


The optimizer only checks the index statistics. It does not check the segment statistics. The optimizer therefore believes that the index tiny.
All the non-null values of the column sparse must be in the index. Therefore it is optimal, from the optimizer’s perspective, to scan the supposedly small index.
However, the index segment still has it’s full size. An index rebuld is required to resolve the issue.

SOLUTION: A Strange Condition in the Where Clause

The strange condition is automatically generated by the database.
The cause is the DDL optimization, which are available starting with version 11G.
When you insert an additional column in a table, the database will not necessarily create a physical column.
It may generate a “DDL optimized” column instead. When specifying a default, this column can also be defined not null.
That way the database saves the effort to enhance each record by one column.
It only generates an entry is in the dictionary, which of course is much faster.
Each row of data may contain a real value for the “DDL optimized” column if a value was inserted.
If no value was inserted, the default value is used instead.
Since it is possible that no value exists, the database needs to replace the column names by the formula.
Here is a simple example:

create table x (y number);
insert into x select rownum from dual connect by rownum < 1000000;
alter table x add ( z number default 1 not null);
select 1 from x where z=1;

If we look at the execution plan of the query, we notice that the column name Z was replaced by the formula:


Here the link to Carlos’s Blog: Interesting case where a full table scan is chosen instead of an index

Solution: Parallel Activity just drops

As you remember we had a hash join outer with dominant null values. Thus, the parallel process dealing with the null values got most of the work to do.
How would we deal with it? Let’s us back up a bit.
A null value is a special case. We are comparing in our join a null value versus a primary key that is defined not null. The null values will certainly not generate a match when joining. Therefore any of the parallel slaves could do the compare, as long as it is guaranteed not to generate match.
It is therefore beneficial to convert the null values into some other value, as long as we to fulfill two prerequisites:

  1. we have to convert the null values in such way that the get evenly distributed across all parallel slaves
  2. we must make sure that the converted values do not generate a match

It took me a long time to figure this. First I tried something like negative values, but they were still going to the same slave.
The issue is that our newly generated key needs to be roughly in range with the pk of the build table to be spread across all slaves.
I could have tried to figure the range in a with clause reading the data dictionary, but could not get myself to do something I considered uncool.
Instead I tried something else, which is honestly a bit of a hack, but it looks way better. I used the pk of the outer table to generate a not null widespread key.
I also took advantage of the fact that the id’s were integer and changed the join condition to:

ON ( NVL(T2.X_ID, T2_id +0.5)    = T1.X_ID)

That is certainly somewhat tricky. The only thing I can say in favor is that is kind of worked.

After I applied my solution the activity tab looked like that:

Compare this to the previous picture. It is quite impressive.
Well, in version 12 the database should be able to deal with skew join distribution. But it still does not work for all cases of the as Randolf Geist states.
If someone does not like my solution he might have a look into _fix_control 6808773, but I do not warrant either solution. 😉


Solution: The Collection in the Collection

The crucial clues to solve the task I found at Adrian Billington’s Blog. It’s an older post, but still valid: introduction to bulk PL / SQL Enhancements in 9i .
The code below should be self explaining with the notes.
To show how to the data, I have added an output via DBMS_OUTPUT.

— First you have to create the type for nested collection

    SAL  NUMBER(7,2),
    COMM NUMBER(7,2) 
create or replace TYPE tbl_emp  AS TABLE OF emp_t


    SELECT deptno,
      CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
                     ) AS tbl_emp  
           ) as emps -- I need an alias to be able to refer the embedded Collection
  FROM dept d;

  depts tbl_dept ;
   OPEN c1;
   CLOSE c1;
   FOR i IN 1..depts.COUNT
      FOR j IN  1.. depts(i).emps.COUNT
      END LOOP;


Solution: Is the migration database slower?

In OTN was once asked whether the optimizer takes the size of the buffer cache into account swhen creating the execution plan . Under the usual disclaimer (it could in the cost review with incorporated), I would say: no. How should the size of the cache be considered anyway? If I have a larger buffer cache, is more likely that a segement X is in the cache, so I’m doing an index access? (I argue here analogous to parameters OPTIMZER_INDEX_CACHING.) That’s not a good logic. The size Buffer caches is no a safe indicator of whether a particular segment actually in the cache or not. As will be seen later, the size of the cache may be even an argument for the full table scan.

To create an execution plan, the selectivity estimate is the key factor.

However, how an existing execution plan is being executed, is another matter. Here the execution enigine makes the final decision.

For example, a full table scan (FTS) is often executed doing “direct path reads”. This means that inevitable a physical I / O will to be done. (For the database, it is a physical I / O even if the result comes from the cache file system.)

But if the execution engine detects that a table is almost fully represented in the buffer cache, it can switch the execution from a “direct path read” to a “scattered read” The “scattered read” in contrast to “direct path read” will benefit from the buffer cache.

Tanel Poder wrote a good, but a somewhat older  explanation of this concept : Optimizer statistics-driven direct path read decision for full table scans .

In short, there are two prerequisites that must be fulfilled that the execution engine switches to “scattered reads”.

  1.   The buffer cache must be large enough to cache the segment which is the subject of the FTS
  2.  The segment must be actually almost completely cached

The first point can be so easily checked. The analysis showed the following: The table was about 25GB large in production and in the migration.
The buffer cache was currently 55 GB in production. In the migration it was only 5 GB in size.
Now we can conclude with certainty: In the production, the table colud be cached to 100% of it’s size. Theoretically, the runtime engine can therefore initiate a scan in memory. In the miragtion this is certainly not possible.
Was the table now cached to almost 100%  in production?
To clarify this question the below statement, which determines the number of cached blocks of a table can be used.

SELECT sum(num_buf)
FROM X$KCBOQH x, dba_objects o
WHERE x.obj#=o.object_id
AND object_name='my table'
If the so calculated number is almost identical to the number of blocks the table (according to my tests> 90%, but no guarantee)  scattered reads” will be used.

The analysis of the productive database revealed that the table was really for the most part in the Buffer Cache. I could also show the rapid FTS in memory did sometimes not happen due to the lack of cached blocks. We now double-checked our result and increased the buffer cache of the migration environment. Then we loaded the table into the cache.
After that the FTS was fast also in mirgration.

Some remarks:

Frits Hoogland published a newer, deeper analysis of the subject :

Investigating the full table direct path / buffered decision

 Because of several observations I think the exadata storage server can apply similar algorithms in relation to the SmartScan. A SmartScan can be converted into a scan of the storage server flash cache. I do not have enough evidence to be 100% certain however.

Solution: Why is the new Hardware slower

As Martin Preiss already pointed out on Twitter Tanel has already documented LOBREAD SQL Trace entry in Oracle 11.2 that the entries in the trace like LOBREAD are really referring to LOBs and that they probably came in with version
Thus we knew that there was a schema change on the database on the new hardware. The next decisive hint was this piece in the raw trace (e.g. spotted again by Martin Berger):

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


FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0, r=1 ,dep=0,og=1,plh=3621104505,tim=39783214696

Thus it looks like with the LOB we are retrieving one row at a time and without the lob we are doing an array fetch with 101 rows at a time. How is this possible when the program code is identical?
Can the existence of a LOB somehow inhibit array fetch?
Well, indeed it does, as documented here: Single Row Fetch from a LOB  (Thanks Hemant).
Stefan Köhler pointed out, that it also depends on the driver: single row fetch depends on client.
Thus, when we change the LOB column to a varchar2 the new hardware was faster than the old one

Solution: Why is no alias needed?

Well, let’s first answer the supplementary question. It seems that in the SQL types can not be used directly.
As Martin Berx rightly remarked in the comment to the English blog, you must first cast to type.
In this case:


This it is a useful little trick that might help on occasions.
Now you can execute the statement. My guess was that the natural join ensures that you compelled an alias.
In fact

SELECT order_id,
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders o USING (customer_id)
JOIN order_items oi ON (oi.order_id=o.order_id )
GROUP BY order_id
GROUP BY order_id

results in:

ERROR in Line 10:
ORA-00918: column ambiguously defined

You need to define a prefix for Order ID in the group by :

SELECT order_id,
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders USING (customer_id)
JOIN order_items ON (order_items.order_id=orders.order_id )
GROUP BY o.order_id
GROUP BY order_id

In my opinion, it should always work as in the case of the natural join. Since you have already defined that the columns are equal, the optimizer should just choose any.


Solution: Where did the downgrades come from ?

As Stefan already correctly ponted out, the downgrades did origin from parallel sessions that Toad issued. Under special circumstance, the invoking client process has long ceased to exist, but the database sessions stays. As a result, we came under increasing pressure as the number of these sessions grew slowly. There were fewer and fewer new px be allocated because the pool is not infinite.
As a measure, however, we have a profile is activated, that kills inactive session after a certain time.
The second reason was that the parameter parallel_adaptive_multi_user was set to true. This ensured that about a half of the parallel session pools could be used for parallel processing. The parameter was obviously absurd in a data warehouse. However, the local DBA’s insisted not to change the default. For the first time I was faced with the absurd idea that a default is something of a taboo. Instead,they set the parameter was parallel_threads_per_cpu triumphantly to the default of 2. However, the OS had reported all threads as CPUs, so now twice as many CPUs were reported as really existed, as twice as many threads.
As for the downgrades three meaningless values ​​in the parameters have happy neutralized each other. Why, however, the Resource Manager was still running, I honestly do not get. 😉