Description


Uniface driver for Oracle support only version of Oracle 11, but it still uses hint first rows. According to many documents from Oracle, this hint is deprecated as long as from Oracle 9. For example, you can find more info here http://www.dba-oracle.com/art_orafaq_oracle_first_rows_sql_optimization.htm - according to that article: \"Staring in Oracle9i release 2, the Oracle performance tuning guide says that the first_rows optimizer mode has been deprecated and to use first_rows_n instead.\". Since this hint first rows is deprecated for such a long time (Oracle 9 is not supported by Uniface 9.6), why the new driver does not use the recommended hint first_rows_n instead? It seems to be really much much faster and much better than the old one. We need Uniface to support newer oprimizer mode first_rows_n (and all_rows) instead of the old one. In many cases, the hint first rows caues serious delay instead of improvement. Several tests results in: select with hint first rows - 1min 40 sec select without any hint - 0.3sec (all rows is used) select with hint first rows(100) - 0.3sec This problem was also discussed with Uniface Technical Support - and there is a registered Bug 30295. In some situations, we construct our own where for use in the read trigger (it is where for Oracle). But sometimes, the hint first rows results in a long time waiting just to read nothing at all. The time is in minutes (with hint first rows) insted of tenths of seconds (using hint first rows (n)). Tracing down these problems is quite a complex task. But if Uniface would use recommended hints (OPTIMIZER_MODE) instead of deprecated ones, this problem would never occur.

Use Case


Uniface driver for Oracle support only version of Oracle 11, but it still uses hint first rows. According to many documents from Oracle, this hint is deprecated as long as from Oracle 9. For example, you can find more info here http://www.dba-oracle.com/art_orafaq_oracle_first_rows_sql_optimization.htm - according to that article: \"Staring in Oracle9i release 2, the Oracle performance tuning guide says that the first_rows optimizer mode has been deprecated and to use first_rows_n instead.\". Since this hint first rows is deprecated for such a long time (Oracle 9 is not supported by Uniface 9.6), why the new driver does not use the recommended hint first_rows_n instead? It seems to be really much much faster and much better than the old one. We need Uniface to support newer oprimizer mode first_rows_n (and all_rows) instead of the old one. In many cases, the hint first rows caues serious delay instead of improvement. Several tests results in: select with hint first rows - 1min 40 sec select without any hint - 0.3sec (all rows is used) select with hint first rows(100) - 0.3sec This problem was also discussed with Uniface Technical Support - and there is a registered Bug 30295. In some situations, we construct our own where for use in the read trigger (it is where for Oracle). But sometimes, the hint first rows results in a long time waiting just to read nothing at all. The time is in minutes (with hint first rows) insted of tenths of seconds (using hint first rows (n)). Tracing down these problems is quite a complex task. But if Uniface would use recommended hints (OPTIMIZER_MODE) instead of deprecated ones, this problem would never occur.

Importance


Important for good performance

Type


Database Drivers

Operating System


Not Applicable

Status


Open

One thought on “The Oracle driver from Uniface needs to support all_rows and hint_first_rows_n.”

  1. May be better  and safer to add option for oracle hints to read statement. I am not sure there is one  optimizer mode,  leading to good performance results  in all circumstances

Leave a Reply