I attempted to ascertain whether I have an issue with my the SAN storage.
Therfore I ran a connected to an oracle 11g database and used the szstem ID of my process.
I then ran the following command :
strace -cp 26806
I then ran my SQL statement once it had return my values etc I cancelled out and these are the results I got.
[oracle@xxxx tmp]$ strace -cp 26806
Process 26806 attached - interrupt to quit
Process 26806 detached
% time seconds usecs/call calls errors syscall
----------- --------- ----------------
60.76 1.167108 34 34379 read
25.56 0.490949 3 176282 poll
10.01 0.192197 0 444382 177753 recvmsg
2.15 0.041320 0 88878 sendmsg
1.33 0.025533 0 183723 times
0.11 0.002168 0 14809 getrusage
0.05 0.001027 0 2956 write
0.01 0.000215 31 7 munmap
0.01 0.000192 2 82 mmap
0.00 0.000000 0 7 semctl
100.00 1.920709 945505 177753 total
From the output the whole thing took 1.92 secs. Does this mean the SAN is really responding in that time or should I not use strace to determine that?
Thanks in advance
How do you know if information was read from disk or buffer/memory?
The strace utility writes the event trace from the STREAMS log driver. Some information may get lost if events are output faster than the strace process can handle. It is a debugging tool to analyze system calls and can show you what an application is doing.
SQL performance analysis is normally done from within the database by analyzing the explain plan and wait events and SQL Performance Analyzer.
Yes, that is understood, but it is possible to trace a process be it a oracle dedicated or any other process to find where time is being spent.
I am trying to determine if our disk configuration has an issue.
The server is a dedicated oracle server, and we are seeing high numbers on output column of vmstat wa and await columns of iostat.
Therefore, I decided to use strace to get timings of a known process.
Yes, we are looking at tuning some SQL but sofar SQL tuned has not had any impact on the stats mentioned above.
I was more concerned with the output of strace and the timings mentioned
Thanks in advance for your input
From what I understand these tools can collect I/O statistics and show performance data, but there is no way to tell whether a specific I/O was buffered or physical.
Btw, your virtual memory management can have a very large impact on performance. Oracle Database, for instance, is using shared memory and Kernel Hugepages can make a real performance difference.
thanks for your continued input.
For sure there appears to be an IO issue at the OS level.
I guess my issue is to determine if there is anything that one can do at the os level to improve things.
Is it possible to analyze how well the OS cache is performing, we do have a large OS cache size.
Will huge pages improve performance then, I think I checked and it was not being used Linux 5.9.
Thanks in advance
Kernel hugepages can be a substantial improvement. However, so far you have not provided any information about your OS version, hardware configuration and Oracle database configuration. Based on what information should there be any recommendation? No one can troubleshoot your system or make any recommendation based on some SQL query that took 1.92 secs.
Yes, I agree I should have provided a bit more system info, let me give it a go.
we are running an Oracle 2 node RAC configuration on the following:
Linux servername 2.6.18-308.el5 #1 SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
64973164k total memory
Swap: 8193140k total, 279088k used, 7914052k free, 51461224k cached
2 Physical CPUs
2 cores per cpu
4 Logical cpus
Were are using an HP SAN.
Oracle 220.127.116.11 two Node RAC with a dataguard to a single instance standy server
If there is any other info that maybe required please le me know.
Thanks in advance
If you have not done so already, I suggest set up kernel hugepages and configure the database for ASMM. You may have to remove the memory_target and memory_max_target parameters.
Oracle recommends kernel hugepages when using more than 8 GB. Posix /dev/shm shared memory uses 4 KB pages, whereas hugepages use 2 MB, resulting in a much smaller memory page table and better use of the TLB cache and drastically increasing performance. Kernel hugepages are reserved at system startup and cannot be swapped to disk. What level of performance increase you will see I do not know, but you might want to configure it anyway.
To test your disk/SAN performance you can use a benchmark tool like Oracle Orion, which simulates database load.
The orion tool is free, but no longer where it used to be, or Oracle has hidden it very well. Google does not find it anymore. However, Orion is included with 11gR2, located in $ORACLE_HOME/bin
You also seem to be using the RHEL kernel and not Oracle UEK.