2018-03-31

spfile parameters derived from cpu_count

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version 11.1.0.7 and 12.1.0.2.
To simulate a machine with sufficient CPUs, I had to use a trick described in a previous post. Using _disable_cpu_check threw an ORA-7445:[ksucre] in 12.2 with cpu_count >126.

the script I used this time is basically (of course with 180 instead of 122 for 18.0).
#!/usr/bin/ksh  -x

integer i=1
while ((i <= 255));
do
  print " $i ";

  echo "*.audit_file_dest='/_some_dir_/BX1/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/_some_dir_/BX1/controlfile/o1_mf_djd017z2_.ctl', '/_some_dir_/fast_recovery_area/BX1/BX1/controlfile/o1_mf_djd01834_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/_some_dir_/oradata'
*.db_name='BX1'
*.db_recovery_file_dest='/_some_dir_/fast_recovery_area/BX1'
*.db_recovery_file_dest_size=7G
*.db_unique_name='BX1'
*.diagnostic_dest='/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=7g
*.undo_tablespace='UNDOTBS1'
*.max_string_size='EXTENDED'
#*._disable_cpu_check='TRUE'
*.cpu_count=$i" > /_some_dir_/cpus/initBX1_$i.ora

  echo "shutdown abort;
startup pfile='/_some_dir_/cpus/initBX1_$i.ora' ;

insert into init_parameters
select '12.2'     ,
       $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;
exit;"> cpus/122doit$i.sql

sqlplus "/ as sysdba" @cpus/122doit$i.sql >cpus/122log$i.log

sleep 5

  (( i = i + 1));
done


11.1.0.712.1.0.212.2.0.118.0.0.0
_cursor_db_buffers_pinnedxxxx
_db_block_buffers xxxx
_db_block_lru_latches xxxx
__db_cache_sizexxxx
_db_file_exec_read_count xx
db_file_multiblock_read_countxx
_db_handlesxxx
db_writer_processesxxxx
dml_locksxxx
_enqueue_hashxxx
_enqueue_hash_chain_latchesxxxx
_enqueue_locksxxxx
_enqueue_resourcesxxx
_flashback_generation_buffer_sizexxxx
_gc_fast_index_split_waitx
_gcs_min_cr_slavesxx
_kghdsidx_countxx

_hang_resolution_scopexx
_ksmsq_hintmaxprocx
__large_pool_sizexxx
_lms_rollbacksxx
log_bufferxxxx
_log_parallelism_maxxxxx
_log_simultaneous_copiesxxxx
_max_log_write_parallelismxx
_max_outstanding_log_writesxxx
_max_pending_scn_bcastsxxx
_messagesxxx
_num_longop_child_latchesxxxx
_num_rlslavesxx
parallel_max_serversxxxx
_parallel_min_message_poolxxxx
parallel_min_serversxxx
parallel_servers_targetxxx
pga_aggregate_limitxxx
_pmon_max_slavesxx
_pmon_slaves_arr_sizexx
processesxxx
_px_proactive_slave_alloc_thresholdxxx
_session_allocation_latchesxxx
sessionsxxx
sga_max_sizexx
shared_pool_reserved_sizexxx
__shared_pool_sizexxxx
_shared_server_num_queuesxxxx
_small_table_thresholdxxxx
_spin_count xxxx
_sqlmon_max_plan
xxx
_super_shared_conversion_threshold
xx
transactions xxx
_use_single_log_writer xxx

To get a good picture of the changes, feel free to select parameters, versions or whatever you want below.
Raw data is available at GistHub.
The js app is available here.
(It's based on an idea PerfSheet.js by Luca Canali.

As some of the values are not numbers, I applied this mapping:
TRUE1
FALSE0
OFF0
ADAPTIVE -1
INSTANCE -2


2018-03-26

Age - hard to calculate


The concept of age seems quite simple.
Right now I'm 42 years old.
I can be more precise and define it as 42 years, some months and several days.
And at a given date I will be 43 years old, and months and date are reset to 0.
Unfortunately it's hard to calculate with these numbers.
For those who wants to go into detail I'd recommend ISO 8601 Data elements and interchange formats. Unfortunately I don't have access to this document.
Another good reason is the explanation for CPANs DateTime.pm module.
I tried a slightly different approach:
Define the age as integer and fractional part, where the integer part is the age in years as we are used to it, and the fractional part is the  number of days already passed divided by the total days in the current year. 
This sounds quite simple, but it has some complicated effects due to leap years. So I wrote a little package ( see below) to handle these effects.
The Package is called BX_YEAR_CALC and has 2 functions:
DIFFERENCE (year1 date, year2 date) returns the AGE.
ADDITION (year1 date, diff number) returns the date at a specific AGE.

Here are some examples:

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

SQL> select bx_year_calc.difference('2004-02-29 12:00:00', '2005-03-01 11:59:00') diff from dual;

      DIFF
----------
1.00136796

SQL> select bx_year_calc.addition ('2004-02-29 12:00:00',  1.00136796042617960426179604261796042618) d from dual;

D
-------------------
2005-03-01 11:59:00

SQL> select bx_year_calc.addition ('2005-03-01 11:59:00', -1.00136796042617960426179604261796042618) d from dual;

D
-------------------
2004-03-01 00:00:00


You can see in these examples there are situations - especially around leap years and Feb 29th - where the calculation goes quite wrong. My implementation tries to reduce this effect to a minimum.

Feel free to play with dates and ages, any comments are very welcome.


create or replace PACKAGE BX_YEAR_CALC AS 

/*  2018-03-25 - berx - initial - martin.a.berger@gmail.com
    BX_YEAR_CALC - to calculate DATES in units or YEARS
                   equal idea as  DATE1 - DATE2 returns a number,
                   where the integer part are full days and fractional part  reflects remaining hours, minutes and seconds
                   
                   As some years has 365 or 366 days, the last year can have different number of total days, 
                   so the fraction must be calculated based on this.
                   Only the number of days of the last year (based on the begin) is calculated.
                   
                   Be aware: there are situations with leap years or gregorian calendar where results are not intuitive.
                   If you find a bette rimplementation for a given situation, let's discuss

*/


/* returns the "age in years" between the 2 parameters. 
   integer is quite obvious - it's the years between the start date and the same day (if it exists) in target year.
   fraction is remaining days divided by days in the last year
*/
  FUNCTION DIFFERENCE(YEAR1 date default sysdate,
                      YEAR2 date default sysdate)
    RETURN number;

/* adds (or substracts - if number is negative) a "age in years" as defined by DIFFERENCE to a given date
   for addition, first the integer part is added and then the fraction - based on days in last year.
   for subtraction, first the fraction is removed (to be more aligned to addition) and the the integer
   */
  FUNCTION ADDITION (YEAR1 date default sysdate,
                DIFF number default 0)
    RETURN date;

/* returns the next "existing" date to a given "old-date" and a specific other year ("new year")
   this handles the situation similar to "today is 2004-02-29. what's the date a year ago?
   to avoid ORA-01840: input value not long enough for date format
   this function searches the next day "above" (with incr => default 1) or "below" (with incr => -1)
   other values for incr are not defined.
   */
    FUNCTION next_real_date (
        new_year VARCHAR2,
        old_date DATE,
        incr number default 1
    )
    return date;

END BX_YEAR_CALC;

CREATE OR REPLACE PACKAGE BODY bx_year_calc AS

    c_debug          NUMBER := 0;

    PROCEDURE debug (
        message VARCHAR2
    )
        AS
    BEGIN
        IF
            c_debug > 0
        THEN
            dbms_output.put_line(message);
        END IF;
    END debug;

    FUNCTION next_real_date (
        new_year   VARCHAR2,
        old_date   DATE,
        incr       NUMBER DEFAULT 1
    ) RETURN DATE AS

        year2_year    NUMBER;
        return_date   DATE; -- the highest full year related to YEAR1  which is smaller than YEAR2  
        year_diff     NUMBER;
        leap_help     BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count    NUMBER := 0; -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
      if NOT abs(incr)=1 then
      raise_application_error( -20001, ' incr msut be +1 or -1 ' );
      end if;
        WHILE NOT leap_help LOOP
            BEGIN
                IF
                    leap_count = 0
                THEN
                    return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date,'MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');

                    leap_help := true;
                ELSE
                    return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date + leap_count,'MM-DD'),'YYYY-MM-DD');

                    leap_help := true; -- this happens only if NO exception is thrown
                END IF;

            EXCEPTION
                WHEN date_not_valid -- ORA-01839: date not valid for month specified
                 THEN
                    leap_count := leap_count + incr;

                    IF -- just a safety in case anything goes wrong 
                        abs(leap_count) > 33
                    THEN
                        leap_count := 1 / 0;
                    END IF;
            END;
        END LOOP;

        RETURN return_date;
    END next_real_date;

    FUNCTION difference (
        year1   DATE,
        year2   DATE DEFAULT SYSDATE
    ) RETURN NUMBER AS

        days_lastyear    NUMBER; -- how many days the last year has
        fract_lastyear   NUMBER; -- remaining part as fraction of a year
        year2_year       NUMBER;
        hi_year_lo       DATE;   -- the highest full year related to YEAR1  which is smaller than YEAR2  
        hi_year_hi       DATE;   -- the smallest full year related to YEAR1  which is higher than YEAR2  
        year_diff        NUMBER;
        leap_help        BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count       NUMBER := 0;      -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
        IF
            year1 > year2
        THEN
            RETURN difference(year2,year1);
        ELSE
            year2_year := to_number(TO_CHAR(year2,'YYYY') );
            hi_year_lo := next_real_date(TO_CHAR(year2_year,'0000'),year1);
            hi_year_hi := next_real_date(TO_CHAR(year2_year + 1,'0000'),year1);
      
      -- how many days are in the last year to take care of
            days_lastyear := hi_year_hi - hi_year_lo;
            fract_lastyear := ( year2 - hi_year_lo ) / days_lastyear;

            year_diff := to_number(TO_CHAR(hi_year_lo,'YYYY') ) - to_number(TO_CHAR(year1,'YYYY') );

            RETURN year_diff + fract_lastyear;
        END IF;
    END difference;

    FUNCTION addition (
        year1   DATE DEFAULT SYSDATE,
        diff    NUMBER
    ) RETURN DATE AS

        year_year        NUMBER;
        year_diff        NUMBER;
        days_lastyear    NUMBER; -- how many days the last year has
        hi_year_lo       DATE;   -- the highest full year related to YEAR1  which is smaller than YEAR2  
        hi_year_hi       DATE;   -- the smallest full year related to YEAR1  which is higher than YEAR2  
        lo_year_lo       DATE;   -- a temp "lowest date" - only to keep the calculation somehow readable
        days_jump_back   NUMBER := 15; -- a number bigger than "365 + (15-4)" - google gregorian calendar reform 1582 & 4 October 1582 
        days_offset      NUMBER;
        leap_help        BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count       NUMBER := 0;      -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
        IF
            diff < 0
        THEN
          -- first let's substract only the fraction of diff
            year_year := to_number(TO_CHAR(year1,'YYYY') );
            hi_year_lo := next_real_date(TO_CHAR(year_year - 1,'0000'),year1);
            days_lastyear := year1 - hi_year_lo;
            hi_year_hi := hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
          -- now the easy part - years
            RETURN next_real_date(TO_CHAR(to_number(TO_CHAR(hi_year_hi,'YYYY') ) + trunc(diff) + 1,'0000'),hi_year_hi);
          -- trunc(diff)+1 is required, as we substracted "-1" in the calculation of "hi_year_lo" above already

        ELSIF diff = 0 THEN
            RETURN year1;
        ELSE
            year_year := to_number(TO_CHAR(year1,'YYYY') ) + trunc(diff);
            hi_year_lo := next_real_date(TO_CHAR(year_year,'0000'),year1);
            hi_year_hi := next_real_date(TO_CHAR(year_year + 1,'0000'),year1);
-- 
            days_lastyear := hi_year_hi - hi_year_lo;
            RETURN hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
        END IF;
    END addition;

END bx_year_calc;

2018-03-20

real virtual CPUs

Some software changes it's behavior based on capabilities of the system it's running on.
but sometimes it's interesting to check how a software would heave on a different system, which is not at hand right now.

On Linux, a lot of information about the current system can be found in /proc and /sys.
These filesystems are virtual, so they can not changed easily with an editor.

In my case I want to simulate  a lot more CPUs.
These are visible in several locations.
The most know is probably /proc/cpuinfo.  There you find a block of information for each CPU the kernel knows about. Based on the current configuration, I create a modified fake file somewhere in a different space:
#!/bin/bash
# cpus.sh

count=0
max_socket=8
max_core=32

END=5
for ((soc=0;soc<max_socket;soc++)); do
    for (( cor=0;cor<max_core;cor++)); do
echo "processor       : $count
vendor_id       : GenuineIntel
cpu family      : 6
model           : 37
model name      : Intel(R) Xeon(R) CPU E5-2660 v3 @ 2.60GHz
stepping        : 1
microcode       : 0x3b
cpu MHz         : 2596.103
cache size      : 25600 KB
physical id     : $soc
siblings        : $max_core
core id         : $cor
cpu cores       : $max_core
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts nopl xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes hypervisor lahf_lm ida arat epb pln pts dtherm pti tsc_adjust
bugs            : cpu_meltdown spectre_v2
bogomips        : 5193.98
clflush size    : 64
cache_alignment : 64
address sizes   : 42 bits physical, 48 bits virtual
power management:

"
    let count=count+1
    done
done
and create a file with ./cpus.sh>cpuinfo.256

There is another location as well: /sys/devices/system/cpu.
In this directory are several directories and files with interesting information.

I copy the fill directory to another place (ignoring all the errors).
First the number of cpu[id] directories might need adjustment.
In my case a simple set of symlinks is sufficient:
for i in {2..255} ; do
  echo $i
  ln -s cpu1 cpu${i}
done  
In every cpu[id] durectory there is a symlinik to which node it belongs: node0 -> ../../node/node0
So it might be required to spoof proper entries in /sys/devices/system/node. In my case it's not required.

The last fix required in my case is in the file cpu/online.
It contains 0-255 now (instead of 0-2).

As I mentioned above the original files can not be manipulated as they are not real files.
The mount option --bind does the trick:
mount --bind <my_working_dir>/cpuinfo.256 /proc/cpuinfo
mount --bind <my_working_dir>/cpu /sys/devices/system/cpu

After these nice manipulations, my sandbox Oracle instance shows now plenty of CPUs:
SQL> show parameter cpu_count                                                                                                                                                      

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     256

Update (2018-03-21):
For Oracle Databases I got 2 hints how to make it calculate with more CPUs than really available.

with this small stap script:
#!/usr/bin/stap
function modify_rax() %{ long ret; ret = 6; memcpy( ((char *)CONTEXT->uregs) + 80 , &ret, sizeof(ret)); %}
probe process(“oracle”).function(“skgpnumcpu”).return { modify_rax(); }

and




2018-03-11

Method R Profiler on x86_64 Linux with HiDPI


For a new installed Linux Laptop I tried to install Method R Profiler (and Tools).
But it was not as smooth as expected.

mrprop_wrapper failed with

/opt/mrprof/mrprof_wrapper: line 45: /opt/mrprof-7.1.1.3-linux32/bin/mrprof.exe: No such file or directory
But the file is there, and it's executable.
So the Error is slightly misleading.
Also strace showed a similar error:
execve("/opt/mrprof-7.1.1.3-linux32/bin/mrprof.exe", ["/opt/mrprof-7.1.1.3-linux32/bin/"...], [/* 62 vars */]) = -1 ENOENT (No such file or directory)
but execve(2) is slightly more clear:
ENOENT The file filename or a script or ELF interpreter does not exist, or a shared library needed for the file or interpreter cannot be found

As I'm very sure the file exists, let's check the other possibility:
$ file mrprof.exe 
mrprof.exe: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked, interpreter \004, stripped
The file is a ELF 32-bit

$ uname -m
x86_64
The system is 64-bit


$ readelf -l -d  mrprof.exe 

Elf file type is EXEC (Executable file)
Entry point 0x804a878
There are 8 program headers, starting at offset 52

Program Headers:
  Type           Offset   VirtAddr   PhysAddr   FileSiz MemSiz  Flg Align
  PHDR           0x000034 0x08048034 0x08048034 0x00100 0x00100 R E 0x4
  INTERP         0x000134 0x08048134 0x08048134 0x00013 0x00013 R   0x1
      [Requesting program interpreter: /lib/ld-linux.so.2]
  LOAD           0x000000 0x08048000 0x08048000 0x10a60 0x10a60 R E 0x1000
  LOAD           0x011000 0x08059000 0x08059000 0x014e8 0x015c8 RW  0x1000
  DYNAMIC        0x011014 0x08059014 0x08059014 0x000f8 0x000f8 RW  0x4
  NOTE           0x000148 0x08048148 0x08048148 0x00020 0x00020 R   0x4
  GNU_EH_FRAME   0x010a10 0x08058a10 0x08058a10 0x00014 0x00014 R   0x4
  GNU_STACK      0x000000 0x00000000 0x00000000 0x00000 0x00000 RW  0x4

Dynamic section at offset 0x11014 contains 26 entries:
  Tag        Type                         Name/Value
 0x00000001 (NEEDED)                     Shared library: [libnsl.so.1]
 0x00000001 (NEEDED)                     Shared library: [libdl.so.2]
 0x00000001 (NEEDED)                     Shared library: [libm.so.6]
 0x00000001 (NEEDED)                     Shared library: [libcrypt.so.1]
 0x00000001 (NEEDED)                     Shared library: [libutil.so.1]
 0x00000001 (NEEDED)                     Shared library: [libpthread.so.0]
 0x00000001 (NEEDED)                     Shared library: [libc.so.6]
 0x0000000c (INIT)                       0x804a3b0
 0x0000000d (FINI)                       0x8057070
 0x00000004 (HASH)                       0x8048168
 0x00000005 (STRTAB)                     0x8049460
 0x00000006 (SYMTAB)                     0x80487b0
 0x0000000a (STRSZ)                      2697 (bytes)
 0x0000000b (SYMENT)                     16 (bytes)
 0x00000015 (DEBUG)                      0x0
 0x00000003 (PLTGOT)                     0x8059110
 0x00000002 (PLTRELSZ)                   592 (bytes)
 0x00000014 (PLTREL)                     REL
 0x00000017 (JMPREL)                     0x804a160
 0x00000011 (REL)                        0x804a140
 0x00000012 (RELSZ)                      32 (bytes)
 0x00000013 (RELENT)                     8 (bytes)
 0x6ffffffe (VERNEED)                    0x804a080
 0x6fffffff (VERNEEDNUM)                 3
 0x6ffffff0 (VERSYM)                     0x8049eea
 0x00000000 (NULL)                       0x0

The required program interpreter is missing:
# ls  /lib/ld-linux.so.2
ls: cannot access '/lib/ld-linux.so.2': No such file or directory

Unfortunately, the documentation is slightly unspecific regarding the requirements: glibc 2.14 is met, but in my system only 64 bit by default.
With a glimpse at this part of the installation path mrprof-7.1.1.3-linux32 it should be obvious, but it wasn't for me, and the error-message was not helpful at first stage.

The solution is quite simple. As root:
dpkg --add-architecture i386
apt-get update
apt-get install libc6:i386 



With this issue solved, the Profiler started, but with incredible tiny fonts. This is a known effect for HiDPI screens. As it's a java- program, one additional line to my profile solves the problem:
export _JAVA_OPTIONS='-Dsun.java2d.uiScale=2'
The start screen still looks like a stamp, but the fonts and windows are fine.



At last, I want to start the profiler from any location, not only it's home directory. So the Profiler.sh is extended to
script="$0"
WORK_DIR="$(dirname $script)"
java -jar "${WORK_DIR}/jlib/com.methodr.profiler.gui.jar" $@
(the WORK_DIR related stuff is new.)

All together nothing special, but it can cost some time when you want to use a tool for the first time - probably for urgent reasons - and first have to fix such obstacles.
So it's collected here, to save me some time when I hit it again.