Hi all
The following problem, which I am facing right now may dig up an oracle v7 issue.Well that's for you all to decide:

It started with the old story: users complaining db's too slow db's too slow....
A fifteen minute interval statspack report during peak time revealed the following top timed events
Code:
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue                                             6,816       5,248    24.38
log file sync                                      16,825       4,178    19.41
db file sequential read                           586,895       3,693    17.16
CPU time                                                        1,699     7.89
local write wait                                    2,412         960     4.46
          -------------------------------------------------------------
The following is the cache size for the database:
Code:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     1,344M      Std Block Size:        16K
           Shared Pool Size:     1,344M          Log Buffer:     5,120K
On querying v$enqueue_stat I found the following:
Code:
EQ_TYPE	TOTAL_REQ#	TOTAL_WAIT#	CUM_WAIT_TIME

CI	297393818	124372109	46706495314
HW	559099673	14279318	99242336
TC	185909	36904	50413500
CF	17088411	70873	38628381
WL	103073	840	23847996
SQ	957295	156528	607980
CU	60813476	482	2989
PS	2376	92	1741
RO	6296	14	707
DV	40357	4	281
JD	4004496	4	149
Too high value for "CI" lock type.
From a JL article:
Extent checkpoints

When a Parallel Query runs in newer versions of Oracle, it has the option to use direct reads, bypassing the SGA completely (there is an exception to this, which will appear in an article on small tables and cache buffering). In general this is a good thing; however to avoid the risk of reading a block from disc which is OLDER than a block in the SGA, all blocks that are likely to be addressed by the query have to be flushed to disc before the query starts. This is achieved by a process called the Extent-based Checkpoint - a call goes to DBWR for every extent that the query may cover, and DBWR hunts out any dirty blocks from that extent in the SGA, and writes them to disc
This does not usually take much time, but if you have a large number of buffers, DBWR has to work through a large number of hash-chains checking for dirty blocks which have not yet been moved to the dirty list. If there are a lot of extents then the process has to be repeated a large number of times
From Oracle 8 onwards oracle addresses this issue by object based checkpoints instead of extent based checkpoints.
But on the footnote of the article:
Footnote:

The same extent-based checkpointing takes place when you drop or truncate an object, and with a large db_block_buffer the checkpointing can take more time than all the updates on the space management tables
Yes there are some regular truncate operations that goes on the database.
Am I facing the same thing as mentioned over here(db version:9204)?
Will it be resolved if I deecrease the db_cache_size?
here is an output from the v$db_cache_advice:

Code:
E_SIZE	SIZE_FACTOR	READ_FACTOR	READS

128	0.0952   0.3994	 1076192705
256	0.1905	 0.1899	 511607412
384	0.2857	 0.1188	 320188770
512	0.381	 0.0693	 186816120
640	0.4762	 0.0257	 69215411
768	0.5714	 1.1629	 3133396490
896	0.6667	 1.1252	 3031691830
1024	0.7619	 1.0896	 2935944847
1152	0.8571	 1.0538	 2839524795
1280	0.9524	 1.0176	 2741975344
1344	1	 0.9991	 2692158150
1408	1.0476	 0.9813	 2644203670
1536	1.1429	 0.9449	 2545878078
1664	1.2381	 0.9117	 2456648412
1792	1.3333	 0.8809	 2373611216
1920	1.4286	 0.8533	 2299137652
2048	1.5238	 0.8277	 2230140529
2176	1.619	 0.8042	 2166888975
2304	1.7143	 0.7827	 2109013064
2432	1.8095	 0.7626	 2054877563
2560	1.9048	 0.7389   1990964437
Quite unusual
Any help on what should be the cache size (640M?)