Ask The Real Tom

“Enq: FB contention” is an ASSM enque wich ensures that only one process format the block. This happen usually on parallel inserts on the same table.

Downgrading 11.2.0.3 to 11.2.0.2 works. But like everything with oracle 11.2 you have to apply a patch.
Oracle Restart (GI) is used for this setup.


# current release
shutdown immediate
startup downgrade
spool downgrade
drop user sysman cascade;
@catdwgrd.sql
spool off
shutdown immediate

shutdown immediate
# change environment
srvctl downgrade database -d dsdb01 -o /opt/app/oracle/product/11.2.0/rdbms1 -t 11.2.0.2
oratab
sid dsdb01
srvctl modify database -d dsdb01 -p /opt/app/oracle/product/11.2.0/rdbms1/dbs/spfiledsdb01.ora
srvctl config database -d dsdb01
???

startup upgrade
spool reload.log
@$ORACLE_HOME/rdbms/admin/catrelod.sql
spool off
shutdown immediate
startup
@utlrp


Actually i go an error ORA-600 [kkoipt:incorrect pwj] on 11.2 an i applied the patch 9929660.

But the error occured on a instance again. So i was wondering how i can check if the online patch is activated.

opatch lsinventory just shows if the patch is installed on the oracle home.

The solution was:

oradebug patch list

Here you see if the patch is activated on the instance. Aswell you could disable the patch for the instance.

I hade recently some trouble with Flashback Data Archive (Total Recal, FBDA) on Oracle 11.2.0.2

On a dissasciate Statement like:
exec DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('USER','TABLE');

Following Oracle error occured:
ORA-00600: internal error code, arguments: [ktfa_get_hist_objn]

The problem was a oracle bug. If you name any column in your table “DATE” - FDBA does not work properly.

“enq:CR - block range reuse ckpt”

http://forums.oracle.com/forums/thread.jspa?messageID=9170907

I think the problem is this: when doing parallel DML, Oracle uses direct path reads to read the data. To make sure the data being read is consistent though it needs to checkpoint blocks it is going to read. CR enqueue seems to be used to synchronize CKPT with PX slaves reading the data. It’s going like this: PX slave requests CKPT to checkpoint a range of blocks it’s about to read (and possibly enqueues on CR in order to be able to do so,) CKPT in turn orders DBWR to write all dirty blocks in the requested range, waits for this write to complete and signals PX slave that the blocks are consistent and can be read. Repeat as necessary. Now, with really large buffer cache and good portion of it dirty due to recent DML on source tables, busy CPUs and relatively slow writes this checkpointing might build up into a major performance inhibitor when these recently touched tables are being read in direct path mode: you have 8 cores and 8 parallel slaves, so CKPT, DBWR and pretty much all other background processes are competing with them for CPU time and I/O bandwidth (they all have the same priority, so your PX slaves can easily evict CKPT or DBWR from CPU only to immediately go back to sleep on an enqueue held by a process it just preempted.)

Things to try here:

1. do a checkpoint before running the insert and see if this makes any difference;
2. lower the degree of parallelism to 5-6;
3. If you have more than one DBWR configured - configure db_writer_processes back to 1.

I am just search since hours for the oracleasm-2.6.32 package … an just coulnd find it.

Now I just came across this pice of information. Maybe it shortens my search

http://www.oracle.com/us/technologies/linux/uek-for-linux-177034.pdf

Be advised that you will have to re-install oracleasm and ocfs2 if you want to revert to the previous PV kernel. Unbreakable Enterprise Kernel itself already includes ocfs2 and oracleasm, so the separate RPMs are not needed.

I’ve wrote a simple SQL script to list all ASM disks with state, size and their diskgroups, if any.

set pages 5000 lines 180
set heading on
set feedback off
set serveroutput on

col LABEL for a28
col DISKGROUP for a9
col STATUS for a9
col TOTAL for a11
col MOUNT_STATE for a11

exec dbms_output.put_line('---');
exec dbms_output.put_line('--- DISK OVERVIEW');
exec dbms_output.put_line('---');

SELECT
        D.LABEL||' ('||D.NAME||')' LABEL,
        (SELECT
          NAME
         FROM
          V$ASM_DISKGROUP
         WHERE
          GROUP_NUMBER=D.GROUP_NUMBER)
        DISKGROUP,
        D.STATE STATUS,
        lpad(D.TOTAL_MB/1024,6)||' GB' TOTAL,
        D.MOUNT_STATUS MOUNT_STATE
FROM
        V$ASM_DISK D
ORDER BY 2;

exec dbms_output.put_line('---');
set feedback on

The output looks as follow:

---
--- DISK OVERVIEW
---

LABEL                        DISKGROUP STATUS    TOTAL       MOUNT_STATE
---------------------------- --------- --------- ----------- -----------
ORAASM1 (ORAASM1)            DG1       NORMAL    99.984 GB   CACHED
ORAASM3 (ORAASM3)            DG1       NORMAL    99.984 GB   CACHED
ORAASM5 (ORAASM5)            DG1       NORMAL    99.984 GB   CACHED
ORAASM4 (ORAASM4)            DG1       NORMAL    99.984 GB   CACHED
ORAASM2 (ORAASM2)            FRA1      NORMAL    99.984 GB   CACHED
ORAASM6 ()                             NORMAL         0 GB   CLOSED
---

Searches on MySQL with expression like ” … like ‘%abc%’” can be incredibly slow on large tables.
The problem is, MySQL can not use indices for expression starting with a wildcard “%”.
Neither MySQL supports function based indexes.

I will explain a simple solution. But first lets have a look to our problem.
Table size: 2G
Row count: 33′000
Query response time: ~ 25s

mysql> desc images;
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| imageid | int(11) | NO | PRI | NULL | auto_increment |
| filename | varchar(200) | YES | MUL | NULL | |
| description | varchar(200) | YES | | NULL | |
| data | longblob | YES | | NULL | |
| type | enum('coversmall','cover','coverback','screenshot','specialcover','banner') | YES | MUL | NULL | |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


The index can not be used


mysql> explain select imageid,filename,description,type from images where filename like '%call%' order by filename;
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | images | ALL | NULL | NULL | NULL | NULL | 371398 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)


Index can be use if the expression does not start with a wild card


mysql> explain select imageid,filename,description,type from images where filename like 'call%' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select imageid,filename,description,type from images where filename like 'call%abc' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+


Since we are searching for the filename, we a new column for the filename. Add a character in front of the string and index the new column.


mysql> alter table images add vfilename varchar(201);

Now we change our application so it add always the same character “a” in the pattern.
So we can use the index.


mysql> explain select imageid,filename,description,type from images where vfilename like 'a%abc%' order by filename;
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | images | range | idx_images_vfilename | idx_images_vfilename | 603 | NULL | 75840 | Using where; Using filesort |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)


The perfomance benefit is, that we can do now a index scan (full/range scan) which read much less data than a full table scan.
We reduced i/o, saved our cache and speed up the query by magnitudes!

This is a real ask the real tom solution!

To lock the SGA into physical memory you have to set the lock_sga parameter to true.

Usually you get following Error message.

SQL> startup
SQL> ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory


The problem it ulimit -l
You have to set it in /etc/security/limits.conf and relogon to the system and restart database.

cat /etc/security/limits.conf
...
oracle soft memlock unlimited
oracle hard memlock unlimited



alter system set lock_sga=true scope=spfile;
shutdown immediate
startup


And you SGA stays in physical memory.

If you SGA gets pages out you will encounter following waits.
Wait Event: latch: shared pool

Enabling and validating constraints is can take a huge amount of time in big oracle databases.
To speed the validaten up, it can be done in parallel.

This can save you valuable time in release windows.

1. Enable Constraint with novalidate option
2. Set parallel degree on the table or on session with force ddl parallel
3. Enable nable the constraints with validation

Example:

ALTER TABLE T_1 ADD CONSTRAINT NC_1 not null (colname) disabled;
ALTER TABLE T_1 ENABLE NOVALIDATE CONSTRAINT NC_1;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE T_1 PARALLEL 8;
ALTER TABLE T_1 ENABLE VALIDATE CONSTRAINT NC_1;
ALTER SESSION DISABLE PARALLEL DDL;