Oracle is taking an age to complete a simple SQL command

[ASmee]

I am excuting the following command on my Oracle server and it has been running for over two hours now (the server is doing 100% CPU):

select permid from iinstrumentsectorhist where permid not in (select permid from iinstrumentidmaster);

If I do the same command on the same database (but hosted by MS SQL) it takes 5 mins. I have increased my SGA to 356 MB but to no avail.

The tables, iinstrumentsectorhist and iinstrumentidmaster hold 252344 and 298602 rows respectively.

 

[Scunningham99]

is permid indexed. what explain plan is sql ststement running. by you run use:-

sql> set autotrace on;



Sy UK

 

[sem]

Do you have indexes on prmid? Is your statistics up to date? You may try to reformulate your query

select permid from iinstrumentsectorhist 
minus
select permid from iinstrumentidmaster

Regards, Dima

http://seminihin.narod.ru

 

[ASmee]

Thanks, the command just completed (less than a minute), the other command had been running for over three hours without completing.

Yes there are indexes on the permid on both tables.

There are no statistics for these queries though, when I look in Enterprise manager, is this the cause of the problem?

 

[sem]

Without fresh statistics or at least good one, CBO is completely blind. Even RBO in most cases produces better plans.

Regards, Dima

http://seminihin.narod.ru

 

[Scunningham99]

if you are using cost based optimizer then you are required to generate stats on tables. this effects the query plan used by the optimizer.

If your optimizer_mode is set to choose and one or more of your tables have stats then cost approach is used. If you have no stats then rule is used which is no good.

I am assuming you are not overiding the instance setting for optimizer mode via hints or alter session.

so yes generate stats so optimizer can work at choosing the best path!

dbms_stats.gather_table_stats is the one to use for only tables.

Sy UK