The lines where “skp=1” are the adaptive plan, which we now know appear as gray text in SQL Developers.

Those lines where “skp=0” are part of the default plan.

We can also the info type shows this is an Adaptive Plan. SQL Developer 4.1 shows both the default plan and the adaptive plan.įurther down in the Explain Plan is the Other XML section, which can be seen below. The gray text in the Explain Plan above are simply the adaptive plan. Pages 3-5 illustrate Adaptive Join Plans in action. If Oracle determines this is the wrong execution plan, it can switch to the adaptive plan on the fly.įor more info on Adaptive Plans, see this white paper from Oracle. Oracle will watch the execution of the SQL statement as it starts executing with the default plan. One plan is the default plan and the other plan is the adaptive plan. Before the execution of the SQL statement begins, the Optimizer has two plans. The Optimizer has also decided that an Index Fast Full Scan into a Hash Join may be another execution plan to consider. Tuning the application SQL to use the proper query plan is the fastest, least risky and most cost effective way to resolve Oracle performance problems. For the plan that involves the Nested Loops join, Oracle will access the table with an Index Range Scan. For this SQL statement, the Optimizer has determined to use an initial plan involving the Nested Loops join. Why two accesses of the same table? What is Oracle 12c doing?Īll of this is part of the new 12c Optimizer’s Adaptive Query Optimization. Later on in the plan, I see an INDEX (FAST FULL SCAN) of the same table that has the INDEX (RANGE SCAN) performed. 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. I was surprised to see some gray text in the Explain Plan output, which you can see below. As I typically do, I like to use SQL Developer to see the Explain Plan. 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.

Pl sql developer explain plan