How To Know Parameters With Which a Statement Is Executed

Many times we need to know that a particular establishment parameterization runs. We can see in a fast way look at V$SQL_OPTIMIZER_ENV view. Consider the example below;

 PWCENTER*ANTO> SELECT
  2    CHILD_NUMBER as "CHILD",
  3    NAME AS "PARAMETER NAME",
  4    VALUE AS "VALUE",
  5    ISDEFAULT AS "DEFAULT?"
  6  FROM
  7    V$SQL_OPTIMIZER_ENV
  8  WHERE
  9     SQL_ID='00tbncd11cdrp'  ORDER BY 1, 2      
 10  ;

     CHILD PARAMETER NAME                           VALUE                     DEF
__________ ________________________________________ _________________________ ___
         1 active_instance_count                    1                         YES
         1 _always_semi_join                        off                       NO
         1 bitmap_merge_area_size                   1048576                   YES
         1 _b_tree_bitmap_plans                     false                     NO
         1 cpu_count                                32                        YES
         1 cursor_sharing                           exact                     YES
         1 hash_area_size                           131072                    NO
         1 _hash_join_enabled                       false                     NO
         1 optimizer_dynamic_sampling               2                         YES
         1 optimizer_features_enable                10.2.0.5                  YES
         1 optimizer_index_caching                  0                         YES
         1 optimizer_index_cost_adj                 10                        NO
         1 _optimizer_max_permutations              100                       NO
         1 optimizer_mode                           first_rows_10             NO
         1 optimizer_secure_view_merging            true                      YES
         1 _optimizer_sortmerge_join_enabled        false                     NO
         1 _optim_peek_user_binds                   false                     NO
         1 parallel_ddl_mode                        enabled                   YES
         1 parallel_dml_mode                        disabled                  YES
         1 parallel_execution_enabled               true                      YES
         1 parallel_query_mode                      enabled                   YES
         1 parallel_threads_per_cpu                 2                         YES
         1 _partition_view_enabled                  false                     NO
         1 pga_aggregate_target                     3072000 KB                YES
         1 _pga_max_size                            614400 KB                 NO
         1 query_rewrite_enabled                    true                      YES
         1 query_rewrite_integrity                  enforced                  YES
         1 skip_unusable_indexes                    true                      YES
         1 sort_area_retained_size                  0                         YES
         1 sort_area_size                           65536                     YES
         1 star_transformation_enabled              false                     YES
         1 statistics_level                         typical                   YES
         1 workarea_size_policy                     auto                      YES

33 rows selected

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s