非共享游标

今天读了https://jonathanlewis.wordpress.com/2007/05/01/optimizer-environment这篇文章,又看了下antognini的书的cursor部分,简单做了一下实验,记录于此。
文中的脚本来自antognini的Troubleshooting Oracle Performance, 2nd Edition、JL的博客和tanel poder的脚本。

环境为linux x86_64 11.2.0.4.7

SQL> CREATE TABLE t
  2  AS
  3  SELECT rownum AS n, rpad('*',100,'*') AS pad
  4  FROM dual
  5  CONNECT BY level <= 1000; Table created. SQL>
SQL> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL>
SQL>
SQL>
SQL>
SQL> show parameter cursor

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
cursor_bind_capture_destination                              string      memory+disk
cursor_sharing                                               string      EXACT
cursor_space_for_time                                        boolean     FALSE
open_cursors                                                 integer     300
session_cached_cursors                                       integer     50
SQL>
SQL> show parameter pga

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
pga_aggregate_target                                         big integer 128M


SQL> SELECT * FROM t WHERE n = 1234;

no rows selected

SQL> select * from t where n = 1234;

no rows selected

SQL> SELECT * FROM t WHERE n=1234;

no rows selected

SQL> SELECT * FROM t WHERE n = 1234;

no rows selected

SQL> COLUMN sql_text FORMAT A36
SQL> COLUMN optimizer_mode FORMAT A14
SQL> COLUMN optimizer_mode_mismatch FORMAT A23
SQL> COLUMN optimizer_mismatch FORMAT A18
SQL>
SQL>
SQL> select sql_id,sql_text,EXECUTIONS from v$sqlarea WHERE sql_text LIKE '%1234';

SQL_ID        SQL_TEXT                             EXECUTIONS
------------- ------------------------------------ ----------
2254m1487jg50 select * from t where n = 1234                1
g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234                2
cgvuzw88sv7gs SELECT * FROM t WHERE n=1234                  1

SQL> select
  2          first_load_time, child_number, last_load_time,
  3          plan_hash_value, loads, executions
  4  from
  5          v$sql
  6  where
  7      sql_id = 'g9y3jtp6ru4cb';

FIRST_LOAD_TIME                                                              CHILD_NUMBER LAST_LOAD_TIME                                                               PLAN_HASH_VALUE      LOADS EXECUTIONS
---------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------- --------------- ---------- ----------
2016-06-29/10:12:20                                                                     0 2016-06-29/10:12:20                                                               1601196873          1          2

SQL> ALTER SESSION SET optimizer_mode = first_rows_1;

Session altered.

SQL>
SQL>
SQL> SELECT * FROM t WHERE n = 1234;

no rows selected

SQL> SELECT sql_id, sql_text, executions
  2  FROM v$sqlarea
  3  WHERE sql_text LIKE '%1234';

SQL_ID        SQL_TEXT                             EXECUTIONS
------------- ------------------------------------ ----------
2254m1487jg50 select * from t where n = 1234                1
g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234                3
cgvuzw88sv7gs SELECT * FROM t WHERE n=1234                  1

SQL> select
  2          first_load_time, child_number, last_load_time,
  3          plan_hash_value, loads, executions
  4  from
  5          v$sql
  6  where
  7      sql_id = 'g9y3jtp6ru4cb';

FIRST_LOAD_TIME                                                              CHILD_NUMBER LAST_LOAD_TIME                                                               PLAN_HASH_VALUE      LOADS EXECUTIONS
---------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------- --------------- ---------- ----------
2016-06-29/10:12:20                                                                     0 2016-06-29/10:12:20                                                               1601196873          1          2
2016-06-29/10:12:20                                                                     1 2016-06-29/10:23:49                                                               1601196873          1          1

SQL> break on child_number skip 1
SQL>
SQL> select
  2          child_number, name, value
  3  from    v$sql_optimizer_env
  4  where
  5      sql_id = 'g9y3jtp6ru4cb'
  6  order by
  7          child_number,
  8          name
  9  ;

CHILD_NUMBER NAME                                     VALUE
------------ ---------------------------------------- -------------------------
           0 _pga_max_size                            799520 KB
             active_instance_count                    1
             bitmap_merge_area_size                   1048576
             cell_offload_compaction                  ADAPTIVE
             cell_offload_plan_display                AUTO
             cell_offload_processing                  true
             cpu_count                                32
             cursor_sharing                           exact
             db_file_multiblock_read_count            128
             deferred_segment_creation                true
             dst_upgrade_insert_conv                  true
             hash_area_size                           131072
             is_recur_flags                           0
             optimizer_capture_sql_plan_baselines     false
             optimizer_dynamic_sampling               2
             optimizer_features_enable                11.2.0.4
             optimizer_index_caching                  0
             optimizer_index_cost_adj                 100
             optimizer_mode                           all_rows
             optimizer_secure_view_merging            true
             optimizer_use_invisible_indexes          false
             optimizer_use_pending_statistics         false
             optimizer_use_sql_plan_baselines         true
             parallel_autodop                         0
             parallel_dblink                          0
             parallel_ddl_mode                        enabled
             parallel_ddldml                          0
             parallel_degree                          0
             parallel_degree_limit                    65535
             parallel_degree_policy                   manual
             parallel_dml_mode                        disabled
             parallel_execution_enabled               true
             parallel_force_local                     false
             parallel_max_degree                      64
             parallel_min_time_threshold              10
             parallel_query_default_dop               0
             parallel_query_mode                      enabled
             parallel_threads_per_cpu                 2
             pga_aggregate_target                     3997696 KB
             query_rewrite_enabled                    true
             query_rewrite_integrity                  enforced
             result_cache_mode                        MANUAL
             skip_unusable_indexes                    true
             sort_area_retained_size                  0
             sort_area_size                           65536
             star_transformation_enabled              false
             statistics_level                         typical
             total_cpu_count                          32
             total_processor_group_count              1
             transaction_isolation_level              read_commited
             workarea_size_policy                     auto

           1 _pga_max_size                            799520 KB
             active_instance_count                    1
             bitmap_merge_area_size                   1048576
             cell_offload_compaction                  ADAPTIVE
             cell_offload_plan_display                AUTO
             cell_offload_processing                  true
             cpu_count                                32
             cursor_sharing                           exact
             db_file_multiblock_read_count            128
             deferred_segment_creation                true
             dst_upgrade_insert_conv                  true
             hash_area_size                           131072
             is_recur_flags                           0
             optimizer_capture_sql_plan_baselines     false
             optimizer_dynamic_sampling               2
             optimizer_features_enable                11.2.0.4
             optimizer_index_caching                  0
             optimizer_index_cost_adj                 100
             optimizer_mode                           first_rows_1
             optimizer_secure_view_merging            true
             optimizer_use_invisible_indexes          false
             optimizer_use_pending_statistics         false
             optimizer_use_sql_plan_baselines         true
             parallel_autodop                         0
             parallel_dblink                          0
             parallel_ddl_mode                        enabled
             parallel_ddldml                          0
             parallel_degree                          0
             parallel_degree_limit                    65535
             parallel_degree_policy                   manual
             parallel_dml_mode                        disabled
             parallel_execution_enabled               true
             parallel_force_local                     false
             parallel_max_degree                      64
             parallel_min_time_threshold              10
             parallel_query_default_dop               0
             parallel_query_mode                      enabled
             parallel_threads_per_cpu                 2
             pga_aggregate_target                     3997696 KB
             query_rewrite_enabled                    true
             query_rewrite_integrity                  enforced
             result_cache_mode                        MANUAL
             skip_unusable_indexes                    true
             sort_area_retained_size                  0
             sort_area_size                           65536
             star_transformation_enabled              false
             statistics_level                         typical
             total_cpu_count                          32
             total_processor_group_count              1
             transaction_isolation_level              read_commited
             workarea_size_policy                     auto


102 rows selected.


SQL> @nonshared g9y3jtp6ru4cb
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

SQL_ID                        : g9y3jtp6ru4cb
ADDRESS                       : 00000002DAF30D98
CHILD_ADDRESS                 : 00000002DAF30B38
CHILD_NUMBER                  : 0
REASON                        : 03Optimizer mismatch(10)3x4012
-----------------
SQL_ID                        : g9y3jtp6ru4cb
ADDRESS                       : 00000002DAF30D98
CHILD_ADDRESS                 : 00000002DAEE82B0
CHILD_NUMBER                  : 1
OPTIMIZER_MODE_MISMATCH       : Y

此条目发表在Infrastructure, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

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 / 更改 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s