Re: More selective means slower?

  • From: stephen booth <>
  • To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 21:06:34 +0000

On Thu, 20 Jan 2005 12:46:11 -0700, Wolfgang Breitling
<breitliw@xxxxxxxxxxxxx> wrote:
> a) what Oracle version?


> b) in case it is not 9i (not that unlikely given it's a COTS) are there
> by any chance statistics on the dictionary objects? Again not that
> unlikely if they gathered statistics with dbms_utility...
> or is optimizer_goal set to first_rows?

No stats that I'm aware of, I haven't gathered any anyway.  I doubt
the other people involved in the system would know how.  On the tuning
course I went on last year the tutor told us to never gather stats on
the sys schema as to do would cause fire and brimstone to rain from
the skies and the dead to walk to earth (I'm paraphrasing, but the way
he was talkign about it it may as well have been that).  Maybe this is
another thing from the course to file under 'Myth'.

> c) Why wouldn't explain plan not work on dictionary objects?

I tried it a few years back and got a wierd error message.  When I
asked about it on Metalink I was pointed to a note that basically said
"It won't work, don't do it.".  I think that was 8i so maybe it will
work now.

> d) How can the application care / know in what tablespace the indexes are?

I don't know.  I could understand it if they said that
updates/migrations wouldn't work if tablespaces of those names didn't
exist (the app might try to create an object in a particular
tablespace, if the tablespace doesn't exist it can't)  but they said
that it won't even start up if an object is out of place.  I suppose
it could do a check on startup (query the user_* views) and check the
results against what it was expecting to find.  I can understand
checking that the tables you expect to be there are there, but
checking they are in a certain tablespace seems pointless.  The
installation/configuration scripts for the app create the tablespaces
and schema owning user as well as the schema; tablespace names,
passwords &c are hard coded.

Unfortunately the environment I'm working in (local council) is very
stratified and compartmentalised.  It's very much that I am tasked
with looking after the databases (and the servers they run on but
that's only cos our Solaris guy is off on sick leave) but if I try to
do anything about the app I get smacked down and told to mind my own

It's better to ask a silly question than to make a silly assumption.

Other related posts: