Tuning effort - understanding results.

Subject: Tuning effort - understanding results.

Platform; Oracle 8.0.5 SE; NT 4.

Two databases, let's call them APPP and CORP. Database APPP has a dblink to CORP. Investigation of performance issues indicates that queries on APPP are doing full table scans even when indexes are available. Nothing we do to the query seems to help. We get a suggestion to take a look at the OPTIMIZER_ initialization parameters. So, I set the following in APPP:

optimizer_index_caching = 95		#previous value: 0 (default)
optimizer_index_cost_adj = 10		#previous value: 100 (default)
optimizer_max_permutations = 1000	#previous value: 80000 (default)

Subsequent stats on APPP indicate that this had the desired effect. The incidence of 'db file scattered read' went down, 'db file sequential read' went up, total db blocks read went down, response per execute and per user call edged down slightly.

The big surprise was on CORP. Since we only made the change on APPP I didn't expect to see any change in the stats on CORP. However, response time per execute and per user call dropped dramatically. Prior to the change it was running from .005 to .045 seconds (if I've got my decimal point in the right place!) with spikes to .06 seconds. After the change to APPP went in the response in CORP immediately dropped, with no sample reaching above .05 seconds. I'm still scratching my head over how altering optimizer assumptions in APPP would have had any effect in the performance of the remote database that is the target of the db links.

Also, in analyzing performance reports, I'm not sure I understand what is meant when a measure like response time is reported "per execute", "per user call" and "per transaction." Yes, I know the mathematical definition (event count / execute, event count / user call, etc.) but within this context I don't know the exact definition of an "execute", a "user call", and a "transaction". These are Oraperf reports, and comparing the numbers there back to the utlestat reports, it appears that a "transaction" is really the entire sampling period of the estat report, which is certainly NOT what I think of as a "transaction" And I would think that a "execute" and a "user call" would be about the same thing - the processing of a single query. Obviously I'm wrong.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)