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

You can invalidate cursors by doing many more things than just computing statistics. Julian Dyke gave an excellent presentation on this at UKOUG that you can find on the web or you can view the list in the database with: desc gv$sql_shared_cursor

That said you don't mention a version number and give any indication of how you have determined that this is the issue or that it relates, necessarily, to the performance issues (you wrote "may no longer"). Can you provide some more details?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org