Oracle Views Performance

[jdmartin74]

I have a view which when ran as raw SQL (i.e. just the SELECT statement) is quicker than when I do a SELECT * FROM {view}

Does anyone have any tips to speed up the performance of Oracle views?

Thanks

J.

 

[Scunningham99]

unless you are getting mixed up with materialized views which are used mainly for DSS systems for improving performance?

Sy UK

 

[jimbopalmer]

One nice thing about views is that you can put optimiser hints in the view, which will be tried during all selects from that view.

In my experience, however, the optimiser is VERY poor when running a view containing other views in its definition. Try to avoid that.

I tried to remain child-like, all I acheived was childish.

 

[nbateman]

J,

I wouldn't expect the performance of the view to be noticably different to that of the straight SQL statement. Is the view consistently slower? How much are we talking about?

 

[jdmartin74]

Hi all,

Thanks for your comments. There is not a lot in it. The view is taking 5 seconds and the raw SQL 850msecs. I realise 5 seconds is not a lot, but I was under the impression that the view was compiled and therefore quicker.

It isn't consistent. I have found that subsequent searches with the view can take less time - I assume that the view is cached more than the raw SQL.

I'm continuing to play and learn!

 

[nbateman]

I would say the difference between 5 seconds and 850 ms is huge.

Of more significance than the time it takes to compile a sql statement is the time it takes to get the data off the disk.

If the data has already been read into the cache by a select from the view, then a select from the table will read the data straight out of memory. This is obviously much faster than getting it off disk, and is the most likely thing that would account for the difference of 850ms and 5 seconds. Of course, it does depend on how much data you are selecting, how big the cache is, and how many other processes are using this shared cache

If you selected from table, view, table, view, etc. one after the other, you should see the difference between the two drop to almost nothing.

The section on the SGA in the Oracle concepts manual, will tell you a lot more about the data and sql caching that goes on.

 

[BJCooperIT]

Try running an explain plan on the select statement from the table and then on the select from the view just to see if the plans are the same. I was taught in tuning class that there always is performance overhead in using a view, but that sounds a bit much. Are you using the same Order By clause and Where clause?

[sup]Everywhere is within walking distance if you have the time. ~Steven Wright[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML to help readability. Thanks![sup]

 

[JS74]

I got the same problem two weeks ago. But my view took 15 minutes and the sql-statement 30 seconds !!! In the explain plan I saw that the view solved an inner join with 9 accesses to an table. I didn´t understand it and tried it with hints, but I dont know much about it. But I think this would be the only solution - or try Oracle 9 !!!