help! performance issue

[Dave Chamberlin]

We have a poorly performing 3rd party app. Several of their views do 10-20 full table scans, causing the app to take 60-90 seconds to run std queries. We made some changes to the views (adding hints). This speeded up things greatly, but due to other app issues I had to back them out (by replacing the views with the original queries). Now queries take several minutes - far worse than before. I analyzed the schema after replacing the original views - no help. The performance in the PROD system is far worse than the DEV system. There is a lot of sorting going on - the disk with the TEMP segment is nailed -though it was . Can anyone offer any advice?
thanks

[Patrick Wallek]

You don't say which DB, but regardless, the best advice would be to add an index if possible.

If you must do full table scans, depending on the table size, it is not going to perform very well.

[Dave Chamberlin]

I have no control over the views at the moment - the problem is why is the performance MUCH WORSE after changing, then replacing the views.

[Patti Johnson]

Without knowing the queries/views involved or the version of the db - this is only a guess.

If you dropped and recreated views in Oracle it would invalidate some of the pl/sql code (packages, procedures, etc) that may use the views, so you will take a performance hit when they are validated.

Even though you have no control over the views or code, you can still add indexes to the database ( again I'm guessing Oracle) and the indexes will be used if they exist.

You state that you did an anaylze schema adter replacing the views - But did you have valid stats before you started making changes. Older application (Oracle apps 11.0 and before) do not benefit from stats and they can even cause problems.

Please post you db version and other details.

Patti

[Dave Chamberlin]

This is an Oracle 8.1.7.4 database (32bit) and schema analysis is being done weekly. I did look at invalid objects and compiled invalid packages, procs, functions etc. Most of these were SYS or ORDSYS objects.

[Kent Ostby]

Dave -- I'm not sure why things would CHANGE. However I have seen in the past issues related to how much swap a machine is running effecting Oracle performance.

How much RAM do you have and how much disk device swap is configured?

[Dave Chamberlin]

I have 5.5GB RAM, swap use is 17%. Using pseudoswap....

[Bill Hassell]

The full table scans are killing performance along with the sorts. Since you are constrained by unreasonble restrictions, you'll have to look at other fixes. The temp sorts need to be accomplished in RAM if possible. This might be done with a much larger SGA. Your 32bit database engine will severely restrict how big SGA can grow so you'll need to use memory windows to isolate the Oracle SGA from all other independent shared memory applications. If the DBAs can use almost 2Gb of SGA, this may be a way to improve performance. Similarly, adding more SGA buffers can help.

The SQL needs to be optimized if possible. Adding indexes is the best way. Also check an EXPLAIN for the long queries to see if index partial searches are being done. This often means an index is severely unbalanced and needs to be rebuilt.

As far as why returning the views has worsened performance, I would compare EXPLAIN listings to see any differences.

[Yogeeraj_1]

hi,

You can adopt both a bottom-up or top-down approach to your problem.

GOing top-down, you would generate a Statspack report and analyse all those bottle neck..

then drill down to applications
then the database


Bottom-up would require you to check that your OS is properly configure to run your oracle database. (Kernel parameters, etc)

Then, review your init.ora parameters
Then review your redologs, tablespaces, datafiles

and so on...

You know your system better than us. You choose the way you think would be easiest for you to solve the problem.

if you need any further guidance, please let us know

kind regards
yogeeraj

[Steve Lewis]

It sounds link you re-computed the stats but didn't update the library cache in the shared pool.
In other words, even though you re-created the views and updated the stats, the pre-parsed SQL statements are still not using them. You need to clear the library cache after computing the stats.

[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.