If you have been a DBA for 7 years, then you know the procedure:
Go to V$SESSION_WAIT and see what are you waiting for. You'll get file
and block (P1 & P2), so you can locate the segment. Do explain plan and
see how this segment is used. In my experience, it's usually an index used to
retrieve the whole table. Then use the classic RBO methods to disable the
index (||'',+0).
What I object to is the casual fashion you used to talk about the data
dictionary modifications. Sure, we've all done that but we've never bragged
about it. It's like sex: that's how we all came in this world but it is rude
to talk about that. Talking about dictionary modifications in "look mom, no
hands" way makes us look like irresponsible hackers, which is not the image
I'd like to project. Thus the rebuke.
On 2003.07.27 05:29, rabbit_at_emirates.net.ae wrote:
> 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).
>
--
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).
Received on Sun Jul 27 2003 - 14:24:24 CDT
