I am having a performance problem with some sql scripts.
I have two Windows 2000 machines, same release + service pack. Both have 2 gig of Ram, both have the same CPU type.
One machine is running Oracle 8.1.6 and the other SQL Server 2000.
We created a database on each machine. We created the same tables on each machine (Table A, B and C). We then loaded each table with 2.1 million rows of data. We then created a view of all three tables -> Select all columns from A, union all Select all columns from B union all Select all columns from C. All the tables have an index (columns 1 & 2).
The issue that has come up is why does it take Oracle a combined total of 36 seconds to extract one record from the three tables when it only takes SQL Server 5 seconds? Here is the select statement:
Select col3 from table A where column 5 = xyz;
Select col3 from table B where column 5 = xyz;
Select col3 from table C where column 5 = xyz;
Please note that column 5 is not part of the index. This was done purposely.
Is there anything that I can do (change, reset, add, delete, etc) so that Oracle will perform close to what SQL Server does?
This really annoys me that SQL Server can beat Oracle.
Set your optimizer to COST. Indexed those colums. Analyze your table and indexes and then have a the
SQL> set autotrace traceonly
SQL> run your query
and see what exaclty oracle does behind the query.
Life is a journey, not a destination!
Could be doing lots of sorting. Increasing the SORT_AREA_SIZE may help it out.
As above need to see the execution plan for both databases.
Performance... Push the envelope!
Click Here to Expand Forum to Full Width