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