Re: Performence on Oracle instance running on Sun Solaris/Sparc

Hi!

> Minutes to hours difference is huge. Check if you missing any indexes or
> have descipancies in query definitions of views.

Sorry, but I don't know the word descipancies, I have looked it up in different wordlists but didn't found it.

Yes we have done some checking on the indexes and tables used in the different view. We have also run explain plan on the sql-query and compare the explain plan and it is the same for both Norway and Canda setup unless one or two questions which have some "minor" different.

But I think it can be some problem in the application level between Canda and Norway. The software of the ERP-software (PL/SQL based) is different between Canada and Norway. But is hard to trace and get a picture of because some of the select/querys which take the longest time is select against a view, which is select on other view, which also run some of there columns through a PL/SQL function/packages which use other function and other views and so on *urgh* Not fun to trace.

But as far as we can see the explain plan between them show now differents.

The most strange part is that the most time acording to trace_sql/timed_statistics is spent in the fetch part? As far as I understand the sql trace out parsed via tkprof
Parse = Is the step where Oracle decice how to find the answer to the question. Exectue = Is the step to run the "code" generated in parse step and found out which row should be used.
Fetch = Is to get the row pointed out in execute step?

If that is true I can't understand why the fetch step takes down more then 200 million blocks which is more then 200 times the size of our databases.

Any suggestions to more digging I can do? THe only step left to try for me is

1. utlbstat.sql/utlestat.sql
2. Reinstall all the system in Norway
3. Export the norway database to Canda and install in new instance to see if it performance as bad as in Canada, if it do something is wrong in software/application level and we can blame to ERP supplier if not something is very strange with the Norway setup.

Kind regards

//TOmmy Received on Wed May 23 2001 - 17:50:39 CDT