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

Stop trying to compare the two databases. They are not the same.
Instead try to figure out what is an acceptable time from your database
server for that specific query result to get to your java.
Then examine all the timing differences. i.e. how long from java to
listener - how long for query to execute and so forth.

Once you have determined this and you have a solid idea of what time is
being spent in which component then you can think that perhaps .5 sec is
acceptable for that many physical IOs
"jyou" <member@dbforums.com> wrote in message
news:2279957.1039814950@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

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@dbforums.com> wrote in message news:2279957.1039814950@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