Actually, this is a follow up post from my performance deep dive into tablespace encryption. After having investigated how tablespace encryption works, this blogpost is looking at the other encryption option, column encryption. A conclusion that can be shared upfront is that despite they basically perform the same function, the implementation and performance consequences are quite different.
This is a run through of a performance investigation into Oracle tablespace encryption. These are the versions this test was performed on:
$ cat /etc/oracle-release Oracle Linux Server release 6.8 $ /u01/app/oracle/product/188.8.131.52/dbhome_1/OPatch/opatch lspatches 24315824;Database PSU 184.108.40.206.161018, Oracle JavaVM Component (OCT2016) 24006101;Database Patch Set Update : 220.127.116.11.161018 (24006101)
In this test I created an encrypted tablespace:
SQL> create tablespace is_encrypted datafile size 10m autoextend on next 10m encryption default storage(encrypt);
(this assumes you have setup a master encryption key already)
And I created an encrypted simple heap table with one row:
Recently I was applying the data dictionary part from an (exadata bundle) patch and ran into the following errors:
Recently, I was trying to setup TDE. Doing that I found out the Oracle provided documentation isn’t overly clear, and there is a way to do it in pre-Oracle 12, which is done using ‘alter system’ commands, and a new-ish way to do it in Oracle 12, using ‘administer key management’ commands. I am using version 18.104.22.168.170117, so decided to use the ‘administer key management’ commands. This blogpost is about an exception which I see is encountered in the Januari 2017 (170117) PSU of the Oracle database, which is NOT happening in Oracle 12.2 (no PSU’s for Oracle 12.2 at the time of writing) and Oracle 22.214.171.124 April 2016 and October 2016 PSU’s.
In order to test the wallet functionality for TDE, I used the following commands:
When sifting through a sql_trace file from Oracle version 12.2, I noticed a new wait event: ‘PGA memory operation’:
WAIT #0x7ff225353470: nam='PGA memory operation' ela= 16 p1=131072 p2=0 p3=0 obj#=484 tim=15648003957
The current documentation has no description for it. Let’s see what V$EVENT_NAME says:
SQL> select event#, name, parameter1, parameter2, parameter3, wait_class 2 from v$event_name where name = 'PGA memory operation'; EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS ------ ------------------------------------- ---------- ---------- ---------- --------------- 524 PGA memory operation Other
Well, that doesn’t help…
One of the things you can do with Pin, is profile memory access. Profiling memory access using the pin tool ‘pinatrace’ is done in the following way:
$ cd ~/pin/pin-3.0-76991-gcc-linux $ ./pin -pid 12284 -t source/tools/SimpleExamples/obj-intel64/pinatrace.so
The pid is a pid of an oracle database foreground process. Now execute something in the session you attached pin to and you find the ‘pinatrace’ output in $ORACLE_HOME/dbs:
This blogpost is an introduction to Intel’s Pin dynamic instrumentation framework. Pin and the pintools were brought to my attention by Mahmoud Hatem in his blogpost Tracing Memory access of an oracle process: Intel PinTools. The Pin framework provides an API that abstracts instruction-set specifics (on the CPU layer). Because this is a dynamic binary instrumentation tool, it requires no recompiling of source code. This means we can use it with programs like the Oracle database executable.
The Pin framework download comes with a set of pre-created tools called ‘Pintools’. Some of these tools are really useful for Oracle investigation and research.
This blogpost is about the Oracle redo log structures and redo efficiency in modern Oracle databases. Actually, a lot of subtle things changed surrounding redo (starting from Oracle 10 actually) which have gone fairly unnoticed. One thing the changes have gone unnoticed for is the Oracle documentation, the description of redo in it is an accurate description for Oracle 9, not how it is working in Oracle 10 or today in Oracle 126.96.36.199.
My test environment is a virtual machine with Oracle Linux 7.2 and Oracle 188.8.131.52.161018, and a “bare metal” server running Oracle Linux 6.7 and Oracle 184.108.40.206.160419. Versions are important, as things can change between versions.
There are many posts about the amount of memory that is taken by the Oracle database executables and the database SGA and PGA. The reason for adding yet another one on this topic is a question I recently gotten, and the complexities which surrounds memory usage on modern systems. The intention for this blogpost is to show a tiny bit about page sharing of linux for private pages, then move on to shared pages, and discuss how page allocation looks like with Oracle ASMM (sga_target or manual memory).
The version of linux in this blogpost is Oracle Linux 7.2, using kernel: 4.1.12-37.6.3.el7uek.x86_64 (UEK4)
The version of the Oracle database software is 220.127.116.11.160719 (july 2016).
In a previous article called ‘memory allocation on startup’ I touched on the subject of NUMA; Non Uniform Memory Access. This article is about how to configure NUMA, how to look into NUMA usage and a real life case of NUMA optimisation using in-memory parallel execution.
At this point in time (start of the summer of 2016) we see that the CPU speed competition has stagnated and settled at somewhere below maximally 4 gigahertz, and instead the number of core’s and the size of memory is growing. The common used server type in the market I am in is a two socket server. It is not unthinkable that in the near future servers with more than two sockets will increase in popularity, which facilitates the increase in (parallel) computing capacity and maximal amount of memory.