So many gets for a single row? Your query must be much more
complex than simple select x from y where id = xxx (and id is
primary/unique key)... Anyway, there are way too many physical
reads on your production db. If this query is critical, try assigning
all related tables and their indexes to the KEEP pool so that they
are always cached. db_block_buffers occupying just 160 megs on
a system like yours also sounds inappropriate, so increasing it may
improve performance significantly by reducing physical I/O. And by
dividing it into KEEP, RECYCLE and default pools and assigning
critical often queried objects to the KEEP pool you will most probably
improve the situation further.
Also, look for ways to reduce I/O - 3000+ gets for single row result
isn't unusual, but it's a lot, Oracle's probably doing a lot of sorting/
filtering before it comes up with that single row you're after. Can you
show the query plan? Maybe rewriting the query would do much
better job than playing with caching.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"jyou" <member_at_dbforums.com> wrote in message news:2279957.1039814950_at_dbforums.com...
>
> Thanks,
>
> I set the trace on in sqlplus and redo the query, following is the
> statistics of the query on our production server:
>
> Statistics
> ----------------------------------------------------
> recursive calls
> 54 db block gets
> 2989 consistent gets
> 1596 physical reads
> redo size
> 232 bytes sent via SQL*Net to client
> 316 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> sorts (memory)
> sorts (disk)
> 1 rows processed
>
> While the statistics in my pc is:
> Statistics
> ----------------------------------------------------
> 18 recursive calls
> 57 db block gets
> 3157 consistent gets
> 1 physical reads
> redo size
> 434 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 2 sorts (memory)
> sorts (disk)
> 1 rows processed
>
> I noticed the "physical reads" in the production server is pretty big.
> I also checked the "db_block_buffer" is 20,000, db_block_size=8096.
> Since the RAM in that server is 10G, maybe increasing the
> db_block_buffer
> will improve the performance?
>
> --
> Posted via http://dbforums.com
Received on Sun Dec 15 2002 - 05:50:24 CST
