Tobi's Notes: 2018

Saturday, December 29, 2018

Note on table ITL (enq: TX - allocate ITL entry)

Oracle keeps track of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'.

Overview of Data Blocks

The INITRANS segment parameter guaranteed the ITL allocation at block allocation while MAXTRANS specify maximum allow depending on free space available in the block.

ITL become a bottleneck when there are heavy parallel DML on segments with scarcity of ITL in associated data blocks. “enq: TX - allocate ITL entry” events is an indicator of ITL contention.

Here I am going to create a test case to simulate ITL contention using a single 8k blocka and how to avoid ITL contention. Tested on Oracle version 18.1/Red Hat 7.4. same principle applies to older versions

CREATE TABLESPACE ITL_8KB datafile SIZE 1M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT auto;

I took a clue from JonathanLewis(each row will require at least 11 bytes (2 bytes row directory entry, 3 bytes row overhead, extra 6 bytes being the pointer to where the migrated row and no bytes for data/null values)

the 8085 bytes after using 107bytes will be systematically dividing to fill the block and allow up to 105 concurrent updates without row contention/lock. Column of char 66 bytes will be created. Each will have overhead of about 11 bytes.

107 bytes is the block overhead that stores transaction entry (at least 1 itl entry{23bytes per it}),table directory & row directory.

create table itl_table (fixed_size77 char(66)) --66bytes for column data + row overhead + row directory (11 bytes)segment creation immediatetablespace ITL_8KBpctfree 0 ;

itl_table table was created using PCTFREE 0. this to allow insert to used up the whole space(i.e 8085bytes) in data block.

insert into itl_table
select rownum from dual connect by level <= 105
; commit;
spool block_content
column rel_file_no new_value m_file
column block_no    new_value m_block
select
        dbms_rowid.rowid_relative_fno(rowid)    rel_file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        count(*)                                number_of_rows_address_in_block
from
        itl_table
group by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
order by
        dbms_rowid.rowid_relative_fno(rowid),
        dbms_rowid.rowid_block_number(rowid)
;

REL_FILE_NO BLOCK_NO ROWS_STARTING_IN_BLOCK
----------- ---------- ---------------------- 2 13 105

--DUMP data block

alter system flush buffer_cache;
alter system dump datafile &m_file block &m_block;
column tracefile new_value m_tracefile
select tracefile
from   v$process where addr = (
                select paddr from v$session where sid = (
                        select sid from v$mystat where rownum = 1
                )
        )
;

/u04/app/base/diag/rdbms/wro18c/wro18c/trace/wro18c_ora_4610.trc

--Interpreting Data Block Dump. Note this is reverse engineered!

open /u04/app/base/diag/rdbms/wro18c/wro18c/trace/wro18c_ora_4610.trc in an file editor.

*** 2018-12-23T10:14:52.169907-05:00
Start dump data blocks tsn: 8 file#:2 minblk 13 maxblk 13
Block dump from cache:
Dump of buffer cache at level 3 for pdb=0 tsn=8 rdba=8388621
Block dump from disk:
buffer tsn: 8 rdba: 0x0080000d (2/13)
scn: 0x3879e9 seq: 0x01 flg: 0x06 tail: 0x79e90601
frmt: 0x02 chkval: 0x8ec1 type: 0x06=trans data

first "scn" represent when block is dump

"frmt": 0x02 represent oracle version 8 and later

"chkval" blcok checksum value

type: 0x06=trans data

Block header dump section:

Block header dump: 0x0080000d
Object id on Block? Y
seg/obj: 0x1254c csc: 0x00000000003879e6 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x800008 ver: 0x01 opc: 0
inc: 0 exflg: 0

"itc" number of ITL entries
seg/obj:" object/segment ID
"typ: 1 - DATA" : table. for index "typ: 2 - INDEX"

data header section

tsiz: 0x1f98
hsiz: 0xe4
pbl: 0x7fa61a96c064
     76543210
flag=--------
ntab=1
nrow=105
frre=-1
fsbo=0xe4
fseo=0x2e2
avsp=0x1fe
tosp=0x1fe
0xe:pti[0]      nrow=105        offs=0
0x12:pri[0]     offs=0x2e2
0x14:pri[1]     offs=0x328
0x16:pri[2]     offs=0x36e
...
...
...
Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0003.005.0000062f 0x010003e1.019b.18 --U- 105 fsc 0x0000.003879e90x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000bdba: 0x0080000d

"tsiz" available space for row data

avsp = available space for data

tosp = total space available

ntab = number of table(always 1 except for cluster table)

nrow = row number

offs=0x2e2 = row directory, for a quick search of row data in block.

Itl = transaction entries

Xid = transaction id

Scn= last scn for Itl entries

block_row_dump section: content actual row database

tab 0, row 2, @0x36e
tl: 70 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [66]
33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x3b4

"@0x36e" directory id for lookup: part of identified row(ROWID)

"tl" total number of bytes for this row + overhead (in our case, char 66 bytes + at least 3bytes overhead according to oracle doc.)

"cc" number of column in column data section of row data/piece.

"fb" flags show: H-row header,F- first piece,L-last piece (this indicator for chained and migrated rows)

col 0: [66] -- shows the bytes size for row data. fixed for char.

33 equals 3 .the actual data are in hexadecimal, can be lookup in ASCII table.

there are trailing 20 (ascii=space), because CHAR(66) have to use all the space up not like varchar2

Simulating ITL contention.

I wrote simple codes to automatic workload generation that will cause ITL contention.

--Sequence to specify row_id number, just for convenient

DROP SEQUENCE row_number;
CREATE SEQUENCE row_number START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 105 NOCYCLE;

--SQL Script for updating specify rowid in block without causing row contention. runs for 5 minutes

SET TIMING ON
SET SERVEROUTPUT ON
SET LINE 400 PAGESIZE 400
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT
    SYSDATE   start_time FROM    dual;
DECLARE
    row_number_id   NUMBER;
    counter         DATE;
BEGIN
    SELECT
        row_number.NEXTVAL   INTO row_number_id FROM dual;
    SELECT        SYSDATE    INTO counter    FROM        dual;
    WHILE counter + 5 / 1440 > SYSDATE LOOP
        UPDATE itl_table
        SET
            fixed_size77 = (
                SELECT TO_CHAR(dbms_random.value(low => 1,high => 10E15) ) FROM dual
            )
        WHERE
            dbms_rowid.rowid_row_number(ROWID) = row_number_id;
         COMMIT;
    END LOOP;
END;
/
SELECT
    SYSDATE   end_time, row_number.CURRVAL FROM    dual;
EXIT

--Bash to automatic sqlscrpt load and awr_snapshot.

SQL_SCRIPT=$1
sqlplus -s tobi/tobi @start_awr_snapshot.sql
for i in `seq 1 32` # Repeat runs to observe variation.
dosqlplus -s tobi/tobi @$SQL_SCRIPT &done
sleep 30
while true; do if [ `ps -ef|grep itl_run.sql|wc -l` -eq 1 ] ; then
sqlplus -s tobi/tobi @end_awr_snapshot.sql;
break; fi; done

--start_awr_snapshot.sql

ALTER SYSTEM SET STATISTICS_LEVEL=ALL;
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

--end_awr_snapshot.sql

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL;

--DUMP BLOCK AFTER the first load run with 105 concurrent session.

Oracle create 21(total now is 23) additional transaction entries based on the available space, and even shrink row data/tsiz to 7584 from 8088 bytes.

as oracle docs says "In data blocks allocated to segments that support transactional changes, free space can also hold transaction entries when the header space is depleted"

you can check ITL count by dumping the block and search for "itc".

Now, going to test implication of ITL contention on a block (and generally speaking table segment) using AWR to measure.

case 1: will use 16 concurrent session. less than ITL available (current available is 0x17 which is 23 in decimal)

case 2: will use 32 concurrent session. greater than ITL available

--analysis of AWR

for 16 concurrent session AWR reported no ITL waits event! e.g. allocate ITL entry.

also segment by waits report no segment under these sessions

Segments by ITL Waits                   DB/Inst: WRO18C/wro18c Snaps: 138-139
                   No data exists for this section of the report.
                          ------------------------------------------------------

For 32 concurrent session AWR reported "TX-Transaction (allocate ITL entry)"= scarcity of ITL resources in block.

enq: TX - allocate ITL entry           499         30   60.11ms     .3 Configur
..
Segments by ITL Waits                   DB/Inst: WRO18C/wro18c Snaps: 140-141
-> % of Capture shows % of ITL waits for each top segment compared
-> with total ITL waits for all segments captured by the Snapshot
-> When ** MISSING ** occurs, some of the object attributes may not be available
                      Tablespace
Owner                   Name
-------------------- ----------
                     Subobject Obj.                                 ITL    % of
Object Name            Name     Type        Obj#   Dataobj#        Waits Capture
-------------------- ---------- ----- ---------- ---------- ------------ -------
TOBI                 ITL_8KB
ITL_TABLE                       TABLE      75084      75084          499 100.00
                          ------------------------------------------------------

--Case 3: will delete couple of entries in the table(4 row). this is to prove that even if you have a higher number of concurrent session, if you have sufficient ITL entries available, ITL allocation will not be a bottleneck for your concurrent transactions.

each transaction entries occupies 23bytes. we can make room for 9 more entries by delete 4 rows(4*70 > 23*9).

DELETE itl_table
WHERE
    dbms_rowid.rowid_row_number(ROWID) IN (
        104,
        103,
        102,
        101
    ); /** remember rowid start from 0. we have 105 rows in the itl_table. this should lead to free atleast 4* 70(66 column size + overhead) bytes **/
COMMIT;

after block dump shows:

avsp=0x6 (only 6 bytes available for data, oracle algorithm is intelligent enough to see this is hot block, definitely a flag is set to ON in the block meta data that signify space pressure for ITL entries allocation; avsp is 0x6bytes instead of 0x11e total space available)

tosp=0x11e (total available space available)

After re-executing the 32-concurrent session workload again, transaction entries increased by 10 making the current transaction entries count 33.

..
itc: 33
..
avsp=0x36
tosp=0x36

mathematically, 286(tosp=0x11e total space available before running 32 concurrent session) - 23(bytes for each itl)*10(number of newly itl created)

The AWR report shows no "enq: TX - allocate ITL entry" events...even though 32 concurrent sessions! ITL have been gracefully increased to 32.

Segments by ITL Waits                   DB/Inst: WRO18C/wro18c Snaps: 147-148

                   No data exists for this section of the report.
                          ------------------------------------------------------

Summary:

when creating table with high update (and insert) consider INITRANS value in your create table statements.

Also PCTFREE is another solution to ITL contention, because oracle can decide to use "free space" in data block for ITL entries.

you can also see importance of ITL for migrated rows and compression at JL

Sunday, November 25, 2018

How to Pause Oracle Startup OR Solution to ORA-600 [kokiasg1]

This blog about an issue in oracle that is related to oracle internal sequence; causing crash after passing mount state. This method can be applied in similar scenario.
error thrown was ORA-600 [kokiasg1]; this has to do with getting a sequence number for auditing. Frits Hoogland has a lookup for Oracle C functions.

below is the error message and follow by step to break/pause oracle startup(opening the DB in this case) before failure. this allow creation of missing sequence.
Caution: get oracle support before doing this on PROD ;)

Error Message

SYS@DB_TEST> startup
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 7663544 bytes
Variable Size 3590324296 bytes
Database Buffers 4932501504 bytes
Redo Buffers 59445248 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 44921
Session ID: 141 Serial number: 8902

Solution or How to Pause Oracle Startup

1. startup mount (and leave the session terminal open)

SYS@DB_TEST> startup mount

2. find the PID of controlling process for example: using ps -ef|grep oracle|grep LOCAL

oracleDB_TEST (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

3. initiate gdb session with pid and oracle executable (if this fail, check OS user privilege, or simply run as root)

/usr/bin/gdb $ORACLE_HOME/bin/oracle 4406

4. little more explanation: next task is to instruct gdb to break/pause oracle opening of database when DB operation reaches KOKIASG1. note the DB is still in mount state.

5. At gdb terminal, issue

(gdb) break kokiasg

6. Issue continue or c , to allow program continue debugging

(gdb) continue

7. go back to the sqlplus and open DB. this will appear hang at some point but will not fail. this will give you opportunity to issue any corrective statement or investigative queries, in other sqlplus session.

sqlplus> alter database open;

8. open another sqlplus session. In this case, SYS object will be re-created. PS: SYS objects was delete because sys user was used to import to DB in Vault database environment. special consideration when dealing with Oracle Database Vault need to be followed when using expdp/impdp utilities.
we execute the corrective statement:

SYS@DB_TEST> create sequence IDGEN1$
start with 1
increment by 50
minvalue 1
cache 2;

9. Close gdb session:

Breakpoint 1, 0x0000000001a99eb0 in kokiasg ()
(gdb) kill
Kill the program being debugged? (y or n) y
(gdb) quit
hang sqlplus session will now return:

10. gdb log session details:

linuxlab[DB_TEST](/home/oracle)$ /usr/bin/gdb $ORACLE_HOME/bin/oracle 4406

GNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6)Copyright (C) 2010 Free Software Foundation, Inc.License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>This is free software: you are free to change and redistribute it.There is NO WARRANTY, to the extent permitted by law. Type "show copying"and "show warranty" for details.This GDB was configured as "x86_64-redhat-linux-gnu".For bug reporting instructions, please see:<http://www.gnu.org/software/gdb/bugs/>...Reading symbols from /oracle/app/121//bin/oracle...(no debugging symbols found)...done.Attaching to program: /oracle/app/121//bin/oracle, process 4406Reading symbols from /oracle/app/121//lib/libodm12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libodm12.soReading symbols from /oracle/app/121//lib/libcell12.so...done.Loaded symbols for /oracle/app/121//lib/libcell12.soReading symbols from /oracle/app/121//lib/libskgxp12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libskgxp12.soReading symbols from /oracle/app/121//lib/libskjcx12.so...done.Loaded symbols for /oracle/app/121//lib/libskjcx12.soReading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/librt.so.1Reading symbols from /oracle/app/121//lib/libmql1.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libmql1.soReading symbols from /oracle/app/121//lib/libipc1.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libipc1.soReading symbols from /oracle/app/121//lib/libclsra12.so...done.Loaded symbols for /oracle/app/121//lib/libclsra12.soReading symbols from /oracle/app/121//lib/libdbcfg12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libdbcfg12.soReading symbols from /oracle/app/121//lib/libhasgen12.so...done.Loaded symbols for /oracle/app/121//lib/libhasgen12.soReading symbols from /oracle/app/121//lib/libskgxn2.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libskgxn2.soReading symbols from /oracle/app/121//lib/libocr12.so...done.Loaded symbols for /oracle/app/121//lib/libocr12.soReading symbols from /oracle/app/121//lib/libocrb12.so...done.Loaded symbols for /oracle/app/121//lib/libocrb12.soReading symbols from /oracle/app/121//lib/libocrutl12.so...done.Loaded symbols for /oracle/app/121//lib/libocrutl12.soReading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libaio.so.1Reading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libaio.so.1Reading symbols from /oracle/app/121//lib/libons.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libons.soReading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/libdl.so.2Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.Loaded symbols for /lib64/libm.so.6Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.[Thread debugging using libthread_db enabled]Loaded symbols for /lib64/libpthread.so.0Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libnsl.so.1Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.Loaded symbols for /lib64/libc.so.6Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/ld-linux-x86-64.so.2Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/libnss_files.so.2Reading symbols from /oracle/app/121//lib/libnque12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libnque12.soReading symbols from /oracle/app/121//lib/libshpkavx12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libshpkavx12.soReading symbols from /oracle/app/121//rdbms/lib/odm/libnfsodm12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//rdbms/lib/odm/libnfsodm12.so0x0000003c78e0e810 in __read_nocancel () from /lib64/libpthread.so.0Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.212.el6.x86_64 libaio-0.3.107-10.el6.x86_64

(gdb) break kokiasg

Breakpoint 1 at 0x1a99eb0(gdb) continue

Continuing.
Breakpoint 1, 0x0000000001a99eb0 in kokiasg ()(gdb) kill

Kill the program being debugged? (y or n) y

(gdb) quit

11. Similar method use here xifenfei

Browse and Edit Oracle Data File Using Oracle BBED. Sometimes you forget such tools exist ;)

Sunday, May 13, 2018

OPTIMIZER MODE when first_rows leads to bad plans

Beware when setting optimizer_mode for application session, esp. if this session is used to execute different statements.

I encounter this issue, which was made more elusive because bind variables are not used in sql text. So, no quick fix by sql profile or baseline. I am going to post only the plan two plan with timing information.
select OPTIMIZER,sql_id from DBA_HIST_SQL_PLAN where OPTIMIZER='FIRST_ROWS'; -- will indicate which sql_id using first_rows .

SYS@APPS_DB1> select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,CPU_TIME,PHYSICAL_READ_REQUESTS,DISK_READS from v$sql where PARSING_SCHEMA_NAME='APPS_SCHEMA' and sql_id in('9705zux4zfg3m','3zkfwn4c0dmfm');

NUMA Server Soft Partitioning Using CGROUP and PROCESSOR_GROUP_NAME in Red Hat 7

It now more common to see server with terabytes of memory and hundreds of CPU cores. this more a revised topic.

Imagine a situation: a client has a NUMA server, for CONF, SAT and DEV environment.

why not instance caging?

1. cpu_count is only for CPU.

2. memory can be allocated from any NUMA node, this can cause numa_miss, increase latency between memory and CPU.

3. there is no restriction on set of CPU core that DBs can used. so, there is a possibility of increase context switch on CPU cores.

Here is a NUMA server memory distribution which hosts up to 100 oracle databases.

Numa_miss can be avoided using PROCESSOR_GROUP_NAME

how to use PROCESSOR_GROUP_NAME. this is based on the concept of cgroup(linux)/resource group(solaris).

cgroup as defined by Linux kernel documentation, associates a set of tasks with a set of parameters for one or more subsystems.

cgroup allow basically larger server to be soft partitioned. there other cool stuff apart from CPU and memory allocation you can do with this, and it easy.

Here is a simple config to show this concept.

vi /usr/lib/systemd/system/ora_sat.service
[Unit]
Description=test PROCESSOR_GROUP_NAME
After=syslog.target network.target auditd.service
[Service]
ExecStartPre=/usr/bin/mkdir -p /sys/fs/cgroup/cpuset/ora_sat
ExecStartPre=/bin/bash -c '/bin/chown -R oracle:dba /sys/fs/cgroup/cpuset/ora_sat '
ExecStartPre=/bin/bash -c '/usr/bin/echo "0" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.mems'
ExecStart=/bin/bash -c '/usr/bin/echo "1" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus'
Restart=on-failure
RemainAfterExit=yes
[Install]
WantedBy=multi-user.target
--
#systemctl start ora_sat.service
#systemctl enable ora_sat.service

Do find and replace for "ora_sat" and create ora_dev and ora_conf or other names/environment.

Since just a PoC, I am allocating one core. I will recommend allocating all CPU core in one NUMA node. Avoid cross CPU core allocation. Below numactl -H, gives good visual overview.

To allocate all CPU on node 1:

ExecStartPre=/bin/bash -c '/usr/bin/echo "0-9,40-49" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.mems'

Enabling at Oracle DB level.

SQL> alter system set processor_group_name='ora_sat' scope=spfile;
SQL> show parameter processor_group_name
NAME                                                        TYPE VALUE
------------------------------------ ----------- ------------------------------
processor_group_name                     string               ora_sat
SQL>
SQL> show parameter cpu_count
NAME                                                        TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count                                               integer            1
SQL>

checking status of service

-bash-4.2# systemctl status ora_sat.service
● ora_sat.service - test PROCESSOR_GROUP_NAME
   Loaded: loaded (/usr/lib/systemd/system/ora_sat.service; disabled; vendor preset: disabled)
   Active: active (exited) since Sun 2018-04-01 17:24:37 EDT; 1h 35min ago
Process: 14792 ExecStart=/bin/bash -c /usr/bin/echo "1" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus (code=exited, status=0/SUCCESS)
Process: 14788 ExecStartPre=/bin/bash -c /usr/bin/echo "0" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.mems (code=exited, status=0/SUCCESS)
Process: 14785 ExecStartPre=/bin/bash -c /bin/chown -R oracle:dba /sys/fs/cgroup/cpuset/ora_sat (code=exited, status=0/SUCCESS)
Process: 14782 ExecStartPre=/usr/bin/mkdir -p /sys/fs/cgroup/cpuset/ora_sat (code=exited, status=0/SUCCESS)
Main PID: 14792 (code=exited, status=0/SUCCESS)
   Memory: 0B
   CGroup: /system.slice/ora_sat.service
Apr 01 17:24:36 lab-server.teits.net systemd[1]: Starting test PROCESSOR_GROUP_NAME...
Apr 01 17:24:37 lab-server.teits.net systemd[1]: Started test PROCESSOR_GROUP_NAME.
-bash-4.2#

Ensure persistence after reboot

-bash-4.2# systemctl enable ora_sat.serviceCreated symlink from /etc/systemd/system/multi-user.target.wants/ora_sat.service to /usr/lib/systemd/system/ora_sat.service.-bash-4.2#

Caution: If you try modifying beyond allow CPU:

SQL> alter system set cpu_count=2;alter system set cpu_count=2*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-02097: parameter cannot be modified because specified value is invalidSQL>

Note: required to set cpuset.mems, if not result to below error

SQL> startup
ORA-56729: Failed to bind the database instance to processor group ora_sat;
Additional Information: cpuset.mems is not set
Location: chkcpuset:3
ORA-01078: failure in processing system parameters
SQL> exit
Disconnected
lab-server[orcl](/usr/share/doc/kernel-doc-3.10.0)$

Dynamically allocate more resource:(this for testing purpose consider the implications).

[oracle@lab-server ~]$ cat /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus1[oracle@lab-server ~]$ echo "0,1" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus[oracle@lab-server ~]$ cat /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus0-1[oracle@lab-server ~]$

...

SQL> alter system set cpu_count=2;System altered.SQL>

Decrease or increase to a lower value of CPU using cgroup from OS. I created workload on database using SLOB. during the period change number of CPU from (1)50% to 100%(2). see the idle time zero-out

07:16:18 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle07:16:19 PM all 27.27 0.00 9.09 0.00 0.00 1.52 0.00 0.00 0.00 62.1207:16:19 PM 0 1.14 0.00 4.55 0.00 0.00 1.14 0.00 0.00 0.00 93.1807:16:19 PM 1 82.93 0.00 17.07 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:16:19 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle07:16:20 PM all 47.27 0.00 11.82 0.00 0.00 1.82 0.00 0.00 0.00 39.0907:16:20 PM 0 22.39 0.00 11.94 0.00 0.00 1.49 0.00 0.00 0.00 64.1807:16:20 PM 1 86.05 0.00 11.63 0.00 0.00 2.33 0.00 0.00 0.00 0.00======>>>>>>>>>>>> echo "0,1" > /sys/fs/cgroup/cpuset/ora_sat/cpuset.cpus07:16:20 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle07:16:21 PM all 92.13 0.00 7.87 0.00 0.00 0.00 0.00 0.00 0.00 0.0007:16:21 PM 0 91.11 0.00 8.89 0.00 0.00 0.00 0.00 0.00 0.00 0.0007:16:21 PM 1 93.33 0.00 6.67 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:16:21 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle07:16:22 PM all 92.86 0.00 5.95 0.00 0.00 1.19 0.00 0.00 0.00 0.0007:16:22 PM 0 92.86 0.00 4.76 0.00 0.00 2.38 0.00 0.00 0.00 0.0007:16:22 PM 1 90.48 0.00 7.14 0.00 0.00 2.38 0.00 0.00 0.00 0.00

Conclusion:

Control Groups has less overhead and easy to create comparing to other isolation method e.g. virtual machines.

Cgroup can also do other cool stuff apart from resource limitation such as prioritization, accounting and control.

Saturday, March 17, 2018

Install18c - Little Hack - Tested on Red Hat 7.4

Installing oracle 18c software is pretty straight forward. see http://www.dbarj.com.br/en/2018/02/installing-oracle-database-18-on-ol-7/

Creating oracle DB, just modify sample scripts below.

ORACLE_BASE=/u04/app/base
ORACLE_HOME=/u04/soft
ORACLE_SID=wro18c
export ORACLE_SID ORACLE_HOME ORACLE_BASE
export PATH=$ORACLE_HOME/bin:$PATH

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName wro18c -sid wro18c -sysPassword orcl -systemPassword orcl -sampleSchema true -initParams _exadata_feature_on=true

Other interesting workaround...

using systemtap

Tobi's Notes

Saturday, December 29, 2018

Note on table ITL (enq: TX - allocate ITL entry)

Sunday, November 25, 2018

How to Pause Oracle Startup OR Solution to ORA-600 [kokiasg1]

Sunday, May 13, 2018

OPTIMIZER MODE when first_rows leads to bad plans

Saturday, April 7, 2018

NUMA Server Soft Partitioning Using CGROUP and PROCESSOR_GROUP_NAME in Red Hat 7

Saturday, March 17, 2018

Install18c - Little Hack - Tested on Red Hat 7.4

Links

My Blog List

Author