My Oracle Support Banner

ORA-00060 on Text Index's $G or $I Table During Heavy Workload (Doc ID 2832604.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Text - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

Symptoms

During heavy concurrent DML workload, a deadlock (ORA-00060) occurs on a Text index's $G or $I table.

Alert.log shows:

 2021-12-20T20:04:50.052177-05:00
 PDB15(17):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support
 for Troubleshooting ORA-60 Errors. More info in file
 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_p000_5923.trc.
 2021-12-20T20:04:50.451227-05:00
 PDB15(17):Errors in file
 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_j000_27185.trc:
 ORA-12012: error on auto execute of job "CTXSYS"."DR$1110#0007$OMJ"
 ORA-20000: Oracle Text error:
 DRG-50857: oracle error in drixmd.run_bg_optimize
 User-Defined Exception
 DRG-50857: oracle error in dreog_run_bg_opt
 ORA-20000: Oracle Text error:
 DRG-50857: oracle error in drvxmd.run_bg_optimize
 User-Defined Exception
 DRG-50857: oracle error in gslm_o
 ORA-12801: error signaled in parallel query server P000
 ORA-29400: data cartridge error
 ORA-06512: at "CTXSYS.DRUE",
 ORA-06512: at "CTXSYS.DRUE", line 186
 ORA-06512: at "CTXSYS.DRIXMD", line 6657



The trace file for the p00x shows the current sql for the waiting session is:

SELECT count(*) from
(SELECT  /*+ parallel ( 4 ) */
  KCISYS_CTXAGG(sys_ctxinfopk('"SOME_OWNER"', '"DR$BIKE_ITEMS_LOCAL_IDX#0007$I"',
  '"DR$BIKE_ITEMS_LOCAL_IDX#0007$G"',  rowid, token_text, token_type, token_first,
  token_last, token_count, token_info)  ORDER BY token_first, token_last)
 FROM "SOME_OWNER"."DR$BIKE_ITEMS_LOCAL_IDX#0007$G"
 WHERE token_last < 12878
 GROUP BY token_text, token_type)



While the current sql statement of the p00x process is:

delete from "DR$BIKE_ITEMS_LOCAL_IDX#0007$G" where rowid in (select column_value from table(:ridlist))

- OR -

delete /*+ rule */ from "OWNER"."DR$BIKE_ITEMS_LOCAL_IDX#0009$I" where rowid in (select column_value from table(:ridlist))


The Text index involved was created with SYNC ON COMMIT and with STAGE_ITAB enabled.

The wait event identified is "enq: TX - allocate ITL entry".

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.