RE: Slow SQL performance
Date: Mon, 21 Apr 2008 11:30:29 -0700
The quickest and most focused way I know of to flush (aka invalidate) a query out of the shared pool is to execute a GRANT SELECT on one of the tables used in the query. This will also invalidate all other cursors dependent on that table, but at least it's better than flushing the entire shared pool. I've submitted an ER to Oracle to provide a procedure for invalidating a single specific cursor, but I'm not aware of any way to do it yet. I'm curious what the point of having histograms is on a table with no indexes? And how could statistics make a difference if the only option is to do a full table scan? I'd suggest you run sql_trace (or just use dbms_xplan) on the query in both systems to help identify the difference that is causing the problem.
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.Received on Mon Apr 21 2008 - 13:30:29 CDT