Hi:
I happeded to use strace to trace system call of an oracle process and suprised to
find that it used so many getrusage/gettimeofday/times system call than I can imagine.
Is it because of timed_statistics or Oracle itself used so many to maintain its
v$ views? From the result, it used 50%+ of the CPU that process consumes.
I tried to set timed_statistics in one running session to false and did another
strace,and there is nearly no getrusage at all! Does it mean that timed_statistics
caused 40%+ resource utilization?
>>default, with timed_statistics = true;
[EMAIL PROTECTED] oracle]$ strace -c -p 26798 (Linux AS2.1)
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
41.87 2.025445 899 2254 read
40.73 1.970439 7 266948 getrusage
13.72 0.663586 4 159727 gettimeofday
3.63 0.175616 23 7596 write
0.03 0.001389 198 7 munmap
0.02 0.000880 73 12 old_mmap
0.00 0.000070 70 1 semctl
------ ----------- ----------- --------- --------- ----------------
100.00 4.837425 436545 total
>>Manually set timed_statistics to false
[EMAIL PROTECTED] sync]$ strace -c -p 22161
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
94.15 0.509831 740 689 read
3.57 0.019323 5 4232 gettimeofday--Still this system call
2.28 0.012367 14 887 write
------ ----------- ----------- --------- --------- ----------------
100.00 0.541521 5808 total
<<Solaris 8/oracle 817/Timed_statistics = true;>>
[EMAIL PROTECTED] oracle]$ truss -c -p 6107
^Csyscall seconds calls errors
read .86 14496
write 1.37 19638
lseek .02 1031
times 1.12 31851
semctl .00 2
semop .00 2
sigprocmask .00 8
getcontext .00 2
yield .01 317
setitimer .00 4
lwp_mutex_wakeup .01 29
lwp_mutex_lock .01 110
lwp_cond_wait .01 837
lwp_cond_signal .20 794
pread .01 151
pwrite .00 643
kaio .25 815 747
kaio .00 68
------- ------ ----
sys totals: 3.87 70798 747
usr time: 21.57
elapsed: 233.47
[EMAIL PROTECTED] oracle]$ sqlplus internal
17:31:12 SQL> @whoisit
Enter value for spid: 6107
old 3: select addr from v$process where spid in(&spid))
new 3: select addr from v$process where spid in(6107))
SID SERIAL# USERNAME OSUSER
MACHINE PROGRAM PROCESS
TO_CHAR(LOGON_TIME,
---------- ---------- ------------------------------ ------------------------------
------------------------------ ---------------------------------------- ---------
-------------------
298 8491 BIDDER store
appg ? @appg (TNS V1-V3) 18818
2003/10/31 16:04:01
17:31:37 SQL> exec
dbms_system.set_BOOL_PARAM_IN_SESSION(298,8491,'TIMED_STATISTICS',false)
<<After change timed_statistics to false and we see few times sys call>>
[EMAIL PROTECTED] oracle]$ truss -c -p 6107
^Csyscall seconds calls errors
read .51 7779
write .76 10432
lseek .03 552
kill .00 1
semctl .00 1
semop .00 2
sigprocmask .00 8
getcontext .00 2
readv .00 1
yield .00 178
setitimer .00 4
lwp_mutex_wakeup .01 11
lwp_mutex_lock .00 44
lwp_cond_wait .00 446
lwp_cond_signal .08 420
pread .00 97
pwrite .00 324
kaio .19 431 384
kaio .00 47
------- ------ ----
sys totals: 1.58 20780 384
usr time: 12.14
elapsed: 131.92
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
