Oracle Database Discussions

Hello,

The optimizer settings and the ones reported in the 10053 trace does not match. Is this a known issue ? Version is printed in the code snippet.

Here, optimizer_mode is set to ALL_ROWS, but 10053 trace reports this as first_rows_100. Similarly, optimizer_index_cost_adj is 1. But, it is 25 in the trace.

The query is not using hints.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter opti

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
object_cache_optimal_size            integer     102400
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.3
optimizer_index_caching              integer     100
optimizer_index_cost_adj             integer     1
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
plsql_optimize_level                 integer     2
SQL>

Contents of 10053 trace

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  sort_area_retained_size             = 65535
  optimizer_mode                      = first_rows_100
  optimizer_index_cost_adj            = 25
  optimizer_index_caching             = 100
  *********************************

I can see the same used in here..

Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : COT_PLUS
  plan_hash      : 733167152
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 100)
      FIRST_ROWS(100)
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "CDW"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "O"@"SEL$2" ("ORDERS"."STATUS_ID"))
      LEADING(@"SEL$5DA710D3" "CDW"@"SEL$1" "O"@"SEL$2")
      USE_NL(@"SEL$5DA710D3" "O"@"SEL$2")
    END_OUTLINE_DATA
  */

Rgds,
Gokul

Edited by: Gokul Gopal on 13-Jun-2012 03:14