Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Direct load generating huge REDO when it shouldnt

oraLaroJan 17 2025

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

Comments

MartinBach-Oracle Jan 8 2025

Hi Salomon,

please have a look at this blog post written by @ulrike-schwinn-oracle :

https://blogs.oracle.com/coretec/post/easy-sql-statement-tracking-in23c

I hope this answers your question, if not, please shout!

- Martin

Solomon Yakobson Jan 8 2025

@martinbach-oracle - No, it doesn't answer my question. Article you pointed to shows uses:

SQL> alter system set sql_history_enabled=true scope=both;

And in my post I said “Works fine when enabled on system level”. My question was about

SQL> ALTER SESSION SET SQL_HISTORY_ENABLED = TRUE;

where I showed SQL history was NOT captured even though it should be based on SQL_HISTORY_ENABLED:

Modifiable **ALTER SESSION**, ALTER SYSTEM

SY.

MartinBach-Oracle Jan 8 2025

As per the article I shared the situation is as follows at the moment

  • You must enable SQL history PDB-wide (only a DBA can do that) so there's a certain level of control over the feature
  • Your session has access to the SQL history
  • If you don't want to record anything, set sql_history_enabled to false.

I'm currently assessing if that's intended behaviour (in which case the documentation should be amended) or a feature not working as it should (in which case it needs fixing). The parameter is indeed session-modifyable, but not in the sense you expected.

We'll keep you posted.

- Martin

Solomon Yakobson Jan 8 2025

Do you mean it must be enabled on system level and not on session level and all session can do is disabe it for the session?

SY.

MartinBach-Oracle Jan 14 2025

Yes,

that's correct as of Oracle Database Free 23.6.

- Martin

Solomon Yakobson Jan 14 2025

Thanks Martin, I hope this will be added to 23AI docs soon.

SY.

1 - 6

Post Details

Added on Jan 17 2025
23 comments
236 views