Query Cache Behavior

[Former Member]

Hi Experts,

I'm running 12.0.5, and have enjoyed 6 months or more of uptime for our production server, until Thursday when an electrical strom coupled with an odd occurance with our UPS system brought it to an abrupt halt.

Dev and Test environments came up OK, however the icon related to the J2EE server on production remained yellow. We were unable to access MII, or NWA for that matter.

I handed the problem over to our Basis team, who couldn't figure it out then passed a note to SAP. While waiting for a response, I decided to do some more poking around, as it was now 8:00pm Friday night and any hope of doing anything over the weekend (other than work) was quickly fading.

Poking around in the NW database, I found that the XMII_QUERYCACHE table had >2.5M rows (that seemed odd). Initital attempts to Truncate the table were unsuccessful (something about resources and NOWAIT, should have captured that error), however I was able to Truncate by shutting down the server, then executing just as the database was starting, and VOILA!, NW and MII came online.

My assumption is that either the table is read on startup, or perhaps an attempt is made to 'delete' all rows on startup (delete would attempt to build a rollback segment in order to execute), is that the case? Also, are 'expired' cache entries supposed to be hanging around?

I found that I have a single frequently executed query that was the culprit, query caching was enabled with a duration of 2Hours (many jobs use this query). I'll correct that situation, but I wanted to try and correct the root cause.

Thanks,

Rod

[jcgood25 · Accepted solution]

Rod,

Please enter a support ticket so that DEV can look into it further. I don't see any System Jobs that indicate cleanup in this table space, and I would assume with the size of your table that you had tons of expired cache objects. 11.5 had one, but 12.0 and 12.1 don't seem to indicate a background task for managing this area.

I assume the workaround URL will do the trick (if NW is running of course) but getting it addressed should save other customers the weekend concerns that were in front of you on Friday night @ 8.

Regards,

Jeremy

[Former Member · Accepted solution]

Manoj,

Your help is appreciated, however you did not specifically address the questions in my post, which were not related to a way to clear the cache manually (no points for you).

Jeremy,

Thanks, I'll enter a ticket. A bit surprised that others haven't noticed this behavior.

Rod

[Former Member · Accepted solution]

I entered a ticket and just received a resolution from SAP, as follows..

We have added this feature in both MII 12.0 and MII 12.1.
The changes will be available with next SP release on SAP Service
Marketplace.
MII 12.0 SP12 will be available on 15-Dec-2010.

My assumption is that there will be a system job that clears out expired query cache rows at some frequency, nightly maybe.

Wanted to update the thread with the resolution in case anyone was having the same problem.

Rod

[Former Member]

Hi Rod,

Noramally we used following URL for query caching.

http://<servername:port>/XMII/Illuminator?Service=QueryCaching&Mode=ClearCache

Please check previous SDN posts for more details

Regards,

Manoj Bilthare