In the emp table, this means I’ve also changed three occurrences of department 10 to department 310. INSERT INTO DEPT VALUES ( 40, 'OPERATIONS', 'BOSTON' ) Originally the departments were created by the statements: I can’t do anything so dramatic on paper with this data set but can demonstrate the nature of the issue by recreating the data set after editing the script to modify one of the department numbers. The problem was that I had carefully updated a specific row in a way that “broke” the caching mechanism and resulted in the session running the subquery more than 3,000 times when it had previously been running it just 6 times. has implemented the caching mechanism.įor many years I used to do a demonstration where I updated one row in a much larger emp table after which a query like the one above that had previously been taking just 10 milliseconds to complete suddenly took 20 seconds of CPU time using exactly the same execution plan. There’s a threat hidden behind the benefit, though, a threat that is a side effect of the way that Oracle Corp. There’s a note that expands on this observation at: This means that some queries can execute much faster than the execution plan would suggest, and that’s a good argument for blocking subquery unnesting (or even rewriting your query) if you know your data well enough to be certain that the caching feature will work in your favour. This is an example of scalar subquery caching at its most effective.Įach time the correlated subquery is called session “remembers” the input value ( deptno) and result ( avg()) of the call in a local cache and if the subquery is called with an input that has previously been used the session gets the result from this cache rather than actually running the subquery again and, as an extra little boost, doesn’t even check the cache if the deptno for the current call is the same as the one for the immediately preceding call. display_cursor ( format = > 'allstats last' ) ) Īs you can see from the Starts column for operations 4 and 5, the subquery ran only 3 times (once for each department in the emp table). If I set serveroutput off and re-execute the query, I can make a call to dbms_xplan.display_cursor() with the allstats last format option to show the rowsource execution statistics – which look like this: You’ll notice that I’ve included the /*+ gather_plan_statistics */ hint in the query. Here’s an important question: how many times does the correlated subquery actually run? Is it really once for every single employee, or can Oracle find a way to avoid some of the work at runtime while still following the structure of the plan? Operation 2 ( FILTER) calls its first child to get rows from emp then for each row in turn calls the subquery passing in the current deptno as the correlation variable (the bind variable :B1 in the Predicate Information). The optimizer has decided to execute this query exactly as it is written, using a correlated subquery to filter out rows from an initial table scan of emp.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |