db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Question on why the statement cache is saying a statement is invalid
Date Thu, 07 Aug 2014 18:09:03 GMT
On 8/7/14 10:42 AM, Dag H. Wanvik wrote:
> If it appears to be invalid right after you compile it, I don't know 
> how that could happen. Rick, Knut?
The invalid statement is a gigantic piece of sql which depends on a lot 
of objects. The invalidation of any of those objects could have made the 
statement invalid.
>
> 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