RE: Completely insane execution plan with 11.2.0.3 but not with 11.2.0.2
Date: Tue, 24 Jul 2012 17:37:19 -0400
Message-ID: <01cb01cd69e4$81e0b660$85a22320$_at_rsiz.com>
Test case builder is not intended to be used on the production database, is it? (If it is, I think that is a security bug, a change management bug, and in general dangerous.)
Now especially given your find and example, I *hope* it comes in future with a stronger warning about either not running it on production or running it very carefully on production.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Niall Litchfield
Sent: Tuesday, July 24, 2012 4:33 PM
To: greg_at_structureddata.org
Cc: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org
Subject: Re: Completely insane execution plan with 11.2.0.3 but not with
11.2.0.2
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#aut
> oId30
> http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#aut
> oId35
>
> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 24 2012 - 16:37:19 CDT