Hi Tom,
I have a query embedded within a from clause in order to select the top rows. The embedded query is bind sensitive and I can easily make it bind aware. The outer most query however is not Bind Sensitive and so I cannot make it Bind Aware. How do I work around this issue so that my query uses ACS?
I have my query listed below. I have shortened the selected values to make easier to make the case.
SELECT /*+ BIND_AWARE */ *
FROM
(
SELECT WOB_CASE_ID ,
WOBE_WOB_ID,
WOBE_WRKT_TYP_ID ,
WOB_IN_USE_IND
FROM TANWOB ,
TANWOB_EVNT ,
TANCASE ,
TANCASE_INFO,
TANCLIENT ,
TIWUSR ,
TANSRV_TEAM ,
TANANN_TYP
WHERE WOBE_ID = WOB_LST_WOBE_ID
AND WOB_ID = WOBE_WOB_ID
AND WOB_CASE_ID = CASE_ID
AND CINF_CASE_ID =CASE_ID
AND CINF_ID =CLNT_CINF_ID
AND trim(CINF_OWN_RACF_ID) = trim(USR_WF_USR_NM(+))
AND SRV_TEAM_ID(+) = CINF_SRV_TEAM_ID
AND CINF_ANN_TYP_ID = ANN_TYP_ID(+)
AND WOBE_QUE_NM = :1
AND WOBE_WRKT_TYP_ID = :2
AND WOBE_OFC_CD = :3
AND WOB_IN_USE_IND = :4
AND CINF_BUS_TYP_ID = :5
ORDER BY SRV_TEAM_PRIO_ID,
ANN_TYP_NM DESC ,
WOBE_QUE_PRI ,
TRUNC(NOGOBRD) ,
CASE_ID
)
T
WHERE rownum<= 999
The use of an inline view does not impact adaptive cursor sharing. If the query in the inline view is really bind_aware, then the outer query should be too. Here is a simple example of what I mean.
SQL> -- This demo shows how in Adaptive Cursor Sharing allows multiple
SQL> -- execution plans for a statement that contains an inline view that
SQL> -- uses bind variables and is bind aware.
SQL> -- Thus ensuring that the best execution plan is always used.
SQL> -- Lets begin by executing a simple Select with an inline view From
SQL> -- the emp table (100,000 rows) with a single Where clause on the deptno column
SQL> -- that contains a bind variable. Lets begin by using the value 9,
SQL> -- Which repeats 10 times in the table, i.e, only 0.0001% of the table
SQL>
SQL> variable deptno number;
SQL> exec :deptno := 9
PL/SQL procedure successfully completed.
SQL>
SQL> Select /*ACS_1*/ *
2 From (Select count(*), max(empno)
3 From emp
4 Where deptno = :deptno
5 )
6 Where rownum<999;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
SQL> -- We expect to get an index range scan which is the most efficient path
SQL> -- For this bind value. Lets check the execution plan.
SQL> Select * From table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 9cvkgrwh0kppk, child number 0
-------------------------------------
Select /*ACS_1*/ * From (Select count(*), max(empno) From emp Where
deptno = :deptno) Where rownum<999
Plan hash value: 1209167532
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | SORT AGGREGATE | | 1 | 16 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 160 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_I1 | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<999)
3 - filter(ROWNUM<999)
5 - access("DEPTNO"=:DEPTNO)
25 rows Selected.
SQL> -- So we get the index range scan we expected. Lets look at the
SQL> -- execution statistics for this statement
SQL>
SQL> Select sql_text, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware
2 From v$sql
3 Where sql_text like 'Select /*ACS_1%';
SQL_TEXT CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
----------------------------------------------------- ------------ ---------- ----------- - -
Select /*ACS_1*/ * From (Select count(*), max(empno) 0 1 60 Y N
From emp Where deptno = :deptno) Where rownum<999
SQL> -- You can see we have 1 child cursor that has been executed one and we got
SQL> -- a small number of buffer gets. Now lets change the value of the bind
SQL> -- variable to 10 which is the most popular value. It repeats 99900 times
SQL> -- in the table, i.e, 99.9%
SQL>
SQL> exec :deptno := 10
PL/SQL procedure successfully completed.
SQL>
SQL> Select /*ACS_1*/ *
2 From (Select count(*), max(empno)
3 From emp
4 Where deptno = :deptno
5 )
6 Where rownum<999;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
SQL> -- We expect to get the same plan as before because we initally assume
SQL> -- they can share the same execution so if we check the execution plan
SQL> -- it will show an index range scan
SQL>
SQL> Select * From table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 9cvkgrwh0kppk, child number 0
-------------------------------------
Select /*ACS_1*/ * From (Select count(*), max(empno) From emp Where
deptno = :deptno) Where rownum<999
Plan hash value: 1209167532
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | SORT AGGREGATE | | 1 | 16 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 160 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_I1 | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<999)
3 - filter(ROWNUM<999)
5 - access("DEPTNO"=:DEPTNO)
25 rows Selected.
SQL>
SQL> -- But if we look at the execution statistics we should see two
SQL> -- executions and a big jump in the number of buffer gets From what
SQL> -- we saw before.
SQL>
SQL>
SQL> Select sql_text, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware
2 From v$sql
3 Where sql_text like 'Select /*ACS_1%';
SQL_TEXT CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
----------------------------------------------------- ------------ ---------- ----------- - -
Select /*ACS_1*/ * From (Select count(*), max(empno) 0 2 1014 Y N
From emp Where deptno = :deptno) Where rownum<999
SQL>
SQL> -- So lets re-execute the statement usxing the same popular value, 10
SQL>
SQL> exec :deptno := 10
PL/SQL procedure successfully completed.
SQL>
SQL> Select /*ACS_1*/ *
2 From (Select count(*), max(empno)
3 From emp
4 Where deptno = :deptno
5 )
6 Where rownum<999;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
SQL> -- Since we had the opportunity to verify that sharing the same execution
SQL> -- plan was not an optimal solution for this bind value we will decide
SQL> -- not to share the plan and generate another one based on the value 10.
SQL> -- Let's check what the new plan is, it should use a full table scan.
SQL>
SQL> Select * From table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 9cvkgrwh0kppk, child number 1
-------------------------------------
Select /*ACS_1*/ * From (Select count(*), max(empno) From emp Where
deptno = :deptno) Where rownum<999
Plan hash value: 177699161
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 234 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 26 | 234 (12)| 00:00:01 |
|* 3 | SORT AGGREGATE | | 1 | 16 | | |
|* 4 | TABLE ACCESS FULL| EMP | 99900 | 1560K| 234 (12)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<999)
3 - filter(ROWNUM<999)
4 - filter("DEPTNO"=:DEPTNO)
24 rows Selected.
SQL>
SQL> -- Now if we display the execution statistics we should see one
SQL> -- more child cursor (#1). Cursor #1 should show a number of
SQL> -- buffers gets lower than when cursor #0
SQL>
SQL>
SQL> Select sql_text, child_number, executions, buffer_gets,
is_bind_sensitive, is_bind_aware
2 From v$sql
3 Where sql_text like 'Select /*ACS_1%';
SQL_TEXT CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
----------------------------------------------------- ------------ ---------- ----------- - -
Select /*ACS_1*/ * From (Select count(*), max(empno) 0 2 1014 Y N
From emp Where deptno = :deptno) Where rownum<999
Select /*ACS_1*/ * From (Select count(*), max(empno) 1 1 767 Y Y
From emp Where deptno = :deptno) Where rownum<999
Remember you may need to execute the query a number of times before it will switch from being bind sensitive to being bind aware.