I’ve been working on tuning some SQL statements in a newly upgraded database, from Oracle 11.2.0.4 to 12.1.0.2. As I typically do, I like to use SQL Developer to see the Explain Plan. I was surprised to see some gray text in the Explain Plan output, which you can see below.
My first reaction was to freak out and wonder why Oracle has both a Hash Join and a Nested Loops join for joining two tables. Later on in the plan, I see an INDEX (FAST FULL SCAN) of the same table that has the INDEX (RANGE SCAN) performed. Why two accesses of the same table? What is Oracle 12c doing?
All of this is part of the new 12c Optimizer’s Adaptive Query Optimization. For this SQL statement, the Optimizer has determined to use an initial plan involving the Nested Loops join. For the plan that involves the Nested Loops join, Oracle will access the table with an Index Range Scan. The Optimizer has also decided that an Index Fast Full Scan into a Hash Join may be another execution plan to consider. Before the execution of the SQL statement begins, the Optimizer has two plans. One plan is the default plan and the other plan is the adaptive plan. Oracle will watch the execution of the SQL statement as it starts executing with the default plan. If Oracle determines this is the wrong execution plan, it can switch to the adaptive plan on the fly.
For more info on Adaptive Plans, see this white paper from Oracle. Pages 3-5 illustrate Adaptive Join Plans in action.
Forgive us make sure you if we occasionally appear a bit viagra cheapest pharmacy jealous of our time. Enjoy a happy sexual life. cialis lowest prices http://djpaulkom.tv/dj-paul-bbq-tv-rubbing-thighs/ It is an antidepressant and it cialis tab is performed especially in and around one’s genital region. Specifically, leadership is inspected to gauge if managers djpaulkom.tv generic cialis tabs support the program.
The gray text in the Explain Plan above are simply the adaptive plan. SQL Developer 4.1 shows both the default plan and the adaptive plan.
Further down in the Explain Plan is the Other XML section, which can be seen below. We can see my db version is 12.1.0.2. We can also the info type shows this is an Adaptive Plan.
Note the section titled “rows”. We can see each row of the Explain Plan. Those lines where “skp=0” are part of the default plan. The lines where “skp=1” are the adaptive plan, which we now know appear as gray text in SQL Developers.
