| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> slow queries with Crystal Reports to Server
First, I'm not an Oracle DBA, nor do I play one on TV. I'm just a Unix SysAdmin who is trying to find a bottleneck in our Oracle server. The system hardware is:
Solaris 2.5.1 Sparc 10, 224 Mb RAM, dual CPUs (90 or 120Mhz), 384Mb swap. Switched 100base-T network connection Oracle 7.3.3.0.0 PL/SQL 2.3.3.0.0 CORE Version 3.5.3.1.0 TNS 2.3.3.0.0 NLSRTL 3.2.3.0.0
Clients are running:
Windows NT 4.0, 64Mb RAM SQL*Net Seagate Crystal Reports 5.0 ODBC drivers
We've got users who are having terrible performance when running some reports using Seagate Crystal Reports. They're looking at tables of data from our accounting system, which is the only thing that has write access to the tables. The system isn't swapping, CPU usage isn't normally very high, but goes up when the problem reports are being run. What happens is that the oracle process just starts taking up lots of CPU time. We did a truss on the process taking the CPU time and it was doing millions of these calls to poll():
poll(0xEFFFA3C0, 1, 0) = 0
poll(0xEFFFA3C0, 1, 0) = 0
poll(0xEFFFA3C0, 1, 0) = 0
poll(0xEFFFA3C0, 1, 0) = 0
and then every once in a while it would do a read() of a datafile. We then tried shutdown down the accounting package that talked to oracle, and restarted the system so that we had a clean system with only Oracle up and running. Same problem. The reports took forever to run and it got into the same poll() loop.
From what I've read in the various Oracle manuals we have, I suspect that there's a lock contention happening here somewhere. In our our init<instance>.ora file we've got the LARGE values selected by default, so I don't think that's the problem.
I did the following query in svrgmrl to see if I could figure out where the problem is:
SVRMGR> select * from v$system_event;
EVENT TOTAL_WAIT TOTAL_TIME TIME_WAITE AVERAGE_WA ------------------------------ ---------- ---------- ---------- ---------- latch free 1 1 2 2 pmon timer 6427 6427 1934540 301.002023 process startup 8 0 126 15.75 rdbms ipc reply 54 0 248 4.59259259 rdbms ipc message 19898 19340 7721778 388.068047 control file sequential read 1495 0 27 .018060201 control file parallel write 55 0 177 3.21818182 write complete waits 3 0 8 2.66666667 log file sequential read 13 0 14 1.07692308 log file single write 7 0 4 .571428571 log file parallel write 525 0 321 .611428571 log file sync 368 0 251 .682065217 db file sequential read 3907 0 1727 .442027131 db file scattered read 172 0 125 .726744186 db file single write 21 0 8 .380952381 db file parallel write 426 0 1506 3.53521127 direct access I/O 9 9 99 11 instance state change 2 0 2 1 smon timer 68 64 1921177 28252.6029 virtual circuit status 644 644 1932645 3001.00155 dispatcher timer 323 323 1932324 5982.42724 SQL*Net message to client 12227 0 47 .003843952 SQL*Net more data to client 509 0 25 .049115914 SQL*Net message from client 12225 0 999753 81.7793865 SQL*Net more data from client 500 0 23 .046 SQL*Net break/reset to client 124 0 7 .056451613 timer in sksawat 8 8 16 227 rows selected.
I'm running the 'utlbstat' script to get some stats, but again, I'm not sure how to interpret the data I have gotten. From reading it through, I'm not seeing anything too out of place from what the comments are telling me. The only thing I see is some "SQL*NET message from client" waits which seem to average 50 seconds. There are 1,907,857 counts, and 95,766,890 total time, average time of 50.2. The "SQL*NET message to client" has a high count, but very low time.
Any and all help or hints would be appreciated. Please try to email me, since I don't always get the chance to read this newsgroup.
John
John Stoffel - Senior Unix Systems Administrator - Fluent, Inc.
jfs@fluent.com - http://www.fluent.com - 603-643-2600 x341
Geological time is not money. - Mark Twain
Received on Tue May 12 1998 - 20:12:31 CDT
 |
 |