Saw a nice option in formatting Oracle explain plans for Oracle 12c for plans that use adaptive plans. Courtesy of Oracle Database 12c: Adaptive Execution Plans with Tom Kyte

 select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));

Those options show for adaptive plans exactly which steps were skipped by the optimizer based on row cardinality. Check out the YouTube video above for an example, at 3:41 https://youtu.be/9o9iuxNBciQ?t=221 You may need to do one of the following:

• Add the hint /*+ gather_plan_statistics */ to the statement
• Set the parameter 'statistics_level' is set to 'ALL', at session or system level. Caution: if you set it at the system level, overall performance will be degraded.

Oracle APEX and /*+ result_cache */ of form variables

I was having a tough time with a bug in an Oracle APEX application. I was filling a shuttle-list with a query, and I added the hint /*+ result_cache */ for performance, and thought that Oracle would be peeking at the bind values being used in the query. Wrong! In regular vanilla queries, Oracle will look at bind variables to see if it can use the result cache, but APEX magic prevents that.

Code sample causing issues:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like v('P215_PROJECT_ID_NUM_SEARCH') 

Well once I took out the hint, all worked as planned!

If you are not familiar with result caching on Oracle, it can make queries that return a small number of rows from large tables run extremely fast.

Actually, if I had coded the query like the following, using bind variable syntax (:P215_PROJECT_ID_NUM_SEARCH) instead of V('') syntax to refer to my form fields, I could probably have used the result_cache hint, and since the V('') syntax is actually calling a PL/SQL procedure, it could be slower than using the bind syntax:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like :P215_PROJECT_ID_NUM_SEARCH