本篇内容介绍了“SQL ACS知识点有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
ACS初探
ACS默认是启用的，当然你可以通过一些隐含参数来打开或关闭ACS,ACS的出现也引入了一些新视图和新列。
l 两个新列在V$SQL视图里：IS_BIND_SENSITIVE 和 IS_BIND_AWARE， IS_BIND_SENSITIVE的值可以为Y和N，为Y的cursor，代表这个cursor被ACS监控了，为N的cursor代表没有被监控或者没有启用ACS功能。而IS_BIND_AWARE为Y的，代表这个cursor每次解析时都要窥探绑定变量的值，计算谓词的选择率然后根据选择率查看是否当前共享池中是否有满足要求的执行计划，如果有则重用，如果没有，就要硬解析重新生成一个。
l V$SQL_CS_HISTOGRAM视图，此视图是ACS的关键视图，主要记录SQL处理的行数的直方图，处理的行数驱动着ACS发挥作用。每个子cursor在这个视图里有3个bucket可用，编号从0-2，字段bucket_id表示桶号，bucket_id 从0-2在每次SQL执行结束后，根据SQL处理行的数量，V$SQL_CS_HISTOGRAM中对应记录的count会发生变化，count代表执行的次数，每一个bucket代表着cursor操作的数据量范围，在11GR2版本，当返回的行数在0-1000时候，SQL执行完成后会更新在bucket_id 为0的桶的count字段，每执行一次， count字段的值会加1，当返回的行数在1000-1000000，SQL执行完成后会更新在bucket_id为1的桶的count字段，返回的行数超过1000000，更新在bucket_id为2的桶的count字段。请读者不要死记数字，以后的版本可能会不同。如果SQL处理的行数发生巨变，也就是说处理的行数散落在了至少2个桶内，下一次解析时，就要窥探绑定变量的值，重新硬解析生成执行计划。
l V$SQL_CS_SELECTIVITY，记录游标谓词的选择率范围，只有被标注为bind aware的cursor才会在此视图中记录。一旦一个cursor被标注为bind aware，每次解析时都要窥探绑定变量值，计算谓词选择率，然后根据计算的结果与此视图中的相关记录做比对，如果计算的选择率落在了此视图中对应游标的选择率范围内，则软解析，复用此游标，如果不在，则硬解析，重新生成一个子游标。文章有一节会专门针对此问题进行探讨。
l V$SQL_CS_STATISTICS 记录游标处理的行数、buffer gets等信息，但是此视图具有一定的误导性，视图里的数据只有在硬解析有新游标产生的时候才会发生变化，软解析的时候，这些指标值不会发生变化。此视图一旦有新的行产生代表着SQL又重新产生了一个新的cursor，新产生的cursor是ACS起作用的结果。
理论的东西太枯燥，我们先来看一个测试示例。再回头来看这些理论就会很容易理解。
|
test@DLSP>CREATE TABLE test 2 AS 3 SELECT ROWNUM id, 4 DBMS_RANDOM.STRING('A', 12) name, 5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status 6 FROM all_objects a,dba_objects b 7 WHERE ROWNUM <= 50000;
Table created. SQL>CREATE INDEX test_id_ind ON test(status);
Index created.
SQL>begin 2 dbms_stats.gather_table_stats(user, 3 'test', 4 method_opt => 'for columns status size 2', 5 cascade => true); 6 end; 7 /
PL/SQL procedure successfully completed.
SQL>SELECT COUNT (*) cnt, status 2 FROM test 3 GROUP BY status 4 / CNT STATUS ———- —————- 49900 Active 100 Inactive |
上面的代码创建了一张测试表test，表上有一列status数据有倾斜，此列上创建了索引，收集表的统计信息，并收集列status的直方图。表中大部分的数据status为Active，极少的数据status为Inactive。我们可以开始我们的测试了，通过测试在针对列status不同值做查询的情况下，几个相关视图中值的变化。
|
SQL>var a varchar2(100) PL/SQL procedure successfully completed. SQL>select /*+ find_me */ count(name) from test where status=:a; COUNT(NAME) ———– 100 1 row selected. SQL>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ——————————————————————————- SQL_ID a9cf9a1ky3bda, child number 0 ————————————- select /*+ find_me */ count(name) from test where status=:a
Plan hash value: 2948918962
——————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————————– | 0 | SELECT STATEMENT| | | | 2 (100)| | | 1 | SORT AGGREGATE| | 1 | 25 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 87| 2175 | 2 (0)| 00:00:01 | |* 3 |INDEX RANGE SCAN |TEST_ID_IND| 87| | 1 (0)| 00:00:01 | ——————————————————————————————–
Predicate Information (identified by operation id): —————————————————
3 – access("STATUS"=:A)
SQL> — 检查ACS状态 SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='a9cf9a1ky3bda';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS ———— ———- ———– — — 0 1 148 Y N
SQL>– 直方图 SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ———- ————— ———— ———- ———- 1709288874 a9cf9a1ky3bda 0 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 0 1 0
SQL>– 统计信息 SQL>SELECT hash_value, sql_id, child_number, executions, 2 rows_processed 3 FROM v$sql_cs_statistics 4 WHERE sql_id='a9cf9a1ky3bda' 5 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED ———- ————— ———— ———- ————– 1709288874 a9cf9a1ky3bda 0 1 201
SQL> SQL>– 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
no rows selected |
从上面的输出我们看到了，v$SQL的IS_BIND_SENSITIVE ='Y'，优化器已经标注此SQL为bind sensitive，优化器参考直方图等统计信息后，使用了索引扫描INDEX RANGE SCAN，由于Inactive值非常少，因此这是一个正确的决定，注意v$sql的输出和其他V$视图的输出，由于此SQL处理的行数是100，因此视图v$sql_cs_histogram中桶号bucket_id为0的行的count字段发生了变化，值从0变为了1，代表执行了1次。这里需要注意，优化器对绑定敏感的SQL需要在每次SQL执行结束后，更新v$sql_cs_histogram视图中对应桶的count字段。
|
SQL>exec :a :='Active' PL/SQL procedure successfully completed. SQL>select /*+ find_me */ count(name) from test where status=:a; COUNT(NAME) ———– 49900 1 row selected. SQL>select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT ——————————————————————————- SQL_ID a9cf9a1ky3bda, child number 0 ————————————- select /*+ find_me */ count(name) from test where status=:a
Plan hash value: 2948918962
——————————————————————————————– | Id | Operation | Name | Rows| Bytes| Cost (%CPU)| Time | ——————————————————————————————– | 0 | SELECT STATEMENT | | 2 (100) | | | 1 | SORT AGGREGATE | |1 | 25 | | | | 2 |TABLE ACCESS BY INDEX ROWID| TEST |87 | 2175 | 2 (0)| 00:00:01| |* 3 |INDEX RANGE SCAN | TEST_ID_IND |87 | | 1 (0)| 00:00:01| ——————————————————————————————–
Predicate Information (identified by operation id): —————————————————
3 – access("STATUS"=:A)
SQL> — 检查ACS状态 SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='a9cf9a1ky3bda';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS ———— ———- ———– — — 0 2 482 Y N
SQL> SQL>– 直方图 SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ———- ————— ———— ———- ———- 1709288874 a9cf9a1ky3bda 0 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 0 1 1
SQL> SQL>– 统计信息 SQL>SELECT hash_value, sql_id, child_number, executions, 2 rows_processed 3 FROM v$sql_cs_statistics 4 WHERE sql_id='a9cf9a1ky3bda' 5 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED ———- ————— ———— ———- ————– 1709288874 a9cf9a1ky3bda 0 1 201
SQL> SQL>– 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
no rows selected
|
我们看到我们查询status为Active时，v$sql中并没有新的游标产生，优化器复用了跟status为Inactive一样的执行计划，这个执行计划是很糟糕的，因为status为Active的有49900个，占了表里的大部分数据，走全表扫描更好。仔细观察输出，我们注意到前后两次v$sql_cs_histogram的输出差异。第一次输出的值，只有bucket_id为0记录的count为1，第二次输出的值，bucket_id为0的记录值保持不变，bucket_id为1的的count已经从0变为了1，说明优化器已经意识到了这个SQL的处理的行数跟第一次已经大大的不同了。就像本章一开头说到的，本次处理的行数已经超过了10000，所以SQL执行结束后更新了bucket_id为1的记录里的count的值。
我们继续拿Active作为查询值看看会出现什么结果：
|
SQL>exec :a :='Active' PL/SQL procedure successfully completed. SQL>select /*+ find_me */ count(name) from test where status=:a; COUNT(NAME) ———– 49900 SQL>select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT ——————————————————————————————– SQL_ID a9cf9a1ky3bda, child number 1 ————————————- select /*+ find_me */ count(name) from test where status=:a
Plan hash value: 1950795681
————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | | | 51 (100) | | | 1 | SORT AGGREGATE | | 1 | 25 | | | |* 2 | TABLE ACCESS FULL | TEST | 49909 | 1218K| 51 (2) | 00:00:01 | —————————————————————————
Predicate Information (identified by operation id): —————————————————
2 – filter("STATUS"=:A) SQL> — 检查ACS状态 SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='a9cf9a1ky3bda';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS ———— ———- ———– — — 0 2 482 Y N 1 1 210 Y Y
SQL> SQL>– 直方图 SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ———- ————— ———— ———- ———- 1709288874 a9cf9a1ky3bda 0 1 1 1709288874 a9cf9a1ky3bda 0 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 1 1 1 1709288874 a9cf9a1ky3bda 1 0 0 1709288874 a9cf9a1ky3bda 1 2 0
6 rows selected.
SQL> SQL>– 统计信息 SQL>SELECT hash_value, sql_id, child_number, executions, 2 rows_processed 3 FROM v$sql_cs_statistics 4 WHERE sql_id='a9cf9a1ky3bda' 5 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED ———- ————— ———— ———- ————– 1709288874 a9cf9a1ky3bda 0 1 201 1709288874 a9cf9a1ky3bda 1 1 49901
SQL> SQL>– 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ———- ————— ———— ———- ———- ———- ———- 1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996 |
优化器终于意识到自己犯了错误，重新硬解析产生了新的执行计划，采用了full table scan。V$SQL已经产生了新游标，IS_BIND_AWARE的值也已经变成了Y，视图v$SQL_CS_HISTOGRAM额外的多了3行，为了记录新游标（child_numer为1）的处理行数直方图信息。由于有了硬解析，视图V$SQL_CS_STATISTICS也新增了一行记录新游标的行处理信息和buffer gets信息。$SQL_CS_SELECTIVITY也有了记录，记录了新游标谓词的选择率范围。截止到目前，这个SQL已经是bind aware的了。bind aware意味着以后每次解析此SQL都要去窥探这个SQL的绑定变量值计算选择率，然后根据计算的结果与v$sql_cs_selectivity视图中的相关记录做比对，如果计算的选择率落在了此视图中对应游标的选择率范围内，则软解析，复用此游标，如果不在，则硬解析，重新生成一个子游标。
|
SQL>var a varchar2(100) PL/SQL procedure successfully completed. SQL>select /*+ find_me */ count(name) from test where status=:a; COUNT(NAME) ———– 100 SQL> — 检查ACS状态 SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='a9cf9a1ky3bda';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS ———— ———- ———– — — 0 2 482 Y N 1 1 210 Y Y 2 1 102 Y Y
SQL>– 直方图 SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ———- ————— ———— ———- ———- 1709288874 a9cf9a1ky3bda 0 1 1 1709288874 a9cf9a1ky3bda 0 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 1 0 0 1709288874 a9cf9a1ky3bda 1 1 1 1709288874 a9cf9a1ky3bda 1 2 0 1709288874 a9cf9a1ky3bda 2 1 0 1709288874 a9cf9a1ky3bda 2 0 1 1709288874 a9cf9a1ky3bda 2 2 0
9 rows selected.
SQL>– 统计信息 SQL>SELECT hash_value, sql_id, child_number, executions, 2 rows_processed 3 FROM v$sql_cs_statistics 4 WHERE sql_id='a9cf9a1ky3bda' 5 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED ———- ————— ———— ———- ————– 1709288874 a9cf9a1ky3bda 0 1 201 1709288874 a9cf9a1ky3bda 1 1 49901 1709288874 a9cf9a1ky3bda 2 1 201
SQL>– 选择率 SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ———- ————— ———— ———- ———- ———- ———- 1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996 1709288874 a9cf9a1ky3bda 2 =A 0 0.001557 0.001903
|
由于这个SQL已经是bind aware的了，当再次执行status='Inactive'时，优化器窥探到传入的值并参考直方图信息等统计信息，计算选择率与现有的游标的选择率不符，因此重新硬解析产生了一个新的游标，这个游标的is_bind_aware字段为'Y'，产生了index range scan的执行计划，非常棒！视图v$sql_cs_histogram，v$sql_cs_statistics，v$sql_cs_selectivity也都有相应的变化。
从上面的实验中，我们可以知道，ACS存在着不稳定期，必须在一个游标的性能变糟后，优化器才能意识到犯了错误，再下次执行时尝试纠正错误。触发这一行为是依靠在v$sql_cs_histogram视图中，此游标的3个桶中出现了两个桶中的count都有非0值，一旦被触发，在每次解析阶段，都要去窥探绑定变量的值计算选择率，如果计算选择率与现有的游标的选择率不符，就会基于窥探到的绑定变量的值硬解析重新产生了一个新的游标，并且此SQL会被标注为bind aware。
“SQL ACS知识点有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/202166.html