Topic
  • 3 replies
  • Latest Post - ‏2014-03-25T17:13:49Z by krmilligan
Ajay Kulkarni
Ajay Kulkarni
19 Posts

Pinned topic SQLPKG Vs SQL Plan Cache

‏2014-03-22T04:15:28Z |

 

Hello DB2 for i experts, 

Since SQL plan cache is probed for every SQE  (which means almost all SQL statements) then does that mean enabling SQLPKG for extended dynamic statements is not helping the engine? 

What is that SQLPKG contains that is not in SQL Plan Cache?

Thanks

  • krmilligan
    krmilligan
    450 Posts

    Re: SQLPKG Vs SQL Plan Cache

    ‏2014-03-25T15:57:46Z  

    The header portion of the query plan is stored in the SQLPKG object (program object with embedded SQL) that helps the SQL runtime engine which does end up helping performance.

  • Ajay Kulkarni
    Ajay Kulkarni
    19 Posts

    Re: SQLPKG Vs SQL Plan Cache

    ‏2014-03-25T16:38:05Z  

    The header portion of the query plan is stored in the SQLPKG object (program object with embedded SQL) that helps the SQL runtime engine which does end up helping performance.

    So in case of Extended Dynamic, the optimizer check SQL package instead of plan cache? What information is missing in Plan Cache that is part of extended package , and can SQL packages be obsoleted since most of the queries use SQE Plan Cache

     

  • Ajay Kulkarni
    Ajay Kulkarni
    19 Posts

    Re: SQLPKG Vs SQL Plan Cache

    ‏2014-03-25T16:40:10Z  

    The header portion of the query plan is stored in the SQLPKG object (program object with embedded SQL) that helps the SQL runtime engine which does end up helping performance.

    So in case of Extended Dynamic, the optimizer check SQL package instead of plan cache? What information is missing in Plan Cache that is part of extended package , and can SQL packages be obsoleted since most of the queries use SQE Plan Cache

     
  • krmilligan
    krmilligan
    450 Posts

    Re: SQLPKG Vs SQL Plan Cache

    ‏2014-03-25T17:13:49Z  

    So in case of Extended Dynamic, the optimizer check SQL package instead of plan cache? What information is missing in Plan Cache that is part of extended package , and can SQL packages be obsoleted since most of the queries use SQE Plan Cache

     

    Think of an access plan having 2 parts - a header and body - with the optimizer's plan stored in the body.  The body of the access plan is all that's stored in the SQE Plan Cache.  The SQL runtime engine which sits above the Query Engine uses information stored in the header for runtime execution of the SQL statement.  Thus, caching the access plan header in an SQL Package (or Program object for embedded SQL in HLL) helps with performance.