RE: High Number of Disk Reads ?

Thanks for the ideas. I ran with SQL_TRACE and it verified what I was saying.

I guess what I left out of the previous message was that there are two instances on the same machine with twin schemas and data. Other than Archive Logging and db_buffer size, the parms for the instances are the same.

Yet the same query on one instance hits the buffer about 4000 times and the disk 3351 while on the other it hits the buffer < 500 times and the disk 8.

I have tried to "alter table cache" on the high-hits instance to try and keep the tables in the buffer, and that did not help.

The high-hits instance is the busier one and I understand that blocks read into the buffer might get moved out, but I don't think the database is THAT active. Besides, the tables I am hitting are tiny. I increased buffer to 32K blocks. And why is the buffer being hit so much more?

What am I missing?

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