Oracle 19C 绑定敏感游标

原创 Asher.HU 2021-02-04

571

绑定敏感的游标是其最佳计划可以取决于绑定变量的值。

数据库在计算基数时已经检查了绑定值，并认为查询“敏感”以便根据不同的绑定值来计划更改。数据库监视绑定敏感游标的行为，该游标使用不同的绑定值来确定不同的计划是否有益。

优化器使用以下条件来确定游标是否对绑定敏感：

优化器偷看了绑定值以生成基数估计。
绑定用于等于或范围谓词。

对于具有新绑定值的查询每次执行，数据库都会记录新值的执行统计信息，并将其与前一个值的执行统计信息进行比较。如果执行统计信息相差很大，则数据库会将游标绑定感知。

示例20-12具有明显数据偏斜的列

本示例假定该hr.employees.department_id列具有明显的数据偏斜。SYSTEM执行以下设置代码，该employees示例代码将部门50的100,000名员工添加到示例架构中的表中，总计100,107行，然后收集表统计信息：

DELETE FROM hr.employees WHERE employee_id > 999;

ALTER TABLE hr.employees DISABLE NOVALIDATE CONSTRAINT emp_email_uk;

DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
 FOR i IN 1..100000 LOOP    
 INSERT INTO hr.employees 
   VALUES (v_counter,null,'Doe','Doe@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
 v_counter := v_counter + 1;
 END LOOP;
END;
/
COMMIT; 

EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');

ALTER SYSTEM FLUSH SHARED_POOL;

以下查询在employees.department_id列上显示直方图：

COL TABLE_NAME FORMAT a15
COL COLUMN_NAME FORMAT a20
COL HISTOGRAM FORMAT a9

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM   DBA_TAB_COLS
WHERE  OWNER = 'HR'
AND    TABLE_NAME = 'EMPLOYEES'
AND    COLUMN_NAME = 'DEPARTMENT_ID';

TABLE_NAME      COLUMN_NAME          HISTOGRAM
--------------- -------------------- ---------
EMPLOYEES       DEPARTMENT_ID        FREQUENCY

示例20-13低基数查询

本示例继续示例20-12中的示例。以下查询显示10该列的基数非常低department_id，占行的.00099％：

VARIABLE dept_id NUMBER
EXEC :dept_id := 10;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
         1              200

优化程序会选择索引范围扫描，这对于这种低基数查询是预期的：

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 0
-------------------------------------
select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

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

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

以下查询V$SQL获得有关游标的信息：

COL BIND_AWARE FORMAT a10
COL SQL_TEXT FORMAT a22
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL BUFF_GETS FORMAT 999999999
COL BIND_SENS FORMAT a9
COL SHARABLE FORMAT a9

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
---------------------- ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*), MAX(e      0     1        196         Y          N        Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

前面的输出显示了一个针对低基数查询执行一次的子游标。游标已被标记为绑定敏感的，因为优化器认为最佳计划可能取决于绑定变量的值。

当游标被标记为对绑定敏感时，Oracle数据库将使用不同的绑定值监视游标的行为，以确定针对不同绑定值的不同计划是否更为有效。数据库将此游标标记为对绑定敏感，因为优化程序使用department_id列上的直方图来计算谓词的选择性WHERE department_id = :dept_id。因为直方图的存在表明该列是倾斜的，所以绑定变量的不同值可能需要不同的计划。

示例20-14高基数查询

本示例继续示例20-13中的示例。以下代码使用value 50（占行的99.9％）重新执行相同的查询：

EXEC :dept_id := 50;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
    100045	     100999

即使使用全表扫描这种非选择查询会更有效，优化器也会选择用于的相同索引范围扫描department_id=10。这是因为数据库假定游标中的现有计划可以共享：

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	a9upgaqqj7bn5, child number 0
-------------------------------------
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

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

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

的查询V$SQL显示，子游标现在已执行两次：

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, 
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, 
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
---------------------- ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N        Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

在此阶段，优化器尚未将游标标记为可识别绑定。

也可以看看：

Oracle数据库参考以了解V$SQL

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 绑定敏感游标

评论