Re: SHARED_POOL_SIZE Increase Makes a Bstat Little Worse - Why?
Date: 1997/07/15
Message-ID: <33cbfebb.0_at_news1.ibm.net>
>
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
session pga memory 1799272 163570.18 105839.53 129.16
session pga memory max 1799272 163570.18 105839.53 129.16
session uga memory -135040 -12276.36 -7943.53 -9.69
session uga memory max 988744 89885.82 58161.41 70.97
.
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
session pga memory 4578640 47202.47 89777.25 268.02
session pga memory max 4578640 47202.47 89777.25 268.02
session uga memory 1029720 10615.67 20190.59 60.28
session uga memory max 2608424 26890.97 51145.57 152.69
.
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
session pga memory 1731064 157369.45 101827.29 106.8
session pga memory max 1731064 157369.45 101827.29 106.8
session uga memory -137136 -12466.91 -8066.82 -8.46
session uga memory max 996272 90570.18 58604.24 61.46
.
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 CEST