You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bergquist, Brett" <BB...@canoga.com> on 2014/08/07 18:51:46 UTC
Question on why the statement cache is saying a statement is invalid
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
Re: Question on why the statement cache is saying a statement is
invalid
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Statements can be invalidated if Derby thinks they need recompilation, e.g.
- some DDL has touched the objects involved in the cached query (e.g.
new column added)
- much data has been inserted since it was compiled, so it might benefit
from a new plan. This can be disabled by switching off the statistics
daemon or its thresholds tweaked.
I think the cached statement will remain in the cache as invalid till
its needed again, at which time it will be recompiled and update in the
cache. I am not completely sure of the mechanics of this, however.
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
>
RE: Question on why the statement cache is saying a statement is
invalid
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Thanks all. I don't know what could invalidate it but I just did my test again, quiet database, prepared the statement, looked at the cache and all is well. Thanks for taking the time to provide some information.
Brett
-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com]
Sent: Thursday, August 07, 2014 2:09 PM
To: derby-dev@db.apache.org
Subject: Re: Question on why the statement cache is saying a statement is invalid
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
>>
>
Re: Question on why the statement cache is saying a statement is
invalid
Posted by Rick Hillegas <ri...@oracle.com>.
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
>>
>
Re: Question on why the statement cache is saying a statement is
invalid
Posted by "Dag H. Wanvik" <da...@oracle.com>.
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
>