| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SHARED_POOL_SIZE Increase Makes a Bstat Little Worse - Why?
>
My first port of call would be to know exactly what the application
batch processes where doing, poorly written PL/SQL or a dodgey index is
most likely the cause.
<
They are all C programs using OCI calls with NO PL/SQL.
>
1) check v$sysstat and look at sorts(disk) if this is > 0 increase
SORT_AREA_SIZE. You have limited the amount of sort data to 1MB after
this is reached, expensive disk sorts must be done. Is there heavy index
drop/create in you batch jobs?
<
Doing it right now and looking at the report I have 0 sorts (disk)!
sorts (memory) 64 1197 sorts (disk) 64 0 sorts (rows) 64 66913
>
2) How are you rollback segments coping ? v$rollstat, switch off the
small ones or assign your batch transaction to a large rollback segment.
<
What does this tell you? What are you looking at? SQL> select * from v$rollstat;
USN EXTENTS RSSIZE WRITES XACTS GETS WAITS
OPTSIZE HWMSIZE SHRINKS
--------- --------- --------- --------- --------- --------- ---------
--------- --------- ---------
WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK --------- --------- --------- --------- --------------- --------- ---------
0 4 202752 1422 0 299 0
202752 0
0 0 0 0 ONLINE 3 17
1 3 31455232 14426867 0 11354 0
31455232 0
2 0 0 10956492 ONLINE 1 2638
2 4 41940992 36148445 0 21319 0
41940992 0
4 0 0 19832377 ONLINE 3 4386
3 5 52426752 29511965 0 169178 0
52426752 0
3 0 0 15629025 ONLINE 0 2139
4 4 41940992 25852028 0 18436 0
41940992 0
3 0 0 15627898 ONLINE 1 4480
>
3) Are your disks stripped? get them stripped. Ditch RAID if you are
using it and switch to mirroring the disk set.
<
We have EMC drives, mirrored.
>
4) Plan to recreate your database using a 4k DB block size ( 40% faster
than 2k)
<
I thought it already was 4k but I think not now.
SVRMGR> show parameter db_block_size
NAME TYPE VALUE ----------------------------------- ------- ------- db_block_size integer 2048
>
5) Consider multiple DBWR
<
I will look into this. Thanks.
Something weird I don't understand. The next nightly run the numbers came back down, but I did not change anything! And what is weirder are these numbers: I was just guessing that these session xx memory sizes had to do with the shared_pool_size because when I made it bigger they got bigger on the 13th. But,..., on the 14th they went back down EVEN THOUGH I left the shared_pool_size at the higher size. Are they not related?
July 10th - Original stat.s
rollback_segments rbs01, rbs02, rbs03, rbs04 sequence_cache_hash_buckets 10 sessions 225 shared_pool_size 80800100 sort_area_retained_size 1100100 sort_area_size 1100100 sort_direct_writes AUTO temporary_table_locks 225 timed_statistics TRUE transactions 247 transactions_per_rollback_segment 16
July 13th
rollback_segments rbs01, rbs02, rbs03, rbs04 sequence_cache_hash_buckets 10 sessions 225 shared_pool_size 200800100 sort_area_retained_size 1100100 sort_area_size 1100100 sort_direct_writes AUTO temporary_table_locks 225 timed_statistics TRUE transactions 247 transactions_per_rollback_segment 16
July 14th
rollback_segments rbs01, rbs02, rbs03, rbs04 sequence_cache_hash_buckets 10 sessions 225 shared_pool_size 200800100 sort_area_retained_size 1100100 sort_area_size 1100100 sort_direct_writes AUTO temporary_table_locks 225 timed_statistics TRUE transactions 247 transactions_per_rollback_segment 16
The database gets shutdown immediate and restarted each night before the night run.
(posted newsgroup reply and sent email)
stantow_at_ibm.net Received on Tue Jul 15 1997 - 00:00:00 CDT
 |
 |