does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice?

Question

So I'm working in application that involving display of data in table/grid. And the grid will invoke paging query to oracle. So does by tuning my application to get the best query cost with optimizer_mode=first_rows_10 is always good and consider best practice with this kind paging search with html table or any table based approach application.

Answer 1

If you're not ready to make this a system-wide setting, you could try it in an isolated fashion by using a query hint:

SELECT /*+ FIRST_ROWS(10) */
       first_column,
       second_column
  FROM my_table_or_view

This is less risky, since you don't need to consider the effects on the entire system.