Re: Client performance.

From: Marcus Hampel <marcus.hampel_at_myview.de>
Date: 2000/06/21
Message-ID: <3950A64B.9E5E0672@myview.de>#1/1

Luggy wrote:
>
> I totally agree with Michael. The first stage in investigating poor
> application performance should always be to see what it is doing. It
> is
> not sufficient to say that SQL Server and Oracle are running
> comparable
> statements, as Oracle's optimizer will be completely different.

Ok, read my first article: The client application cause the CPU load, but only if it works with the Oracle server. With SQL-Server the Server the client produce only a small part of complete CPU load.

What does the client do? Does the client compute optimizing on Oracle? I believe not, but i don't know.

> You may well find that the addition of an index or two will vastly
> improve the Oracle performance. Try SQL tracing the client session
> (alter session set sql_trace = true) and then TKPROF the resulting
> trace file.

Ok, for the unbelievers, the result of tkprof:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query   
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse      155      0.23       0.31          0          0         
0           0
Execute    155      0.03       0.07          0          0         
0           0
Fetch      675      0.23       0.23          0       5117         
0         968

------- ------ -------- ---------- ---------- ---------- ----------

total      985      0.49       0.61          0       5117         
0         968

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query   
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        2      0.02       0.02          0          0         
0           0
Execute      2      0.00       0.00          0          0         
0           0
Fetch       17      0.00       0.00          0         53         
0          16

------- ------ -------- ---------- ---------- ---------- ----------

total       21      0.02       0.02          0         53         
0          16

Misses in library cache during parse: 0

155 user SQL statements in session.
2 internal SQL statements in session. 157 SQL statements in session.

>
> (However, I'm a little surprised if you're getting no disk activity).
It's always the secound try. All data are cached. The application should fly. But the application takes 4s to complete work (with full CPU load!). The Oracle server needs 0.6s to compute results. With SQL-Server as database the application completes work in 0.8s.

>
> Dave.

Thanks,

Marcus Received on Wed Jun 21 2000 - 00:00:00 CDT