cursor_sharing=similar 与 直方图

最新推荐文章于 2024-09-05 10:00:22 发布
最新推荐文章于 2024-09-05 10:00:22 发布
阅读量76 收藏
点赞数
文章标签： 数据库
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 很多MOS文档和书籍都描述，cursor_sharing=similar时，如果谓词列上直方图，那么ORACLE将认为是unsafe的，从而重新硬解析SQL语句，但今天遇到1个问题，发现其和直方图无必然关系，而是和谓词列上是否有统计信息直接相关

 

测试1：使用method_opt => 'for all columns size 1'收集了统计信息

 

SQL> @traceon 10046 12 fl2  

 

[MESSAGE] EVENT:10046  LEVEL:12

[MESSAGE] GREP:fl2  TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_15709.trc

 

SQL> SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>1

  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     16923

 

1 row selected.

 

SQL> /

Enter value for a: 2

old   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>2

  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     15384

 

1 row selected.

 

SQL> /

Enter value for a: 3

old   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>3

  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     13845

 

1 row selected.

 

SQL> /

Enter value for a: 4

old   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>4

  oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     12306

 

1 row selected.

 

SQL> SELECT parse_calls,executions,loads,version_count FROM v$sqlstats WHERE sql_text LIKE '%zq2%'

  2  ;

 

PARSE_CALLS EXECUTIONS      LOADS VERSION_COUNT

----------- ---------- ---------- -------------

          4          4          4             4

 

SQL> SELECT sql_id,plan_hash_value FROM v$sql WHERE sql_text LIKE '%zq2%';

 

SQL_ID        PLAN_HASH_VALUE

------------- ---------------

fph8hajpxnj20      3508397080

fph8hajpxnj20      3508397080

fph8hajpxnj20      3508397080

fph8hajpxnj20      3508397080

 

可以看到，SQL执行了4次，解析了4次，VERSION_COUNT为4

游标不能重用的原因为：0x0300，按照MOS文档

       High Version Count with CURSOR_SHARING = SIMILAR or FORCE [ID 261020.1]

        if the field named oacflg2 or flg2 has a value 300 this means the literal was replaces (100) and the literal is unsafe (200).

       

ORACLE认为这种绑定是不安全的（unsafe），导致绑定失败，使得本来应该共享的游标没有共享，从而导致硬解析

       Unsafe Literals or Peeked Bind Variables [ID 377847.1]

        Note that, prior to 11g, unsafe literals are NOT covered by 'bind mismatch' in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches.

 

因为cursor_sharing=similar，SQL的字面值在替换后相同，其是1个父游标（对应替换后的SQL语句），下带N个子游标（对应subheap 0）

这里4次执行的执行计划是相同的，硬解析是完全没有必要的

 

一般来说，在cursor_sharing=similar时，当查询相关的列在存在直方图的情况下，并且运算条件非“=”的情况下，ORACLE会认为是unsafe的。

目前的情况是，已经未收集直方图，DBA_HISTOGRAMS中的2条记录是在收集列统计信息的情况下的最小值（2条数据，一条代表min value，一条代表max value）

 

测试2：使用DBMS_STATS.DELETE_COLUMN_STATS(NULL,'TEST','id')删除列的统计信息

 

SQL> @traceon 10046 12 fl2

 

[MESSAGE] EVENT:10046  LEVEL:12

[MESSAGE] GREP:fl2  TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_16394.trc

 

SQL> SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>1

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     16923

 

1 row selected.

 

SQL> /

Enter value for a: 2

old   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>2

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     15384

 

1 row selected.

 

SQL> /

Enter value for a: 3

old   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>3

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     13845

 

1 row selected.

 

SQL> /

Enter value for a: 4

old   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>4

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     12306

 

SQL> SELECT column_name,endpoint_number,endpoint_value

  2    FROM DBA_HISTOGRAMS

  3   WHERE TABLE_NAME = 'TEST'

  4     AND WNER = 'SYS'

  5   ORDER BY 2, 3;

 

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

NAME                                         0     5.0563E+35

NAME                                         1     5.0563E+35

 

SQL> SELECT parse_calls,executions,loads,version_count FROM v$sqlstats WHERE sql_text LIKE '%zq4%';

 

PARSE_CALLS EXECUTIONS      LOADS VERSION_COUNT

----------- ---------- ---------- -------------

          4          4          1             1

 

 

SQL> SELECT sql_id,plan_hash_value FROM v$sql WHERE sql_text LIKE '%zq4%';

 

SQL_ID        PLAN_HASH_VALUE

------------- ---------------

97dm6rb1bf8ah      2901380809

 

可以看到，当删除了ID列统计信息，以彻底删除ID列的直方图后，SQL能如期望中的共享游标

 

根据MOS文档

        QUERIES HAVING HUGE VERSION COUNTS WHEN CURSOR_SHARING=SIMILAR [ID 731468.1]

        Note that the usual reason for seeing high version counts with CURSOR_SHARING=SIMILAR is that there is histogram data on some column/s involved in predicates where the value of the

replaced literal in the predicate varies a lot (eg: on a primary key columns).

        In this case we only share the cursor if the values are identical.

        Be sure to avoid histogram data on such columns.

 

       Unsafe Literals or Peeked Bind Variables [ID 377847.1]

        With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

    Is the operator NEITHER of   '=' or '!='

    OR

    Are there Column Histograms present on the column.

        If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, =, <=, LIKE) are unsafe).

 

这里的描述具有迷惑性，直方图信息的来源是sys.histgrm$，min/max value的来源为sys.Hist_Head$，这2张表都是DBA_HISTOGRAMS的基表，按照描述理解，删除直方图信息（sys.histgrm$）后，SQL就应该认为是safe的，应该共享游标，而实际情况下，其参考的是sys.Hist_Head$中的数据（sys.Hist_Head$为dba_tab_cols的基表），也就是列统计数据。

 

测试3：重新收集统计信息，包括直方图，然后清除sys.Hist_Head$中的列统计

收集直方图，手工删除sys.Hist_Head$中的数据（列统计）（非客户测试环境）

        dbms_stats.gather_table_stats(NULL,'TEST',method_opt => 'for all columns',cascade => TRUE);

        DELETE sys.Hist_Head$ WHERE obj#=14023

 

SQL> @traceon 10046 12 fl2

 

[MESSAGE] EVENT:10046  LEVEL:12

[MESSAGE] GREP:fl2  TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_16835.trc

 

SQL> SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>1

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     16923

 

1 row selected.

 

SQL> /

Enter value for a: 2

old   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>2

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     15384

 

1 row selected.

 

SQL> /

Enter value for a: 3

old   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>3

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     13845

 

1 row selected.

 

SQL> /

Enter value for a: 4

old   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new   1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>4

  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

 

  COUNT(*)

----------

     12306

 

1 row selected.

 

SQL> SELECT column_name,endpoint_number,endpoint_value

  2    FROM DBA_HISTOGRAMS

  3   WHERE TABLE_NAME = 'TEST'

  4     AND WNER = 'SYS'

  5     AND column_name='ID'

  6   ORDER BY 2, 3;

 

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

ID                                        1538              0

ID                                        3077              1

ID                                        4616              2

ID                                        6155              3

ID                                        7694              4

ID                                        9233              5

ID                                       10772              6

ID                                       12310              7

ID                                       13848              8

ID                                       15386              9

ID                                       16924             10

 

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

ID                                       18462             11

ID                                       20000             12

 

SQL> SELECT A.LOW_VALUE, A.HIGH_VALUE, A.AVG_COL_LEN, A.HISTOGRAM

  2    FROM DBA_TAB_COLUMNS A

  3   WHERE TABLE_NAME = 'TEST'

  4     AND WNER = 'SYS';

 

LOW_VALUE            HIGH_VALUE           AVG_COL_LEN HISTOGRAM

-------------------- -------------------- ----------- ---------------

                                                      NONE

                                                      NONE

 

是否认为SQL的变量是unsafe的，很大一部分是参考列统计

 

ORACLE未提供不收集列统计信息的子句，只有method_opt选项，可以明确指定一个在查询谓词中不会使用的列给他分析，避免其对其他列生成列统计信息，但是，列统计信息（如density）是CBO成本计算的重要来源，缺失列的统计可能导致执行计划突变

 

还可以修改cursor_sharing = force，但force可能导致一些原来值倾斜的列执行计划不正常，修改前应该先检查SQL的子游标的执行计划是否相同：

select hash_value,plan_hash_value,count(*) from v$sql

where sql_text like '%:"SYS_B_0"%'

group by hash_value,plan_hash_value;

如果全部相同，才能修改。

 

如果部分不同，可以修改后，对这些特定的SQL使用cursor_sharing_exact 提示或者opt_param提示

 

cursor_sharing = similar将在12G被废除

   ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-676570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-676570/

cti27515
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ORACLE set head,Oracle 10.2.0.4 SYSTEM表空间中HIST_HEAD$ 过大如何处理？
weixin_32234493的博客
04-16 358
该楼层疑似违规已被系统折叠隐藏此楼查看此楼Rem Base column statisticscreate table hist_head$ /* histogram header table */(obj# number not null, /* object numb...
Oracle扩展的统计信息
cuiwangxie1183的博客
01-14 307
Oracle扩展的统计信息 --性能优化和故障处理小组 我们在收集列的统计信息与直方图时，往往都是对某一列的收集。当谓词使用多个相关列时，...
Oracle 数据字典表 -- SYS.COL$
tianshibaijia
09-28 316
一.数据字典表SYS.COL$说明 Oracle的数据字典分两类，一个数据字典表，另一个是数据字典视图。 数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。 数据字典表由$ORACLE_HOME/rdbms/admin/sql.bsq脚本创建。 这些数据字典表名称不好记，所以Or...
查看Oracle sys_lob,system表空间满的处理-SYS_LOB0003450292C00039$$
weixin_30213477的博客
04-06 2675
今天同事发现一个库的system表空间快满了，正好没事，登录上去也分析一下。$ sqlplus '/ as sysdba'SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 13 09:57:25 2011Copyright (c) 1982, 2005, Oracle.All Rights Reserved.sys@CQTMSZTF_SQL&...
cursor_sharing=similar深度剖析
03-24 315
在metalink ID 1169017.1文档中，提到建议客户不设置cursor_sharing= similar，因为存在许多性能问题，该参数将在12g中废除该参数，具体给出的理由如下：1、对于语句中包含...
cursor_sharing参数设置exact,similar,force的区别
羽之大公公的博客
06-25 905
Oracle中为了提高sql的执行效率，需要减少硬解析，实现shared cursor共享，最常见的方法是使用绑定变量，但很多时候由于各种原因未能在开发初期使用绑定变量，对于减少硬解析的目的，退而求其次地方法是设置cursor_sharing. 1.准备环境 实验环境 BALLON@PROD> select * from v$version; BANNER ------------...
Cursor_sharing=SIMILAR取值与直方图（上）
ciqu9915的博客
10-18 87
从业界普遍接受绑定变量、硬解析和软解析概念以及对Oracle数据库性能的影响之后。开发和运维DBA就都在寻找一种方法，将系统中已经存在和即将存在的硬编码改为绑定变量方式，进而减少硬解析的出现。 Cursor_sha...
Oraclecursor_sharing参数详解
rede
04-16 318
原 Oracle cursor_sharing 参数 详解https://blog.csdn.net/tianlesoftware/article/details/6551723版权声明： https://blog.csdn.net/tianlesoftware/article/details/6551723 一. 官网的说明 http://download.oracle.com/docs/c...
oracle cursor_sharing 参数设置测试
木子的博客
08-06 2503
题目：Oracle的cursor_sharing参数告诉Oracle数据库何时重用SQL语句，有Exact、force和similar三种设置，请阐述三种设置的区别，并通过一个例子距离说明三者之间的区别。最后请设计一个实际应用场景，执行一系列操作，说明这个参数设置的意义（提示，可以设计一个循环操作语句来验证），分别说明在什么情况下用何种参数设置。     答： Cursor_sharing
CONSOLE_CURSOR_INFO类型详解
qq_42591783的博客
12-02 5379
光标信息的结构体类型 typedef struct _CONSOLE_CURSOR_INFO { DWORD dwSize; BOOL bVisible; } CONSOLE_CURSOR_INFO, *PCONSOLE_CURSOR_INFO; 成员类型 其中typedef unsigned long DWORD; typedef int BOOL; 成员代表的含义...
系统学习Pytorch笔记三：Pytorch数据读取机制(DataLoader)与图像预处理模块(transforms)
热门推荐
Miracle8070
04-17 6万+
Pytorch官方英文文档：https://pytorch.org/docs/stable/torch.html? Pytorch中文文档：https://pytorch-cn.readthedocs.io/zh/latest/ 1. 写在前面 疫情在家的这段时间，想系统的学习一遍Pytorch基础知识，因为我发现虽然直接Pytorch实战上手比较快，但是关于一些内部的原理知识其实并不是太懂，这样...
C语言——函数的综合运用。自定义函数，gotoxy清屏函数与HideCursor隐藏光标，防闪屏，共同制作打飞机游戏。
冰小九的博客
09-06 5787
①在变量中，我们必须进行定义赋值初始化后，才能在程序中使用，所以需要一个“地方”，整理好这些变量，在程序中整洁一些void startup() //数据初始化②打出画面中所显示的所需代码，也给一个函数void show() //显示画面③在游戏运行中，飞机的移动，发射等操作必须使用键盘，此时就要进行输入判断，给出相应的函数，实行不同的命令 ，其中分为两种，一种是程序运行时对输入做出变化，另一种是不变化void updateWithoutInput() //与用户输入无关的更新。
光标设置
hanhaipingsea123的博客
01-07 4549
[cpp] view plain copy #include "MyConsole.h"   #include       int main()     {         CMyConsole myConsole;            system("pause");    // 暂停（默认光标样式）          myConsole.Hid
数据同步是如何实现的？为什么需要数据同步？
最新发布
oOBubbleX的博客
09-05 745
实时数据同步，确保数据的一致性和实时更新性，将有利于企业各部门之间高效协作，从而支持企业的业务运作和决策。本文将介绍数据同步概念、数据同步的步骤、数据同步的方式，从而加深对数据同步的认识。
Redis基础命令和事务，redis持久化和主从复制
baomingshu的博客
08-31 1871
nosql数据库介绍：解释为 non-relational(非关系型数据库)。在NoSQL 数据库中数据之间是无联系的数据的结构是松散的，可变的。优势：大数据量，高性能，灵活的数据模型，高可用，低成本劣势：（1）无关系，数据之间是无联系的。
您应该使用哪个矢量数据库？ 选择最适合您需求的数据库
weixin_41446370的博客
09-01 1730
在现实世界中，并非所有数据都能整齐地排列成行和列。在处理复杂的非结构化数据（如图像、视频和自然语言）时尤其如此。矢量数据库是一种以高维矢量形式存储数据的数据库，本质上是代表对象特征或特性的数字列表。每个矢量对应一个独特的实体，如一段文本、图像或视频。但为什么要使用矢量呢？奥妙就在于它能够捕捉语义和相似性。通过将数据表示为向量，我们可以对它们进行数学比较，并确定它们的相似或不相似程度。这使我们能够执行复杂的查询，如 "为我查找与此相似的图片 "或 “检索与此文本语义相关的文档”。
编译原理简介
m0_52796585的博客
09-01 1094
编译原理是计算机科学中的一个核心领域，它涉及到将高级编程语言编写的源代码转换成机器语言的过程。这个过程对于计算机程序的运行至关重要，因为计算机硬件只能理解机器语言指令。编译原理不仅关注编译器的设计和实现，还涉及语言的语义、语法分析、代码优化等多个方面。
Value* Next() { if (cursor_ == NULL || ((Node*)(cursor_->pValue))->used_flag_ == 0) { return NULL; } DOUBLE_NODE* tmp_node = DoubleLinkGetNext(&double_link_, cursor_); if (tmp_node == NULL) { return NULL; } cursor_ = tmp_node; return ((Node*)(tmp_node->pValue))->value_; }什么意思
06-09
如果tmp_node非空，则将其作为当前节点，即将其赋值给了cursor_。最后，该函数返回了tmp_node节点的值的指针，其中tmp_node指向的是双向链表节点中的Node结构体，而Node结构体中的value_成员即为该节点的值。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值