systemtap : A mini oracle db firewall

As the blog post name suggest this article is about writing a “mini” program for displaying and filtering statement execution issued from a specific IP address (parameter 1) on a specific database (parameter 2). This is heavily based on the great work done by Luca Canali here (Must read !).

Mapping table blocks to NUMA nodes : Scattered or Clustered ?

Depending on the location of table blocks in the buffer cache performance may be affected (Ex : Tables blocks scattered across all the nodes or clustered on one node). In this blog post i will show quickly how to map table blocks to NUMA nodes based on my previous work done in this blog post.(Mapping ORACLE SGA components to numa nodes using NUMA API)

Manual balancing of SGA components across numa nodes [NUMA API:move_pages()]

In my previous blog post i showed how we can display memory components  (Buffer cache,Shared pool,Large Pool,etc) distribution across the different NUMA nodes using the NUMA API. But what to do if we want to have more control ? Can we for example isolate a specific SGA components in a specific set of nodes ?

Suppose for example that you are using the IN-MEMORY column store and only a few user are relying heavily on it.Would it be useful to collocate them on a specific set of nodes to improve memory access latency.For sure it depend  ! But we can do it ! Using the NUMA API and specifically the function “move_pages” we can distribute the memory pages  across NUMA nodes as we want !

Automatic NUMA Balancing which is enabled by default on UEK R4  rely on a similar mechanism for moving the memory pages closer to where the task is executing.(For more info check this) but it does not support for now the migration of  Huge Pages (hugetlbfs)

[root@svltest ~]# sysctl -a | grep numa_balancing
kernel.numa_balancing = 1
kernel.numa_balancing_scan_delay_ms = 1000
kernel.numa_balancing_scan_period_max_ms = 60000
kernel.numa_balancing_scan_period_min_ms = 1000
kernel.numa_balancing_scan_size_mb = 256

This is what we are going to achieve in this blog post :

Mapping ORACLE SGA components to numa nodes using NUMA API

How every SGA component (Buffer cache,Shared pool,Large Pool,etc) is distributed across NUMA nodes after initial startup ?  And what it will look like after dynamically shrinking or growing of memory area ?  In this post i will show a way to display memory components distribution across the different NUMA  nodes using the NUMA API.

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 :

TrcExtProf.sql the raw trace file (10046) profiler based on external tables + regexp

UPDATE 21/09/2015 : For the new version of TrcExTprof  click here.

There is already many great free trace file profiler that you can find like tkprof ,Trace Analyzer ,tvdxtat , parsetrc and OraSRP for a description of some of them here http://carlos-sierra.net/2013/02/26/about-tkprof-trace-analyzer-and-method-r-profiler/.So why another profiler ?

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:

Profiling ORACLE CPU performance using SYSTEMTAP (Queuing time,cr,Top function call,Context switch,etc)

UPDATE 26/05/2015 :  For the new version of StapOra (previously Oracle CPU monitor) including bug fixes and enhancement please click here

In this post i would like to provide one more option to investigate ORACLE CPU performance problem using SYSTEMTAP.

Here is a quick overview of the systemtap script (Oracle CPU monitor version 0.1):

• 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

Description of the different part of the tool :

Improving IN-MEMORY query with Attribute Clustering (IMCU pruning and compression ratio)

Attribute clustering is a new feature of oracle 12c for more info check http://docs.oracle.com/database/121/DWHSG/attcluster.htm#DWHSG9338.

I will focus here only on how attribute clustering can improve in-memory query.For a description of in-memory pruning please check my previous post.

Here is a quick example to demonstate how Attribute clustering can improve in-memory query pruning and compression ratio. Here is the simple steps :

1. Create a table and load it in-memory.
2. Check for the min-max column for every IMCU and the total allocated space in-memory.
3. Use attribute clustering for one column and then reload the table in-memory.
4. Check for the min-max column for every IMCU and the total allocated space in-memory.
5. Campare the results.

ORACLE 12C in-memory : Part 4 (IMCU Stale entries)

“The more stale entries in an IMCU,the slower the scan of IMCU will become” (Oracle in-memory white papers)
In fact the stale row will be read from the transaction journal (if it’s there) or from the buffer cache.

The question is how much stale entries do we have ? How to reduce them if required ?