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
----------- --------- ----------------
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 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.
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.
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.