Query Entity: Effect of Adding /*+FIRST_ROWS(10)*/ Hint in View

By 'hint,' are you referring to the comment block in your view statement? If so, I don't believe a code comment would result in the unexpected behavior.

Do you have an order by clause in the view or are you providing sortInfo in your queryEntity?
Would be interesting to know how Appian has implemented paginination for Oracle databases as it didn’t do it natively until 12c.

I was always told that the most important thing to know about optimiser hints was not to use them.

Yes we have sortInfo in queryEntity. We have upgraded our Oracle DB to 12c and we saw performance degradation, so this hint has been added.

As per our observation if we don't have order by in view and no sort info was provided then Appian will automatically add a Order By on the Last field of the CDT corresponding to the View or Table

What could possibly be go wrong if we are using the optimizer hints?

Interesting that performance dived after the 12c upgrade, I wonder if it's implementation of the ANSI OFFSET functionality has degraded something else although unlikely. Normally in Oracle you would use sub-queries and rownum for paging data efficiently. Hopefully Appian will (or maybe have) updated the DB layer to make use of the 12c functionality for its performance benefits.

Without knowing exactly how Appian's paging with Oracle works it's possible that the FIRST_ROWS hint is causing your observed issues so the simple (and unhelpful) answer is that you shouldn't use it.

I assume your sortInfo is pointing to the primary key field in the view and you've confirmed that all your data rows are unique? Since the upgrade has your DBA confirmed that the database stats have been collected and have you looked at the explain plan on 12c R2 as a new method of SQL optmization was introduced and if it hasn't been tuned may be responsible for your performance hit since the upgrade.

As a very general statement you shouldn't need to use optimizer hints on an OLTP database as the CBO should, if statistics are gathered correctly, provide the best results. Of course adding a layer such as Appian can always disrupt things.

Please trace the sql being called inside from Appian on that view. For instance if you are displaying this view data in a report, set the SQL trace property in appian_log4j.properties file located in appian/ear/suite.ear/resources folder. Then tail the jboss log and click on the report and see what kind of sql is being executed by appian from inside. Consider evaluating that sql with an Oracle DBA to troubleshoot the issue especially when you reach batchsize of 600

Hi @prakash as per my understanding, there can be multiple reasons behind this issue.

1. Improper pagination
2. Unavailability of sortInfo() in pagingInfo
3. Appian is much flexible with MySQL db compared to any other including Oracle, hence I suspect, whether Appian really supports all the features of Oracle db or not, so this might be an another root cause

Hope this will be helpful

Hi Alok,

Answers to your point:

1. Improper pagination - Working Fine without any issue
2. Unavailability of sortInfo() in pagingInfo - Already provided on Primary Key
3. Appian is much flexible with MySQL db compared to any other including Oracle, hence I suspect, whether Appian really supports all the features of Oracle db or not, so this might be an another root cause - This May be possible.