A few weeks ago i was reading a great article written by Franck Pachot about investigating lock issue using event 10704 , so come to me the idea to use systemtap for this investigation as it give us more flexibility (For example : monitoring TM lock only on a specific table).
SYSTEMTAP
Simple ORACLE Data masking using systemtap
In my previous article i showed how we can write a mini database firewall using systemtap.In the second post of this series i will extend the previous script by adding a simple data masking functionality .This is what we are going to achieve :
Improved start-up times for ORACLE database in UEK4 (Concurrent Huge Page faults)
As you may know Unbreakable Enterprise Kernel Release 4 is finally here with many enhancement.So If you plan to upgrade your kernel to UEK4 and you are using Huge page for your database (And you probably should) you “may” see a great improvements in start-up times. So what have changed ?
Assessing impact of Major Page Fault on ORACLE database [Systemtap in action]
“A more severe memory latency is a major page fault. These can occur when the system has to synchronize memory buffers with the disk, swap memory pages belonging to other processes, or undertake any other Input/Output activity to free memory. This occurs when the processor references a virtual memory address that has not had a physical page allocated to it. The reference to an empty page causes the processor to execute a fault, and instructs the kernel code to allocate a page and return, all of which increases latency dramatically.” Chapter 2. Memory allocation
As stated in the previous definition a high number of Major Page Fault can cause a serious degradation in server performance due to the added disk latency to the interrupted program execution. This can occur in case of high memory utilization or when the parameter swappiness is set to a high value.
https://en.wikipedia.org/wiki/Swappiness
vm.swappiness = 0 |
The kernel will swap only to avoid an out of memory condition, when free memory will be below vm.min_free_kbytes limit. See the “VM Sysctl documentation”. |
vm.swappiness = 1 |
Kernel version 3.5 and over, as well as kernel version 2.6.32-303 and over: Minimum amount of swapping without disabling it entirely. |
vm.swappiness = 10 |
This value is sometimes recommended to improve performance when sufficient memory exists in a system. |
vm.swappiness = 60 |
The default value. |
vm.swappiness = 100 |
The kernel will swap aggressively. |
So how to assess the impact of a Major Page Fault on an Oracle session ?
Combining SQL TRACE & SYSTEMTAP Part 2: No more Unaccounted-for Time due to time spent on CPU run queue
I my previous post i showed how we can eliminate one of the causes for Unaccounted-for Time,which is CPU double-counting, from SQL trace file using systemtap. But we can do more,The other important causes of missing data in an Extended SQL trace file is “Time Spent Not Executing” (Cary Millsap) which is time spent on CPU run queue.So how to measure it ?
Here is an excerpt of what we are going to achieve :
Old trace file :
New trace file showing cpu consumption inside wait event and time spent on CPU run queue :
Combining SQL TRACE & SYSTEMTAP Part1: No more CPU double-counting (Unaccounted-for Time)
There is many reason for unaccounted for time in an Extended SQL trace file one of them is CPU consumption double-counting and this is the subject of this post.For a good case showing when CPU double counting can be significant see Luca Canali Post
So here is an excerpt of what we are going to achieve :
Normal trace file :
New trace file showing cpu consumption inside wait event :
Continue reading
IN-MEMORY population and auto I/O slot resize
In my last blog post on IN-MEMORY when investigating the amount of work done on behalf of IN-MEMORY population/[trickle] re-population i have done some observation concerning auto I/O slot resize.So here is more detail in a very short post(Sadly no time for a deeper investigation 😦 ).
V$SQL_CS_STATISTICS return no rows in 12C ! It’s time to trace ! [systemtap tracing]
In my last blog post i tried to demonstrate the effect of activating row source statistics on the calculation of “rows_processed” in v$sql_cs_statistics and the effect this may have in Adaptive cursor sharing. Sadly V$SQL_CS_STATISTICS appear to return no rows in 12c (12.1.0.2) and also as Mohamed Houri said “Even in 11g the number of rows processed was not updated before a cursor is bind aware” (In the comment section of my last post ). So there is apparently no way to see the different raw execution statistics used by the monitoring component of adaptive cursor sharing in 12C or before the cursor is marked bind aware in 11G ! Really ? It’s time to dig deeper !
StapOra V1.0 BETA : Monitoring session perf using systemtap
SYSTEMTAP Oracle session perf (CPU + WAITS) Direct SGA access (StapOra V0.2)
UPDATE 26/05/2015 : For the new version of StapOra including bug fixes and enhancement please click here
In the previous post i have developed a systemtap script to monitor CPU usage (Oracle CPU monitor version 0.1). So here i’am going to extend the script to include oracle wait events and CPU usage from the point of view of the oracle database using direct SGA access.
Here is a quick overview of the systemtap script (Renamed StapOra V0.2 )
- Top wait events
- Time spent on the run queue
- IO wait time
- Top kernel function
- Top user function
- Consistent Read by object
- Consistent Read elapsed time and cpu time
- Number of context switches
I will Explain here only the new added part and how it was developed:
Hatem Mahmoud's blog 