Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> *Very* high 'Fetch/elapse' cell in TKPROF report: cause ?

*Very* high 'Fetch/elapse' cell in TKPROF report: cause ?

From: Spendius <spendius_at_muchomail.com>
Date: 26 Aug 2004 04:59:05 -0700
Message-ID: <aba30b75.0408260359.71281f76@posting.google.com>


Hi,
We're experiencing very bad response times on a DB, this slowlyness is also reflected on queries against the data dictionary
>select segment_type,count(1),sum(bytes) from
>dba_segments where owner='XXX' group by segment_type;
gives
call count cpu elapsed disk query current rows ------- ------ ----- ------- ------- ------- ------- ----- Fetch 2 0.58 3.76 279 8018 0 4 or
>select distinct object_type from dba_objects
>where owner='XXX' order by 1;

call count cpu elapsed disk query current rows ------- ------ ----- ------- ------- ------- ------- ----- Fetch 4 0.14 1.61 0 350 0 13

We're in 9i.
What I can't understand is that on the same server we have 5 other 9.2 databases with similar config. params (cf. below) that do not behave this way, and in particular we have the SAME database running in 8i on the same machine, where SELECTs like those above respond immediately.

Where do these pretty high fetch durations come from ? Where should we look ?

Thanks.

SQL> show sga
Total System Global Area 1079217616 bytes

Fixed Size                   732624 bytes
Variable Size             889192448 bytes
Database Buffers          188743680 bytes
Redo Buffers                 548864 bytes

Optimizer-related params values (the SAME as on the 8i version except for the block size):

cpu_count                       12
cursor_sharing                  EXACT
db_block_buffers                23040
db_block_size                   8192
db_file_multiblock_read_count   32
db_writer_processes             1
fast_start_io_target            0
fast_start_parallel_rollback    LOW
hash_area_size                  1048576
hash_join_enabled               TRUE
large_pool_size                 67108864
log_buffer                      262144
log_checkpoint_interval         10000
log_checkpoint_timeout          1800
optimizer_dynamic_sampling      1
optimizer_features_enable       9.2.0
optimizer_index_caching         50
optimizer_index_cost_adj        25
optimizer_max_permutations      2000
optimizer_mode                  CHOOSE
partition_view_enabled          FALSE
query_rewrite_enabled           false
query_rewrite_integrity         enforced
shared_pool_size                67108864
sort_area_size                  3145728
star_transformation_enabled     FALSE
Received on Thu Aug 26 2004 - 06:59:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US