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?
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
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.
//------------------------------- // Stan Towianski // Senior Programmer/Analyst