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.

reason

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.

 

example

This was the query

SELECT
r.labwarebarcode
      ,r.orderid
      ,r.orderlineitemid
      ,r.streamid
      ,r.status
      ,r.lineitemcomment
      ,r.bat_identifier
      ,r.labwareitemid
      ,r.xposition
      ,r.yposition
      ,r.layoutid
      ,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

/*+
LEADING(OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23
CWFI@SEL$3 SOA@SEL$4 LI@SEL$3
POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6
SOA@SEL$13 CE@SEL$13
OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
ALL_ROWS
*/

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

/*+
LEADING(OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23
CWFI@SEL$3 SOA@SEL$4 LI@SEL$3
POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6
SOA@SEL$13 CE@SEL$13
OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
ALL_ROWS
*/

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:

LEADING(@SEL$6A1B73E7 CWFI@SEL$3 SOA@SEL$4 LI@SEL$3)
LEADING(@SEL$AB668856 POS@SEL$8 SOA@SEL$7 CWFI@SEL$6 LI@SEL$6)
LEADING(@SEL$5B04CACE SOA@SEL$13 CE@SEL$13)
LEADING(@SEL$FC92CB92 OSM@SEL$12 O@SEL$12 S@SEL$12 LI@SEL$12)
LEADING(@SEL$5461C9FE OLIS@SEL$11 ERRORS@SEL$11 WFI@SEL$2 EXTRAINFO@SEL$11 ILI@SEL$19 PL@SEL$27 ICH@SEL$17 SOL@SEL$25 BAT@SEL$21 CPD@SEL$23)
ALL_ROWS

Ok lets look more closely at one of the leading hints that we have:
LEADING(@SEL$5B04CACE SOA@SEL$13 CE@SEL$13)

@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.

Fazit

“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.

drawbacks

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.

scheduled job on procedure with DATE parameters

To set up a scheduled job in an oracle database is really simple. Oracle SQL Developer helps a lot to do so. It has a nice configuration wizard that creates all the needed function calls to DBMS_SCHEDULER for you.
scheduler_date_1

But some certain small issues can still be a hassle. For example if you want to run a procedure with parameters. Number and string parameters can be passed using DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE. If you use sql developer to create the job for you, then it will use this function for all types of parameters.

That means you can’t easily pass sysdate. Because that is simply passed as a string.

This is the code that SQL developer generates.

SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."myScheduledJob"',
argument_position => 1,
argument_value => '10');
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."myScheduledJob"',
argument_position => 2,
argument_value => 'sysdate');
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => '"MYSCHEMA"."myScheduledJob"',
argument_position => 3,
argument_value => 'false');

It is less known that you can also pass other types of parameters using DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE. It accepts an anydata data type. Anydata is like a placeholder for any scalar data type. You put a certain value or expression in and it “stores” the value and the data type.

I always change the job_name parameters from SQL Developer and replace it with a variable.
Here is an example of a real job that I set up using a date parameter value (value = “in 10 minutes”). always change the job_name parameters from SQL Developer and replace it with a variable.


-- create job
declare
v_jobname varchar2(30) := 'myScheduledJob';
v_date_format varchar2(100);
BEGIN
select value
into v_date_format
from v$nls_parameters
where parameter = 'NLS_DATE_FORMAT';

SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => v_jobname,
job_type => 'STORED_PROCEDURE',
job_action => 'MYSCHEMA.MYPACKAGE.RUN_BATCH',
number_of_arguments => 2,
start_date => TO_TIMESTAMP_TZ('2015-06-03 19:05:00 Europe/Berlin','YYYY-MM-DD HH24.MI.SS TZR'),
--start_date => systimestamp + interval '1' minute,
repeat_interval => NULL,
--end_date => TO_TIMESTAMP_TZ('2015-07-29 05:30:00 Europe/Berlin','YYYY-MM-DD HH24.MI.SS TZR'),
job_class => '"SYS"."DEFAULT_JOB_CLASS"',
enabled => FALSE,
auto_drop => FALSE,
comments => 'Do run something longrunning');

SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => v_jobname,
argument_position => 1,
argument_value => 10);

SYS.DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(
job_name => v_jobname,
argument_position => 2,
argument_value => sys.anydata.convertDate(sysdate + interval '10' min)
);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => v_jobname,
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name =>v_jobname,
attribute => 'max_run_duration', value => INTERVAL '1' DAY);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => v_jobname,
attribute => 'schedule_limit', value => INTERVAL '1' DAY);

SYS.DBMS_SCHEDULER.enable(
name => v_jobname);
commit;
END;
/

The expression “anydata.convertDate(sysdate + interval ’10’ min)” was used to set the parameter value.

Feel free to use that as a template for your own scheduled jobs.