RE: High Number of Disk Reads ?

John,

Thanks for the input. It eventually helped me find me problem. In the words of Pogo, "We have met the enemy and they is us." The high disk reads were not from the users app at all, but rather from the audit table, AUD$. I moved it from the SYSTEM tablespace over a year ago and failed to recreate the proper indexes. The table grew enough that the resulting table scans slowed down their logon process.

Lesson to myself.

Thanks,

Mike

Michael Bond
OC-ALC\TILC
Oracle DBA
405 736-3840
DSN 336-3840 -----Original Message-----
Sent: Monday, August 05, 2002 2:13 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bond Mike A Contr OC-ALC/TILC
  INET: Mike.Bond_at_tinker.af.mil

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