Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> *Very* high 'Fetch/elapse' cell in TKPROF report: cause ?
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 FALSEReceived on Thu Aug 26 2004 - 06:59:05 CDT