RE: 2 small tables - Query takes 3 hours

Sam

   Well, sometimes ya just gotta tell the vendor NO. I would start by explaining to the people in your organization and the vendor why this is a very bad idea, totally unsupported by Oracle. If due to the local politics this isn't feasible, make sure you keep this application on its own instance. Run the upgrade on a test instance first.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Sunday, July 27, 2003 4:29 AM
To: Multiple recipients of list ORACLE-L  

This view is part of the upgrade script provided by the Vendor (duh):

I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- .

 I was looking for some proper advise as Ive never seen anything like it before.

Sam

• Original Message ----- Date: Sunday, July 27, 2003 11:39 am

> People should not play with the SYS schema, period. No ifs, no
buts,
> it just isn't done. You are on your own, pal. If I were your employer,
> you would have hit the road by now. Whatever application that
Maximo
> thing is, creating objects owned by SYS is simply not acceptable.
> It's guys like you that give database administrators a bad name.
>
>
> On 2003.07.27 01:29, rabbit_at_emirates.net.ae wrote:
> > Unix Solaris 8 and Oracle 8.1.7.4
> >
> > As part of an upgrade to the Maximo application I run a join on 2
> > tables:
> >
> > select count(*) from sys.syskeys s, maxsysindexes m where
> > s.ixname=m.name;
> > sys.syskeys is 705 rows and maxsysindexes is 443 rows.
> > when I trace the statement I find it doing a hash join and it
> estimates> 434 blocks and it does each block in 30 secs.
> >
> > Prior to running the query I create the sys.syskeys view as
detailed
> > below:
> > The only way I get round the problem is to recreate the
> > maxsysindexes table: And of course query then takes 1 second. I
> > have disabled hash join and the query just goes down another
path
> > taking just as long.
> >
> > Any thoughts as this only happened once the first upgrade in 10
> > schemas, now its happening all the time.
> >
> > --schema owner
> >
> > DROP VIEW SYS.SYSKEYS;
> >
> > CREATE VIEW SYS.SYSKEYS
> > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ,
> > ORDERING, FUNCTION)
> > AS
> > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', ''
> > FROM SYS.COL$ C, SYS.OBJ$ IDX,
> > SYS.OBJ$ BASE, SYS.ICOL$ IC,
> > SYS.USER$ IO, SYS.USER$ BO
> > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ#
> > AND IC.COL# = C.COL#
> > AND IC.BO# = BASE.OBJ#
> > AND IO.USER# = IDX.OWNER#
> > AND BO.USER# = BASE.OWNER#
> > AND IC.OBJ# = IDX.OBJ#
> > AND (IDX.OWNER# = UID OR
> > BASE.OWNER# = UID
> > OR
> > BASE.OBJ# IN ( SELECT OBJ#
> > from sys.objauth$
> > where grantee# in ( select kzsrorol
> > from x$kzsro
> > )
> > )
> > )
> > ;
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> > INET: rabbit_at_emirates.net.ae
> >
> > Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services> ---------------------------------------------------------
> ------------
> > To REMOVE yourself from this mailing list, send an E-Mail
message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
and in
> > the message BODY, include a line containing: UNSUB ORACLE-
L
> > (or the name of mailing list you want to be removed from). You
may
> > also send the HELP command for other information (like
subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> -------------------------------------------------------------------
> --
> To REMOVE yourself from this mailing list, send an E-Mail
message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You
may
> also send the HELP command for other information (like
subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: rabbit_at_emirates.net.ae

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).