Question How much free information is too much?

[tamhas]

Jean has been working on this for a long time, so I wouldn't hold your breath. Which said, my understanding is that someone does look at it fairly promptly, but unless that happens to be at a point where they are picking features for the next release and it makes the cut, there is no other status to change it too. However, they did get quite a few into 11.6, so let's hope that this approach continues.

 

[RealHeavyDude]

Thanks for the explanations. Frankly, I did hope that it would be that easy to monitor the B2. Nevertheless I am still looking for a reasonable way to monitor the B2. As I have no access to the production database I need to be able to put a monitoring in place that throws an alert when B2 is not large enough. That way a ticket gets automatically created which allows me to request the operator to bump the B2 ...

@Rob & @TheMadDBA: Thanks for you valuable insights

Thanks, RealHeavyDude.

 

[Rob Fitzpatrick]

That way a ticket gets automatically created which allows me to request the operator to bump the B2 ...

There are bugs in certain releases with proutil increaseto -B... Which release are you running?

 

[Rob Fitzpatrick]

As I have no access to the production database I need to be able to put a monitoring in place that throws an alert when B2 is not large enough.

I would say monitor ABP OS reads and alert when they reach 90% of -B2.

 

[Rob Fitzpatrick]

Thanks for you valuable insights

You can find some more info in my ABP presentation here:
Ontario PUG DBA Day 2015

Also, Tom has a great presentation on ABP:
http://dbappraise.com/ppt/B2Buzz.pptx

 

[RealHeavyDude]

We are currently running on OE11.3.1 Solaris SPARC 64Bit.

Currently I am bumping the B2 in a test environment until after a DB analyse the B2 is not exhausted.

Thanks, RealHeavyDude.

 

[Rob Fitzpatrick]

11.3.1 is included in the releases that has a bug related to exhausting -B2 when object-level assignments to ABP are in use. In that case, the LRU replacement policy is not enabled. Instead, a FIFO mechanism is enabled, which is even worse. And in that case, increasing the size of -B2 online does not help to prevent LRU2 latch contention. The only relief is to restart the DB with a larger -B2.

This bug is fixed in 11.3.2 and later.

 

[RealHeavyDude]

Thanks for the information. That might also explain me being puzzled Question - B2 Puzzle

Maybe I should wait until we upgrade to 11.5 until I use the B2 on a production system ...

Thanks, RealHeavyDude.

 

[RealHeavyDude]

I just figured that the advisor.p sets the buffer pool to the primary buffer pool whenever the storage object resides in a type I storage area. That usually is true for the schema area. I did move the contents of the schema area to the alternate buffer pool with proutil enableB2 though.

Is there a rule that says that only storage objects residing in a type II storage area can be moved to the alternate buffer pool or are there any reasons why not to move storage objects residing in a type I storage area to the alternate buffer pool.

Thanks in Advance, RealHeavyDude.

 

[TomBascom]

Type 1 areas are "all or nothing". You cannot designate individual objects to B2.

With type 2 areas you can put the whole area in B2 or you can pick and choose objects in the area.

 

[TheMadDBA]

Part of the advisor is also telling you to move those objects into Type II areas with potentially different RPB settings too

 

[RealHeavyDude]

Thanks for that information. That does make sense to me. Is there any compelling reason NOT to move the schema with proutil -C mvsch to a dedicated type II storage area?

The reason I am asking is because it appears to me that the proutil -C enableB2 "Schema Area" does not survice backup/restore.

Thanks in Advance, RealHeavyDude.

 

[Cringer]

Thanks for that information. That does make sense to me. Is there any compelling reason NOT to move the schema with proutil -C mvsch to a dedicated type II storage area?

The reason I am asking is because it appears to me that the proutil -C enableB2 "Schema Area" does not survice backup/restore.

Thanks in Advance, RealHeavyDude.

You won't be able to move the schema into Type II. You will have to put the enableB2 step into your DR/Backup restore plan.

 

[RealHeavyDude]

Hi Cringer,

I just figured out just that. Should have read the documentation on that one.


Thanks, RealHeavyDude

 

[TheMadDBA]

I guess PSC considers having the schema area in Type II low hanging fruit... I wish they would just go ahead and fix that so we can all eliminate Type I areas completely.

Everything else should be relatively easy to move... obviously larger tables will take more work and longer outages. Usually you end up getting more value out of moving small to medium size tables anyways, since they are more prone to scatter than the larger tables.

 

[Rob Fitzpatrick]

I heard it said by PSC that converting the schema area to Type II is a fair bit of work and they'd rather spend those development resources on other things. Fair enough.

I wouldn't mind at all that the schema area is Type I if they would (a) allow area-level ABP assignments to persist across probkup/prorest, and (b) give us the ability to configure default storage areas for the addition of tables, indexes, and LOBs. That way you could ensure that application objects don't accidentally wind up in the schema area.