oracle数据集库优化-绑定变量sql语句优化

最新推荐文章于 2021-04-09 21:30:32 发布

执笔画情ora

最新推荐文章于 2021-04-09 21:30:32 发布

阅读量729

点赞数

分类专栏： ORACLE数据库性能优化

本文链接：https://blog.csdn.net/oradbm/article/details/84956939

版权

ORACLE数据库性能优化专栏收录该内容

66 篇文章 5 订阅

订阅专栏

1 对于oracle10g，

可以检查绑定变量的sql是否由于窥视导致sql执行计划异常

1select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced')); --查询当前的执行计划，分析到绑定变量窥视对应的值.

2 采用如下sql语句查询真正执行时赋予的变量

select snap_id,
b.sql_text
sql_id,
dbms_sqltune.extract_bind(a.bind_data, 1).value_string,
dbms_sqltune.extract_bind(a.bind_data, 2).value_string,
dbms_sqltune.extract_bind(a.bind_data, 3).value_string,
dbms_sqltune.extract_bind(a.bind_data, 4).value_string,
dbms_sqltune.extract_bind(a.bind_data, 5).value_string
from dba_hist_sqlstat a,v$sql b where a.sql_id=b.sql_id
AND b.SQL_Id= 'btrtxjn6rms82';

select * From v$sql_shared_cursor where sql_id='ft3ngxyys1bmc';
select * From table(dbms_xplan.display_awr('ft3ngxyys1bmc'));
SELECT VERSION_COUNT,T.LOADED_VERSIONS fROM V$SQLAREA T WHERE SQL_ID='ft3ngxyys1bmc'

分析执行计划即可。

案例分析1：在当前的业务系统中，存在sql语句，谓词采用 <=的方式进行筛选数据，由于第一次采用索引扫描，而测试使用max()最大值时仍然采用索引扫描 id<10- 索引，id<1000000仍然索引。(绑定变量窥视的锅）

11g使用如下查询查询产生自适应的sql

对于11g以上环境，则可以根据查询

select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

来确定产生了自适应的游标详细。

缺点：1 可能导致一定数量的额外的硬解析。

2 可能导致一定数量的额外的child_cursor挂在同意个parent_cursor下，增加软解析和软软解析的工作量。
————————————————

1 10G出现绑定变量窥视。

对于oracle10g版本,如果cursor_sharing为force则产生系统变量类似:sys_B0,

则不管是安全变量还是不安全变量，则无条件重用之前硬解析时产生的解析树木和执行计划。---应用从10.2版本升级到11.2版本消失。

(SELECT * FROM T WHERE A BETWEEN 1 AND 2;)

(SELECT * fROM T WHERE A BETWEEN :SYS_B0 AND :SYS_B1;)

数据库系统版本11.2.0.4，cursor_sharing为force，上述sql会转化为绑定变量的语句，11.2版本又出现绑定变量的自适应属性

。对于存在绑定变量的列（存在偏斜会自适应执行计划）

安全变量：主键等值，不安全范围变量和直方图。

为exact则不会。

一个SQL 第一次执行时，会进行硬解析，同时创建parent cursor 和child cursor。

当再次执行这个SQL时，那么首先会对SQL 语句进行特殊的hash 运算，对应生成一个hash value。Hash value存放在parent cursor中，然后会用这个hash value到paranet cursor的bucket中匹配，如果相同的hash value 已存在parent cursor里，则继续遍历这个child cursor，如果可重用，那么就沿用child cursor的信息，如果不能重用，就会重新生成一个新的child cursor。

一个parent cursor下child cursor 的总数，就是这个SQL的version count。

事实上，我们很难去准确定义一个high version count的值，只能根据不同的系统来判断是否为high verison count。在AWR报告中，默认verion count超过20的SQL就会显示在order by version count一栏中。根据经验version count如果超过100，可能就需要引起注意了。

我们可以通过查看v$sqlarea视图的loaded_versions来判断当前这个SQL的version count是多少，然后再通过address的值来查询v$sql_shared_cursor视图看那些字段的返回值为Y，Y代表mismatch。Mismatch是引起产生version count的直接原因。

通常我们可以综合：v$sqlarea, v$sql_shared_cursor, v$sql_bind_metadata, v$sql_bind_captures来诊断这类问题，但是手工去查这些表往往过于繁琐， Abel Macias 开发了一个小工具叫做version_rpt，这个工具可以用来诊断导致是那个模块出现mismatch，从而导致了high version count。我们可以到High SQL Version Counts – Script to determine reason(s) (DOC ID 438755.1)上下载这个小工具

对19c版本进行的一次测试：

var x number
var y number
exec :x:=1
exec :y:=10
select object_name,object_id,NAMESPACE from t1 where object_Id between :x and :y;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1pm8c037m91f9, child number 0
-------------------------------------
select object_name,object_id,NAMESPACE from t1 where object_Id between
:x and :y

Plan hash value: 2283968702

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
)| Time |

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

| 0 | SELECT STATEMENT | | | | 3 (100
)| |

|* 1 | FILTER | | | |
| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 9 | 396 | 3 (0
)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX1 | 9 | | 2 (0
)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T1@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------

1 - :X (NUMBER): 1
2 - :Y (NUMBER): 10

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(:Y>=:X)
3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "NAMESPACE"[NUMBER,2
2]

2 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "NAMESPACE"[NUMBER,2
2]

3 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

57 rows selected.

SQL>

exec :x:=1
exec :y:=1000
select object_name,object_id,NAMESPACE from t1 where object_Id between :x and :y;

exec :x:=1
exec :y:=1000000
select object_name,object_id,NAMESPACE from t1 where object_Id between :x and :y;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 1pm8c037m91f9, child number 1
-------------------------------------
select object_name,object_id,NAMESPACE from t1 where object_Id between
:x and :y

Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 387 (100)| |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 72769 | 3126K| 387 (1)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
*/

Peeked Binds (identified by position): ---如果是10g没有自适应游标，则仍然显示x为1 y为100
--------------------------------------

1 - :X (NUMBER): 1
2 - :Y (NUMBER): 1000000

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
1 - filter(:Y>=:X)
2 - filter(("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],
"NAMESPACE"[NUMBER,22]
2 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],
"NAMESPACE"[NUMBER,22]

55 rows selected.

SQL>

set lines 120
col sql_text for a50
col sql_id for a20
select sql_text,sql_id,version_count,LOADED_VERSIONS,executions from v$sqlarea where sql_text like '%where object_Id between :x and :y%'; ---version_COUNT+1代表执行一次硬解析。且存在2个子游标。如果已经被刷新出shared_POOL则查询不到。

SQL> select distinct PLAN_HASH_VALUE from v$sql where sql_id='1pm8c037m91f9';

PLAN_HASH_VALUE
---------------
3332582666
2283968702

SQL>

2 11G出现11g新特性之自适应游标共享（Adaptive Cursor Sharing）

窥视+自适应

缺点：1 可能导致一定数量的额外的硬解析。

2 可能导致一定数量的额外的child_cursor挂在同意个parent_cursor下，增加软解析和软软解析的工作量。

3 sql第一次执行效率很高谓词列(高度不集中）走索引，第2次查询可能缓慢(应该全表仍然走索引），第3次就重新进行解析，（游标v$sql.runtime buffer_gets相差较大，游标变为is_bind_AWARE） select count(1) from v$sql_cs_selectivity where sql_Id='1pm8c037m91f9';

2.1 自适应游标过程总结。

1 一个游标建立之后会存在3个变量值，runtime，buffer_gets,is_bind_sensitive ,is_shareable , is_bind_aware

2 第2次执行，直接继承第一次执行计划，并比较runtime，差异较大则is_bind_aware 调整为Y，第一个游标is_shareable为N，并在v$sql_cs_selectivity出现一条范围值。范围之内共享，范围之外硬解析。

SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
0 1786 Y N N
1 1437 Y Y Y
2 4 Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
2 <X 0 0.001194 0.001460
1 <X 0 0.900000 1.100000 ###########没有child为0的范围值。

SELECT ROUND((谓词条数/总数)*0.9，6） low,ROUND((谓词条数/总数)*0.9，6） HIGH FROM DUAL;

alter system flush shared_pool;

var x number
exec :x:=100

select object_name,count(1) from t1 where object_Id<:x group by object_name;
select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

exec :x:=100000000000000 --出现第2次就硬解析了，不用连续。

执行计划均为:
SQL> select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8g00231cdyxus, child number 0
-------------------------------------
select object_name,count(1) from t1 where object_Id<:x group by
object_name

Plan hash value: 1125902940

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 97 | 3880 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 97 | 3880 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX1 | 97 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T1@SEL$1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"<:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22]
2 - "OBJECT_NAME"[VARCHAR2,128]
3 - "T1".ROWID[ROWID,10]

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

56 rows selected.

exec :x:=100000000000000
select object_name,count(1) from t1 where object_Id<:x group by object_name;
select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

SQL> select * From table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8g00231cdyxus, child number 1
-------------------------------------
select object_name,count(1) from t1 where object_Id<:x group by
object_name

Plan hash value: 136660032

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 60608 | 2367K| 3440K| 1052 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 72769 | 2842K| | 387 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000000000000

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
2 - filter("OBJECT_ID"<:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22]
2 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128]

52 rows selected.

SQL>

SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
0 1786 Y N N
1 1437 Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
1 <X 0 0.900000 1.100000

----这次还是发生了硬解析。
var x number
exec :x:=100
select object_name,count(1) from t1 where object_Id<:x group by object_name; 在执行一次
SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
0 1786 Y N N
1 1437 Y Y Y
2 4 Y Y Y
SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
2 <X 0 0.001194 0.001460
1 <X 0 0.900000 1.100000

在执行一个

var x number
exec :x:=300

select object_name,count(1) from t1 where object_Id<:x group by object_name;

SQL> select child_number,buffer_gets,is_bind_sensitive bs,is_shareable sh, is_bind_aware ba from v$sql where sql_id='8g00231cdyxus';

CHILD_NUMBER BUFFER_GETS B S B
------------ ----------- - - -
0 3546 Y N N
1 4 Y N Y
2 1437 Y Y Y
3 21 Y Y Y

SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_Id='8g00231cdyxus';

CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
3 <X 0 0.001194 0.004438
2 <X 0 0.900000 1.100000
1 <X 0 0.001194 0.001460

SQL>

执笔画情ora

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle数据集库优化-绑定变量sql语句优化

1对于oracle10g，可以检查绑定变量的sql是否由于窥视导致sql执行计划异常1select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced')); --查询当前的执行计划，分析到绑定变量窥视对应的值.2采用如下sql语句查询真正执行时赋予的变量select snap_id,b.sql_te...
复制链接

扫一扫