`
itspace
  • 浏览: 978602 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle cursor_sharing，histogram对于sql version count的影响

SQLOracleJ# 
阅读更多
本文章主要研究cursor_sharing参数不同设置，histograms收集与否对SQL version count 产生的影响。
一、cursor_sharing参数与SQL version count
数据库研究版本：
引用
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


创建测试脚本
引用
create table t1( i number, j number, k number);
begin
  for i in 1..10000 loop
    insert into t1 values( i, i, i);
  end loop;
end;
/

将cursor_sharing参数改为similar，可以看出谓词连接采用>（还有>, <, >=, <=, LIKE）时，SQL在共享parent cursor时不能共享child cursor
引用
SQL> alter session set cursor_sharing=similar
  2  ;

Session altered.

SQL> select count(*) from t1 where i > 10;

  COUNT(*)
----------
      9990

SQL>select sql_text,version_count from v$sqlarea where
  2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            1


SQL> select count(*) from t1 where i > 20;

  COUNT(*)
----------
      9980

SQL> select sql_text,version_count from v$sqlarea where
  2      sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            2


SQL> select count(*) from t1 where i > 30;

  COUNT(*)
----------
      9970

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            3


将谓词连接改为=时，SQL在共享parent cursor时能共享child cursor
引用
SQL>  select count(*) from t1 where i=100;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            3

select count(*) from t1 where i=:"SYS_B_0"
            1

SQL> select count(*) from t1 where i=200;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            3

select count(*) from t1 where i=:"SYS_B_0"
            1


将cursor_sharing参数改为force，我们看到cursor实现了共享。
引用
SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select count(*) from t1 where i > 40;

  COUNT(*)
----------
      9960

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            4

select count(*) from t1 where i=:"SYS_B_0"
            1

SQL> select count(*) from t1 where i > 50;

  COUNT(*)
----------
      9950

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            4

select count(*) from t1 where i=:"SYS_B_0"
            1

进一步将cursor_sharing改为exact，cursor甚至不能在parent级别实现共享
引用
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select count(*) from t1 where i > 70;

  COUNT(*)
----------
      9930

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            4

select count(*) from t1 where i >:"SYS_B_0"
            1

select count(*) from t1 where i=:"SYS_B_0"
            1


SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > 70
            1


SQL> select count(*) from t1 where i > 80;

  COUNT(*)
----------
      9920

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
            4

select count(*) from t1 where i >:"SYS_B_0"
            1

select count(*) from t1 where i=:"SYS_B_0"
            1


SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > 80
            1

select count(*) from t1 where i > 70
            1

二、cursor_sharing参数histogram与SQL version count

在table t1所有列上收集直方图（J列除外）
引用
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sys',tabname=>'t1',method_opt=>'FOR ALL COLUMNS SIZE 10');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sys',tabname=>'t1',method_opt=>'FOR COLUMNS J SIZE 1');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_BUCKETS,HISTOGRAM from dba_tab_columns  where OWNER='SYS' and TABLE_NAME='T1';

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
I                                       10 HEIGHT BALANCED
J                                        1 NONE
K                                       10 HEIGHT BALANCED


将cursor_sharing改为similar，发现在有直方图的列上不能实现child cursor共享。
引用
SQL> alter session set cursor_sharing = similar;

Session altered.

SQL> select count(*) from t1 where i = 10;

  COUNT(*)
----------
         1

SQL>     select sql_text,version_count from v$sqlarea where
  2      sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1


SQL> select count(*) from t1 where i = 20;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            2


SQL> select count(*) from t1 where i = 30;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            3




SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from t1 where j = 10;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
            1


SQL> select count(*) from t1 where j = 20;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
            1


SQL> select count(*) from t1 where j = 30;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
            1


将cursor_sharing改为force时，无论在有直方图或无直方图的列上都实现了游标共享。
引用
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select count(*) from t1 where i = 10;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1


SQL>  select count(*) from t1 where i = 20;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1


SQL> select count(*) from t1 where i = 30;            

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1

SQL> select count(*) from t1 where j = 10;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1

select count(*) from t1 where i = :"SYS_B_0"
            1


SQL> select count(*) from t1 where j = 20;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1

select count(*) from t1 where i = :"SYS_B_0"
            1


SQL> select count(*) from t1 where j = 30;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
            1

select count(*) from t1 where i = :"SYS_B_0"
            1

将cursor_sharing改为exact，并使用绑定变量，经测试实现了游标共享，但是需要注意的是如果使用绑定变量由于BIND PEEKING，会导致执行计划不稳定
引用
SQL>  alter system flush shared_pool;

System altered.

SQL>  alter  session set cursor_sharing=exact;

Session altered.

SQL> variable i number;         
SQL> exec :i:=10

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
            1


SQL> exec :i:=20

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
            1


SQL> exec :i:=0

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

  COUNT(*)
----------
         0

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
            1

SQL> variable j number;
SQL> exec :j:=10

PL/SQL procedure successfully completed.

SQL>  select count(*) from t1 where j = :j;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
            1


SQL> exec :j:=100

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where j = :j;

  COUNT(*)
----------
         1

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :j
            1

select count(*) from t1 where i = :i
            1


SQL> exec :j:=1000

PL/SQL procedure successfully completed.

SQL> select sql_text,version_count from v$sqlarea where
  2  sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :j
            1

select count(*) from t1 where i = :i
            1
0
顶
1
踩
分享到：
| Oracle Sequence Audses$研究
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Exp_CCI_Histogram - MetaTrader 5EA.zip

    《基于MetaTrader 5的CCI_Histogram交易系统详解》 在金融市场的交易世界里，智能交易系统（Expert Advisor，简称EA）是自动化交易策略的重要工具，它能够帮助交易者实现无需人工干预的自动买卖。本文将详细介绍名...

    LBPH_Sub_Functions.rar_lbph_lbph matlab_sub_sub histogram

    标题中的"LBPH_Sub_Functions.rar_lbph_lbph matlab_sub_sub histogram"暗示了这是一个与局部二值模式（Local Binary Patterns, LBP）相关的MATLAB代码库，特别关注的是子函数和直方图处理。LBP是一种简单而有效的...

    local_histogram_eq.rar_local histogram

    在"local_histogram_eq.rar_local histogram "这个压缩包中，包含了两个关键的MATLAB脚本文件：`local_histo_eq.m`和`neighbor.m`。`local_histo_eq.m`应该是实现局部直方图均衡化的主程序，而`neighbor.m`则可能...

    HISTOGRAM-ANALYSIS.zip_histogram_local histogram

    Histogram Equalization and Local Histogram Equalization of Images

    DRAW_COLOR_HISTOGRAM2 - MetaTrader 5脚本.zip

    《DRAW_COLOR_HISTOGRAM2 - MetaTrader 5 脚本详解》 在金融交易领域，MetaTrader 5（MT5）是一个广泛使用的交易平台，它提供了丰富的技术分析工具和编程语言MQL5，用于创建自定义指标和交易机器人。本篇文章将详细...

    Exp_ATR_Normalize_Histogram - MetaTrader 5EA.zip

    《基于ATR_Normalize_Histogram指标的MetaTrader 5 EA交易系统详解》 在金融交易领域，自动交易系统（Expert Advisor，简称EA）已经成为许多投资者的首选工具，它能够根据预设的策略进行自动化交易，大大提升了交易...

    Oracle常用技术资料合集.zip

    二、《Oracle数据库SQL执行计划的取得和解析》PPT（附SQL文件） 三、Oracle 常用脚本 2pc_clean.txt ash_sql_line_id.txt ash_top_sql_event.txt ash_used awr_db_time.txt awr_event_histogram.txt awr_metric_...

    Exp_BalanceOfPower_Histogram - MetaTrader 5EA.zip

    《MetaTrader 5 EA：Exp_BalanceOfPower_Histogram 智能交易系统解析》 在金融交易领域，自动化交易系统已经成为许多投资者和交易者的首选工具。MetaTrader 5（MT5）作为一款强大的外汇交易平台，提供了丰富的技术...

    Exp_XWPR_Histogram_Vol_Direct - MetaTrader 5EA.zip

    《MetaTrader 5 EA——基于XWPR_Histogram_Vol_Direct指标的交易系统》 在金融交易领域，自动交易系统（Expert Advisor，简称EA）是利用编程技术实现的智能化交易策略，它能够根据预设的规则自动进行买入、卖出等...

    Exp_RAVI_Histogram - MetaTrader 5EA.zip

    同时，由于EA依赖于特定的振荡指标，因此，对RAVI_Histogram的理解和市场趋势的判断也会影响交易效果。 总结来说，"Exp_RAVI_Histogram" EA是基于RAVI_Histogram振荡指标的自动化交易策略，通过MT5平台执行。它利用...

    Exp_XRSI_Histogram_Vol_Direct - MetaTrader 5EA.zip

    《基于XRSI_Histogram_Vol_Direct指标的MetaTrader 5 EA交易系统详解》 在金融交易领域，自动交易系统（Expert Advisor，简称EA）是利用编程语言编写的一种智能化交易程序，它能够根据预设的规则自动执行买卖操作。...

    xxx.zip_HSV histogram_video

    本文将详细解析"xxx.zip_HSV histogram_video"中的知识点，包括HSV色彩空间以及如何生成视频的HSV直方图。 HSV，全称Hue（色调）、Saturation（饱和度）、Value（明度），是一种色彩模型，与我们常见的RGB色彩空间...

    Exp_XDeMarker_Histogram_Vol_Direct - MetaTrader 5EA.zip

    《基于XDeMarker_Histogram_Vol_Direct指标的MetaTrader 5 EA交易系统详解》 在金融交易领域，自动交易系统已经成为许多投资者和交易者的首选工具。MetaTrader 5（MT5）作为一款强大的外汇、期货和股票交易平台，...

    XCCI_Histogram_Vol_Direct - MetaTrader 5脚本.zip

    《XCCI_Histogram_Vol_Direct - MetaTrader 5脚本》是一款专为MetaTrader 5交易平台设计的技术分析工具。该脚本的核心是XRSI_Histogram_Vol指标，它结合了RSI（相对强弱指数）和成交量的特征，通过在直方图上用彩色...

    DIB.rar_DIB_projection histogram_垂直投影_灰度直方图_鍨傜洿 鎶曞奖

    "DIB.rar_DIB_projection histogram_垂直投影_灰度直方图_鍨傜洿 鎶曞奖"这个压缩包文件显然包含了与图像处理相关的源代码，特别是关于水平投影、垂直投影、灰度直方图和平滑处理的算法。让我们逐一深入探讨这些概念...

    Exp_XDPO_Histogram - MetaTrader 5EA.zip

    《基于XDPO_Histogram指标信号的MetaTrader 5 EA交易系统详解》 在外汇交易领域，智能化交易系统（Expert Advisor，简称EA）扮演着至关重要的角色。MetaTrader 5（MT5）作为一款广泛使用的交易平台，提供了丰富的...

    ColorSTD_Histogram_HTF - MetaTrader 5脚本.zip

    《MetaTrader 5中的ColorSTD_Histogram_HTF脚本详解》 MetaTrader 5（MT5）是一款全球广泛使用的外汇交易和分析平台，其强大的功能和丰富的技术指标库深受交易者喜爱。今天我们将深入探讨一个名为"ColorSTD_...

    Exp_WPR_Histogram - MetaTrader 5EA.zip

    《基于WPR_Histogram指标的MetaTrader 5 EA交易系统详解》 在金融交易领域，自动交易系统（Expert Advisor，简称EA）已经成为一种常见的工具，它允许投资者在无需人工干预的情况下进行交易。本文将深入探讨名为"Exp...

    Exp_XWPR_Histogram_Vol - MetaTrader 5EA.zip

    《基于MetaTrader 5 EA的XWPR_Histogram_Vol交易系统详解》 在金融交易领域，自动交易系统，特别是Expert Advisors（EA），已经成为许多交易者的首选工具。本文将深入探讨一个名为"Exp_XWPR_Histogram_Vol - ...

    DRAW_COLOR_HISTOGRAM - MetaTrader 5脚本.zip

    本次提到的"DRAW_COLOR_HISTOGRAM - MetaTrader 5脚本.zip"是一个与MetaTrader 5相关的脚本，用于创建一种特殊的图表元素——彩色直方图。 首先，我们需要理解什么是直方图。直方图是一种统计图表，它将数据分布...

Magicbox
Global site tag (gtag.js) - Google Analytics