After installation of EPS 10.2.1 on Oracle 11 there is a default new-connection-sql in the ps-ds.xml. This sql is executed and is altering the Oracle session parameters just before executing for example a PMQL query.
Why are these Oracle parameters altered?
It just seems that these are some DEBUG settings which are somehow accidently part of the default installation.
We assume that these setting are influencing the Oracle query optimizer so our PMQL queries are runnig very slow, causing timeouts.
<new-connection-sql>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF' OPTIMIZER_FEATURES_ENABLE = '10.2.0.1' OPTIMIZER_MODE = FIRST_ROWS_1 "_HASH_JOIN_ENABLED" = FALSE "_PUSH_JOIN_PREDICATE" = TRUE "_PUSH_JOIN_UNION_VIEW" = TRUE "_COMPLEX_VIEW_MERGING" = TRUE "_TABLE_SCAN_COST_PLUS_ONE" = TRUE "_ORDERED_NESTED_LOOP" = TRUE "_NEW_INITIAL_JOIN_ORDERS" = TRUE "_UNNEST_SUBQUERY" = TRUE "_INDEX_JOIN_ENABLED" = TRUE NLS_SORT = BINARY NLS_COMP = BINARY NLS_NUMERIC_CHARACTERS = '.,' QUERY_REWRITE_ENABLED = FORCE DB_FILE_MULTIBLOCK_READ_COUNT = 1 OPTIMIZER_INDEX_COST_ADJ = 5 OPTIMIZER_INDEX_CACHING = 95</new-connection-sql>
regards,
Jeroen Verhagen