SQL parse time issue – use “minified hinted outlines”

problem description

Recently I encountered a simple looking query that returned a very small result set, but used to take more than 5 seconds time to execute. When executing it a second time it was fast (less than 0.01 second). When adding a comment then the execution time was bad again (5 seconds). Adding a comment forces the optimizer to parse the query again. However cached blocks can be reused. So this simple test gives a good indication where the time is spent.

The query was simple looking at first but turned out to be a complex set of views within views with nested subqueries, union all combinations and so on. However the execution plan was all nested loops, the filter criteria was correctly pushed down through all the sub layers.


The problem was that the complex sql took a very long time to hard parse.
Executing the plan was fast, but creating the execution plan was eating up the time. The CBO had trouble finding the best execution plan. The more tables are joined, the more different join orders are possible and need to be considered. This is called permutations (see tuning section below). It can take a considerably long time.



This was the query

      ,case when
         count(*) over (partition by r.streamid )                                        -- Number of wells
         =                                                                               -- equals
         count(case when r.status = 'SUCCESS' then 1 end) over (partition by r.streamid) -- number of successfull wells
         then 'FULFILLED'
         else 'NOT'
         end  as platestatus
from v_lnk_check_part_order_results r
where orderid = :orderid
order by streamid, labwareitemid;

Now how can we demonstrate that the parsing time is the problem?

To get a clear picture would be to trace your statement and run a TKPROF report for it. This is often something that is very hard to do for developers, because they do not have access to the OS and are not able without DBA privs to enable and disable the tracing and above all to run the TKPROF command. Here is a tutorial from John Watson that shows how to trace and use tkprof.

The way described at the beginning only gives us an indication. First run the statement several times until the timings are stable. This means the data blocks are cached in the buffer cache. Then slightly modify the statement, for example by adding a comment, adding a meaningless where condition (2=2) or removing one column from the output. This will lead to a new cursor and to a new hard parse. Run the same statement a second time and it should be fast again. The difference in execution time gives an approximization for the time needed to hard parse.

It is not perfect, but is a solid “educated guess”. I didn’t see an easy way in SQL developer to show those statistics using typical developer privs only.

the solution

Adding the following hint to the select solved the issue

OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)

What this hint does and how to get it is explained below.

I call it the “minified hinted outline” approach. Although “Extrem lead hinting” was a close runner up.

tuning effort

We already identified the hard parsing as the point where the CBO spends its time. And more specifically it is the point where the CBO creates multiple execution plans and compares them.

Source:Oracle Docs – Query Optimizer Concepts

To understand the problem that the CBO faces, here is a table that lists the number of potential table combinations that the CBO needs to consider. The permutations go up exponentially with the number of tables.

permutation considerations

# tables join permutations to consider
2 2! = 2, table A => table B & table B => table A
3 3! = 6, A=>B=>C & A=>C=>B & B=>A=>C & B=>C=>A & C=>A=>B & C=>B=>A
4 4! = 24
5 5! = 120
6 6! = 720
7 7! = 5040
15 15! = 1,307,674,368,000

There is a maximum number of permutations that the CBO considers.
This maximum number is 2000 (default since 11g). It can be influenced by the hidden parameter _optimizer_max_permutations. In older database version this was a normal init.ora parameter, but it was deprecated in 9i and replaced by the “hidden” parameter (http://oracleinaction.com/undocumented-params-11g/
). It might be that this maximum number is per query block. The documentation is not perfectly clear about it. A query block can be an independent subquery, a non-merged/inline view or a WITH clause section. So for ten query blocks, it might be that the maximum number of permutations to consider is 20000. I didn’t test that hypothesis but there are some indications that this happened in this specific case.

As we can see from the list this magical number of 2000 permutations is already reached when 7 tables are joined. After that the optimizer will stop looking at other combinations. Btw: that is why the table order in the FROM clause can still make a difference! So put the main tables at the beginning of the FROM clause, so that the CBO considers them correctly in its access path permutations.

general approaches

  • Stored outlines and SQL plan management
  • global table hints

stored outlines

A stored outline will store the execution plan for one specific SQL statement. They are mostly used for plan stability, however a nice side effect is, that the hard parse phase is shortend when a stored outline is found.
A good introduction about stored outlines can be found in the 10g documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14211/outlines.htm#i30836 . Stored outlines are available since 9i at least. The modern version of a stored outline is a SQL plan baseline and SQL plan management (SPM).

Further reading:
Oracle white paper: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf#22

Blog “Insight into the workings of the Optimizer”: https://blogs.oracle.com/optimizer/entry/how_to_use_sql_plan

global table hints

Essentially what stored outlines do is to set a couple of hints.

We can do this directly ourselfs by using global table hints.
The normal way to provide a hint, is to add it to the section where it is relevant for. But when views are involved, you probably won’t want to change the view definitions and cutter them with hints. First of all the views might belong to some other schema. Also they are used in different points in the application, so a hint that my be good for one specific SQL might not be good for another SQL using the same view.

Global table hints come to the rescue! A global table hint is able to “inject” a hint into the deeper layers of a view.

Quick and not so dirty solution

As already mentioned adding the following hint to the select solved the issue

OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)

All we need to get such a hint is clever usage of the Oracle SQL Developer.

how to

Using SQL developer we can check the execution plan for our query (F10).
In the lower section of the plan there is a section “Other XML” and in this section is a long list of HINTs. This section can be exported.

OR we can use the explain plan command and after that do this:

select *
from table(dbms_xplan.display(
             format=>'BASIC ALIAS OUTLINE'

“OUTLINE” will show the complete sql outline. Which is the sum of all hints that need to be applied to get this specific plan.
“ALIAS” will show the query block names that are used.
“BASIC” is just to focus on the relevant parts for this example.

A solid first test strategy is to copy and paste the complete outline section into the sql statement and see if it is now fast when using all the hints.

However this list is way to long and we can eliminate step by step most hints. I would recommend to keep only the leading hints. Remember, we just want to reduce the complexity for the high number of table permutations. The leading hints (and a few others like merge/no_merge) are responsible for freezing the join orders. Make sure to get all leading hints. If one of them is missing, the whole order of execution might break.

If the query is still reasonably fast then we can work with them only. In my case the execution time slightly increased to 0.15 seconds. Which is still way below the full 5 seconds when calling it without a hint.

The result may look like this:


Ok lets look more closely at one of the leading hints that we have:

@SEL$5B04CACE is a named query block. Where oracle itself had choosen the name. We can see those query block names when using the “ALIAS” setting during explain plan creation. I believe those query block names could potentially change when useing the same statement on a different database (e.g. moving the code from DEV to TEST DB). Therefore the goal is to get rid of those aliases for query blocks.

SOA@SEL$13 is the table(or view) alias “SOA” and SEL$13 is the 13th SELECT command in our query. Including the outermost select call that we issue ourselfs. So the table order is SOA before CE inside the query block “SEL$5B04CACE”.

We can then combine all those leading hints into one single leading hint. The order is relevant. Sometimes it needs a little testing to find the proper order of the hints. This is because the starting points, the query block names need to be removed when only one hint is used. In my case the “@SEL$5461C9FE” was the outermost block (the select itself), so the leading tables that came after that block were the first ones to consider. This outermost block can be identified by studying the ALIAS section of the execution plan.


“minified hinted outlines” are a quick solution for a very specific type of problem. Typically when using complex queries on views we might want to consider such a solution. It is a quick solution as an alternativ to stored outlines or SPM.


This solution freezes parts of the execution plan. But in a less invasive way than a real stored outline would do. For example we do not say which index to use, instead the CBO still decides about the best way how to access a specific table. It is just that the order of the tables and subquery blocks is hinted and by that frozen.

If the select or the views change, then the leading hint needs to be regenerated. However this concept was working sucessfully even when moving the query including hint from test to a production environment.

One thought on “SQL parse time issue – use “minified hinted outlines”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.