Oracle面对“数据倾斜列使用绑定变量”场景的解决方案（下）

2020年1月6日

赵靖宇

339

3.3 场景测试深入分析

You can use the V$ views for adaptive cursor sharing to see selectivity ranges, cursor information (such as whether a cursor is bind-aware or bind-sensitive), and execution statistics:
V$SQL shows whether a cursor is bind-sensitive or bind-aware
V$SQL_CS_HISTOGRAM shows the distribution of the execution count across a three-bucket execution history histogram
V$SQL_CS_SELECTIVITY shows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing
V$SQL_CS_STATISTICS summarizes the information that the optimizer uses to determine whether to mark a cursor bind-aware.

通过v$sql查看SQL（SQL_ID = '7mz2mhz0nq92n'）的child_number, executions, buffer_gets, bind-sensitive, bind-aware, is_shareable信息：

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BS",
2 IS_BIND_AWARE AS "BA", IS_SHAREABLE AS "SH", PLAN_HASH_VALUE
3 FROM V$SQL
4 WHERE SQL_ID = '7mz2mhz0nq92n';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE
------------ ---------- ----------- -- -- -- ---------------
0 2 348 Y N N 3167530345
1 1 502 Y Y Y 2333720604

--再次分别执行绑定变量值为3和2的SQL：
SQL> select count(*) from t_skew where object_id = :v1;

COUNT(*)
----------
86412
SQL> exec :v1 := 2;
SQL> select count(*) from t_skew where object_id = :v1;

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

--再次查询v$sql
CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE
------------ ---------- ----------- -- -- -- ---------------
0 2 348 Y N N 3167530345
1 2 1004 Y Y Y 2333720604
2 1 2 Y Y Y 3167530345

可以看到目前该SQL的parent cursor下挂了3个child_number(0和1和2，其中1和2的SH值为Y，意思为可共享；0的SH值为N，意思为不可共享)。

通过v$sql_cs_*查询acs的相关信息：

--V$SQL_CS_HISTOGRAM
SQL> select * from V$SQL_CS_HISTOGRAM where sql_id = '7mz2mhz0nq92n';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- -------------------------- ------------ ---------- ----------
0000000087F34700 3242927188 7mz2mhz0nq92n 2 0 1
0000000087F34700 3242927188 7mz2mhz0nq92n 2 1 0
0000000087F34700 3242927188 7mz2mhz0nq92n 2 2 0
0000000087F34700 3242927188 7mz2mhz0nq92n 1 0 0
0000000087F34700 3242927188 7mz2mhz0nq92n 1 1 2
0000000087F34700 3242927188 7mz2mhz0nq92n 1 2 0
0000000087F34700 3242927188 7mz2mhz0nq92n 0 0 1
0000000087F34700 3242927188 7mz2mhz0nq92n 0 1 1
0000000087F34700 3242927188 7mz2mhz0nq92n 0 2 0

--V$SQL_CS_SELECTIVITY
SQL> col PREDICATE for a30
SQL> select * from V$SQL_CS_SELECTIVITY where sql_id = '7mz2mhz0nq92n';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- -------------------------- ------------ ------------------------------ ---------- -------------------- --------------------
0000000087F34700 3242927188 7mz2mhz0nq92n 2 =V1 0 0.000167 0.000204
0000000087F34700 3242927188 7mz2mhz0nq92n 1 =V1 0 0.899749 1.099694
SQL>

--V$SQL_CS_STATISTICS
SQL> select * from V$SQL_CS_STATISTICS where sql_id = '7mz2mhz0nq92n';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- -------------------------- ------------ ------------------- -- ---------- -------------- ----------- ----------
0000000087F34700 3242927188 7mz2mhz0nq92n 2 2064090006 Y 1 4 2 0
0000000087F34700 3242927188 7mz2mhz0nq92n 1 2706503459 Y 1 172826 502 0
0000000087F34700 3242927188 7mz2mhz0nq92n 0 2064090006 Y 1 4 49 0
SQL>

4.总结

4.1 清理某条SQL的执行计划

--查询SQL的ADDRESS和HASH_VALUE
SQL> select sql_id, ADDRESS, HASH_VALUE from v$sqlarea where sql_id = '7mz2mhz0nq92n';

SQL_ID ADDRESS HASH_VALUE
-------------------------- ---------------- ----------
7mz2mhz0nq92n 0000000087F34700 3242927188

--清理SQL的执行计划
SQL> exec sys.DBMS_SHARED_POOL.PURGE('0000000087F34700,3242927188','C');

4.2 bind peeking和acs特性的关闭

--均为动态参数
--bind peeking（绑定变量窥探）
alter system set "_optim_peek_user_binds"=false;

--acs(adaptive cursor sharing)
alter system set "_optimizer_extended_cursor_sharing_rel"=NONE;
alter system set "_optimizer_extended_cursor_sharing"=NONE;
alter system set "_optimizer_adaptive_cursor_sharing"=false;

特别注意：如果bind peeking是关闭的，实际上acs也就不会起作用，比如我这里只将_optim_peek_user_binds参数设置为false，再次按照3.2步骤重复同样实验，查询结果如下，不会用到acs特性，即使我没有显示禁用掉acs对应的参数：

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BS",
2 IS_BIND_AWARE AS "BA", IS_SHAREABLE AS "SH", PLAN_HASH_VALUE
3 FROM V$SQL
4 WHERE SQL_ID = '7mz2mhz0nq92n';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA SH PLAN_HASH_VALUE
------------ ---------- ----------- -- -- -- ---------------
0 3 1506 N N Y 2333720604

--可以看到这3次执行执行计划都是一样的，因为受到OPT_PARAM('_optim_peek_user_binds' 'false')影响，采用了INDEX FAST FULL SCAN的执行计划，Plan hash value: 2333720604：

SQL> select * from table(dbms_xplan.display_cursor('7mz2mhz0nq92n',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7mz2mhz0nq92n, child number 0
-------------------------------------
select count(*) from t_skew where object_id = :v1

Plan hash value: 2333720604

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 82 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T_SKEW | 43207 | 126K| 82 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

1 - SEL$1
2 - SEL$1 / T_SKEW@SEL$1

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T_SKEW"@"SEL$1" ("T_SKEW"."OBJECT_ID"))
END_OUTLINE_DATA
*/

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

2 - filter("OBJECT_ID"=:V1)

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

1 - (#keys=0) COUNT(*)[22]

所以在确认acs特性是否开启时，同时也要查询bind peek的设置情况。

原文：https://www.cnblogs.com/jyzhao/p/11415820.html