bug

On a client DB, after migrating it to OL8, the application started to complaint the DB seems slower. Checking the OEM, there is not much done by the application, but I see a good blue stripe from W00x processes doing sequential reads. Nonstop. This is a 19.31 single-instance database.

After tracing these sessions, they are reading two distinct LOB segments, each between 500 and 700GB, where a lot of deletes happened. All the time “db file sequential read”, up to 3 or 4 W00x parallel processes.

WAIT #0: nam='db file sequential read' ela= 199 file#=476 block#=2460662 blocks=1 obj#=53176 tim=1024371028390

I open an SR with Oracle. They ask more traces and session short stack. The short_stack looks like:

SQL> oradebug setospid 15297
Oracle pid: 28, Unix process pid: 15297, image: oracle@srv0nnnn.xxx.ch (W000)
SQL> oradebug unlimit
Statement processed.
SQL>  oradebug short_stack

ksedsts()+426<-ksdxfstk()+63<-ksdxcb()+872<-sspuser()+236<-__sighandler()<-semtimedop()+14<-sskgpwwait()+245<-skgpwwait()+187<-ksliwat()+2296<-kslwaitctx()+188<-ktsj_slave_main()+944<-ksvrdp_int()+1906<-opirip()+579<-opidrv()+581<-sou2o()+165<-opimai_real()+173<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46

No underscore parameters are set and changing “_enable_space_preallocation” to 0 has no immediate effect.

The tablespace is composed of several smallfiles, EXTENT_MANAGEMENT=LOCAL, ALLOCATION_TYPE=SYSTEM, SEGMENT_SPACE_MANAGEMENT=AUTO. All standard.

The LOB segment is securefile, configured in-row, with several larger values which go out of row.

I ask several times to Oracle: what triggers the KTSJ Slaves processes to fully read huge LOB segments? I do not get a satisfactory answer.

We do not have this problem with all other LOB segments of the DB. The workaround at the end was to defragment the LOBs, moving them to a new tablespace (which we wanted to do anyway). The LOB segment reduced from >500GB to 75G and the Space Manager processes disappeared completely. This lead to the next problem (next post).

I leave this post here just in case someone encounters the same.

One of my clients had a quite easy desire to try Oracle Text for an existing application. Oracle Text allows to use “standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web”.

It seemed simple, until we tried to implement on table named after the city where I studied: T_COIMBRA

create table T_COIMBRA (COL_ID timestamp not null, COL_TXT varchar2(100));

create unique index COIMBRA_IDX_ID on T_COIMBRA(COL_ID);

alter table T_COIMBRA add constraint PK_COIMBRA primary key (COL_ID) using index COIMBRA_IDX_ID;

create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;

Nothing special it seems. But we get an error:

create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context

Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10528: primary keys of type TIMESTAMP(6) are not allowed
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

Below I show how to workaround this and keep an “unsupported” column as unique identifier of the table.

Surprises happen when you go to the latest version of Oracle without deep testing.
My client just hit the bug:
Bug 20513930 : UPDATE FAILS WITH [QERHJDISABLEHJCBK: NOT A HASH JOIN]

Basically the client was getting a ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] when doing a certain query. Looking at it, it was an UPDATE of a view. This view had a “instead of update” trigger, which was doing several actions. After enabling trace on the session, the bug does not happen!

Fortunately someone had already open the bug at Metalink. The workaround is to disable the new “groundbreaking feature” (dixit Oracle) called Adaptive Plans.

SQL> alter system set "_optimizer_adaptive_plans"=false;
System altered.

Hopefully there will be soon a patch for this problem.