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?