The statistics in V$BUFFER_POOL_STATISTICS are cumulative since the
instance started. The fact you still see numbers for an 8K buffer pool
does not mean that it is "active", it just means that it was there. If
you see the numbers for the 8K buffer pool incrementing, then that
would indicate that it is still somehow "active", but you would have to
watch the view over time to see that.

Buffer pools can come and go. V$ views that are cumulative since
instance startup are just that -- cumulative since instance startup.
They are not going to "clear" themselves because you're no longer using
something that you were just using.

When I've removed the tablespace with 8K, why is it still showing this
many buisy buffers and block gets for 8K? Yes I didnt recycle the
database but how can I make it flush the 8k blocks from SGA and give
that memory to 4K blocks?

No the problem didnt resolve but since I needed to do something very
urgently, I reverted the 8k thing after reading all that which Tom Kyte
has written and some of you suggested as well. So now its back to 4k.

But that has done some reorg but I dont think that will resolve the
original performance issue. The "do something" song is still being
drummed on my head.

I
believe that Saad's problem got solved yesterday, but it gave me
further thought.

Saad said that the application is processing 1 row at a time. Oracle
operates in blocks. Is it possible that some of his increase in time
when he increased the block size from 4K to 8K could be simply in the
time needed to read/write the larger number of bytes for each row
processed? Of course, I am assuming that each single row will require
that a new block be read/written. I am ignoring the fact that some of
the blocks will already be in cache. Guru's what are your answers to
this further question.

Thanks,
Claudia

Oliver Jost wrote:

Just some two-bits on this one. If you are doing massive inserts are
you spending a lot of time allocating space? If so, your rowcache may
be very busy allocating more space. You could pre-allocate some space
to the segment and increase the size of the next to accommodate future
growth.
Good luck,
Oliver

Gather statistics? Are they a big change from the last time you
gathered statistics?

Is the new tablespace in files that occupy comparable underlying
volumes in terms of I/Os supported per unit time?

Is your storage in some flavor of SAME, or were the tables being
selected from formerly on independently operating units of i/o
(especially from the insert target) and now you’ve lumped them all
together?

What was your db_cache_size before?

Are you memory lean on the machine and using filesystems? Have you
robbed the OS of file caching space by adding to the SGA size?

This is a very critical app and I dont want to rebuild "whole"
thing. Moving a bunch of tables is entirely different ofcourse.

Those are all bits of a partial change analysis you might do, not
that you’ve stepped in it. If one or more of them is on target
(measure, don’t guess) then you might have a shortcut out of your
problem. Others might add to the list.

Now if you had a time machine, I’d say get in it and measure
things to evaluate what (if any) performance benefit there was to
be expected if you could get i/o service time to zero by moving to
8K. Then, if that idealized ceiling of possible benefit was
significant, figure what the likely benefit was if everything
meshed in your favor with no side effects. Then, if that still
seemed worthwhile, plan and engineer the move so that you ruled
out in advance negative side effects. (And I’m wondering why not
rebuild the whole thing at 8K if the database block size was
measured to predict an advantage.)

So what to really do now? See where the time is going. One often
useful bit of information is routing the output of the select to
dev/null and seeing how long that takes. If the lion’s share of
your time is in the select, fix that. Likewise, if you queue up
the results of the select in a single table and just select from
there and insert into the destination, does that reveal a
bottleneck on the insert side?

Before you would move back, you would want to have some evidence
that moving back would eliminate some problem. Unless of course
the urgency now is such that just getting back where you were
right away is more important than minimizing the amount of work to
reach better performance. Then you could pretend you went through
the time machine, figure out where your time is going and attack
the problem from that standpoint.

I've a production database running on oracle10.2.0.3 at SUSE linux
10. The default DB_BLOCK_SIZE for the database is 4K.

There was a performance complain coming from the users and
developers asked me to look into that. They particularly
complained about one stored procedure that was taking too much
time. Now when I looked into the stored proc, I saw the insert
statement in one particular table which is something more than 4
million rows while selecting from a bunch of other tables.

So what I did, I created a new tablespace with the db_block_size
8K and moved all the tables that were used in that SP in the new tbs.

And guess what, the new response came after that showed its taking
almost double the time as it was taking earlier. The AWR report
shows a lot of user IO activity and the tablespace that is hit
most is the new one. Now is it due to the different block size for
this new tablespace? Is Oracle finding it hard to manage 8k blocks
inside the SGA designed for 4K originally?

The db_cache_size is set to 8192 and db_8k_cache_size is also set
to 8192.

Is there any other step I can take? I dont want to revert it back
to 4k , I think it should work.