Forum Stats

  • 3,824,944 Users
  • 2,260,442 Discussions
  • 7,896,356 Comments

Discussions

CBO should peek at SYS_CONTEXT values just like bind values

Lukas Eder
Lukas Eder Member Posts: 126 Bronze Badge
edited Oct 20, 2016 6:21AM in Database Ideas - Ideas

I have no idea about the complexity of a possible implementation, just hinting at the idea that this might be possible:

Since 11g and adaptive cursor sharing, the CBO peeks at bind variables and might decide to use a different execution plan depending on the input. This could also be done for SYS_CONTEXT values that are embedded in queries, which would help write complicated stuff like this:

CREATE OR REPLACE VIEW v_categories_per_actor AS

SELECT DISTINCT actor_id, c.name

FROM category c

JOIN film_category fc USING (category_id)

JOIN film_actor fa USING (film_id)

WHERE fa.actor_id = TO_NUMBER(sys_context('MY_APP', 'ACTOR_ID'))

AND sys_context('MY_APP', 'USER_TYPE') = 'C'

UNION ALL

SELECT DISTINCT actor_id, c.name

FROM category c

JOIN film_category fc USING (category_id)

JOIN film_actor fa USING (film_id)

WHERE sys_context('MY_APP', 'USER_TYPE') = 'O'

Imagine this view uses SYS_CONTEXT to distinguish between session / user types, e.g. customers (C) who can see only parts of the data and operators (O) who can see all the data. Now, this view can be embedded in other views to form very complex access control lists. In situations like these, peeking at the actual SYS_CONTEXT could greatly help because in those cases, the execution plan could completely avoid one of the two UNION ALL subqueries, rather than keeping it in place and costing it, because those wrong costs and cardinalities will propagate to every upstream view that uses this one.

Lukas EderChris AntogniniMautro PaganoFranck PachotGalo BaldaBPeaslandDBAPhilipp SalvisbergberxSven W.Kevan GellingSagar -OracleJon TheriaultLigang FangokomollApexBineulohmanngassenmjKevin J Wrightifan.s.jonesSKartaevRainer StenzelUser_FL517MarkStewart
25 votes

Active · Last Updated

Comments

  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 181 Silver Badge
    those cases, the execution plan could completely avoid one of the two UNION ALL subqueries

    For that bind-peeking has to be enforced after changing a context variable to avoid wrong results. But I still support this idea.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    So essentially each SYS_CONTEXT call should be treated like a parameter bind?

    This is an interesting idea, I do support it, as long as it keeps up the extreme fast performance of such a sys_context call.

    I don't like the presented example, because in this case the context seem to be misused as a filter parameter. It could be solved by a simple view, where the parameters are added to the view as filters. The DISTINCT that is in the view, might prevent/disturb parameter passing, so maybe that is one of the reasons, why the context was introduced into this example in first place.

    However I do agree that if you use a sys_context for emphasizing or restricting certain profiles or security contexts, then it would be very benefitial to create execution plans in the same way as if bind parameters were used.

  • Kevan Gelling
    Kevan Gelling Member Posts: 33 Green Ribbon

    There's a similar issue with dynamic SQL.  The CBO doesn't do bind peeking for SQL which is run using DBMS_SQL (tested in 11R2).

    This is a particular problem for APEX which uses DBMS_SQL to run the embedded SQL.

    MarkStewart
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    There's a similar issue with dynamic SQL.  The CBO doesn't do bind peeking for SQL which is run using DBMS_SQL (tested in 11R2).

    This is a particular problem for APEX which uses DBMS_SQL to run the embedded SQL.

    Kevan Gelling wrote:There's a similar issue with dynamic SQL. The CBO doesn't do bind peeking for SQL which is run using DBMS_SQL (tested in 11R2)....

    At first I didn't believe that is true, at least not anymore|completly. I did a small test in 12.1.0.1 and could see that multiple cursors were created. So bind peeking and adaptive sql plans seemed to work.

    However my tests also showed, that using DBMS_SQL the dictionary views were not populated in the normal way. This can lead to the misconception that no bind peeking/adaptive plans were used. Especially the columns IS_BIND_SENSITIVE and IS_BIND_AWARE were not set to 'Y'.

    But a more detailed look revealed that the only plan that was used was a full table scan and never an index access plan (as opposed to when running the binded sql directly in SQL Developer).

    So it seems as if you are right. Hard parsing is avoided even with DBMS_SQL. But adaptive binding does not seem to work then... This is worth an extra idea or maybe even a bug report in support.oracle.com.

    But to be fair, it does not have much to do with the proposed idea here.

    berx
  • Kevan Gelling
    Kevan Gelling Member Posts: 33 Green Ribbon
    Kevan Gelling wrote:There's a similar issue with dynamic SQL. The CBO doesn't do bind peeking for SQL which is run using DBMS_SQL (tested in 11R2)....

    At first I didn't believe that is true, at least not anymore|completly. I did a small test in 12.1.0.1 and could see that multiple cursors were created. So bind peeking and adaptive sql plans seemed to work.

    However my tests also showed, that using DBMS_SQL the dictionary views were not populated in the normal way. This can lead to the misconception that no bind peeking/adaptive plans were used. Especially the columns IS_BIND_SENSITIVE and IS_BIND_AWARE were not set to 'Y'.

    But a more detailed look revealed that the only plan that was used was a full table scan and never an index access plan (as opposed to when running the binded sql directly in SQL Developer).

    So it seems as if you are right. Hard parsing is avoided even with DBMS_SQL. But adaptive binding does not seem to work then... This is worth an extra idea or maybe even a bug report in support.oracle.com.

    But to be fair, it does not have much to do with the proposed idea here.

    x

    Sven W. wrote:Kevan Gelling wrote:There's a similar issue with dynamic SQL. The CBO doesn't do bind peeking for SQL which is run using DBMS_SQL (tested in 11R2).......So it seems as if you are right. Hard parsing is avoided even with DBMS_SQL. But adaptive binding does not seem to work then... This is worth an extra idea or maybe even a bug report in support.oracle.com.But to be fair, it does not have much to do with the proposed idea here.

    I raised an SR with the APEX team 4 years ago.  I've still got the test case, so I'll use it to create a new Idea.

    Sven W.Jon TheriaultApexBine