Oracle 19
Have 2 particular table. Largest has maybe 10 million rows. 2 indexes. fairly normal, a PK and an index on a date. Normal size, around 8-900mb. No table or index bloat or fragmentaion.
I have a peculiar case where someone started to load data into a table using a direct load class from dotnet called OracleBulkCopy
https://docs.oracle.com/en/database/oracle/oracle-database/19/odpnt/OracleBulkCopyClass.html
I have 2 tables where strange behaviour occurs. Trying to push about 50k records in. The records go in but there is something really strange going on. When we use direct load, oracle has some operations it needs to do in background.
It takes about 4 minutes to run. Active_Session_history shows me for the 4 minutes its stuck on a SQL_ID thats running a direct load insert but all the columns are null
INSERT /*+ SYS_DL_CURSOR */ INTO my_table (col1,col2,col3.....) VALUES (NULL,NULL,NULL,NULL,NULL)
Anyone who has ever seen the hint SYS_DL_CURSOR knows it comes from a direct load call. Heres the bit thats strange, for the 4 minutes or so the session is stuck on “db file sequential read” and the object its stuck on is the index on the table and the session is generating 5-6gb of REDO while its doing this. This should not be happening.
Top_level_call is DirPathMiscOps
db_opname is LOAD.
As a test, I used sqlldr to push the rows in from a csv they load in sub second as expected for data levels around 50k
Has anyone seen anything like this before? It looks to me that the loader is trying to update the index but also writing to it as well. Maybe something to do with that particular loader class. We were getting other session delayed on “log file sync” while this was running hence me investigating and finding it. resolved once we stopped the loader