Hang On Insert Into DR$INDEX_NAME$P Table When Creating Text Index Using URL_DATASTORE (Doc ID 2733254.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 19.7.0.0.0 version, RDBMS

ACTUAL BEHAVIOR
---------------
Oracle Text index sync is is blocked on insert into DR$INDEX_NAME$P table

The session is consuming lot of CPU on OS side
 ora_dev 15876 1 0 09:01:40 ? 265:41 oracleGDIV30 (LOCAL=NO)

Following is noticed in the AWR report:

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
DB CPU 10.5K 97.8
TEXT: URL_DATASTORE network wait 417,955 3.5 8.36us .0 Network
local write wait 523 1.2 2.30ms .0 User I/O
log file sync 664 .6 917.76us .0 Commit
latch: shared pool 233 .5 2.18ms .0 Concurrency
db file sequential read 673 .3 484.74us .0 User I/O
library cache: mutex X 42 .3 6.61ms .0 Concurrency
PX Deq: Worker Session Stats 128 .3 1.99ms .0 Other
PGA memory operation 6,085 .2 36.43us .0 Other
PX Deq: Join ACK 64 .2 3.00ms .0 Other

SQL ordered by CPU Time

CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
10,280.44 79,562 0.13 97.94 10,281.18 99.99 0.00 3yhb272q07y8d oracle@gvadevt8 (TNS V1-V3) insert into "OWNER"."DR$INDX_NAME...
2,323.68 81,353,346 0.00 22.14 2,327.04 99.86 0.00 2jfqzrxhrm93b select /*+ rule */ c.name, u.n...
14.71 12 1.23 0.14 15.11 97.36 1.54 1c05u4yjtmm89 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
14.44 12 1.20 0.14 14.84 97.35 1.57 ampw9ddqufjd3 DBMS_SCHEDULER begin /*KAPI:capture*/ dbms_au...
13.59 99 0.14 0.13 13.59 99.99 0.00 a4akgk9g69h83 SQL Developer SELECT d.* FROM ( SELECT d.*, ...
9.97 12 0.83 0.10 9.98 99.92 0.02 c61ajdcqbqn42 SYS_AUTO_STS_MODULE SELECT new.sql_seq, old.plan_h...
7.91 12 0.66 0.08 7.91 99.91 0.02 8rv2ym2sgxkbk SYS_AUTO_STS_MODULE SELECT VALUE(P) FROM TABLE(DBM...
6.07 12 0.51 0.06 6.08 99.91 0.00 cs7jd60ay387d SYS_AUTO_STS_MODULE SELECT /*+ first_rows(1) */ s...
4.91 3 1.64 0.05 4.91 100.00 0.00 6f396067ttdzu SQL Developer select count(*) from "OWNER"."D...
2.56 2 1.28 0.02 2.74 93.32 7.41 a95n8p4vpxmps begin prvt_hdm.execute_..

SQL Text:

3yhb272q07y8d insert into "OWNER"."DR$INDEX_NAME$P" (pat_part1, pat_part2) values (:1, :2)

Cause

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