Re: Drop of partition with update global indexes, now system is unsuable

james.gabe_at_gmail.com wrote:
> We normaly have the users log out of the app hitting this DB when we do
> partition maintenence but last night we let them stay in while doing a
> drop partition update global indexes. All SQL AREA in library cache is
> invalid. We have restarted the db to relaod all SQL but it loads right
> back at invalid. Users can connect but the system is just crawling and
> everyone gets timouts. The SQL AREA should only go invalid, from what I
> have read, "For example, if the optimizer statistics for a table were
> recomputed, then all SQL statements currently in the library cache at
> the time the recompute occurred would be invalidated, because their
> execution plans may no longer be optimal."
>
> Has anyone run into this issue before?
>
> --Jaga

"All SQL AREA in library cache is invalid" .. Would you mind explaining what you mean by this statement? How did you find out what you found out?

One can run statistics analyze with an option to not invalidate the sql. Thats a different point though. By what you state, some objects might have gone invalid. Did you find what these objects are and if there are invalid objects then did you try running utlrp.sql?

Try posting explicit details of what issue you are seeing and what action you took (did you just drop one table partition .. or did more than just that).

If required try tracing a user session which appears slow/hung. By timeouts you might be referring to application timeouts? If yes, then maybe your app does a lot of data caching which need to be warmed again after your database bounce.

Anurag Received on Thu Jan 18 2007 - 12:36:21 CST