8i query not running in 9i 2

[BJCooperIT]

Let me preface this by the fact that I have no control over this recursive table. I simply was asked to find out why a batch job was not running. The indexes on license_tab in 8i are the same as in 9i (red columns are indexed). This query runs in 8i in 2 milliseconds:

SELECT license
FROM   license_tab
WHERE  [COLOR=red]fkdi[/color red] IN
       (SELECT fkdi
        FROM   license_tab
        WHERE  [COLOR=red]license[/color red] = 'Z50438522610')
AND    fkbl IS NOT NULL

However in 9i, it goes out to lunch and finally dies on a rollback error, The 9i explain plan shows a full table scan not in the 8i plan. If I change the "IN" to "=" it runs just fine. I thought this might have to do with whether the tables have been analyzed recently but encountered some user "resistance" to this theory. Any ideas on how I should proceed?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Barb,

My first thought was to suggest re-analysing the 9i tables, then I read your "resistance" issue. Why would you be receiving resistance to having "good statistics" when "bad statistics" can only screw up your queries?

Secondly, your query may run a bit faster if it reads:

SELECT license
  FROM license_tab a
 WHERE exists (SELECT 'x'
                 FROM license_tab b
                WHERE license = 'Z50438522610'
                  and a.fkdi = b.fkdi
              )
AND    fkbl IS NOT NULL
;

Let us know,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

Unfortunately the department I report to seems to have a... ummm... how do I say this diplomatically... counter-productive relationship with the DBA group. This is a DBA group that doles privileges out with an eye-dropper and you don't even want to know what I did to gain access to the 9i schema after waiting 5 days for grants that never came. I was receiving intense pressure to figure out what was wrong without being able to even log in !

The production database is 8i and a non-oracle COBOL programmer is testing all batch jobs to insure they work in the newly created 9i environment. That is how I got involved since he was able to show that a function in an Oracle package was "hanging" via his display messages.

Your query is a good idea which I could do, but this is "functional production code" which would need to be modified and not the solution my project leader was wanting. I am putting together a document that shows the 8i versus 9i explain plans and a line by line comparison of the table creation script I extracted from the 2 databases. I am trying to simply outline the problem and, hopefully, keep politics or finger-pointing out of the resolution.

The development group here do not seem to know anything about explain plans or analyzing tables or statistics. Their eyes glaze over like I am speaking a foreign language.

If you have any other suggestions, they will be gratefully accepted.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[BJCooperIT]

I have discovered that in the 9i database the foreign key constraint on license[/color red] is disabled. Would that be it?

 

[BJCooperIT]

Scratch that... it is disabled in the 8i database.

 

[Beilstwh]

An interesting query, I think the developer is simply trying to determine if fkbl is null. It would have been MUCH easier to simply

SELECT license
FROM license_tab
WHERE license = 'Z50438522610'
AND fkbl IS NOT NULL;

That being said, are you SURE that license is indexed on the 9i machine. A FTS would seem to indicate that it is not. To verify that it is, run the following query on the 9i machine

SELECT TABLE_OWNER,TABLE_NAME,INDEX_NAME
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME='LICENSE_TAB'
AND COLUMN_NAME='LICENSE';


Bill
Oracle DBA/Developer
New York State, USA

 

[taupirho]

Dunno if this is relavent, but are you using the same optimization in 8i and 9i ??

 

[carp]

I had the same question as taupirho! We had a very similar situation here, where a query ran very quickly in our development database but took forever in production. The only way we were ever able to solve this was by looking at the execution plans - they were completely different.

 

[taupirho]

To determine if the optimisation has any effect try the following on both system and see if there are any differences

On both do

SELECT /*+ RULE */ license
FROM license_tab
WHERE fkdi IN
(SELECT fkdi
FROM license_tab
WHERE license = 'Z50438522610')
AND fkbl IS NOT NULL


Then on both do

SELECT /*+ COST */ license
FROM license_tab
WHERE fkdi IN
(SELECT fkdi
FROM license_tab
WHERE license = 'Z50438522610')
AND fkbl IS NOT NULL


Be interested to know if there are diffs

 

[taupirho]

Mmmmm, think the COST hint in my last post should be CHOOSE instead

 

[BJCooperIT]

Bill,
Yes, the indexes are identical in 8i and 9i. It seems significant that if I change the query from IN to = it runs quickly and correctly.

LICENSE_TAB holds all license numbers for an entity (fkdi).
License is unique.
Current License (WHERE fkbl IS NULL) <- Can only be one
Old Licenses (WHERE fkbl IS NOT NULL) <- Any number

This is a recursive table where fkbl of the old license rows points to the current license. The function uses this query in a cursor to return the old licenses for the entity.

taupirho,
You hit the nail straight on the head! The query with either RULE or CHOOSE runs properly, whereas the query with COST hangs. Thanks a star's worth.

Thanks for the input everyone, now I can finish my problem evaluation. I just have to praise the membership here at Tek-Tips - a great technical community.

 

[karluk]

BJCooperIT
The query with either RULE or CHOOSE runs properly, whereas the query with COST hangs.

That's pretty decisive, BJ. There can hardly be any statistics collected on your table. If there were, running the query with a "choose" hint would have used the cost-based optimizer. But the performance indicates that it is running rule-based, implying no statistics.

Have you tried checking when (or if) statistics were last collected on your table? You can do this by querying the "all_tables" catalog view:

select owner, table_name, last_analyzed from all_tables
  where table_name = 'LICENSE_TAB'

I suspect that you will see a null in the last_analyzed column, indicating no statistics.

 

[BJCooperIT]

Thanks karluk, I will try that first thing in the morning. The more "proof", the better.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[karluk]

It would be a good idea to also check the last time the table's indexes were analyzed. Missing statistics on indexes could most likely also cause Oracle to prefer rules-based optimization.

Select owner, index_name, last_analyzed from all_indexes
  where table_name = 'LICENSE_TAB'

 

[BJCooperIT]

The table and indexes were re-analyzed last night and now the batch job runs successfully. Thanks for teaching me how to provide proof of my "gut" feeling. This is knowledge every developer should have, not just DBAs.

 

[Beilstwh]

Since the DBA group is not very helpful, I would suggest that you press the idea of automatically updating statistics at least once a week. Going it once and then having the data distribution change over time will just get you back into the same problem you had before. Also, I am hoping that at least all the application schema's were analyzed.

Bill
Oracle DBA/Developer
New York State, USA

 

[BJCooperIT]

Bill,
I will forward your suggestion to the DBAs. They are faceless folks on an email distribution group for me, I don't even know if they are located in the same building. The relationship between most developers and DBAs is always a bit strained, but when working as a consultant on a state contract it is even harder. Ever work with ghosts?

Thanks,
Barbara

 

[SantaMufasa]

Barb,

Bill's solution is excellent. We automatically re-gather statistics weekly and have found great success in that policy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]