Massive performance issues with a view

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.

Do an explain plan , on both but analyze before hand .
Check what is it doing.

I think I made a misleading statement in my earlier post. When I count the core tables, they return results relatively correct time. It's when I do a count(*) on the views that I get my performance issues.

I did an explain plan on the view before I used the analyze command. I will have to wait until tomorrow morning to use analyze so as not to lock up the table.


(hope this comes out right)

0 SELECT STATEMENT Cost=
1 MERGE JOIN
2 SORT
3 MERGE JOIN
4 SORT
5 MERGE JOIN
6 SORT
7 MERGE JOIN
8 SORT
9 MERGE JOIN
10 SORT
11 MERGE JOIN
12 SORT
13 MERGE JOIN
14 SORT
15 MERGE JOIN
16 SORT
17 MERGE JOIN
18 SORT
19 MERGE JOIN
20 SORT
21 MERGE JOIN
22 SORT
23 MERGE JOIN
24 SORT
25 MERGE JOIN
26 SORT
27 MERGE JOIN
28 SORT
29 MERGE JOIN
30 SORT
31 MERGE JOIN
32 SORT
33 MERGE JOIN
34 SORT
35 MERGE JOIN
36 SORT
37 MERGE JOIN
38 SORT
39 MERGE JOIN
40 SORT
41 TABLE ACCESS SARMIS86_94
42 SORT
43 TABLE ACCESS LOCATE_CD
44 SORT
45 TABLE ACCESS RESOURCE_CD
46 SORT
47 TABLE ACCESS NATURE_CD
48 SORT
49 TABLE ACCESS METHOD_CD
50 SORT
51 TABLE ACCESS CAUSE_CD
52 SORT
53 TABLE ACCESS OWNER_CD
54 SORT
55 TABLE ACCESS PROPULSION_CD
56 SORT
57 TABLE ACCESS SORTIETYPE_CD
58 SORT
59 TABLE ACCESS INITIALRESPONSE_CD
60 SORT
61 TABLE ACCESS SEVERITY_CD
62 SORT
63 TABLE ACCESS DISTANCE_CD
64 SORT
65 TABLE ACCESS PROPERTYASSISTSEC_CD
66 SORT
67 TABLE ACCESS PROPERTYASSISTPRI_CD
68 SORT
69 TABLE ACCESS ABORTREASON_CD
70 SORT
71 TABLE ACCESS PERSONASSIST_CD
72 SORT
73 TABLE ACCESS VISIBILITY_CD
74 SORT
75 TABLE ACCESS WIND_CD
76 SORT
77 TABLE ACCESS SEA_CD
78 SORT
79 TABLE ACCESS LENGTH_CD
80 SORT
81 TABLE ACCESS USAGE_CD

It seems to be doing Full Table scan and sort merge , for small tables this is ok, but you have two table with large number of rows, it depend how you have placed those two tables in the join query and whether they are indexed.
Also your cost value is not giving any value , i wonder whether yor are using RBO.

For sort merge, sort_area_size has to big , 5MB atleast.
Also , note whether you have high db_file_scattered_read wait.


Did you try count(rowid), for single table they are very fast.

Thank you for your response gpsingh, I really appreciate it.

Admittably, I am still a little wet behind the ears in the DBA realm, and this is my first "major" performance issue. Therefore, don't hold it against me when I ask you what you mean by RBO. Likewise, I'm not really sure how to interpret the cost value either. I have been reading up on Oracle's website, but their explanation of the whole Explain Plan function is scant at best.

Also, Oracle had mentioned that the SORT_AREA_SIZE shouldn't be more than 256K. What would be the impact to the system if I were to up it to about 4 or 5M? If you think this would be the best thing to do to solve my problem, I'll gladly do it, seeing as we are sure to run into this scenario again eventually.

The db_file_scattered_read wait is a new term to me, but I will read up on it.

On the subject of indexes... the core table is indexed on the primary key, which is not included in the view. I did not put an index on any of the foreign key columns, nor did I index any of the lookup tables, since they are so small relatively. Should I create an index on the columns that I join?

Again, thanks for the help. I'm sorry to sound like such a newbie, but I guess we all need to learn somehow.

RBO = rule based optimizer.
(not recomended to be used)

CBO = cost based optimizer.
(u have to use the analyze table command, to fill up the statistics table .... so that this optimizer mode can be used)
(u can specify that cbo be used , both at command line and in init file).

---------------------------------------------------------------------
please check the performance after creation of indexes on the join column ! this may improve u'r performance a lot or in the wrong conditions , may degrade the performance .
normally indexes should be used more where the tables participate in a DSS (decission support) type application !

---------------------------------------------------------------------

say dude , where's my car ?

hope this note helps u !

cheers

Ok, so far so good. I created an index on all of my join columns, and on my larger table it changed everything to a hash join, which is working nicely for me. Specifically, it dropped the count time from 3 hours down to 8 minutes, not a bad performance boost if you ask me.

However, the second view is still using a sort merge. They both are joined at the same exact columns, and even select the same columns. The column names match, although the datatypes are different. Would it make any difference to use VARCHAR2 instead of CHAR?

Can anyone think of why I would still be getting a full table scan from this?

Btw, thanks for the help everyone. One more hurdle and I'm in the clear.