RE: Slow SQL performance

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: <sxmte@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • 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.

Regards,
Brandon


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.

--
//www.freelists.org/webpage/oracle-l


Other related posts: