SQL ACS有哪些知识点？

分类：编程技术时间：2024-02-20 16:04 浏览：0 评论：0

本文介绍《SQL ACS有哪些知识点》的相关知识。在实际案例操作过程中，很多人都会遇到这样的困境。接下来就让小编带领大家学习一下如何处理这些情况。！我希望你能仔细阅读并学到一些东西！

ACS初步研究

ACS默认启用。当然，您可以通过一些隐式参数来打开或关闭 ACS。 ACS的出现也引入了一些新的观点和新的栏目。

l V$SQL 视图中有两个新列：IS_BIND_SENSITIVE 和 IS_BIND_AWARE。 IS_BIND_SENSITIVE的值可以是Y和N。Y的游标表示该游标被ACS监控，N的游标表示该游标被ACS监控。没有被监控或者ACS功能没有开启。 IS_BIND_AWARE为Y，表示游标每次解析时都要窥探绑定变量的值，计算predi的选择率cate，然后根据选择率检查当前共享池中是否有满足要求的执行计划，如果有则重用。如果没有，您必须硬解析并重新生成一个。

l V$SQL_CS_HISTOGRAM视图，该视图是ACS的关键视图。主要记录SQL处理的行数柱状图。处理的行数驱动 ACS 运行。每个子光标在此视图中都有 3 个可用的存储桶，编号从 0-2。字段bucket_id表示桶号。 Bucket_id的范围是0-2。每次SQL执行后，根据SQL处理的行数，对应的值在V$SQL_CS_HISTOGRAM中。记录的计数将会改变。计数代表执行次数。每个桶代表游标操作的数据量范围。在11GR2版本中，当返回的行数在0到1000之间时，SQL执行完成后bucket_id会更新为0。对于计数字段该bucket，每次执行时，count字段的值都会加1。当返回的行数在1000-1000000之间时，bucket_id为1的bucket的count字段会在SQL执行后更新完全的。返回的行数超过1000000。，将bucket的count字段更新为bucket_id 2。建议读者不要记住这些数字，因为以后的版本可能会有所不同。如果SQL处理的行数发生剧烈变化，也就是说处理的行数分散在至少2个桶中，下次解析时，必须监听绑定变量的值，执行计划必须通过硬解析重新生成。

l V$SQL_CS_SELECTIVITY，记录游标谓词的选择性范围，只有那些标记为bind aware的游标才会记录在这个视图中。一旦游标被标记为绑定感知，每次解析它时，都必须监听绑定变量值，计算谓词选择率，然后根据计算结果与该视图中的相关记录进行比较。如果计算出的选择率落在该视图中对应光标的选择率范围内，则进行软解析并重新使用该光标。如果不是，则执行硬解析并重新生成子光标。文章中有一节专门讨论这个问题。

l V$SQL_CS_STATISTICS记录了游标处理的行数、缓冲区获取等信息，但这个视图有些误导。仅当硬解析期间生成新游标时，视图中的数据才会发生变化。，这些指标值在软分析时不会改变。一旦该视图中生成了新行，就意味着 SQL 重新生成了新的游标。新生成的游标是ACS的结果。

理论的东西太枯燥了，我们先看一个测试例子。如果你会很容易理解回顾一下这些理论。

test@DLSP>创建表测试

2 AS

3 选择 ROWNUM ID，

4 DBMS_RANDOM.STRING('A', 12) 名称，

5 DECODE(MOD(ROWNUM, 500), 0 、“非活动”、“活动”）状态

6 FROM all_objects a,dba_objects b

7 WHERE ROWNUM <= 50000;

已创建表。

SQL>CREATE INDEX test_id_ind ON test(status);

已创建索引。

SQL>开始

2 dbms_stats.gather_table_stats(user,

3 3 大小 2',

5 级联 => true );

6 end;

7 /

PL/SQL 过程成功完成。

SQL>SELECT COUNT (*) cnt, status

2 FROM test

3 GROUP BY status

4 /

CNT状态

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

49900活跃

100 Inactive

上面的代码创建了一个测试表test。表上有一列状态数据是倾斜的。在此列上创建索引，收集表的统计信息，收集状态列的直方图。表中大部分数据状态为Active，极少数数据状态为Inactive。我们可以通过在查询状态列的不同值时测试几个相关视图中值的变化来开始我们的测试。

SQL>var a varchar2(100)
SQL>exec :a :='Inactive';

PL/SQL 过程成功完成。

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

---------

100

已选择 1 行。

SQL>从表中选择 *(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID a9cf9a1ky3bda，子编号 0

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

选择/*+ find_me */count(name) from test where status=:a

计划哈希值：2948918962

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

|身份证 |运营|名称 |行|字节 |成本 (%CPU) |时间|

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

| 0 |选择语句|排序聚合| > |* 3 |索引范围扫描 |TEST_ID_IND| 87| | 1 (0)| 00:00:01 |

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

谓词信息（通过操作id标识）：

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

3 - access("STATUS"=:A)

SQL> -- 检查 ACS 状态

SQL>SELECT child_number 、执行、buffer_gets、is_bind_sensitive、

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER 个执行缓冲区_GETS IS IS

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

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、执行次数

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 次执行 ROWS_PROCESSED

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

1709288874 a9cf9a1ky3bda 0 1 1 201

SQL>

SQL>--选择率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

SQL> p>

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

未选择任何行

从上面的输出中我们可以看到，v$SQL的IS_BIND_SENSITIVE = 'Y'，优化器已经将此SQL标记为绑定敏感。在参考直方图和其他统计信息后，优化器使用了索引扫描INDEX RANGE SCAN。由于Inactive值很小，这是一个正确的决定，注意v$sql的输出和其他V$视图的输出。由于这条SQL处理的行数为100，因此视图v$sql_cs_histogram中bucket_id为0的行的count字段发生了变化，值从0变成了1，w这意味着它被执行过一次。这里需要注意的是，优化器对绑定SQL比较敏感，每次SQL执行后都需要更新v$sql_cs_histogram视图中对应bucket的count字段。

SQL>exec :a :='Active'

PL/SQL 过程已成功完成。

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

49900

已选择 1 行。

SQL>select *来自表(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

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

SQL_ID a9cf9a1ky3bda，子编号 0

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

select /*+ find_me */ count(name) from test where status=:a

计划哈希值：2948918962

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

|身份证 |运营|名称 |行|字节|成本（%CPU）|时间 |

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

| 0 |选择语句 | | 2 (100) | 2 (100) | |

| 1 |排序聚合|按索引 ROWID 访问表|测试 |87 | 2175 | 2175 2 (0)| 00:00:01|

|* 3 |索引范围扫描|测试 ID_IND |87 | | 1 (0)| 00:00:01|

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

谓词信息（通过操作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 次执行 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 计数

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

1709288874 a9cf9a1ky3bda 0 0 0 1

1709288874 a9cf9a1ky3bda 0 0 2 0

1709288874 a9cf9a1ky3bda 0 1 1 1

SQL>

SQL>--统计

SQL >选择 hash_value、sql_id、child_number、执行次数，

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 次执行 ROWS_PROCESSED

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

1709288874 a9cf9a1ky3bda 201

SQL>

SQL>-- se选择性

SQL>SELECT hash_value、sql_id、child_number、谓词、range_id、低、高

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

未选择任何行

我们看到，当我们查询状态为Active时，v$sql中没有生成新的游标，优化器会重用它。执行计划与Inactive状态的执行计划相同。这个执行计划非常糟糕，因为有 49,900 个状态为 Active 的项目，占了表中的大部分数据。最好扫描整个表。仔细观察输出，我们注意到 v$sql_cs_histogram 前后输出的差异。在第一个输出值中，只有bucket_id为0的记录的计数为1。在第二个输出值中，bucket_id为0的记录值保持不变，而bucket_id为1的记录的计数从0变成了1，表明认为最优的izer我已经意识到这个SQL处理的行数与第一次有很大不同。正如本章开头提到的，本次处理的行数已经超过10000，因此SQL执行完成后，会更新bucket_id为1的记录中的count值。

我们继续使用Active作为查询值，看看会出现什么结果：

SQL>exec :a :='Active'

PL/SQL 过程成功完成。

SQL>select /*+ find_me */ count (name) 来自测试，其中 status=:a;

COUNT(NAME)

---------

49900 
SQL>从表中选择*(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

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

SQL_ID a9cf9a1ky3bda，子编号1

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

选择/*+ find_me*/ count(name) from test where status=:a

计划哈希值：1950795681

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

|身份证 |运营|名称 |行|字节 |成本（%CPU）|时间|

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

| 0 |选择语句 | | | | 51（100）| | 00:01 |

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

谓词信息（通过操作id标识）：

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

2 - 过滤器("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 次执行 BUFFER_GETS 是

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

0 2 482 是否

1 1 1 210 是是

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 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 1 1 0

已选择 6 行。

SQL>

SQL>--统计

SQL>SELECT hash_value、sql_id、child_number、执行次数，

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 次执行 ROWS_PROCESSED

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

1709288874 a9cf9a1ky3bda 201

1709288874 A9CF9A1KY3BDA 1 49901

SQL>

SQL >-选择率

SQL> 选择 Hash_value、SQL_ID、Child_number、Child_number、Child_number、Child_number、 Child_number、Child_number、Child_number、Child_number、Child_number 谓词、range_id、低、高

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 低高

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

1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996

优化器终于意识到自己犯了一个错误，重新硬解析生成新的执行计划，并使用了全表扫描。 v$SQL生成了新的游标，IS_BIND_AWARE的值也变成了Y，视图v$SQL_CS_HISTOGRAM多了3行记录处理的行数的直方图信息新光标（child_numer 为 1）。由于硬解析，视图V$SQL_CS_STATISTICS还添加了一个新行来记录行处理信息和缓冲区获取新游标的信息。 $SQL_CS_SELECTIVITY还有一条记录，记录新游标谓词的选择率范围。到目前为止，该 SQL 已经是绑定感知的。 Bind aware是指以后每次解析这条SQL时，都必须监听这条SQL的绑定变量值来计算选择性，然后将计算结果与v$sql_cs_selectivity视图中的相关记录进行比较。如果计算出的选择性落在该视图中对应光标的选择率范围内，则进行软解析以重用该光标。如果不是，将执行硬解析重新生成子光标。

SQL>var a varchar2(100)
SQL>exec : a :='Inactive'

PL/SQL 过程成功完成。

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 来自 v$sql

4 WHERE sql_id='a9cf9a1ky3bda' ;

CHILD_NUMBER 次执行 BUFFER_GETS IS

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

0 2 482 Y N

1 1 210 y y

2 102 y y

SQL>- 直方图

SQL> 选择 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 sp;0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0 0

1709288874 a9cf9a1ky3bda 1 1 0 0

1709288874 a9cf9a1ky3bda 1 1 1 1

1709288874 a9cf9a1ky3bda 1 1 2 0

1709288874 a9cf9a1ky3bda 2 1 1 0

1709288874 a9cf9a1ky3bda 2 0 1

1709288874 A9CF9A1KY3BDA 2 2 0

选择 9 行。

SQL >--统计

SQL>SELECT hash_value、sql_id、child_number、执行次数，

2 rows_processed

3 来自 v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER执行 ROWS_PROCESSED

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

1709288874 a9cf9a1ky3bda 0 1 1 201

1709288874 a9cf9a1ky3bda 1 1 49901

1709288874 a9cf9a1ky3bda 201

SQL>--选择率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 来自 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 低高

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

1709288874 a9cf9a1ky3bda 1 =A 0 0.898361 1.097996

1709288874 a9cf9a1ky3bda 2 =A 0 0.001557 0.001903

由于此 SQL 已经是绑定感知的，因此当再次执行 status='Inactive' 时，优化器会监听传入值并引用统计数据直方图信息等信息。计算出的选择率与现有的光标选择率不匹配。因此，通过重新硬解析生成新的游标。这个游标的is_bind_aware字段是'Y'，它生成索引范围扫描的执行计划，这太棒了！视图 v$sql_cs_histogram、v$sql_cs_statistics 和 v$sql_cs_selectivity 也有相应的更改。

从上述实验可知，ACS存在一个不稳定期。仅在之后当游标的性能变得更差时，优化器能否意识到已经犯了错误并尝试在下一次执行中纠正它。错误。触发此行为依赖于 v$sql_cs_histogram 视图。该游标的三个桶中，有两个桶的计数为非零值。一旦触发，在每个解析阶段，都必须监听绑定。变量的值计算选择率。如果计算出的选择率与现有游标的选择率不匹配，则会根据绑定变量的硬解析值重新生成新游标，并且 SQL 将被标记为绑定。意识到的。

《SQL ACS有哪些知识点》已经介绍到这里了。感谢您的阅读。如果您想了解更多行业资讯，可以关注网站，小编将为大家输出更多优质实用文章！