Re: Completely insane execution plan with 11.2.0.3 but not with 11.2.0.2

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 24 Jul 2012 21:32:43 +0100
Message-ID: <CABe10sYpL0zy+nsUYpKFUj7L4K+k2NXTTVhLWoDK7hF=EQLCFg_at_mail.gmail.com>

Test case builder does indeed not require a separate license (us outside the Oracle firewall tend to be conscious of these things for some reason).

Aside

One thing that caught me out when investigating test case builder is that it recreates the objects and dependencies involved in a new schema. This is of course by design. Now consider a schema as follows pseudo code

User A

---

create table A.t1 (id number not null

, c1 varchar2(50)
, c2 date
, constraint pk_t1 primary key (id) );

create table A.t1_aud(

   id number
, c2 date
, last_update);

create or replace trigger A.trg_t1
before insert on A.t1
begin
  insert into A.t2 (id,c2,last_update) values (:new.id,new.c2, sysdate); end;
/

then the code with the fully qualified object references will be created with the same fully qualified object references in the test case schema. Subsequent testing will try and insert into the wrong table which either invalidates the test case, or messes up your original data. Fortunately this failed for me because the trigger in test failed to compile because the test case user didn't have privs on user A's objects.

On Tue, Jul 24, 2012 at 4:58 PM, Greg Rahn <greg_at_structureddata.org> wrote:

> I'm nearly 100% sure that dbms_sqldiag is not part of any management pack
> license (do note you mentioned dbms_sqltune which is not required for TCB
> export).
> See there is no mention of it here:
> http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#autoId30
> http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#autoId35
>
> I think your best bet is to be able to package the issue so Oracle Support
> can reproduce it.
>
>
> On Tue, Jul 24, 2012 at 4:26 AM, Thomas Kellerer <
> thomas.kellerer_at_mgm-tp.com
> > wrote:
>
> > Hi Greg,
> >
> > thanks. Will do that but it will take a while because we don't have
> direct
> > access to production.
> > (And the tuning/diagnostic packs are not licensed on either machine, so I
> > cannot use dbms_sqltune)
> >
> >
> --
> Regards,
> Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <
> http://bit.ly/v733dJ> |
> linkedin <http://linkd.in/gregrahn>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 24 2012 - 15:32:43 CDT