Archive

Posts Tagged ‘Oracle 11gr1’

Oracle : ANSI vs Old Style Joins

August 17, 2013 Leave a comment

I came across this problem and I still can’t find any documented reason as to why the Oracle query processor can not handle mixing the join types in a statement. The reason I was mixing join types at the time was due to an ORA-600 bug involving nested table types; the cross join to the nested table field was not working properly. This resulted in me having to use a combination of ANSI joins (which is my default approach to joins for many years) and the old style join using a comma and the where clause.

So a quick look at the docs : http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm – the query_block::= section is what interests me here.

query_block

Now for me, the train tracks here show a syntax fault. The loop back to add another join shows a comma on it which is not required depending on the route taken after the FROM part of the statement. technically, that would end up with a FROM clause like this:

FROM tbl_a, INNER JOIN tbl_b on tbl_a.field = tbl_d.field

We know that is just not legal syntax – so we can already consider this train track diagram a bit off. Am I reading the diagram incorrectly? http://docs.oracle.com/cd/B28359_01/server.111/b28286/ap_syntx.htm#SQLRF018 is the guide to reading them, and the syntax loops section hows an example where fields are seperated by commas in a loop. Optional keywords and parameters show that if the comma was optional, it would appear differently in the diagram.

Using the diagram we should also be able to use this syntax:
select *
FROM tbl_a a, tbl_b b
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 = b.field2

This again fails – although the error thrown is correct, but not as helpful as you might like.
ORA-00904: "A"."FIELD1": invalid identifier
00904. 00000 - "%s: invalid identifier"

So the error is basically saying that the query processor can not find a reference to A.FIELD1, even though it is clearly there in the FROM clause. This leads me to assume that it processes ANSI joins first, and that the old style joins using a where clause are processed second. At the time that the ANSI join is processed it has no way of knowing A.FIELD1 does indeed exist.

Could the error message be better? most definitely, if you tried the same but using an old style left join, like this:
select *
FROM tbl_a a, tbl_b b
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 (+)= b.field2

Oracle will kindly inform you that ORA-25156: old style outer join (+) cannot be used with ANSI joins – so the query processor is quite aware of the distinction between the join styles. Just to add to the frustration, re-ordering the joins allows it to work:
select *
FROM tbl_b b, tbl_a a
INNER JOIN tbl_c c on c.field1 = a.field1
WHERE c.field2 = b.field2

This is because the ANSI join processed sees this as tbl_a inner join tbl_c, so tbl_a is ‘visible’ to the query processor again.

This is just an annoying bit of behaviour by the query processor with bad error messaging and it seems flawed documentation – to be fair, if I was not forced to mix join styles due to the ORA-600, I wouldn’t have come across it either.

Categories: Oracle Tags: ,

Oracle : Recursive Common Table Expressions and Parallelism

April 14, 2012 3 comments

Sometimes, no matter how hard you search you just can’t find an answer – that was the problem this week. Oracle’s recursive common table expressions (RCTE), or Recursive Sub Query Refactoring to put it in Oracle’s terms were proving to be pretty bad on performance. (Hopefully, the next person searching will now find this answer.)

As feature’s go, this one is should be a relatively well-known feature – it’s part of the ANSI SQL-99 standard and available in a number of RDBMs, with near identical implementation on the syntax.

Even the esteemed Mr Kyte has changed his position on RCTE’s from being more code and harder to understand than the CONNECT BY syntax, to being a somewhat useful feature.

So what was the question to which we could find no answer?

Why does a RCTE seem to ignore parallel hints?

Amazingly, we can’t find anything documented about this against RCTE’s themselves or in the parallelism sections of the documentation. No mention of restrictions of parallelism on RCTE’s appear anywhere.

We have quite a complex example but needed a simple scenario to submit to Oracle to get an answer. Kudos for the krufting of this goes to Phil Miesle – it was his turn to deal with Oracle support.

First, create a numbers table, and fill it with data, we even used a RCTE to do that part.

create table vals (n ,constraint vals_pk primary key (n) ) 
as 
with numbers(n) as (
select 1 as n    
from dual   
union all  
select n+1 as n    
from numbers   
where n < 1000000 
)
select n from numbers;

We now need a data table, that is basically going to act as a hierarchy, for us to test the RCTE against. A simple parent / child table is suffice:

create table pairs (
  parent 
  ,child 
  ,constraint pairs_pk primary key (parent,child) ) 
as 
select 
  'B'||to_char(mod(n,100000)+1) as parent      
  ,'A'||to_char(n) as child   from vals;

Using the numbers table, the table now contains A1 to 1000000 linking to B1 to B100000 – so in effect, every B has 10 A’s linked to it.

This then continues, with every 10 B’s linking to a C:

insert into pairs 
select distinct        
  'C'||to_char(mod(n,10000)+1) as parent      
   ,'B'||to_char(mod(n,100000)+1) as child  
from vals;

And so on, with each successive layer having a 10 to 1 ratio:

insert into pairs 
select distinct       
  'D'||to_char(mod(n,1000)+1) as parent      
  ,'C'||to_char(mod(n,10000)+1) as child  
from vals;

insert into pairs 
select distinct       
  'E'||to_char(mod(n,100)+1) as parent       
  ,'D'||to_char(mod(n,1000)+1) as child  
from vals;

insert into pairs 
select distinct        
  'F'||to_char(mod(n,10)+1) as parent      
  ,'E'||to_char(mod(n,100)+1) as child  
from vals;

insert into pairs 
select distinct       
  'G'||to_char(mod(n,1)+1) as parent      
  ,'F'||to_char(mod(n,10)+1) as child  
from vals;
commit;

And finally we have G1 linking to F1 to F10 – so the structure is clearly a very basic tree.

Gather the stats to make sure the optimizer is going to have a half chance at a decent plan.

begin 
dbms_stats.gather_table_stats(ownname=>user,tabname=>'PAIRS'); 
end; 
/

So for the test cast query, we wished to generate all the possible paths within the tree – which is in effect a non-cyclical directed graph. This is the ideal scenario for connect by / RCTE to perform its magic, I need to recurse the dataset in a single set based statement.

explain plan for
select count(*)
from (
  with e (parent,child) 
  as (
    select parent,child
    from pairs
    union all
    select e.parent,pairs.child
    from e
    join pairs on e.child = pairs.parent
  )
  select * from e
);

Grab the plan output using

select * from table(dbms_xplan.display)

- which I’ve included the important bit below:

---------------------------------------------------- 
| Id  | Operation                                  |
---------------------------------------------------- 
|   0 | SELECT STATEMENT                           |
|   1 |  SORT AGGREGATE                            |
|   2 |   VIEW                                     |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|
|   4 |     TABLE ACCESS FULL                      |
|*  5 |     HASH JOIN                              |
|   6 |      TABLE ACCESS FULL                     |
|   7 |      RECURSIVE WITH PUMP                   |
----------------------------------------------------

There is nothing shocking or unusual about the plan, it is what we would expect to see. So let’s now add some parallelism to the query:

explain plan for
select count(*)
from (
  with e (parent,child) 
  as (
    select /*+ parallel(pairs,4) */
      parent,child
    from pairs
    union all
    select  /*+ parallel(pairs,4) parallel(e,4) */ 
      e.parent,pairs.child
    from e
    join pairs on e.child = pairs.parent
  )
  select * from e
);

The expected effect on the plan should be that we would see parallelism operations against both table accesses.

----------------------------------------------------
| Id  | Operation                                  |
----------------------------------------------------
|   0 | SELECT STATEMENT                           |
|   1 |  SORT AGGREGATE                            |
|   2 |   VIEW                                     |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|
|   4 |     PX COORDINATOR                         |
|   5 |      PX SEND QC (RANDOM)                   |
|   6 |       PX BLOCK ITERATOR                    |
|   7 |        TABLE ACCESS FULL                   |
|*  8 |     HASH JOIN                              |
|   9 |      TABLE ACCESS FULL                     |
|  10 |      RECURSIVE WITH PUMP                   |
----------------------------------------------------

This now shows us the problem, you can see the PX Co-ordinator is present within the anchor clause of the RCTE, but there is no parallelism listed against the recursion. At first we though it might be ignoring the hints for some reason, but the following idea disproved that theory immediately.

explain plan for
select count(*)
from (
  with e (parent,child) 
  as (
    select /*+ parallel(pairs,4) */
      parent,child
    from pairs
    union all
    select  /*+ parallel(pairs,4) parallel(e,4) use_merge(e,pairs) */ 
      e.parent,pairs.child
    from e
    join pairs on e.child = pairs.parent
  )
  select * from e
);

The plan altered to use the merge hint as follows:

---------------------------------------------------- 
| Id  | Operation                                  |
| --------------------------------------------------
|   0 | SELECT STATEMENT                           |
|   1 |  SORT AGGREGATE                            |
|   2 |   VIEW                                     |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|
|   4 |     PX COORDINATOR                         |
|   5 |      PX SEND QC (RANDOM)                   |
|   6 |       PX BLOCK ITERATOR                    |
|   7 |        TABLE ACCESS FULL                   |
|   8 |     MERGE JOIN                             |
|   9 |      SORT JOIN                             |
|  10 |       RECURSIVE WITH PUMP                  |
|* 11 |      SORT JOIN                             |
|  12 |       TABLE ACCESS FULL                    |
----------------------------------------------------

The explain plan is of course a terrible plan – there would be no reason to use a merge join, but the fact it appears in the plan demonstrates the hints on the recursion clause are being read by the query engine and that it chose to discard the parallelism ones.

Given this example – an SR was raised to find out why the performance is so bad, and are we looking at a bug? If it was a bug, then we could look for a fix of some kind.

The test case was accepted and reproduced inside Oracle very efficiently – it was given to the parallel query department to determine what was the problem.

The response back?

This is the expected behavior. Oracle does not parallelize the iterations sub-query of connect-by/recursive-with clause.

That’s the last thing we wanted to hear – ‘by design’. It’s by design that this feature is going to be incredibly slow on larger data sets. That’s not so much as ‘design’ as rendering RCTE’s useless in Oracle unless you have small data sets, or don’t mind waiting around for a long time to get answers back.

We were already close to ditching any use of the RCTE syntax, this fully nailed the coffin shut on that feature.
(The other reason we are still looking to sort out the test case for – but we have witnessed problems with RCTEs contained within a view. When the view is joined to and accessed with a predicate against the view, we have seen the predicate pushed into the recursion – which results in an incorrect answer. The predicate pushing cuts the recursion short in effect. We had worked around this – but it was an annoying bug.)

Oracle stalwarts will consider that we were foolish to use the RCTE’s over oracle connect by syntax – except that we were not. An RCTE can do far more complex recursion than the Connect By can do, and for the specific instance we wanted to use it, that complexity was required

Another reason for trying to go down that route was performance, because the connect by clause is no better at parallelism:

explain plan for 
select count(*) 
from 
(  
  select /*+ parallel(pairs,4) */         
    parent,child    
  from pairs   
  start with parent = 'G1' 
  connect by parent = prior child 
);

-------------------------------------
| Id  | Operation                   | 
-------------------------------------
|   0 | SELECT STATEMENT            |
|   1 |  SORT AGGREGATE             |
|   2 |   VIEW                      |
|*  3 |    CONNECT BY WITH FILTERING|
|*  4 |     INDEX RANGE SCAN        |
|   5 |     NESTED LOOPS            |
|   6 |      CONNECT BY PUMP        |
|*  7 |      INDEX RANGE SCAN       |
-------------------------------------

The plan is no better for using a CONNECT BY – but from a performance perspective  the connect by clause is clearly faster when we ran some comparisons.

So the verdict on Oracle and RCTE / Recursive Sub-Query Refactoring – excellent language feature – unscalable performance – will refuse to parallel the recursion – very useless for those of us in the VLDB world.

Oracle : Duplicate GUID values being returned from sys_guid() when run in parallel

January 22, 2012 2 comments

A post? yes, it’s been a while and because I am having to spend all my time on Oracle these days – it’s a post relating to a problem in Oracle.

I had to construct a test case recently to try track down a primary key failure. The primary key was a sys_guid value and the failure was coming from the insertion of new values, that didn’t make much sense since the odds of a collision on a GUID should be astronomically high – assuming they used an up to date algorithm. Even with those astronomical odds, primary key failures were occurring very regularly, so the immediate suspicion is that the sys_guid algorithm in Oracle is not up to date and not-consistent across all platforms. It can return GUIDs that appear totally random, or GUIDs that are clearly within a sequence. It’s easy enough to test any individual platform to see how it behaves:

select sys_guid from dual
union
select sys_guid() from dual;
SYS_GUID()
--------------------------------
B71D52B1531167D9E040760ADD7E0B80
B71D52B1531267D9E040760ADD7E0B80

12th character in has increased by one, the rest of the guid remains identical.

This isn’t too surprising, the documentation is delightfully vague in using the term ‘most':

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

So ‘most’ platforms will behave like this – that’s helpful documentation, thanks for that.

So back to the problem and test case – whenever I come across potential Oracle bugs, I have an immediate suspicion that parallelism is at play – this is just from the consistent experience of Oracle getting parallelism wrong within the database – I have multiple outstanding SR’s for various features when combined with parallelism causing failures – anything from ORA-600’s to incorrect data being returned. (Parallel + Pivot = missing columns, nice!).

When you have these GUIDs being generated in a pseudo sequence, it makes sense that adding parallelism is a recipe for disaster, since the parallel slaves would all have to communicate and co-ordinate to ensure that they did not duplicate values in that sequence. After many hours whittling down the original statement, I was able to construct a repeatable test case to finally submit to Oracle for fixing – the shocking part is how trivial it was to demonstrate the problem on a specific AIX environment.

So let’s walk through the test case, firstly, create a numbers table:

create table n (c1 number);

..and populate it:

begin  
  for i in 1..30 loop   
    insert into n     
      select i*100000 + level from dual connect by level<=100000;  
  end loop;
  commit;
end;
/

This just populates the table with 3 million rows, 30 iterations of 100k rows, it’s a bit faster to do it that way than populate it in a single statement – the connect by level goes slower as the number rises.

That is all we need for the set up, the test code is pretty simple but I will explain it:

declare 
  e number := 0;
begin  
  for i in 1..10 loop
    begin      
      select count(*) into e      
      FROM (        
        select sid, count(*)        
        from (
          select /*+ parallel(n,40) */                
            sys_guid() as sid              
          from n              
        )        
        group by sid        
        having count(*) > 1      
      ) t;    
    exception      
      when no_data_found then null;      
      when others then raise;    
    end;            
    if e>0 then raise_application_error(-20000
        ,e||' duplicates found in iteration '||i); end if;
  end loop;
end; /

The easiest way to explain this is from the inside out – the inner most query generates 3 million sys_guid values by selecting from the numbers table and asking for a sys_guid value per row – the statement is given a parallel hint.

We then perform an outer select that group’s by the SID (Sys guID) values, and uses a having count(*) > 1 clause to only show duplicates. Under normal conditions this of course should return 0 rows at that point, since every sys_guid generated should be unique. The next outer select count’s up how many instances of duplicates occurred and finally places this into a variable e.

If e is ever greater than 0, we have encountered a duplicate and an error will be raised.

When run on an AIX box with SMT enabled, the error does get raised.

202148 duplicates found in iteration 1

The number of duplicates changes per run and seems to have no pattern; it can be anything from about ~40k duplicates up to ~250k duplicates. If you take the parallel hint out of the script, it never fails. So it is clearly linked to the simultanesous creation of sys_guid values.

As yet, Oracle have not been able to reproduce this themselves which is indicating that this is a platform specific bug, but the client’s DBA’s have been provided the script and have seen it churn out duplicates time and time again, much to their amazement. They really should use a better algorithm, having such a predictable sequentially guid as their default guid for ‘most’ platforms is less than ideal.

Categories: Oracle Tags: ,
Follow

Get every new post delivered to your Inbox.