| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: 2 small tables - Query takes 3 hours
Sam:
The proper advice is not to do it. Also, warn the vendor that what they are
doing will possibly void the support contract with Oracle for any of their
customers that use their softwarre. Ask Oracle about it to get their
opinion, but I'm sure they will tell you that if you alter the Data
Dictionary all bets are off.
You should admonish the vendor for taking liberties with the Data
Dictionary and warn your employer that your Oracle support might be voided.
As a rule, I always insist on looking at the vendors installation scripts
and documentation. I never let them use the SYS account for installation
unless it is absolutely necessary, and even then I try to determine if it
will work with another userid. Any vendor that insists on using SYS's
schema and altering SYS's objects obviously is not used to installing into
Oracle and I would be wary of them. I encountered a vendor like that who
was used to installing into Microsoft SQL Server. He wanted to use SYS
under Oracle believing it was the correct counterpart to the Database Owner
in SQL Server. I wouldn't allow them to use it and I wound up rewriting
most of their installation routine for them for the Oracle database. I
suggested that they give our company a break on the price since we had to
modify their product for them. (I also told our legal and purchasing
department , in case they wanted to arrange compensation or royalties from
the vendor.)
As the DBA you have to put your foot down and let everyone know what the consequences are for altering the Data Dictionary for one application. Naturally it will have to be in the instance by itself. If you use OEM or some other tool, there is the possiblity that altering the Data Dictionary might cause these tools to not work properly. There are some serious ramifications for doing what the vendor wanted.
As for some practical advice, try updating the statistics with DBMS_STATS, and if possible, run the Oracle Expert, part of Oracle Enterprise Manager, against the instance while the query or report is running.
Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
reginald.w.bailey_at_jpmorgan.com
baileyre_at_us.ibm.com
rabbit_at_emirate
s.net.ae To: ORACLE-L_at_fatcity.com
Sent by: cc:
ml-errors_at_fatc Subject: Re: 2 small tables - Query takes 3 hours
ity.com
07/27/2003
04:29 AM
Please respond
to 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
> 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: INET: Reginald.W.Bailey_at_jpmorgan.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).Received on Sun Jul 27 2003 - 15:19:24 CDT
 |
 |