Re: More selective means slower?

On Thu, 20 Jan 2005 12:46:11 -0700, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> a) what Oracle version?

9i2

>
> 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 business.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l