Re: Slow SQL, too many logical reads ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 4 May 2007 19:03:11 +0100
Message-ID: <v8udnZ106fxZ6KbbnZ2dnUVZ8qeknZ2d@bt.com>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1178039702.039366.18770_at_o5g2000hsb.googlegroups.com...

---

ALTER SESSION SET STATISTICS_LEVEL=ALL; (Run your query)

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));

---

For instance, ALLSTATS (note, might want to flush the shared pool first so that Starts indicates the number of times that portion of the SQL statement executed):

Charles,

There is an option for display_cursor which is 'ALLSTATS LAST'. this give you the stats from the last execution of the query, and makes the shared pool
flush unnecessary.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Fri May 04 2007 - 13:03:11 CDT