A problem with Bind Variable Peeking in DSS systems turns out to be a confederate

written by Victor Varlamov, OCP at 07-JUL-2017

There is a Reporting system, where a lot (more than 100) of complex long running queries are launched by Reporting Engine, triggered by different business events. Queries are executed with quite different input parameters (list of customers) and data ranges (daily, weekly, monthly). Because of skewed data in tables, one report can return anywhere from 1 row to 1 million rows depending on the input parameters of the report (different clients have different quantities of rows in fact tables). Every report is implemented as a PL/SQL package with a main function that accepts input parameters, prepares some additional data, and then opens a static cursor with PL/SQL variables as parameters, and finally returns the opened cursor. The CURSOR_SHARING parameter is set to FORCE in the DB server. In such scenarios, SQL can lead to poor performance if the optimizer re-uses unsuitable execution plans that otherwise would not be used if a hard parse was forced using literals. Bind variable peeking can cause a suboptimal plan.

In the book Expert Oracle Practices, Alex Gorbachev relates a story — told to him by Tom Kyte — in which the query plan would change on Monday mornings if it was raining. It’s a true story.

“According to the end-users’ observations, if it was raining heavily Monday morning, then database performance was terrible. Any other day of the week, or on Mondays without rain, there were no problems. Talking to the DBA responsible for the system, Tom found that the performance problems continued until the DBA restarted the database, at which point performance went back to normal. That was the workaround: Rainy Mondays = Reboots.”

It is a real case, and this case was solved without any mysteries and magic, just good knowledge of how to Oracle DB works. I’ll show the solution at the end of the article.

Here is a simple example of how bind variable peeking works:

Let’s create test table with highly skewed data:

  1. SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300));
  2. TABLE created.
  3.  
  4. SQL> INSERT INTO VVP_HARD_PARSE_TEST
  5. SELECT ROWNUM C1,
  6. CASE
  7. WHEN LEVEL < 9 THEN 1
  8. WHEN MOD(ROWNUM, 100)=99 THEN 99
  9. ELSE 1000000
  10. END C2,
  11. RPAD('A', 300, 'A') C3
  12. FROM DUAL
  13. CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2);
  14. INDEX created.
  15.  
  16. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
  17. TABNAME => 'VVP_HARD_PARSE_TEST',
  18. CASCADE => TRUE,
  19. METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254');
  20. PL/SQL PROCEDURE successfully completed.
  21.  
  22. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST'
  23. AND column_name = 'C2';
  24. HISTOGRAM
  25. ---------
  26. FREQUENCY
  27. SQL> SELECT c2, COUNT(*) FROM VVP_HARD_PARSE_TEST GROUP BY c2 ORDER BY 1;
  28. C2 COUNT(*)
  29. -----------------------
  30. 1 8
  31. 99 10000
  32. 1000000 989992

In other words, we have a table VVP_HARD_PARSE_TEST with 1 000 000 rows, where 10 000 rows have C2=99, 8 rows have C2=1 and others have C2=1000000. A histogram on columns C2 exists and tells Oracle about this data distribution. The above is known as a skewed data distribution, and a histogram can help Oracle to choose the right plan, depending on the value.

Let’s observe simple queries on VVP_HARD_PARSE_TEST table. Obviously for the query

SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :p

if :p=1 then INDEX RANGE SCAN is the fastest data access, if :p=1000000 then FULL TABLE SCAN is better. Query1000000 and Query1 are identical, the only difference is in comments to get a different SQL_IDs.

  1. DECLARE p NUMBER; v NUMBER;
  2. BEGIN
  3. V := 0; p := 1000000;
  4. FOR rec IN (SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP
  5. V := v + 1;
  6. END LOOP;
  7. dbms_output.put_line(v);
  8. v : =0; p := 1;
  9. FOR rec IN (SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP
  10. V := v + 1;
  11. END LOOP;
  12. dbms_output.put_line(v);
  13. -----------------
  14. V := 0; p := 1;
  15. FOR rec IN (SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP
  16. V := v + 1;
  17. END LOOP;
  18. dbms_output.put_line(v);
  19. v := 0; p := 1000000;
  20. FOR rec IN (SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP
  21. V := v + 1;
  22. END LOOP;
  23. dbms_output.put_line(v);
  24. END;

Now, let’s look at the query execution plans:

  1. 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%';
  2. SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
  3. SQL_TEXT
  4. -------------------------------------------------
  5. 7rqnhhp6pahw2 0 2 2782757451
  6. SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1
  7. 7xwt28hvw3u9s 0 2 2463783749
  8. SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1
  9.  
  10. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7rqnhhp6pahw2', format => 'basic +peeked_binds'));
  11. SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1
  12. PLAN hash VALUE: 2782757451
  13. -------------------------------------------------
  14. | Id | Operation | Name |
  15. -------------------------------------------------
  16. | 0 | SELECT STATEMENT | |
  17. | 1 | TABLE ACCESS FULL| VVP_HARD_PARSE_TEST |
  18. -------------------------------------------------
  19. Peeked Binds (IDENTIFIED BY position):
  20. --------------------------------------
  21. 1 - :B1 (NUMBER): 1000000
  22.  
  23. SQl> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7xwt28hvw3u9s', format => 'basic +peeked_binds'));
  24. SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1
  25. PLAN hash VALUE: 2463783749
  26. ------------------------------------------------------------------
  27. | Id | Operation | Name |
  28. ------------------------------------------------------------------
  29. | 0 | SELECT STATEMENT | |
  30. | 1 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST |
  31. | 2 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 |
  32. ------------------------------------------------------------------
  33. Peeked Binds (IDENTIFIED BY position):
  34. --------------------------------------
  35. 1 - :B1 (NUMBER): 1

So, as we can see, a plan for each query is built only once, when the query executed the first time (only one child cursor with CHILD_NUMBER=0 exists for each query). Each query executed twice (EXECUTION = 2). During a hard parse Oracle peeked behind the bind variables values and chose plan according to these values. But it used the same plan for the next execution despite the bind variable values have been changed for the second query execution! Suboptimal plans used for the queries during the second execution: Query1000000 with bind variable C2=1 used FULL TABLE SCAN instead of using INDEX RANGE SCAN, and QUERY1 with bind variable C2=1000000 used INDEX RANGE SCAN instead of using FULL TABLE SCAN.

Obviously fixing the application and using literals where appropriate is the correct approach, but it leads to dynamic SQL with well-known disadvantages. Another way is to switch off bind variable peeking (ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE) and/or removing histograms (www.pythian.com/blog/stabilize-oracle-10gs-bind-peeking-behaviour).

One possible solution is to «mis-use» the row level security feature of Oracle (RLS, often also called Virtual Private Database, VPD, also known as FGAC, Fine Grained Access Control) to modify the queries on the fly and therefore forcing a hard parse every time a query uses one of the objects the RLS policy is applied to. This technique is described perfectly in Randolf Geist’s (oracle-randolf.blogspot.com/2009/02/how-to-force-hard-parse.html) and Jonathan Lewis’s (jonathanlewis.wordpress.com) blogs. Side effects of this method are the great increase of hard parses and the inability to manage query plans.

So, what we have done in the following. After analyzing our data we decided to split customers into 3 categories: Large, Middle and Small (L-M-S , or 9-5-1) according to quantity of deals (transactions) during the last year. Also quantity of rows in report strongly depends on data range parameter. (Monthly = Large, Weekly = Middle, Daily = Small or 9-5-1). Going further the decision is simple: make the policy function dependent on the client category and the data range. So for every query we have only 9 possible child cursors. Moreover, the query with different policies leads us to the same SQL_ID which gives us a possibility to implement SQL PLAN MANAGEMENT (sql plan baseline).

  1. SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual;
  2. TABLE created.
  3. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER);
  4. TABLE created.
  5. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year)
  6. VALUES (1, 'SMALL CLIENT', 8);
  7. 1 ROW inserted.
  8. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year)
  9. VALUES (99, 'MIDDLE CLIENT', 50001);
  10. 1 ROW inserted.
  11. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year)
  12. VALUES (1000000,'LARGE CLIENT', 989992);
  13. 1 ROW inserted.
  14.  
  15. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS
  16. gc_small CONSTANT NUMBER := 1;
  17. gc_middle CONSTANT NUMBER := 5;
  18. gc_large CONSTANT NUMBER := 9;
  19. gc_client_middle CONSTANT NUMBER := 50000;
  20. gc_client_large CONSTANT NUMBER := 500000;
  21. gc_daterange_middle CONSTANT NUMBER := 10;
  22. gc_daterange_large CONSTANT NUMBER := 50;
  23. FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2)
  24. RETURN VARCHAR2;
  25. PROCEDURE SET_PREDICATE (n NUMBER);
  26. PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL,
  27. p_clientrange NUMBER DEFAULT NULL);
  28. PROCEDURE CALC_PREDICATE;
  29. PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1,
  30. p_client_seqno NUMBER DEFAULT NULL,
  31. p_client_id VARCHAR2 DEFAULT NULL,
  32. p_client_seqno_list VARCHAR2 DEFAULT NULL
  33. );
  34. END FORCE_HARD_PARSE_PKG;
  35. PACKAGE created.
  36. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS
  37. g_predicate NUMBER; -- g_daterange || 0 || g_clientrange
  38. g_daterange NUMBER; -- 1 - small, 5 - middle, 9 - large
  39. g_clientrange NUMBER; -- 1 - small, 5 - middle, 9 - large
  40. --
  41. FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2)
  42. RETURN VARCHAR2
  43. IS
  44. BEGIN
  45. IF NVL(g_predicate, 0) = 0 THEN
  46. RETURN NULL;
  47. ELSE
  48. RETURN TO_CHAR(g_predicate, 'TM') || ' = ' || TO_CHAR(g_predicate, 'TM');
  49. END IF;
  50. END FORCE_HARD_PARSE;
  51. --
  52. PROCEDURE SET_PREDICATE (n NUMBER)
  53. IS
  54. BEGIN
  55. g_predicate := n;
  56. END;
  57.  
  58. PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL,
  59. p_clientrange NUMBER DEFAULT NULL)
  60. IS
  61. BEGIN
  62. IF p_daterange IS NOT NULL THEN
  63. g_daterange := p_daterange;
  64. CALC_PREDICATE;
  65. END IF;
  66. IF p_clientrange IS NOT NULL THEN
  67. g_clientrange := p_clientrange;
  68. CALC_PREDICATE;
  69. END IF;
  70. END SET_PREDICATES;
  71.  
  72. PROCEDURE CALC_PREDICATE
  73. IS
  74. BEGIN
  75. g_predicate := NVL(g_daterange, 0) * 100 + NVL(g_clientrange, 0);
  76. END CALC_PREDICATE;
  77.  
  78. PROCEDURE CALC_PREDICATES (p_date_interval NUMBER DEFAULT 1,
  79. p_client_seqno NUMBER DEFAULT NULL,
  80. p_client_id VARCHAR2 DEFAULT NULL,
  81. p_client_seqno_list VARCHAR2 DEFAULT NULL)
  82. IS
  83. v_cnt NUMBER;
  84. BEGIN
  85. IF p_date_interval IS NOT NULL THEN
  86. g_daterange := CASE
  87. WHEN p_date_interval < gc_daterange_middle
  88. THEN gc_small
  89. WHEN p_date_interval < gc_daterange_large
  90. THEN gc_middle
  91. ELSE gc_large
  92. END;
  93. CALC_PREDICATE;
  94. END IF;
  95. IF COALESCE(p_client_seqno, p_client_id, p_client_seqno_list) IS NOT NULL
  96. THEN
  97. SELECT NVL(SUM(cnt_year), 0) AS cnt
  98. INTO v_cnt
  99. FROM CLIENTS_HP_STATISTICS t
  100. WHERE 1=1
  101. AND (p_client_seqno IS NULL OR p_client_seqno = t.client_seqno)
  102. AND (p_client_id IS NULL OR p_client_id = t.client_id)
  103. AND (p_client_seqno_list IS NULL OR t.client_seqno IN
  104. (SELECT SUBSTR(s,
  105. CASE
  106. WHEN LEVEL > 1 THEN
  107. INSTR(s, ',', 1, LEVEL - 1 ) + 1
  108. ELSE 1
  109. END,
  110. INSTR(s, ',', 1, LEVEL)
  111. CASE
  112. WHEN LEVEL > 1 THEN
  113. INSTR(s, ',', 1, LEVEL – 1) + 1
  114. ELSE 1
  115. END)
  116. FROM (SELECT p_client_seqno_list||',' AS s FROM DUAL)
  117. CONNECT BY INSTR(s, ',', 1, LEVEL) > 0));
  118. g_clientrange := CASE
  119. WHEN v_cnt > gc_client_large THEN gc_large
  120. WHEN v_cnt > gc_client_middle THEN gc_middle
  121. ELSE gc_small
  122. END;
  123. CALC_PREDICATE;
  124. END IF;
  125. END CALC_PREDICATES;
  126.  
  127. END FORCE_HARD_PARSE_PKG;
  128. PACKAGE BODY created.
  129.  
  130. SQL> EXEC DBMS_RLS.ADD_POLICY (USER, 'HARD_PARSE_TABLE', 'HARD_PARSE_POLICY', USER, 'FORCE_HARD_PARSE_PKG.FORCE_HARD_PARSE', 'select');
  131. PL/SQL PROCEDURE successfully completed.

Now, if we are going to implement this technique in a report, we should add HARD_PARSE_TABLE into the query (it doesn’t change the query result at all!) and call CALC_PREDICATES function before the main query is executed.

Let’s see how this technique can be fixed similar to our previous example:

  1. DECLARE p NUMBER; v NUMBER;
  2. BEGIN
  3. V := 0; p := 1000000;
  4. FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000);
  5. FOR rec IN (SELECT /*+query_hp1000000*/ *
  6. FROM VVP_HARD_PARSE_TEST,
  7. HARD_PARSE_TABLE
  8. WHERE c2 = p) LOOP
  9. V := v + 1;
  10. END LOOP;
  11. dbms_output.put_line(v);
  12. v := 0; p := 1;
  13. FORCE_HARD_PARSE_PKG.SET_PREDICATE(1);
  14. FOR rec IN (SELECT /*+query_hp1000000*/ *
  15. FROM VVP_HARD_PARSE_TEST,
  16. HARD_PARSE_TABLE
  17. WHERE c2 = p) LOOP
  18. V := v + 1;
  19. END LOOP;
  20. dbms_output.put_line(v);
  21. -----------------
  22. V := 0; p := 1;
  23. FORCE_HARD_PARSE_PKG.SET_PREDICATE(1);
  24. FOR rec IN (SELECT /*+query_hp1*/ *
  25. FROM VVP_HARD_PARSE_TEST,
  26. HARD_PARSE_TABLE
  27. WHERE c2 = p) LOOP
  28. V := v + 1;
  29. END LOOP;
  30. dbms_output.put_line(v);
  31. v := 0; p := 1000000;
  32. FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000);
  33. FOR rec IN (SELECT /*+query_hp1*/ *
  34. FROM VVP_HARD_PARSE_TEST,
  35. HARD_PARSE_TABLE
  36. WHERE c2 = p) LOOP
  37. V := v + 1;
  38. END LOOP;
  39. dbms_output.put_line(v);
  40. END;

Let’s take a look on query execution plans:

  1. 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;
  2. SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
  3. SQL_TEXT
  4. --------------------------------------------------------------------------------
  5. 7wva3uqbgh4qf 0 1 1136240498
  6. SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1
  7. 7wva3uqbgh4qf 1 1 3246475190
  8. SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1
  9. 8cju3tfjvwm1p 0 1 3246475190
  10. SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1
  11. 8cju3tfjvwm1p 1 1 1136240498
  12. SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1
  13. --
  14. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7wva3uqbgh4qf', cursor_child_no => 0, format => 'basic +peeked_binds'));
  15. SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST,
  16. HARD_PARSE_TABLE WHERE C2 = :B1
  17. PLAN hash VALUE: 1136240498
  18. ----------------------------------------------------
  19. | Id | Operation | Name |
  20. ----------------------------------------------------
  21. | 0 | SELECT STATEMENT | |
  22. | 1 | MERGE JOIN CARTESIAN| |
  23. | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE |
  24. | 3 | BUFFER SORT | |
  25. | 4 | TABLE ACCESS FULL | VVP_HARD_PARSE_TEST |
  26. ----------------------------------------------------
  27. Peeked Binds (IDENTIFIED BY position):
  28. --------------------------------------
  29. 1 - :B1 (NUMBER): 1000000
  30. --
  31. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7wva3uqbgh4qf', cursor_child_no => 1, format => 'basic +peeked_binds'));
  32. SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST,
  33. HARD_PARSE_TABLE WHERE C2 = :B1
  34. PLAN hash VALUE: 3246475190
  35. --------------------------------------------------------------------
  36. | Id | Operation | Name |
  37. --------------------------------------------------------------------
  38. | 0 | SELECT STATEMENT | |
  39. | 1 | MERGE JOIN CARTESIAN | |
  40. | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE |
  41. | 3 | BUFFER SORT | |
  42. | 4 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST |
  43. | 5 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 |
  44. --------------------------------------------------------------------
  45. Peeked Binds (IDENTIFIED BY position):
  46. --------------------------------------
  47. 1 - :B1 (NUMBER): 1
  48. --
  49. SQl> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '8cju3tfjvwm1p', cursor_child_no => 0, format => 'basic +peeked_binds'));
  50. SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST,
  51. HARD_PARSE_TABLE WHERE C2 = :B1
  52. PLAN hash VALUE: 3246475190
  53. --------------------------------------------------------------------
  54. | Id | Operation | Name |
  55. --------------------------------------------------------------------
  56. | 0 | SELECT STATEMENT | |
  57. | 1 | MERGE JOIN CARTESIAN | |
  58. | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE |
  59. | 3 | BUFFER SORT | |
  60. | 4 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST |
  61. | 5 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 |
  62. --------------------------------------------------------------------
  63. Peeked Binds (IDENTIFIED BY position):
  64. --------------------------------------
  65. 1 - :B1 (NUMBER): 1
  66. --
  67. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '8cju3tfjvwm1p', cursor_child_no => 1, format => 'basic +peeked_binds'));
  68. SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST,
  69. HARD_PARSE_TABLE WHERE C2 = :B1
  70. PLAN hash VALUE: 1136240498
  71. ----------------------------------------------------
  72. | Id | Operation | Name |
  73. ----------------------------------------------------
  74. | 0 | SELECT STATEMENT | |
  75. | 1 | MERGE JOIN CARTESIAN| |
  76. | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE |
  77. | 3 | BUFFER SORT | |
  78. | 4 | TABLE ACCESS FULL | VVP_HARD_PARSE_TEST |
  79. ----------------------------------------------------
  80. Peeked Binds (IDENTIFIED BY position):
  81. --------------------------------------
  82. 1 - :B1 (NUMBER): 1000000

Looks good. Each query executed twice, with a different child cursor for execution and different plans. For parameter C2=1000000 we see the FULL TABLE SCAN in the query execution plans for both queries, for parameter C2=1 we see the INDEX RANGE SCAN.

Finally, here is a solution for the Monday rainy mornings issue:

“It turned out that there was a cold backup on Sunday nights so all query plans would be regenerated upon first use on Monday morning. Now a certain user would start work earlier than everybody else and the values of the bind variables in her case resulted in a query plan performed well for values used by other users during the course of the work week. However, if it was raining on Monday mornings, this user would be late for work because of certain aspects of her Monday morning routine. A batch report would then be the first to execute the problem query on Monday morning but the values of the bind variables in that case resulted in a query plan that was terribly inappropriate for other values.“

Here are some useful views:

  • dba_tab_histograms, all_tab_histograms, user_tab_histograms
  • v$vpd_policy
  • v$sql_bind_capture
  • dba_hist_sqlbind

Добавить комментарий