“U” entries in MLOG$

Recently in a discussion with colleague, on what entries do MLOG$ have, the following was agreed to :-

1. “I” for insert
2. “D” for delete
3. “D” + “I” for update.

Along with the above entries, you might notice “U” also. The “U” entry is for update. But then, on what condition do we get “U” and when do we get “D” and “I” entries.

I have used “EMP” table as master table in the example

DBA@:1> CREATE MATERIALIZED VIEW "MV_EMP"
  ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  USING INDEX
  REFRESH FAST ON DEMAND NEXT null
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT * from EMP;
 
Materialized view created.

DBA@:1> insert into emp values (0001,'ANAND','DBA',7839,sysdate,3500,null,20);
 
1 row created.
 
DBA@:1> select * from emp where empno=0001;
 
     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
	 1 ANAND      DBA	      7839 2016.12.03 21:45:16	     3500		     20
 
col CHANGE_VECTOR$$ for a20
 
DBA@:1> select * from mlog$_emp;
 
     EMPNO SNAPTIME$$	       D O CHANGE_VECTOR$$	     XID$$
---------- ------------------- - - -------------------- ----------
	 1 4000.01.01 00:00:00 I N FEFF 		1.1259E+15

DBA@:1> exec dbms_mview.refresh('MV_EMP');
 
PL/SQL procedure successfully completed.

DBA@:1> select * from mlog$_emp;
 
no rows selected
 
DBA@:1> select * from RUPD$_EMP;
 
no rows selected

DBA@:1> update emp set empno=2 where empno=1;
 
1 row updated.
 
DBA@:1> commit;
 
Commit complete.
 
DBA@:1> select * from mlog$_emp;
 
     EMPNO SNAPTIME$$	       D O CHANGE_VECTOR$$	     XID$$
---------- ------------------- - - -------------------- ----------
	 1 4000.01.01 00:00:00 D O 0000 		4.2222E+15
	 2 4000.01.01 00:00:00 I N FFFF 		4.2222E+15

DBA@:1> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
 
Session altered.
 
DBA@:1> exec dbms_mview.refresh('MV_EMP');
 
PL/SQL procedure successfully completed.

bdj59v5mx6tx9
/* MV_REFRESH (MRG) */ MERGE INTO "PRAKANAN_DBA"."MV_EMP" "SNA$" USING (SELECT CURRENT$."EMPNO",CURRENT$."ENAME",CURRENT$."JOB",CURRENT$."MGR",CURRENT$."HIREDATE",CURRENT$."SAL",CURRENT$."COMM",CURREN
T$."DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM
"EMP" "EMP") CURRENT$, (SELECT DISTINCT MLOG$."EMPNO" FROM "PRAKANAN_DBA"."MLOG$_EMP" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."EMPNO" = LOG$."EMPNO")"AV$" ON ("SNA$
"."EMPNO" = "AV$"."EMPNO") WHEN MATCHED THEN UPDATE  SET "SNA$"."EMPNO" = "AV$"."EMPNO","SNA$"."ENAME" = "AV$"."ENAME","SNA$"."JOB" = "AV$"."JOB","SNA$"."MGR" = "AV$"."MGR","SNA$"."HIREDATE" = "AV$"."
HIREDATE","SNA$"."SAL" = "AV$"."SAL","SNA$"."COMM" = "AV$"."COMM","SNA$"."DEPTNO" = "AV$"."DEPTNO" WHEN NOT MATCHED THEN INSERT  (SNA$."EMPNO",SNA$."ENAME",SNA$."JOB",SNA$."MGR",SNA$."HIREDATE",SNA$."
SAL",SNA$."COMM",SNA$."DEPTNO") VALUES (AV$."EMPNO",AV$."ENAME",AV$."JOB",AV$."MGR",AV$."HIREDATE",AV$."SAL",AV$."COMM",AV$."DEPTNO")

In the above example, with an update, “D” and “I” entries are created.

Now, in which scenario, is “U” entry created :-

With the same table example, lets update a row

DBA@:1> update emp set job='DataEng' where JOB='DBA';

1 row updated.

DBA@:1> commit;

Commit complete.

DBA@:1> select * from MLOG$_EMP;

     EMPNO    SNAPTIME$$          D O CHANGE_VECTOR$$      XID$$
   ---------- ------------------- - - -------------------- ----------
            3 4000.01.01 00:00:00 U U 0800                 2.8148E+15

To conclude –

If you are updating a Primary Key column/Rowid column, you will see “D”(delete)+ “I”(Insert) on update(U). For rest of the cases, you will see just “U”.

Enabling Flashback – Be Cautious

Enabling flashback on 11.2.0.4 database can take minutes and cause multiple database wait events degrading database performance.

On a quite busy system, during the low peak hours, I tried to enable flashback on 11.2.0.4.6 version database and multiple sessions waited on

1. buffer exterminate
2. enq: SQ – contention
3. latch: cbc contention
4. SGA: allocation forcing component growth

PRAKANAN_DBA@:1> show parameter sga_target
 
NAME                           TYPE        VALUE
------------------------------ ----------- ---------------
sga_target                     big integer 0

23:35:40 PRAKANAN_DBA@:1> show parameter db_cache_size
NAME                           TYPE        VALUE
------------------------------ ----------- ------------
db_cache_size                  big integer 39G

23:35:53 PRAKANAN_DBA@:1> show parameter shared_pool
 
NAME                           TYPE        VALUE
------------------------------ ----------- -------------
shared_pool_reserved_size      big integer 128M
shared_pool_size               big integer 4G

23:36:21 PRAKANAN_DBA@:1> @free_memory_in_shared_pool.sql
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 283039728

23:38:48 PRAKANAN_DBA@:1> @shared_pool_detail.sql
 
POOL         NAME                               MB
------------ -------------------------- ----------
shared pool  KGLH0                      785.823174
shared pool  SQLA                        592.82975
shared pool  free memory                269.572815
shared pool  db_block_hash_buckets      268.503906
shared pool  SQLP                       192.214272
shared pool  KKSSP                       191.37767
shared pool  FileOpenBlock              156.083519
shared pool  event statistics per sess  142.083984
shared pool  ksunfy : SSO free list     135.169922
shared pool  KGLHD                      132.602089
shared pool  Checkpoint queue              128.125
shared pool  dirty object counts array         128
shared pool  dbktb: trace buffer          117.1875
shared pool  ASH buffers                       100
shared pool  parameter table block      83.7121429
shared pool  KGLDA                      36.5461655
shared pool  object queue hash buckets          36
shared pool  procs: ksunfy              30.6129456
shared pool  transaction                30.5776443
shared pool  enqueue                    27.7831573

SQL> alter database flashback on;
 
Database altered.

23:41:28 PRAKANAN_DBA@:1> /
 
USERNAME                      SID    SERIAL# SPID       EVENT                          LAST_CALL_ET  WAIT_TIME SECONDS_IN_WAIT STATE               SQL_ID        SQL_CHILD_NUMBER PLAN_HASH_VALUE MODULE
------------------------- ------- ---------- ---------- ------------------------------ ------------ ---------- --------------- ------------------- ------------- ---------------- --------------- -------------------------
APPS_USER_2                  3458       9875 15518      SQL*Net message from client               0          0               0 WAITING             b4pav99kxux32                0      3764792327 SessionV2
PRAKANAN_DBA                 9290      35933 21649      SQL*Net message to client                 0         -1               0 WAITED SHORT TIME   8q6awbsup6jm5                0      1761607409 SQL*Plus
APPS_USER_2                  1963      13699 28348      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0 JDBC Thin Client
SQL_240                      7301      36487 28346      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0
APPS_USER_2                  7467      12721 28352      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0 JDBC Thin Client
APPS_USER_2                  9038      42107 28351      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0 JDBC Thin Client
APPS_USER_2                 11153      29227 28354      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0 JDBC Thin Client
APPS_USER_2                   438      52879 28356      buffer exterminate                        1          0               0 WAITING             2zrudchusrcwa                3               0 JDBC Thin Client
APPS_USER_2                  1449      29585 3901       read by other session                     0          0               0 WAITING             4n4u2qqbfcmhu                1      1270994832 ResourceMonitor
APPS_USER_2                  5405      26175 6055       latch: cache buffers chains               0         -1               0 WAITED SHORT TIME   4n4u2qqbfcmhu                1      1270994832 ResourceMonitor
APPS_USER_2                  7807       9423 6057       db file sequential read                   0          0               0 WAITING             2ksm378ta54rw                2       231135236 ResourceMonitor
APPS_USER_2                  2319      34579 12665      db file sequential read                   0          0               0 WAITING             4n4u2qqbfcmhu                1      1270994832 ResourceMonitor
APPS_USER_2                   570      11479 5193       read by other session                     0          0               0 WAITING             4n4u2qqbfcmhu                1      1270994832 ResourceMonitor
SYS                          7106      60689 14609      SGA: allocation forcing compon           11          0               0 WAITING             1pbrwgrgmfu40                0               0 sqlplus@
                                                        ent growth                                                                                                                                
APPS_USER_2                   196      22161 5270       read by other session                     0          0               0 WAITING             4n4u2qqbfcmhu                1      1270994832 ResourceMonitor
 
15 rows selected.
 
Elapsed: 00:00:00.45
23:41:29 PRAKANAN_DBA@:1> /
 
USERNAME                      SID    SERIAL# SPID       EVENT                          LAST_CALL_ET  WAIT_TIME SECONDS_IN_WAIT STATE               SQL_ID        SQL_CHILD_NUMBER PLAN_HASH_VALUE MODULE
------------------------- ------- ---------- ---------- ------------------------------ ------------ ---------- --------------- ------------------- ------------- ---------------- --------------- -------------------------
APPS_USER_2                   899      26003 12761      enq: TX - index contention                0          0               0 WAITING             7mvrrf2cr8w26                1      3334570644 ResourceMonitor
PRAKANAN_DBA                 9290      35933 21649      SQL*Net message to client                 0         -1               0 WAITED SHORT TIME   8q6awbsup6jm5                0      1761607409 SQL*Plus
SYS                          7106      60689 14609      SGA: allocation forcing compon           12          0               1 WAITING             1pbrwgrgmfu40                0               0 sqlplus@
                                                        ent growth 
 
Elapsed: 00:00:00.44
23:41:30 PRAKANAN_DBA@:1> /
 
USERNAME                      SID    SERIAL# SPID       EVENT                          LAST_CALL_ET  WAIT_TIME SECONDS_IN_WAIT STATE               SQL_ID        SQL_CHILD_NUMBER PLAN_HASH_VALUE MODULE
------------------------- ------- ---------- ---------- ------------------------------ ------------ ---------- --------------- ------------------- ------------- ---------------- --------------- -------------------------
APPS_USER_2                    45       5745 18731      db file sequential read                   0         -1               0 WAITED SHORT TIME   7mvrrf2cr8w26                0      3334570644 ResourceMonitor
PRAKANAN_DBA                 9290      35933 21649      SQL*Net message to client                 0         -1               0 WAITED SHORT TIME   8q6awbsup6jm5                0      1761607409 SQL*Plus
APPS_USER_2                 10446      23443 14617      SQL*Net message from client               0          5               0 WAITED KNOWN TIME   8tk54xvssnx13                0      3718310410 SessionV2
SYS                          7106      60689 14609      SGA: allocation forcing compon           13          0               2 WAITING             1pbrwgrgmfu40                0               0 sqlplus@
                                                        ent growth

23:42:17 PRAKANAN_DBA@:1> show parameter shared_pool
 
NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------
shared_pool_reserved_size      big integer 128M
shared_pool_size               big integer 4224M

23:42:48 PRAKANAN_DBA@:1> @free_memory_in_shared_pool.sql
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                2147133056

PRAKANAN_DBA@:1> @hidden_param
Enter value for parameter: _memory_imm_mode_without_autosga
Enter value for 1: _memory_imm_mode_without_autosga
old  11: AND    a.ksppinm like DECODE(LOWER('%&parameter%'), 'all', a.ksppinm, LOWER('%&1%'))
new  11: AND    a.ksppinm like DECODE(LOWER('%_memory_imm_mode_without_autosga%'), 'all', a.ksppinm, LOWER('%_memory_imm_mode_without_autosga%'))
 
PARAMETER                             DESCRIPTION                    SESSION_VA INSTANCE_V
------------------------------------- ------------------------------ ---------- ----------
_memory_imm_mode_without_autosga      Allow immediate mode without   TRUE       TRUE
                                      sga/memory target

23:43:18 PRAKANAN_DBA@:1> select start_time,COMPONENT, PARAMETER, INITIAL_SIZE/1024/1024 "INITIAL_SIZE",FINAL_SIZE/1024/1024 "FINAL_SIZE", STATUS from v$SGA_RESIZE_OPS where start_time > sysdate -1 order by 1;
 
START_TIME          COMPONENT                                                        PARAMETER                                                                        INITIAL_SIZE FINAL_SIZE STATUS
------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ ---------- ---------
2016.11.01 23:41:21 shared pool                                                      shared_pool_size                                                                         4096       4096 ERROR
2016.11.01 23:41:21 DEFAULT buffer cache                                             db_cache_size                                                                           39936      39936 ERROR
2016.11.01 23:41:31 DEFAULT buffer cache                                             db_cache_size                                                                           39936      39808 COMPLETE
2016.11.01 23:41:31 shared pool                                                      shared_pool_size                                                                         4096       4224 COMPLETE
 
23:43:33 PRAKANAN_DBA@:1> @shared_pool_resize_ops.sql
 
END                     OPER_TYPE     INITIAL_SIZE TARGET_SIZE FINAL_SIZE
----------------------- ------------- ------------ ----------- ----------
01-Nov-2016 23:41       GROW            4294967296  4429185024 4294967296
01-Nov-2016 23:41       GROW            4294967296  4429185024 4429185024
29-Nov-2015 09:01       STATIC                   0  4294967296 4294967296

23:43:56 PRAKANAN_DBA@:1> @shared_pool_detail.sql
 
POOL         NAME                               MB
------------ -------------------------- ----------
shared pool  free memory                2044.86245
shared pool  db_block_hash_buckets      268.503906
shared pool  KKSSP                      191.963104
shared pool  FileOpenBlock              156.083519
shared pool  event statistics per sess  142.083984
shared pool  ksunfy : SSO free list     135.169922
shared pool  Checkpoint queue              128.125
shared pool  dirty object counts array         128
shared pool  flashback generation buff  124.156067
shared pool  dbktb: trace buffer          117.1875
shared pool  ASH buffers                       100
shared pool  parameter table block      83.8214722
shared pool  object queue hash buckets          36
shared pool  procs: ksunfy              30.6129456
shared pool  transaction                30.5776443
shared pool  enqueue                    27.7831573
shared pool  buffer handles             25.1770096
shared pool  state objects              22.5323486
shared pool  write state object         19.9843826
shared pool  KGLH0                      18.8174286

Alert log :-

alter database flashback on
Starting background process RVWR
Tue Nov 01 23:40:08 2016
RVWR started with pid=6159, OS id=1030
Tue Nov 01 23:40:29 2016
Errors in file /dumps-01/diag/rdbms/db1/db1/trace/db1_ora_29214.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K

Trace file shows --

Scanned into hot portion of LRU
Deadlock because all buffers pinned.
In set=527, bsz=8192, wsi=0, poolid=3, pool_name=DEFAULT
Buffers originally scanned and found pinned = [189 0], evict=189, aged=0
Buffers rescanned on auxiliary list = 0
Buffers rescanned on main list = 140
Buffers in working set 189 (0), hbufs=50, hbmax=50
Buffers on repl list main=189, aux=0
Scanned into hot portion of LRU
Deadlock because all buffers pinned.
In set=528, bsz=8192, wsi=0, poolid=3, pool_name=DEFAULT
Buffers originally scanned and found pinned = [185 1], evict=184, aged=0
Buffers rescanned on auxiliary list = 0
Buffers rescanned on main list = 136
Buffers in working set 185 (0), hbufs=50, hbmax=50
Buffers on repl list main=185, aux=0

*** 2016-11-01 23:40:29.473
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

From Metalink Doc id 2195173.1

The normal behavior is that the flashback buffer is only allocated at mount time if flashback is already enabled, otherwise the flashback buffer is allocated later when the Database Administrator decides to enable flashback (either explicitly, or by creating the first restore point) but in the latter case, there is the possibility that the database performance may be impacted as the instance searches for the required amount of contiguous memory in shared pool to allocate to the flashback buffer.

The issue is related to

Bug 24838599 Turning on Flashback Database May Take Minutes to Complete and may cause errors ORA-4031 ORA-379″ Doc ID 24838599.8

Patch 24838599: TURNING ON FLASHBACK DATABASE TAKES 15 MINUTES TO COMPLETE

Post Install Instructions for Bug 24838599 – Turning on Flashback Database May Take Minutes to Complete (Doc ID 2195173.1)

Hope this helps!!

EM Express Login – Get Flash

I have 12c setup on OEL 6.7 on  OVM and while trying to access EM express, I was receiving the error

em_express_error

Just for the notes, a user can log into EM Express using URL

https://<hostname>:<port>/em

and port details can be found using

lsnrctl status | grep HTTP
or  
select dbms_xdb_config.getHttpsPort() from dual;

Clicking on the “Get Flash” takes you to abode flash page from where you can download the rpm for flash.

[root@oel67-12c Downloads]# ls -lrt
-rw-r--r-- 1 root root 7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
[root@oel67-12c Downloads]#
[root@oel67-12c Downloads]# rpm -ivh flash-plugin-11.2.202.621-release.x86_64.rpm
Preparing... ########################################### [100%]
1:flash-plugin ########################################### [100%]
[root@oel67-12c Downloads]#

After installing the rpm I still received the same error.So lets debug

[root@oel67-12c Downloads]# cd /usr/lib64/mozilla/plugins/
[root@oel67-12c plugins]# ls -lrth
total 368K
-rwxr-xr-x. 1 root root 5.2K Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root  78K Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root  70K Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root 105K Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root  97K Oct 28  2013 libtotem-cone-plugin.so
lrwxrwxrwx  1 root root   41 May 30 11:43 libflashplayer.so -> /usr/lib64/flash-plugin/libflashplayer.so

[root@oel67-12c plugins]# ls -lrt /usr/lib64/flash-plugin/libflashplayer.so
-rwxr-xr-x 1 root root 0 Apr 26 20:10 /usr/lib64/flash-plugin/libflashplayer.so

A zero byte file, something is wrong!!! I downloaded the .tar.gz version of the file from adobe and after untar

[root@oel67-12c Downloads]# ls -lrt
total 48064
drwxr-xr-x  6 1001  1001     4096 Apr 26 20:10 usr
-r--r--r--  1 1001  1001     4009 Apr 26 20:10 readme.txt
-rw-r--r--  1 1001  1001 19314752 Apr 26 20:10 libflashplayer.so
drwxr-xr-x  2 1001  1001     4096 Apr 26 20:10 LGPL
-rw-r--r--  1 root root   7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
-rw-r--r--  1 root root  20408320 May 30 11:51 install_flash_player_11_linux.x86_64.tar

Hmmm, so the file is ~18MB. Lets copy the file to /usr/lib64/mozilla/plugins/ and change the permissions

[root@oel67-12c plugins]# cp -p /root/Downloads/libflashplayer.so .
[root@oel67-12c plugins]# ls -lrt
total 19232
-rwxr-xr-x. 1 root root     5264 Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root    79752 Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root    70904 Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root   106984 Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root    99176 Oct 28  2013 libtotem-cone-plugin.so
-rw-r--r--  1 1001 1001 19314752 Apr 26 20:10 libflashplayer.so
[root@oel67-12c plugins]# chown root:root libflashplayer.so
[root@oel67-12c plugins]# chmod 775 libflashplayer.so

And, now lets refresh the page and there you go !!!

em_express

 

Fixed Table x$ktfbue has not statistics

While playing around 12.1.0.2 db, I ran my usual script to check tablespace usage details and it took ~59 secs (Elapsed: 00:00:59.02), which was high.
Ran the sql using “gather_plan_statistics” hint and below lines in the execution plan were of interest –

|  34 |      HASH GROUP BY                     |                    |      1 |      6 |      3 |00:00:58.68 |     129K|  54801 |  1160K|  1160K|  758K (0)|
|  35 |       VIEW                             | DBA_FREE_SPACE     |      1 |  21019 |     13 |00:00:00.01 |     129K|  54801 |       |       |          |
|  36 |        JOIN FILTER USE                 | :BF0000            |      1 |        |     13 |00:00:00.01 |     129K|  54801 |       |       |          |
|  37 |         UNION-ALL                      |                    |      1 |        |     19 |00:00:00.01 |     129K|  54801 |       |       |          |
|  38 |          NESTED LOOPS                  |                    |      1 |      1 |      0 |00:00:00.01 |       7 |      0 |       |       |          |
|  39 |           NESTED LOOPS                 |                    |      1 |      1 |      0 |00:00:00.01 |       7 |      0 |       |       |          |
|* 40 |            INDEX FULL SCAN             | I_FILE2            |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |       |       |          |
|* 41 |            TABLE ACCESS CLUSTER        | FET$               |      4 |      1 |      0 |00:00:00.01 |       6 |      0 |       |       |          |
|* 42 |             INDEX UNIQUE SCAN          | I_TS#              |      4 |      1 |      4 |00:00:00.01 |       2 |      0 |       |       |          |
|* 43 |           TABLE ACCESS CLUSTER         | TS$                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 44 |            INDEX UNIQUE SCAN           | I_TS#              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  45 |          NESTED LOOPS                  |                    |      1 |     10 |     10 |00:00:00.01 |      25 |      0 |       |       |          |
|  46 |           NESTED LOOPS                 |                    |      1 |     10 |     10 |00:00:00.01 |      21 |      0 |       |       |          |
|* 47 |            TABLE ACCESS FULL           | TS$                |      1 |      4 |      4 |00:00:00.01 |       9 |      0 |       |       |          |
|* 48 |            FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1)   |      4 |      2 |     10 |00:00:00.01 |      12 |      0 |       |       |          |
|* 49 |           INDEX UNIQUE SCAN            | I_FILE2            |     10 |      1 |     10 |00:00:00.01 |       4 |      0 |       |       |          |
|  50 |          NESTED LOOPS                  |                    |      1 |  21007 |      9 |00:00:57.85 |     129K|  54801 |       |       |          |
|* 51 |           HASH JOIN                    |                    |      1 |      7 |      9 |00:00:00.01 |      13 |      0 |  1115K|  1115K|  471K (0)|
|  52 |            NESTED LOOPS                |                    |      1 |      7 |      9 |00:00:00.01 |      12 |      0 |       |       |          |
|  53 |             NESTED LOOPS               |                    |      1 |     32 |      9 |00:00:00.01 |      11 |      0 |       |       |          |
|* 54 |              TABLE ACCESS FULL         | TS$                |      1 |      4 |      4 |00:00:00.01 |       9 |      0 |       |       |          |
|* 55 |              INDEX RANGE SCAN          | RECYCLEBIN$_TS     |      4 |      8 |      9 |00:00:00.01 |       2 |      0 |       |       |          |
|  56 |             TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$        |      9 |      2 |      9 |00:00:00.01 |       1 |      0 |       |       |          |
|* 57 |            INDEX FULL SCAN             | I_FILE2            |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |       |       |          |
|* 58 |           FIXED TABLE FULL             | X$KTFBUE           |      9 |   3125 |      9 |00:00:58.67 |     129K|  54801 |       |       |          |
|  59 |          NESTED LOOPS                  |                    |      1 |      1 |      0 |00:00:00.01 |      21 |      0 |       |       |          |
|  60 |           NESTED LOOPS                 |                    |      1 |      1 |      0 |00:00:00.01 |      21 |      0 |       |       |          |

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 3 Sql Plan Directives used for this statement

The E-Rows for X$KTFBUE is 3125 whereas A-Rows 9. Checking the stats on the table

SYS@orcl:1> set serveroutput on
SYS@orcl:1>
SYS@orcl:1>
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

Looking at the directive details shows missing details on “F(SYS.TS$) – F(SYS.UNDO$) – F(SYS.X$KTFBUE)”

SYS@orcl:1> exec  print_table('select * from DBA_SQL_PLAN_DIR_OBJECTS where owner=''SYS'' and object_name=''X$KTFBUE'' ');
DIRECTIVE_ID                  : 16383234857226438245
OWNER                         : SYS
OBJECT_NAME                   : X$KTFBUE
SUBOBJECT_NAME                :
OBJECT_TYPE                   : TABLE
NOTES                         : NONONOYES
-----------------

PL/SQL procedure successfully completed.

SYS@orcl:1> exec  print_table('select * from DBA_SQL_PLAN_DIRECTIVES where DIRECTIVE_ID=''16383234857226438245'' ');
DIRECTIVE_ID                  : 16383234857226438245
TYPE                          : DYNAMIC_SAMPLING
ENABLED                       : NO
STATE                         : USABLE
AUTO_DROP                     : YES
REASON                        : JOIN CARDINALITY MISESTIMATE
CREATED                       : 28-MAY-16 09.21.20.000000 PM
LAST_MODIFIED                 : 28-MAY-16 11.12.20.000000 PM
LAST_USED                     : 28-MAY-16 10.21.29.000000000 PM
NOTES                         : MISSING_STATSNO{F(SYS.TS$) - F(SYS.UNDO$) - F(SYS.X$KTFBUE)}
-----------------

PL/SQL procedure successfully completed.

Lets gather fixed object stats :-

SYS@orcl:1> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:02:47.92

Ran the script again and it still took the same time, so checked the stats on X$KTFBUE and its still missing

SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

PL/SQL procedure successfully completed.

Reviewed few documents

Query Against DBA_EXTENTS Performs Slowly After Upgrade to 11.2.0.3 (Doc ID 1453425.1)
Bug 5259025 – The fixed table x$ktfbue has no statistics (Doc ID 5259025.8)

Gathered the stats on fixed table x$ktfbue

SYS@orcl:1>  exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.09
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      : 9581
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   : 47
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   : 9581
LAST_ANALYZED                 : 28-may-2016 22:55:47
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

Re-ran the sql script and Elapsed: 00:00:00.41

Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to oracleasm

[root@oel67-12c dev]# rpm -qa | grep oracleasm
[root@oel67-12c dev]#

2. As no rpm were installed, I started installing the packages

[root@oel67-12c dev]# yum install oracleasm-support
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.8-1.el6 will be installed
--> Finished Dependency Resolution
..........................
Installed:
  oracleasm-support.x86_64 0:2.1.8-1.el6

Complete!
[root@oel67-12c dev]#
[root@oel67-12c dev]# yum install kmod-oracleasm
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kmod-oracleasm.x86_64 0:2.0.8-13.el6_8 will be installed
--> Processing Dependency: kernel >= 2.6.32-642.el6 for package: kmod-oracleasm-2.0.8-13.el6_8.x86_64

...........................
Is this ok [y/N]: y
Downloading Packages:
(1/2): kernel-2.6.32-642.el6.x86_64.rpm
(2/2): kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm   

...........................

Installed:
  kmod-oracleasm.x86_64 0:2.0.8-13.el6_8

Dependency Installed:
  kernel.x86_64 0:2.6.32-642.el6

Complete!
[root@oel67-12c dev]#

3. Download the oracleasmlib rpm from http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html and install it. Without this RPM, you will not have the oracleasm-discover executable


[root@oel67-12c dev]# ls -lrt /media/sf_12cR1/oracleasmlib*
-rwxrwx--- 1 root vboxsf    13336 May 18 21:44 oracleasmlib-2.0.12-1.el6.x86_64.rpm

[root@oel67-12c sf_12cR1]# rpm -ihv oracleasmlib-2.0.12-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
[root@oel67-12c sf_12cR1]#

4. Verify all the components are installed

[root@oel67-12c sf_12cR1]# rpm -qa | grep oracleasm
oracleasm-support-2.1.8-1.el6.x86_64
oracleasmlib-2.0.12-1.el6.x86_64
kmod-oracleasm-2.0.8-13.el6_8.x86_64
[root@oel67-12c sf_12cR1]# cd 
[root@oel67-12c ~]# which oracleasm
/usr/sbin/oracleasm
[root@oel67-12c ~]#

5. Configure oracleasm — will configure the on-boot properties of the Oracle ASM library driver as well as set the appropriate file permissions.

[root@oel67-12c dev]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@oel67-12c dev]#

The configuration setting done above, can be checked in /etc/sysconfig/oracleasm

[root@oel67-12c dev]# ls -lrt /etc/sysconfig/oracleasm
lrwxrwxrwx 1 root root 24 May 18 22:02 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

6. Check the status

[root@oel67-12c ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@oel67-12c ~]#

7. Load oracleasm module with the init command

[root@oel67-12c ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@oel67-12c ~]#

8. Verify the oracleasm configuration

a. Perform ‘df –ha’, this should show you that /dev/oracleasmfs is mounted.


[root@oel67-12c ~]# df -ha
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oel6712c-lv_root
                       45G  7.4G   36G  18% /
....................
oracleasmfs              0     0     0    - /dev/oracleasm

b. Execute the ‘lsmod’ command, to show the loaded oracleasm module.

[root@oel67-12c ~]# lsmod | grep oracleasm
Module                  Size  Used by
oracleasm              53591  1

9. Create the partition on disk , if not already done

[root@oel67-12c oracleasm]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom  /media/VBOXADDITIONS_5.0.20_106931


[root@oel67-12c disks]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x50e6a869.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4):
Value out of range.
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
└─sdb1                           8:17   0   10G  0 part
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom /media/VBOXADDITIONS_5.0.20_106931

Perform the same above steps for remaining disk.

10. Once the disk are created, label the disks as ASM disks and give then an ASM name.


[root@oel67-12c disks]# oracleasm createdisk data0101 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# oracleasm createdisk data0102 /dev/sde1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# ls -lrt
total 0
brw-rw---- 1 oracle oinstall 8, 17 May 18 22:49 DATA0101
brw-rw---- 1 oracle oinstall 8, 65 May 18 22:50 DATA0102

11. Check the configured disk using listdisks

[root@oel67-12c disks]# oracleasm listdisks
DATA0101

12. You can query the disk to check if its a valid ASM Disk

[root@oel67-12c disks]# oracleasm querydisk -d DATA0101
Disk "DATA0101" is a valid ASM disk on device [8,17]

[root@oel67-12c disks]# more /proc/partitions
major minor  #blocks  name
   8    17   10482381 sdb1

[root@oel67-12c sbin]# blkid|grep asm
/dev/sdb1: LABEL="DATA0101" TYPE="oracleasm"
/dev/sde1: LABEL="DATA0102" TYPE="oracleasm"
[root@oel67-12c sbin]#

The [8,17] is the major,minor number of the device with this partition. The value comes from /proc/partitions

major — The major number of the device with this partition. The major number in the /proc/partitions, (3), corresponds with the block device ide0, in /proc/devices.

minor — The minor number of the device with this partition. This serves to separate the partitions into different physical devices and relates to the number at the end of the name of the partition.

#blocks — Lists the number of physical disk blocks contained in a particular partition.

name — The name of the partition.

13. oracleasm-discover


[root@oel67-12c sbin]# oracleasm-discover
-bash: /usr/sbin/oracleasm-discover: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

For the above error install/lib/ld-linux.so.2

[root@oel67-12c ~]# yum install /lib/ld-linux.so.2
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
public_ol6_latest/filelists                                                                                                                                                             |  61 MB     00:43
Resolving Dependencies
--> Running transaction check
---> Package glibc.i686 0:2.12-1.192.el6 will be installed
Dependencies Resolved

..................
Complete!
[root@oel67-12c ~]#

[root@oel67-12c ~]# ls -lrt /lib/ld-linux.so.2
lrwxrwxrwx 1 root root 10 May 18 23:25 /lib/ld-linux.so.2 -> ld-2.12.so
[root@oel67-12c ~]#
[root@oel67-12c ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DATA0101 [20964762 blocks (10733958144 bytes), maxio 512]
Discovered disk: ORCL:DATA0102 [20964762 blocks (10733958144 bytes), maxio 512]
[root@oel67-12c ~]#

Tuned-adm and Oracle

“Tuned” in RHEL7/OEL7 is tuning daemon for automatically tuning the system via the use of tuning profile. It can also be configured to react to changes to improve performance of the server and also to make system settings persistent.

“tuned-adm” is a command line tool that provides a number of different profiles to improve performance.

Below are the profiles provided and supported in RHEL 7 :-

  1. throughput-performance
  2. latency-performance
  3. network-latency
  4. network-throughput
  5. virtual-guest
  6. virtual-host

Apart from the provided profile, we can create custom profiles. All the profile are stored in /usr/lib/tuned/ in RHEL7.

The recommended profile for Oracle database workloads is “throughput-performance”.

In my virtualbox, by default “vitual-guest” was set as active profile

[root@racnode1 ~]# cd /usr/lib/tuned/
[root@racnode1 tuned]# tuned-adm active
Current active profile: virtual-guest

If tuned is not installed, install it using yum

#yum install tuned

Enabled tuned to ensure it is started upon boot time

# systemctl enable tuned.service

Start the tuned service

#systemctl start tuned.service

To check the status of tuned service

#systemctl status tuned.service

Now, create new “oracle” profile to be used

1. Create oracle directory –

[root@racnode1 ~]# mkdir /usr/lib/tuned/oracle

2. Create tuned.conf –

[root@racnode1 ~]# cd /usr/lib/tuned/oracle
[root@racnode1 oracle]# vi tuned.conf
[root@racnode1 oracle]# more tuned.conf
#
# tuned configuration
#
[main]
include=throughput-performance

[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
kernel.shmmax = 4398046511104
kernel.shmall = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.panic_on_oops = 1

[vm]
transparent_hugepages=never

[root@racnode1 oracle]#

3. Activate the newly added oracle profile –

[root@racnode1 oracle]# tuned-adm profile oracle
[root@racnode1 oracle]# sysctl -a | grep vm.swappiness
vm.swappiness = 1
[root@racnode1 oracle]# sysctl -a | grep vm.dirty_ratio
vm.dirty_ratio = 80
[root@racnode1 oracle]#

4. To check the profile list –

[root@racnode1 ~]# tuned-adm list

One of the benefits of tuned is the profiles can be applied dynamically.
To configure dynamic tuning behavior, edit the dynamic_tuning parameter in the /etc/tuned/tuned-main.conf file.

If we are using tuned profile (as shown above) which make system settings persistent, it is recommended to remove all the oracle related entries from /etc/sysctl.conf as the entries are susceptible of being overwrittern, replaced or removed all together.

Hope the article is helpful!!