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.