Re: Performance issue after creating higher block size tablespace - a further question
Date: Mon, 1 Mar 2010 10:48:15 -0500
Gurus, have a look at the following statistics:
10:38:31 SYS_at_cldprod2 > select block_size, BUFFER_BUSY_WAIT, DB_BLOCK_GETS, PHYSICAL_READS, PHYSICAL_WRITES from v$buffer_pool_statistics;
BLOCK_SIZE BUFFER_BUSY_WAIT DB_BLOCK_GETS PHYSICAL_READS PHYSICAL_WRITES
---------- ---------------- ------------- -------------- --------------- 2048 0 0 0 0 8192 1032171136 114987203 3.0442E+10 27422279 4096 44045127 5118516066 9281895243 353200354 16384 0 0 0 0
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?
Is it due to db_8k_cache_size set to 64 MB?
On Mon, Mar 1, 2010 at 10:30 AM, Saad Khan <saad4u_at_gmail.com> wrote:
> 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.
> On Fri, Feb 26, 2010 at 1:16 PM, girlgeek <girlgeek_at_live.com> wrote:
>> I believe that Saad's problem got solved yesterday, but it gave me further
>> 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.
>> 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,
>>> *From:* oracle-l-bounce_at_freelists.org on behalf of Saad Khan
>>> *Sent:* Thu 2/25/2010 12:39 PM
>>> *To:* Mark W. Farnham
>>> *Cc:* oracle-l_at_freelists.org
>>> *Subject:* Re: Performance issue after creating higher block size
>>> No the query plan didnt change, the cost changed very marginally.
>>> Yes, i rebuild the indexes after moving the tables.
>>> This is 10g, so it gathers stats on base of skewness. The last_analyzed
>>> column for this tables shows yesterday.
>>> Its the joins that are killing the query i think. I'm going to send the
>>> queries to list .
>>> DB_CACHE_SIZE was set to 0 as the sga_target is set here which is 1.7 GB
>>> (its a 32-bit installation)
>>> I couldnt understand your last message. May be if you elaborate it more.
>>> On Thu, Feb 25, 2010 at 12:10 PM, Mark W. Farnham <mwf_at_rsiz.com <mailto:
>>> mwf_at_rsiz.com>> wrote:
>>> Did your query plan change?
>>> Did you also rebuild the indexes after you moved the tables?
>>> 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
>>> 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.
>>> *From:* oracle-l-bounce_at_freelists.org
>>> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Saad Khan
>>> *Sent:* Thursday, February 25, 2010 11:24 AM
>>> *To:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
>>> *Subject:* Performance issue after creating higher block size
>>> Hi gurus.
>>> 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.
>>> Any suggestions?
>>> Thanks in advance.