Fetch First performing worse than Rownum

Hibernate ORM
21

Hi @hashemi,
yes, it should work. Here is the procedure signature in 23ai:

PROCEDURE DUMP_TRACE
  Argument Name             Type                 In/Out Default?
  ------------------------- -------------------- ------ --------
  P_SQL_ID                  VARCHAR2             IN
  P_CHILD_NUMBER            NUMBER               IN     Y
  P_COMPONENT               VARCHAR2             IN     Y
  P_FILE_ID                 VARCHAR2             IN     Y
  P_OPTIONS                 CLOB                 IN     Y

Moreover you’d need SELECT ANY TABLE being granted directly to the database user invoking this stored procedure.

HTH

22

Hi everyone,

I have some updates on this issue and where we are with my team.

We tried the following query:

ALTER SYSTEM SET "_optimizer_cbqt_or_expansion"=on;

When enabled, the plan is incorrect and performs a full table scan.
When disabled, the plan is as expected and looks similar to the one obtained with the ROWNUM method.

We discovered that an Oracle patch exists since version 19.7, and that we needed to enable it to fix this issue. We found that we had the patch in our current version.
So we tried the following:

ALTER SESSION SET "_fix_control" = '33649782:1';
or
ALTER SESSION SET "_fix_control" = '33649782:ON';
or
ALTER SYSTEM SET "_fix_control" = '33649782:1';
or
ALTER SYSTEM SET "_fix_control" = '33649782:ON';

But we don’t see any differences… How can we know if the patch is correctly enabled on our database?

We’ve been in touch with Oracle support for three weeks, but after requesting multiple traces, they don’t seem to know why this is happening and are advising us to disable the

_optimizer_cbqt_or_expansion

parameter for each query that is poorly executed, which is a lot of work and not a good solution at all.

In the meantime, we’re attempting to downgrade Hibernate in order to revert to using ROWNUM, as a temporary workaround to circumvent the issue.

Thanks

1 Like
23

Hi @loiclefevre the SR number of our discussions with the Oracle team is SR 3-37401103791.

24

Hi!

Is there a way to use the old ROWNUM approach without downgrading Hibernate?
Is extending the OracleDialect and overriding this behavior possible/recommended?

Thanks!

25

The only way you can do this is by subclassing org.hibernate.dialect.OracleSqlAstTranslator to override shouldEmulateFetchClause as well as subclassing OracleDialect to return that custom translator.

26

Hi,

We got rownum back with hibernate 6.1.7 and this property:
jpaProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");

As oracle cannot tell us why their patch does not seem to work, we will do this way for now…