I have two tables, each with pretty much the exact same view on it. The views, however, have an outer join to 20 different lookup tables.

The lookup tables themselves range from 4 records to 164 records. One of the core tables has about 700,000 records, and the other has 400,000.

The data and the views were originally hosted on a SQL Server database, in which there were no performance issues at all. However, now that I have rehosted them on our Oracle DB, the performance is totally unacceptable.

When I do a count(*) on the view set on the smaller table, it takes about 20 minutes to get the result from the database. When I do a count(*) on the view set on the larger table, it takes about 3 HOURS to get the result.

I researched and asked around some. I increased the pagefile.sys to 4095 M (we have about 2 gigs of RAM on the server), and I set the SORT_AREA_SIZE init parameter to 256K (it's a data warehouse with very few sequential users). This cut the time to get a count on the larger table in half, but this is still not an acceptable amount of time for a result.

Can anyone think of what I am doing wrong? Why would this query work so well on SQL Server and then kill our Oracle DB?

In case anyone is wondering, the database version is 8.1.6.3.0, running off of Win/NT 4.0.