Re: FULL SCANS

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Mon, 7 Aug 2000 12:48:42 -0400
Message-Id: <10582.113981@fatcity.com>

I did that once. I created a procedure that:

   looked for full table scans
   turned on tracing
   slept for minute or two
   turned off tracing and finally saved the name of the trace file, the timestamp and the session event (in this case 'db file scattered read') to a table.

The procedure was run in the job queue every 5 minutes and did catch a few full-table scans, but I got busy with other priorities and never followed through to see if I could find the query in the trace file and then to see if it could be optimized.

Here is the SQL that I used, (the sid and serial# were needed to turn on tracing and the spid was used to compute the name the trace file). CURSOR c1 IS
SELECT e.sid, s.serial#, spid, e.event
FROM v$session_event e, v$session s,

       v$process p

WHERE  e.event = 'db file scattered read' AND
       s.sid = e.sid AND
       p.addr = s.paddr AND
       TYPE = 'USER'

ORDER BY total_waits DESC;

"Libal, Ivo" <ivo.libal_at_knapp-systems.com> on 08/02/2000 09:15:01 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Chaim Katz/Completions/Bombardier)

Subject: FULL SCANS

Hello All
is it possible to check which tables are read by full scans? Is it possible to make some script to check and log such a tables? Do I have to search all execution plains and find full scans or is it possible to get it from some wait events - how to make it automatically not reading v$session_waits?
Do you have any experience with this problem? Thank you for your help
Ivo Libal

--
Author: Libal, Ivo
  INET: ivo.libal_at_knapp-systems.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Aug 07 2000 - 11:48:42 CDT