Re: SHARED_POOL_SIZE Increase Makes a Bstat Little Worse - Why?
Date: 1997/07/15
Message-ID: <33cc0cf4.0_at_news1.ibm.net>
Hi,
>
Am I correct in interpreting these figures as:
you are doing a very large number of small inserts, possibly in a PL/SQL loop,
with a small and simple cross-table validation test.
<
The nightly batch run is all C programs using OCI to make sql stmt calls, NO
PL/SQL,
(that I know of unless that what OCI does).
I use maybe all bind input var.s! and a lot of bind output var.s too.
SELECT --+ INDEX_ASC( mfg.bom_tbl mfg.bom_bill_key )
ROWID
FROM mfg.bom_tbl
WHERE (bill_level_0 = :1 AND bill_level_1 = :2 AND bill_level_2 = :3 AND
bill_level_3 = :4 AND bill_level_4 = :5 AND bill_level_5 = :6)
ORDER BY bill_level_0, bill_level_1, bill_level_2, bill_level_3, bill_level_4,
bill_level_5
OR
SELECT * from mfg.bom_tbl WHERE ROWID = :1 FOR UPDATE NOWAIT
I even include the same bind input var.s even if not all are needed just to be able to reuse them more!
>
The stats you supply suggest that virtually every single statement you are
running is unique.
<
Most of our program sql statements should be extremely duplicated by design
for this very reason.
Have any other ideas? Could it not be using a shared sql area?
Another weird thing I don't understand. The next nightly run the numbers came back down, but I did not change anything! The sql area hits are still as bad though (62%). LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI ------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY 0 1 0 1 0 0 CLUSTER 0 1 0 1 0 0 INDEX 0 1 0 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 360913 .62 2269049 .875 6754 0 TABLE/PROCED 144027 .999 245386 1 17 0 TRIGGER 0 1 0 1 0 0And 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.
//------------------------------- // Stan Towianski // Senior Programmer/Analyst
// Cargill Detroit Corp.
// email: stantow_at_ibm.net Received on Tue Jul 15 1997 - 00:00:00 CEST