help! performance issue

[Eric Antunes]

Hi Dave,

I'm pretty sure that your issue is bad SQL code in those views so this is where your battle must be fighting!

Yesterday for example I had a little code executing for more than 3 minutes to get 1 record. I've changed the logic and now it runs in 1 or 2 seconds...

First of all, in your Production environment, execute directly the SQL code of one of those views WITHOUT SORTING and post here your feedback about performance: if it is much better then the sorts are your problem. Check why most sorts are simply wasting resources in this thread:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=979701

Best Regards,

Eric Antunes

[Patti Johnson]

Dave,

You mention lots of disk sorts - how large is your sort_area_size, and do you have enough memory to increase it?

You may never be able to explain why it got worse, but looking at the execution plan of some of the top queries may help you to correct them.

If TEMP is on one disk - is it possible to spread it to more disks?

Patti

[Rory R Hammond]

A couple thoughts. It might take awhile for the excution plans an cache to get set.

Might consider building the indexes in a different tables space/disk location. Or check to make sure that you rebuid the index in their orginal location.

Rory

[Volker Borowski]

Hi Dave,

no Information to really work on.
Could you attach at least the view definition (CREATE VIEW ...) if no EXPLAIN for the access plan is currently available ?

Best regards
Volker

[Dave Chamberlin]

thanks for your replies. I think that Steve may have had the answer with Library cache, since that database was not bounced after the views changed. Since the change I increased the sort_area_size from 65K to 1M and increased sort_multiblock_read_count to reduce sort problems. So far this has helped considerably. I also will change optimizer_index_caching and optimizer_index_cost_adj to see if I can make indexes more attractive.