Re: Performance problem with Oracle server

OK, Frank/Xho/Roman, tks for your comments. Our dbase is 9.2.0.5.0.

I edited the query and ran it again:

SQL> col EVENT format a30 heading 'Event'
SQL> col TOTAL_WAITS format 999,999,999 heading 'Waits'
SQL> col TOTAL_TIMEOUTS format 999,999,999 heading 'Timeouts'
SQL> col MINS_WAITED format 999,999 heading 'Total|Waited|(Minutes)'
SQL> col SECS_PER_WAIT format 9,999.99 heading 'Average|Wait|(Seconds)'
SQL> select EVENT,
2 TOTAL_WAITS,
3 TOTAL_TIMEOUTS,
4 round(TIME_WAITED/6000) MINS_WAITED,
5 round(AVERAGE_WAIT/100,2) SECS_PER_WAIT
6 from V$SYSTEM_EVENT
7 where EVENT not in ('pmon timer','smon timer','rdbms ipc
reply','rdbms ipc message')
8 order by MINS_WAITED desc;

Total
Average
Waited
Wait
Event Waits Timeouts (Minutes)
(Seconds)
------------------------------ ------------ ------------ ---------
---------
SQL*Net message from client 82,948,635 0 315,437
.23
db file sequential read 125,360,178 0 9,652
.00
db file scattered read 74,566,347 0 4,255
.00
jobq slave wait 62,099 58,927 3,117
3.01
PL/SQL lock timer 59,312 59,305 2,011
2.03
buffer busy waits 18,059,575 346 1,882
.01
enqueue 32,270 22,079 1,202
2.24
latch free 4,511,028 2,748,645 1,113
.01
pipe get 6,342 2,919 112
1.06
log file parallel write 548,996 0 54
.01
log file sync 272,566 7 54
.01
write complete waits 2,339 2,337 40
1.02
SQL*Net more data to client 20,199,490 0 11
.00
log file sequential read 20,735 0 9
.02
log file switch completion 2,359 0 5
.13
library cache load lock 269 57 5
1.04
control file parallel write 53,151 0 4
.00
control file sequential read 55,311 0 3
.00
local write wait 341 131 2
.43
SQL*Net more data from client 223,086 0 2
.00
SQL*Net break/reset to client 51,140 0 2
.00
library cache pin 586 6 2
.17
process startup 2,320 3 1
.03
row cache lock 21 9 1
1.64
SQL*Net message to client 82,949,434 0 1
.00
LGWR wait for redo copy 19,891 2,882 1
.00
wait list latch free 260 0 0
.02
<...>

Frank - Re your point that 'SQL*Net message from client' event should
be included, I'm not sure I follow this. Indeed this event comes out on
top, but isn't that normal? You would get this event every time the
dbase waits for the front-end, right? So assuming users don't respond
at lightning speed and even go for coffee with the form still open,
isn't it to be expected that this event accounts for most of the
waiting time? Please explain if I misunderstand this event.

Xho - I know read time exceeds enqueue/latch time, but I mean, wouldn't
the enqueue and latch times normally be lower on a properly tuned
system, given the scattered/sequential read times? What is typical?

Roman - CPU is high, I/O and mem are fine. Of course we can add CPU's,
but I'd like to know anything is amiss other than insufficent CPU
power, before we go ahead with that.

Thanks all.

.

Relevant Pages

  • Re: Performance problem with Oracle server
    ... Our dbase is 9.2.0.5.0. ... SQL> col TOTAL_WAITS format 999,999,999 heading 'Waits' ...
    (comp.databases.oracle.server)
  • Re: Inserting regional settings specific data into sql server
    ... All EU countries withouth the two English speaking use the format dd-MM-yy ... By using the parameters, while the program is in the right culture setting, ... than all those formats results using SQLparameters in the way the SQL server ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Standard Word doc to Book format
    ... We type seperate lists of acronyms, ... To create your TOC, you will have to mark, or verify that the major minor ... If your headings do not show a heading style, ... choose to apply the heading style (format menu, ...
    (microsoft.public.word.docmanagement)
  • Re: Inserting regional settings specific data into sql server
    ... Italianyou will observe that the settings used are 10/02/2006 ... it a better idea to Format it using the string format functions or a very ... into the sql database.Sorry to bug the shorts off you but u now have context ... By using the parameters, while the program is in the right culture setting, ...
    (microsoft.public.dotnet.framework.adonet)
  • Proposal: String::Format::General
    ... This should be considered pre-Alpha, since it has changed since yesterday, and I am seriously considering passing the format parameters to the output conversion code as a hash reference instead of a large number of arguments. ... Some limited heading capability is provided. ... This module provides a formatter class which allows its user to ...
    (comp.lang.perl.modules)