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.
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.
Is there a way to use the old ROWNUM approach without downgrading Hibernate?
Is extending the OracleDialect and overriding this behavior possible/recommended?
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.
I have the same issue on Oracle 19c. Queries with fetch first are much slower than with rownum. Oracle Support can’t help so far. Currently, we modified OracleSqlAstTranslator and OracleDialect to use the rownum syntax.
Is it possible for Hibernate to support both syntaxes, so the user can decide which one to use?
Hibernate ORM already has way too many flags and we hate to add new ones. Unless we get an official response from Oracle that says both approaches are sensible, we will continue to treat this as Oracle bug or data statistics issue. So far, Oracle officially said that fetch first is the proper solution and if it doesn’t work, there must be a bug.
@cstein - Could you kindly share the specific modifications you made to the OracleSqlAstTranslator and OracleDialect classes to incorporate the ROWNUM syntax? Your insights on this would be greatly appreciated and extremely helpful.
I have commented out a part regarding Cause.FOR_UPDATE because it’s missing in the current version of the enum.
open class CustomOracleDialect : OracleDialect() {
override fun getLimitHandler(): LimitHandler {
// version must be greater 9, see org.hibernate.dialect.pagination.LegacyOracleLimitHandler
return LegacyOracleLimitHandler(DatabaseVersion.make(19))
}
override fun supportsFetchClause(type: FetchClauseType): Boolean {
// don't use "fetch first"
return type != FetchClauseType.ROWS_ONLY
}
override fun getSqlAstTranslatorFactory(): SqlAstTranslatorFactory {
return object : StandardSqlAstTranslatorFactory() {
override fun <T : JdbcOperation?> buildTranslator(
sessionFactory: SessionFactoryImplementor, statement: Statement
): SqlAstTranslator<T> {
return CustomOracleSqlAstTranslator(sessionFactory, statement)
}
}
}
}
// methods rolled back to revision 4e9a64334657c98e95df30c7eaca7826ac5424ed
open class CustomOracleSqlAstTranslator<T : JdbcOperation?>(
sessionFactory: SessionFactoryImplementor,
statement: Statement
) : OracleSqlAstTranslator<T>(
sessionFactory, statement
) {
private fun supportsOffsetFetchClause(): Boolean {
return dialect.supportsFetchClause(FetchClauseType.ROWS_ONLY)
}
override fun shouldEmulateFetchClause(queryPart: QueryPart): Boolean {
// Check if current query part is already row numbering to avoid infinite recursion
if (queryPartForRowNumbering === queryPart) {
return false
}
val hasLimit =
queryPart.isRoot && hasLimit() || queryPart.fetchClauseExpression != null || queryPart.offsetClauseExpression != null
if (!hasLimit) {
return false
}
// Even if Oracle supports the OFFSET/FETCH clause, there are conditions where we still want to use the ROWNUM pagination
if (supportsOffsetFetchClause()) {
// When the query has no sort specifications and offset, we want to use the ROWNUM pagination as that is a special locking case
return !queryPart.hasSortSpecifications() && !hasOffset(queryPart) // Workaround an Oracle bug, segmentation fault for insert queries with a plain query group and fetch clause
|| queryPart is QueryGroup && clauseStack.isEmpty && statement is InsertSelectStatement
}
return true
}
override fun getFetchClauseTypeForRowNumbering(queryPart: QueryPart): FetchClauseType? {
val fetchClauseType = super.getFetchClauseTypeForRowNumbering(queryPart)
val hasOffset = if (queryPart.isRoot && hasLimit()) {
limit.firstRow != null
} else {
queryPart.offsetClauseExpression != null
}
if (queryPart is QuerySpec && !hasOffset && fetchClauseType == FetchClauseType.ROWS_ONLY) {
// We return null here, because in this particular case, we render a special rownum query
// which can be seen in #emulateFetchOffsetWithWindowFunctions
// Note that we also build upon this in #visitOrderBy
return null
}
return fetchClauseType
}
override fun emulateFetchOffsetWithWindowFunctions(
queryPart: QueryPart?,
offsetExpression: Expression?,
fetchExpression: Expression,
fetchClauseType: FetchClauseType,
emulateFetchClause: Boolean
) {
if (queryPart is QuerySpec && offsetExpression == null && fetchClauseType == FetchClauseType.ROWS_ONLY) {
// Special case for Oracle to support locking along with simple max results paging
val querySpec = queryPart
withRowNumbering(
querySpec,
true
) // we need select aliases to avoid ORA-00918: column ambiguously defined
{
appendSql("select * from ")
emulateFetchOffsetWithWindowFunctionsVisitQueryPart(querySpec)
appendSql(" where rownum<=")
val clauseStack = clauseStack
clauseStack.push(Clause.WHERE)
try {
fetchExpression.accept(this)
// removed code: Cause.FOR_UPDATE is missing in current hibernate version
// // We render the FOR UPDATE clause in the outer query
// clauseStack.pop()
// clauseStack.push(Clause.FOR_UPDATE)
// visitForUpdateClause(querySpec)
} finally {
clauseStack.pop()
}
}
} else {
super.emulateFetchOffsetWithWindowFunctions(
queryPart,
offsetExpression,
fetchExpression,
fetchClauseType,
emulateFetchClause
)
}
}
override fun visitOrderBy(sortSpecifications: List<SortSpecification?>?) {
// If we have a query part for row numbering, there is no need to render the order by clause
// as that is part of the row numbering window function already, by which we then order by in the outer query
val queryPartForRowNumbering = queryPartForRowNumbering
if (queryPartForRowNumbering == null) {
renderOrderBy(true, sortSpecifications)
} else {
// This logic is tightly coupled to #emulateFetchOffsetWithWindowFunctions and #getFetchClauseTypeForRowNumbering
// so that this is rendered when we end up in the special case for Oracle that renders a rownum filter
if (getFetchClauseTypeForRowNumbering(queryPartForRowNumbering) == null) {
val querySpec = queryPartForRowNumbering as QuerySpec
if (querySpec.offsetClauseExpression == null
&& (!querySpec.isRoot || offsetParameter == null)
) {
// When rendering `rownum` for Oracle, we need to render the order by clause still
renderOrderBy(true, sortSpecifications)
}
}
}
}
}