RE: Slow SQL performance
Date: Mon, 21 Apr 2008 12:39:25 -0700 (PDT)
Even commenting on the table used in the query will invalidate all the cursors referencing the table in the Shared Pool.
COMMENT ON MY_TABLE IS "simply invaliding Shared Pool";
"Allen, Brandon" <Brandon.Allen_at_OneNeck.com> wrote: 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.
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
http://www.freelists.org/webpage/oracle-l Received on Mon Apr 21 2008 - 14:39:25 CDT