Re: High Number of Disk Reads ?

Michael,

As you don't give the avg. row size for t2, are we to assume that table has not been analysed? Could be the source of your problem.

If not, in SQL*Plus, do SET AUTOTRACE ON or better still set TIMED_STATISTICS=TRUE and either set an event to generate a trace for the user, or ALTER SESSION SET SQL_TRACE=TRUE. Use TKPROF to analyse the trace file and it might give you a handle on what'sgoing on.

Cheers,

John Thomas

In message <F001.004AB725.20020805101824_at_fatcity.com>, Bond Mike A Contr OC-ALC/TILC <Mike.Bond_at_tinker.af.mil> writes
>Hi,
>
>I have a performance question. v8.1.6.3.8 NT4.0
>The following query performs 3335 disk reads. All tables are in a 64K
>Locally managed tablespace.
>
>Upon connection, the users app executes this once per user in table t2 and
>cause over 100K disk reads.
>
>
>What am I missing/misunderstanding?? Is this normal? It seems terribly
>high, even if no blocks are in the buffer when the query is executed.
>
>TIA.
>
>Mike
>
>
>Table t0 has 1 row with an avg row len of 31
>Table t1 has 13 rows with an avg row len of 13
>Table t2 has 39 rows
>
>SELECT t0.sftwr_evnt_grp_desc_tx,
> t0.sftwr_evnt_grp_key_id,
> t0.sftwr_evnt_grp_nm
>FROM gimms_admin.inf_sys_usr t2,
> gimms_admin.inf_sys_usr_evt_grp_rl t1,
> gimms_admin.sftwr_evnt_grp t0
>WHERE t2.inf_sys_usr_key_id = 25 AND
> t1.inf_sys_usr_key_id = t2.inf_sys_usr_key_id AND
> t0.sftwr_evnt_grp_key_id = t1.sftwr_evnt_grp_key_id
>
>
>
>Query to get # of disk reads ...
>SELECT
> SQL_TEXT,
> SORTS,
> EXECUTIONS,
> DISK_READS,
> BUFFER_GETS,
> ROWS_PROCESSED,
> OPTIMIZER_MODE
>FROM
> V$SQLAREA
>
>
>Michael Bond
>OC-ALC\LPCC
>Oracle DBA
>405 736-3840
>DSN 336-3840
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 
John Thomas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Thomas
  INET: john_at_toronto.demon.co.uk

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).