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, [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
