RE: wait events

Ok let me explain this problem in more detail. We have 2 development environments A and B. The only difference is that Env B has All of Env A's objects + some objects of its own. Thus, Env B is a super-set of Env A. The platform is Digital Unix with Oracle 8.0.4.

Comparing the Init.ora params, these are the ones which are different:

Initialization Parameters Env B Env A

db_block_lru_latches                4         1
db_file_multiblock_read_count       64         8
shared_pool_reserved_size  4500000    250000
shared_pool_size         90000000   5000000
sort_area_retained_size          1002400     65536
sort_area_size                  2000000    524288
--------------------------------------------------

By the looks of it Env B seems better tuned. Both are running under CHOOSE with exactly same tables being analyzed on each environment.

A Batch job which involves only Env A's objects, takes 20 Mins in Env A against 4 Hrs in Env B. This is where I noticed a large waits for 'db file seqential read' and 'db file scattered read' using this SQL:

select event
, total_waits
, round(time_waited / 100) time_waited
, round(average_wait / 100) average_wait
from v$system_event
order by total_waits desc
/

Event                      Waits  Waited(sec) Avg.wait
------------------------------------------------------
db file sequential read     1,467,902   2,612     0
SQL*Net message to client     553,059       4     0
SQL*Net message from client   553,045 452,801     1
db file scattered read        361,710   4,800     0
------------------------------------------------------

Any reason why there's so much difference in the response time.

Thanks,
Deepak

--openmail-part-092ae953-00000001

Content-Type: application/ms-tnef; name="WINMAIL.DAT"
Content-Disposition: attachment; filename="WINMAIL.DAT"
Content-Transfer-Encoding: base64

eJ8+IspEAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5N aWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEDkAYADAAAAAEAAAADABcAAQAA ABwAAQOQBgAMAAAAAQAAAAMANgAAAAAAOgABBIABABAAAABSRTogd2FpdCBldmVudHMAWwUB A5AGAAwAAAABAAAACwACAAEAAAAPAAEDkAYAEAAAAAEAAABAADkAgKsRrorDvwFxBAEDkAYA JAAAAAEAAAAeAEIAAQAAABIAAABKb2huIEouIEthbmFnYXJhagAAADsGAQOQBgAQAAAAAQAA AEAASAAgZ5CwisO/AV0EAQOQBgAgAAAAAQAAAB4AcAABAAAAEAAAAFJFOiB3YWl0IGV2ZW50 cwD7BQEDkAYAKAAAAAEAAAACAXEAAQAAABYAAAABv8OKrg4Ii5sFL3AR1JNFAGCXmRGzAAA4 CQEDkAYADAAAAAEAAAADAAYQVU75Cb8BAQOQBgAMAAAAAQAAAAMABxDfCQAAAwEBA5AGAHgA AAABAAAAHgAIEAEAAABlAAAAREVFUEFLLEhPV01BTllDUFVTRE9ZT1VIQVZFP1RIRVZBTFVF T0ZEQkJMT0NLTFJVTEFUQ0hFU1NIT1VMRERFUEVORE9OTlVNQkVST0ZDUFVTREJCTE9DS0xS VUxBVENIRVNTUAAAAADfHQEDkAYADAAAAAEAAAADABAQAAAAACQAAQOQBgAMAAAAAQAAAAMA ERAAAAAAJQABA5AGABAAAAABAAAAQAAHMODXxqmJw78BqgUBA5AGABAAAAABAAAAQAAIMODX xqmJw78BqwUBA5AGACAAAAABAAAAAgELMAEAAAAQAAAAApuLCHAv1BGTRQBgl5kRszAGAQOQ BgAMAAAAAQAAAAMA3j+vbwAAPwIBA5AGACQAAAABAAAAAwDGggggBgAAAAAAwAAAAAAAAEYA AAAAEIUAAAAAAAAVAwEDkAYAJAAAAAEAAAADAMeCCCAGAAAAAADAAAAAAAAARgAAAAABhQAA AAAAAAcDAQOQBgAkAAAAAQAAAAMAyIIIIAYAAAAAAMAAAAAAAABGAAAAABGFAAAAAAAAGAMB A5AGACQAAAABAAAAAwDJggggBgAAAAAAwAAAAAAAAEYAAAAAUoUAAOMVAABSBAEDkAYALAAA AAEAAAAeAMqCCCAGAAAAAADAAAAAAAAARgAAAABUhQAAAQAAAAQAAAA4LjUAGAQBA5AGACQA AAABAAAACwDLggggBgAAAAAAwAAAAAAAAEYAAAAABoUAAAAAAAAYAwEDkAYAJAAAAAEAAAAL AMyCCCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAACEDAQOQBgAkAAAAAQAAAAMAzYIIIAYA AAAAAMAAAAAAAABGAAAAABiFAAAAAAAAJAMBA5AGACwAAAABAAAAHgDOggggBgAAAAAAwAAA AAAAAEYAAAAANoUAAAEAAAABAAAAAAAAAGADAQOQBgAsAAAAAQAAAB4Az4IIIAYAAAAAAMAA AAAAAABGAAAAADeFAAABAAAAAQAAAAAAAABiAwEDkAYALAAAAAEAAAAeANCCCCAGAAAAAADA AAAAAAAARgAAAAA4hQAAAQAAAAEAAAAAAAAAZAMBA5AGACQAAAABAAAACwDRggsgBgAAAAAA wAAAAAAAAEYAAAAAAIgAAAAAAAAeAwEDkAYAJAAAAAEAAAALANKCCyAGAAAAAADAAAAAAAAA RgAAAAAFiAAAAAAAACQDAQOQBgAUAAAAAQAAAB4APQABAAAAAQAAAAAAAABeAAEDkAYADAAA AAEAAAADAIAQ/////5AEAQkABAACAAAAAAAAAAEDkAYADAAAAAEAAAALACMAAAAAAC8AAQOQ BgAMAAAAAQAAAAsAKQAAAAAANQABBJAGABAEAAACAAAAEgAAAAMAADADAAAACwAPDgAAAAAC Af8PAQAAAEcAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAAQBvcmFjbGUtbEBmYXRjaXR5LmNv bQBTTVRQAG9yYWNsZS1sQGZhdGNpdHkuY29tAAAeAAIwAQAAAAUAAABTTVRQAAAAAB4AAzAB AAAAFQAAAG9yYWNsZS1sQGZhdGNpdHkuY29tAAAAAAMAFQwBAAAAAgH5DwEAAABHAAAAAAAA AIErH6S+oxAZnW4A3QEPVAIAAAEQb3JhY2xlLWxAZmF0Y2l0eS5jb20AU01UUABvcmFjbGUt bEBmYXRjaXR5LmNvbQAAAwD+DwYAAAAeAAEwAQAAABcAAAAnb3JhY2xlLWxAZmF0Y2l0eS5j b20nAAACAQswAQAAABoAAABTTVRQOk9SQUNMRS1MQEZBVENJVFkuQ09NAAAAAwAAOQAAAAAL AEA6AAAAAAMAcToAAAAAHgD2XwEAAAAVAAAAb3JhY2xlLWxAZmF0Y2l0eS5jb20AAAAAAgH3 XwEAAABHAAAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAEAb3JhY2xlLWxAZmF0Y2l0eS5jb20A U01UUABvcmFjbGUtbEBmYXRjaXR5LmNvbQAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAA AAAAAAMSAAAAAwAAMAQAAAALAA8OAAAAAAIB/w8BAAAASQAAAAAAAACBKx+kvqMQGZ1uAN0B D1QCAAABAHNoYXJtYWtkZWVwQHlhaG9vLmNvbQBTTVRQAHNoYXJtYWtkZWVwQHlhaG9vLmNv bQAAAAAeAAIwAQAAAAUAAABTTVRQAAAAAB4AAzABAAAAFgAAAHNoYXJtYWtkZWVwQHlhaG9v LmNvbQAAAAMAFQwCAAAAAgH5DwEAAABJAAAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAEQc2hh cm1ha2RlZXBAeWFob28uY29tAFNNVFAAc2hhcm1ha2RlZXBAeWFob28uY29tAAAAAAMA/g8G AAAAHgABMAEAAAAYAAAAJ3NoYXJtYWtkZWVwQHlhaG9vLmNvbScAAgELMAEAAAAbAAAAU01U UDpTSEFSTUFLREVFUEBZQUhPTy5DT00AAAMAADkAAAAACwBAOgAAAAADAHE6AAAAAB4A9l8B AAAAFgAAAHNoYXJtYWtkZWVwQHlhaG9vLmNvbQAAAAIB918BAAAASQAAAAAAAACBKx+kvqMQ GZ1uAN0BD1QCAAABAHNoYXJtYWtkZWVwQHlhaG9vLmNvbQBTTVRQAHNoYXJtYWtkZWVwQHlh Received on Thu May 18 2000 - 11:31:23 CDT