本文的作者是OCP的
Victor Varlamov （
varlamovVp18 ）。
原始文章发布于2017年7月7日。
特别感谢翻译的作者
-brutaltag 。
我们的报告系统通常会执行数百个由各种事件触发的冗长的请求。 查询参数是客户端列表和时间间隔（每天，每周，每月）。 由于表中数据的不均匀性，一个查询可以产生一行和一百万行，具体取决于报表参数（不同的客户端在事实表中具有不同的行数）。 每个报告都以具有主要功能的程序包的形式生成，该功能接受输入参数，执行其他转换，然后打开带有
关联变量的静态游标，最后返回此打开的游标。 数据库参数CURSOR_SHARING设置为FORCE。
在这种情况下，无论是在优化程序重新使用查询计划的情况下，还是在使用
字面量形式的参数
完全解析查询的情况下，都必须应对性能低下的问题。 绑定变量可能会导致查询计划不理想。
亚历克斯·戈尔巴乔夫（Alex Gorbachev）在他的《 Oracle专家实践》一书中，讲述了汤姆·基特（Tom Kite）讲的一个有趣的故事。 每个下雨的星期一，用户都必须应对修改后的查询计划。 很难相信，但这是：
“根据最终用户的观察，在星期一下大雨时，数据库性能非常糟糕。 在一周的任何其他日子或星期一，没有雨就没有问题。 通过与DBA的交谈，汤姆·基特（Tom Kite）知道困难一直存在，直到数据库被迫重新启动，然后性能恢复正常。 那是一个解决方法：下雨的星期一-重新启动。”
这是一个真实的例子，并且由于对Oracle工作原理的深入了解，该问题得以完全解决而没有任何魔术。 我将在文章末尾显示解决方案。
这是一个有关相关变量如何工作的小例子。
创建具有不均匀数据的表。
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
换句话说，我们有一个具有一百万行的表VVP_HARD_PARSE_TEST，其中在10,000种情况下，该字段为C2 = 99,8个记录的C2 = 1，其余为C2 = 1,000,000.C2字段的直方图指示Oracle优化器关于此数据分布的信息。 这种情况称为
数据分布不均 ，直方图可以帮助您根据请求的数据选择正确的查询计划。
我们观察到对该表的简单查询。 显然是为了要求
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :p如果p = 1，则INDEX RANGE SCAN是最佳选择，对于p = 1000000的情况，最好使用FULL TABLE SCAN。 Query1和Query1000000查询是相同的，只是注释中的文本不同，这样做是为了获得不同的查询计划标识符。
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
现在让我们看一下查询计划：
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
如您所见，在第一次执行时，针对不同请求的计划仅创建一次（每个请求仅存在一个CHILD_NUMBER = 0的子游标）。 每个请求执行两次（EXECUTION = 2）。 在硬解析期间，Oracle检索关联变量的值，并根据这些值选择计划。 但是，即使相关变量在第二次运行中发生了变化，他仍对下一次运行使用相同的计划。 使用非最佳计划-变量C2 = 1的Query1000000使用FULL TABLE SCAN而不是INDEX RANGE SCAN，反之亦然。
显然，修复应用程序并将参数用作查询中的文字是解决问题的最合适方法，但是它导致动态SQL及其已知的缺点。 另一种方法是禁用对相关变量的查询（
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ）或删除直方图（
链接 ）。
一种可能的解决方案是数据访问策略的替代使用，也称为
虚拟专用数据库 （粒度访问控制，细粒度访问控制，行级别控制）。 这使您可以即时更改请求，因此每次请求使用详细的访问控制时，都可以对请求计划进行完整的分析。
Randalph Geist在一篇
文章中详细描述了此技术。 此方法的缺点是完整解析的数量不断增加，并且无法操纵查询计划。
看看我们现在要做什么。 在分析了我们的数据之后，我们决定根据一年中的交易数或交易数将客户分为大，中和小三类（LMS或9-5-1）。 此外，报告中的行数严格取决于以下时间段：月-大，周-中，日-小或9-5-1。 此外，解决方案很简单-我们将使安全策略谓词取决于每个类别和每个期间。 因此，对于每个请求，我们都有9个可能的子游标。 此外，具有不同策略的查询将导致我们获得相同的查询标识符，这使得实现SQL PLAN MANAGEMENT（SQL计划基线）成为可能。
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
现在，如果要在报表中嵌入此类技术，则需要在查询中添加HARD_PARSE_TABLE（这不会破坏它），并在执行主查询之前调用CALC_PREDICATES。
让我们看看这种技术如何改变前面的例子：
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
让我们看一下执行计划：
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
看起来很棒！ 每个查询执行两次，使用不同的子游标和不同的计划。 对于参数C2 = 1,000,000，我们在两个查询中都看到FULL TABLE SCAN，对于参数C1 = 1，我们总是看到INDEX RANGE SCAN。
最后，我对下雨星期一的情况给出了解决方案：
事实证明，每个星期日的周末都有一个冷备份 ，因此所有查询计划都在星期一早上的第一次执行时重新生成。 其中一名员工通常比其他员工早开始工作，并且他的请求计划在一周内对其他用户的执行情况很好。 但是，如果正在下雨，由于早上路线问题，该用户在工作日开始之前要迟到。 然后，首先开始进行报表的批处理计算，但是由于相关变量的值不合适，因此对于其余情况，查询计划完全不利。”
还有一些有用的系统视图：
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind