1

The other day I noticed something strange that perhaps someone have an explanation for. The problem I was facing was a query that due to a skewed distribution of data generated a poor plan when a parameter marker was used. The execution time was prox 10 times higher compared to a hardcoded value concatenated into the query.

I have never tried optimization profiles before, so I thought it would give it a shot. I installed a profile like:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="10.5.8.0">
<STMTPROFILE ID="Guideline for update of process_state_id">
    <STMTKEY>
       <![CDATA[UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 WHERE EXISTS ( SELECT     1 FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID AND SUBSTR(P.PNR, 9, 1) <> 'F' ) AND NOT EXISTS ( SELECT    1 FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID ) AND PCO.ADMISSIONROUND_ID = ? AND PCO.RESULT = 20]]>
    </STMTKEY>
    <OPTGUIDELINES> 
      <REOPT VALUE='ALWAYS'/> 
    </OPTGUIDELINES>
</STMTPROFILE>

This gave me what I wished for, i.e. the query where recompiled every time it got a new parameter and benefitted from distribution statistics. So far so good.

I wanted to take a closer look at how the plan changed due to the optimization profile, so I turned on explain mode and fired of the query:

]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
SQL0217W  The statement was not executed as only Explain information requests are being processed.  SQLSTATE=01604
]$ db2exfmt -d ...

Much to my surprise, I get an error doing the same thing with the optimization profile enabled:

]$ db2 "set current optimization profile test.optprof"
DB20000I  The SQL command completed successfully.
]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql 
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0313N  The number of variables in the EXECUTE statement, the number of 
variables in the OPEN statement, or the number of arguments in an OPEN 
statement for a parameterized cursor is not equal to the number of values required.  SQLSTATE=07004

If I run the query from python or java everything works well, so it is more a question of the convenience to be able to explain statements from command line.

In addition, it also works to explain the plan like:

]$ db2 "explain plan for UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 
> WHERE EXISTS (
>      SELECT     1     
>         FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID     
>         AND SUBSTR(P.PNR, 9, 1) <> 'F' 
> ) AND NOT EXISTS (
>     SELECT    1    
>         FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL    
>         WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID     
>         AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID    
>         AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID 
> ) AND PCO.ADMISSIONROUND_ID = ?
> AND PCO.RESULT = 20"
DB20000I  The SQL command completed successfully.

This, however, becomes a bit unpractical for large queries since it requires either to change the query in the file, keep two files, one for explain and one without, or as above executing the query directly.

Does the optimization profile somehow invalidate the explain mode? It does not matter in what order I set explain mode and optimization profile, I cannot execute a query with a parameter marker either way. Any thoughts anyone?

3

The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of reoptimization, isn't it. The manual says in particular:

The REOPT bind option defers statement compilation from PREPARE to OPEN or EXECUTE time, when the input variable values are known. The values are passed to the SQL compiler so that the optimizer can use the values to compute a more accurate selectivity estimate.

You are not providing the expected value, so compilation cannot proceed.

The EXPLAIN statement succeds I believe because it doesn't get to the re-optimization phase therefore not expecting a value for the bind variable. As a result, the plan it generates will probably differ from the reoptimized one.

You can address this by creating a global variable: CREATE VARIABLE myparm INT DEFAULT 5 (or whatever data type and value you need) and then referencing myparm in the statement where you have the parameter marker now. You will also need to create a new profile that matches the new statement text.

Tested and works on v11.1 but I assume the behaviour would be the same in 10.5.

  • I don't have time to investigate this further at the moment, but some additional observations are. Replacing ? with a host variable like :A becomes a query does not match the profile, so the bad plan remains. I tried using a variable as suggested, but the effect is the same as for the host variable. – Lennart Sep 29 '17 at 6:31
  • @Lennart please see an update above -- you'll need a new profile to match the statement with the variable. – mustaccio Sep 29 '17 at 18:32
  • thanks for your interest. I think it will be easiest to create a small python wrapper so that the query can be executed with a parameter. When I get back from IDUG Lissabon, I will check and see what happens during "explain plan for...". I'll post back if I find something interesting about that. – Lennart Sep 30 '17 at 16:50
  • You might want to try also a profile with inexact matching. – mustaccio Oct 1 '17 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.