db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@oracle.com>
Subject Re: Question on why the statement cache is saying a statement is invalid
Date Thu, 07 Aug 2014 17:42:10 GMT
If it appears to be invalid right after you compile it, I don't know how 
that could happen. Rick, Knut?

Dag

On 07. aug. 2014 18:51, Bergquist, Brett wrote:
>
> I am trying to figure out an issue where the optimizer is taking a 
> long time to compile a statement and want to make sure that the 
> subsequent times through it will use the cached statement if present 
> in the statement cache.
>
> I prepare the statement and then I look at the cache with:
>
> SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE
>
> And it shows that the statement is invalid.  Why would the a statement 
> be in the cache and be invalid?
>
> ij> SELECT * FROM SYSCS_DIAG.STATEMENT_CACHE;
>
> ID |SCHEMANAME 
>                                                                                     
                    |SQL_TEXT
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 3d3b0068-0147-b12d-b252-000065089f97|CSEM         |SELECT * FROM 
> SYSCS_DIAG.STATEMENT_CACHE order by ID desc
>
> |true |true |NULL
>
> 5e838074-0147-b12d-b252-000065089f97|APP |call 
> SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>
>                                                                                     
                                            |true 
> |true |NULL
>
> 6302404f-0147-b12d-b252-000065089f97|CSEM 
>                                                         |call 
> SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>
>                          |true |true |NULL
>
> 65f6406f-0147-b12d-b252-000065089f97|CSEM |SELECT
>
> t0.ID,
>
> t0.DTYPE,
>
> t0.RELATIVE_POSITION,
>
> t0.ALIAS_NAME,
>
> t0.DESCRIPTION,
>
> t0.DEVICE_NAME,
>
> t0.OPLOCK,
>
> t0.PARENTENTITY_ID,
>
> t1.ID,
>
> t1.MODEL,
>
> t1.SERIAL_NUMBER,
>
> t1.HARDWARE_TYPE,
>
> t1.HARDWARE_VERSION,
>
> t2.ID,
>
> t2.NEXT_CONFIGURATION_NUMBER,
>
> t2.NEWEST_CONFIGURATIONSET_ID,
>
> t2.OLDEST_CONFIGURATIONSET_ID,
>
> t3.ID,
>
> t3.TEMPLATE,
>
> t3.SYSTEM_TEMPLATE,
>
> t3.ADMIN_ENABLED,
>
> t3.TEMPLATE_PROPERTIES_ID,
>
> t3.ENTITY_FACET_MANAGER_ID,
>
> t4.ID,
>
> t4.LAST_OBSERVED_PING,
>
> t4.SNMP_READ_TIMESTAMP,
>
> t4.LAST_OBSERVED_SNMP_WRITE,
>
> t4.LAST_OBSERVED_SNMP_READ,
>
> t4.PING_TIMESTAMP,
>
> t4.SNMP_WRITE_TIMESTAMP,
>
> t5.ID,
>
> t5.POWER_LED_STATE_TIMESTAMP,
>
> t5.LAST_OBSERVED_STATUS_LED_STATE,
>
> t5.MAINBOARD_VOLTAGE_STATUS_TIMESTAMP,
>
> t5.LAST_OBSERVED_MAINBOARD_VOLTAGE_STATUS,
>
> t5.STATUS_LED_STATE_TIMESTAMP,
>
> t5.LAST_OBSERVED_POWER_LED_STATE,
>
> t6.ID,
>
> t7.ID
>
> FROM CORE_V1.SNMP_DEVICE t9,
>
> CORE_V1.SNMP_DEVICE_IP t8,
>
> PKG_9145E_V1.CHASSIS_9145E_104_0_0 t7,
>
> PKG_9145E_V1.CHASSIS_9145E_BC04 t6,
>
> PKG_9145E_V1.CHASSIS_9145E t5,
>
> CORE_V1.AGENT_MANAGED_HARDWARE t4,
>
> CORE_V1.MANAGED_HARDWARE t3,
>
> CORE_V1.CONFIGURABLE_HARDWARE t2,
>
> CORE_V1.HARDWARE t1,
>
> CORE_V1.DEVICE_ENTITY t0
>
> WHERE
>
> (
>
>    (
>
>       (t8.IP = CSEM.STRING_TO_IP(?))
>
>       AND
>
>       (
>
>          (
>
>             (t7.ID = t0.ID)
>
>             AND
>
>             (
>
>                (t6.ID = t0.ID)
>
>                AND
>
>                (
>
>                   (t5.ID = t0.ID)
>
>                   AND
>
>                   (
>
>                      (t4.ID = t0.ID)
>
>                      AND
>
>                      (
>
>                         (t3.ID = t0.ID)
>
>                         AND ((t2.ID = t0.ID) AND (t1.ID = t0.ID))
>
>                      )
>
>                   )
>
>                )
>
>             )
>
>          )
>
>          AND (t0.DTYPE = 'CHASSIS_9145E_104_0_0')
>
>       )
>
>    )
>
>    AND ((t9.AGENT_MANAGED_HARDWARE_ID = t0.ID) AND (t8.SNMPDEVICE_ID = 
> t9.ID))
>
> ) |true |true |NULL
>
> 832a0051-0147-b12d-b252-000065089f97|CSEM |SELECT
>
> t0.ID,
>
> t0.DTYPE,
>
> t0.RELATIVE_POSITION,
>
> t0.ALIAS_NAME,
>
> t0.DESCRIPTION,
>
> t0.DEVICE_NAME,
>
> t0.OPLOCK,
>
> t0.PARENTENTITY_ID,
>
> t1.ID,
>
> t1.MODEL,
>
> t1.SERIAL_NUMBER,
>
> t1.HARDWARE_TYPE,
>
> t1.HARDWARE_VERSION,
>
> t2.ID,
>
> t2.NEXT_CONFIGURATION_NUMBER,
>
> t2.NEWEST_CONFIGURATIONSET_ID,
>
> t2.OLDEST_CONFIGURATIONSET_ID,
>
> t3.ID,
>
> t3.TEMPLATE,
>
> t3.SYSTEM_TEMPLATE,
>
> t3.ADMIN_ENABLED,
>
> t3.TEMPLATE_PROPERTIES_ID,
>
> t3.ENTITY_FACET_MANAGER_ID,
>
> t4.ID,
>
> t4.LAST_OBSERVED_PING,
>
> t4.SNMP_READ_TIMESTAMP,
>
> t4.LAST_OBSERVED_SNMP_WRITE,
>
> t4.LAST_OBSERVED_SNMP_READ,
>
> t4.PING_TIMESTAMP,
>
> t4.SNMP_WRITE_TIMESTAMP,
>
> t5.ID,
>
> t5.POWER_LED_STATE_TIMESTAMP,
>
> t5.LAST_OBSERVED_STATUS_LED_STATE,
>
> t5.MAINBOARD_VOLTAGE_STATUS_TIMESTAMP,
>
> t5.LAST_OBSERVED_MAINBOARD_VOLTAGE_STATUS,
>
> t5.STATUS_LED_STATE_TIMESTAMP,
>
> t5.LAST_OBSERVED_POWER_LED_STATE,
>
> t6.ID,
>
> t7.ID
>
> FROM CORE_V1.SNMP_DEVICE t9,
>
> CORE_V1.SNMP_DEVICE_IP t8,
>
> PKG_9145E_V1.CHASSIS_9145E_104_0_0 t7,
>
> PKG_9145E_V1.CHASSIS_9145E_BC04 t6,
>
> PKG_9145E_V1.CHASSIS_9145E t5,
>
> CORE_V1.AGENT_MANAGED_HARDWARE t4,
>
> CORE_V1.MANAGED_HARDWARE t3,
>
> CORE_V1.CONFIGURABLE_HARDWARE t2,
>
> CORE_V1.HARDWARE t1,
>
> CORE_V1.DEVICE_ENTITY t0
>
> WHERE
>
> (
>
>    (
>
>       (t8.IP = ?)
>
>       AND
>
>       (
>
>          (
>
>             (t7.ID = t0.ID)
>
>             AND
>
>             (
>
>                (t6.ID = t0.ID)
>
>                AND
>
>                (
>
>                   (t5.ID = t0.ID)
>
>                   AND
>
>                   (
>
>                      (t4.ID = t0.ID)
>
>                      AND
>
>                      (
>
>                         (t3.ID = t0.ID)
>
>                         AND ((t2.ID = t0.ID) AND (t1.ID = t0.ID))
>
>                      )
>
>                   )
>
>                )
>
>             )
>
>          )
>
>          AND (t0.DTYPE = 'CHASSIS_9145E_104_0_0')
>
>       )
>
>    )
>
>    AND ((t9.AGENT_MANAGED_HARDWARE_ID = t0.ID) AND (t8.SNMPDEVICE_ID = 
> t9.ID))
>
> ) 
>                                                                                     
                                            |true 
> |false|NULL
>
> 6ebdc076-0147-b12d-b252-000065089f97|APP 
>                                                         |SELECT * FROM 
> SYSCS_DIAG.STATEMENT_CACHE
>
>         |true |true |NULL
>
> c5bf806d-0147-b12d-b252-000065089f97|SYS |SELECT    
> M->allProceduresAreCallable(), M->allTablesAreSelectable(),
>
> M->supportsCatalogsInTableDefinitions(), 
> M->supportsCatalogsInIndexDefinitions(), 
> M->supportsCatalogsInPrivilegeDefinitions(), 
> M->supportsPositionedDelete(), M->supportsPositionedUpdate(), 
> M->supportsSelectForUpdate(),
>
> 2589c06b-0147-b12d-b252-000065089f97|CSEM |CALL SYSIBM.MetaData()
>
>                                                                                     
   |true 
> |true |NULL
>


Mime
View raw message