Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Pluggable database in 12C

296 views
Skip to first unread message

Mladen Gogala

unread,
Oct 17, 2012, 11:13:16 PM10/17/12
to
I haven't seen the demo, probably because I haven't attended the OOW, but
the whole "pluggable database" thing looks like MySQL to me. When another
database is needed, an appropriate "use <db>" command is issued and voila,
we're in a different database plug-in.
Did the mighty Oracle finally cave in to accept the same one instance/
multiple database architecture as every other vendor. Here is another
example of pluggable database:

db2 => connect to sample

Database Connection Information

Database server = DB2/NT64 10.1.0
SQL authorization ID = MGOGALA
Local database alias = SAMPLE

db2 =>

I am now connected to one of 3 databases or "pluggable databases". Funny
thing is that I can even use Oracle SQL*Developer to connect to DB2. I
wonder when will Oracle give us local temporary tables? Is that a big
innovation for the release 12.2?


--
Mladen Gogala
http://mgogala.freehostia.com

Noons

unread,
Oct 18, 2012, 5:06:23 AM10/18/12
to
Mladen Gogala wrote,on my timestamp of 18/10/2012 2:13 PM:
> I haven't seen the demo, probably because I haven't attended the OOW, but
> the whole "pluggable database" thing looks like MySQL to me. When another

More or less the same as MSSQL and I suppose MySQL. I'd venture the purchase of
MySQL by Oracle *finally* opened the eyes of the sacrosanct "marketing experts"
who decide what gets added in each new release...

Last bit of info I have indicates they got it completely wrong: all PDBs share
the same container redo log set! Talk about contention...
One of the *biggest* advantages MSSQL has in this field is that it is possible
to optimize I/O for each PDB log set.
With 12c if it stays with a global redo log? Ah yes, of course: buy Exadata!
That's gonna work really well...


> thing is that I can even use Oracle SQL*Developer to connect to DB2. I
> wonder when will Oracle give us local temporary tables? Is that a big
> innovation for the release 12.2?

I think it is. But to me the best thing about it is the easier to handle
security across PDBs and within each, as well as the data masking capabilities
and being able to use PL/SQL from inside SQL without going through the whole
"create this and that" rigmarole. Really useful stuff.


Jonathan Lewis

unread,
Oct 18, 2012, 5:22:51 AM10/18/12
to
"Noons" <wizo...@yahoo.com.au> wrote in message
news:k5ogqf$bds$1...@dont-email.me...
|
| Last bit of info I have indicates they got it completely wrong: all PDBs
share
| the same container redo log set! Talk about contention...
| One of the *biggest* advantages MSSQL has in this field is that it is
possible
| to optimize I/O for each PDB log set.
| With 12c if it stays with a global redo log? Ah yes, of course: buy
Exadata!
| That's gonna work really well...
|


That was such an obvious design flaw that I raised it at (I think) one of
the Engineered Systems breakfast seminars.

The point I made was in reply to the "you only need one of each background
process for the whole system rather than one of each for each database."

The follow-up answer to this was that you are able to define multiple log
writers (not just I/O slaves for a single log writer). At that point I
should have asked whether these multiple writers would behave like the
multiple log writers you get in RAC, viz: separate log file groups that
have to be resynchronized on recovery - but I didn't ask that question
because it was such an obvious implementation detail that I didn't even
think about thinking about it.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


ddf

unread,
Oct 18, 2012, 10:08:37 AM10/18/12
to
I agree that the single set of LGWR processes will be the Achiles' heel of the 'pluggable database' revolution. To be fair, though, MySQL got that idea from Sybase years before (the same place , I believe, Informix got it) so it's been around for a LONG, long time. As Noons mentioned SQL Server (and Sybase, if I remember correctly) have transaction logs for each database, not a single set for the entire server, so that contention between database transaction logging shouldn't happen. That Oracle couldn't think that far ahead ... well, I suppose the marketing department was getting a lot of grumpy email and feedback on the fact that 12c had been announced for quite a while but nothing was ever said to solidify WHEN it would be available. I would expect that in 12.2 the LGWR flaw will be corrected (once the early adopters find bucketloads of contention after they load up their containers with several fairly active databases each) as the hoi paloi will not stand for such an obvious and correctable performance problem (again, if Sybase, SQL Server and MySQL can prevent it why can't Oracle).


David Fitzjarrell

Mladen Gogala

unread,
Oct 18, 2012, 10:18:10 AM10/18/12
to
On Thu, 18 Oct 2012 20:06:23 +1100, Noons wrote:

> I think it is. But to me the best thing about it is the easier to
> handle security across PDBs and within each, as well as the data masking
> capabilities and being able to use PL/SQL from inside SQL without going
> through the whole "create this and that" rigmarole. Really useful
> stuff.

My feelings are mixed. It will be easier to handle security and to
separate applications, but on the other hand, it goes contrary to
everything that Oracle was about until now: a centralized data dictionary,
easy to tune and easy to monitor. I will reserve my judgement for the
moment I will be able to get my hands on a 12c DB.

joel garry

unread,
Oct 18, 2012, 12:14:56 PM10/18/12
to
On Oct 18, 2:21 am, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Noons" <wizofo...@yahoo.com.au> wrote in message
> Jonathan Lewishttp://jonathanlewis.wordpress.com/all_postings
>
> Author: Oracle Core (Apress 2011)http://www.apress.com/9781430239543

My (admittedly muddled) thoughts on reading Noon's comments were:
Maybe this could really be an extension, conceptually anyways, of
private redo log threads (including the part about falling back to
"old ways" when the "new ways" are inappropriate); and not to
discount the Exadata ability to write the same thing to different
devices including non-volatile memory - why assume that architecture
would stay specific to Exadata (aside from the obvious milking maximum
money)? Which kind of begs the question of how to deal with redo when
you have no spinning rust at all (as already kind-of asked on the
forums, and there was a Linus Q&A session on /. where he mentioned his
personal machine).

The stock market discounts 6-12 months ahead. Physical DB
architecture has to think 5-10 years ahead.

jg
--
@home.com is bogus.
"I think they're Iranians" - http://www.imdb.com/title/tt0080520/alternateversions
http://mobile.nytimes.com/2012/10/14/world/middleeast/us-suspects-iranians-were-behind-a-wave-of-cyberattacks.xml

Noons

unread,
Oct 19, 2012, 5:16:02 AM10/19/12
to
ddf wrote,on my timestamp of 19/10/2012 1:08 AM:

> I agree that the single set of LGWR processes will be the Achiles' heel of
> the 'pluggable database' revolution. To be fair, though, MySQL got that idea
> from Sybase years before (the same place , I believe, Informix got it) so
> it's been around for a LONG, long time. As Noons mentioned SQL Server (and
> Sybase, if I remember correctly) have transaction logs for each database, not
> a single set for the entire server, so that contention between database
> transaction logging shouldn't happen.

It's not just the contention. In MSSQL I can easily restore a PDB from a backup
and do a PITR without affecting any of the other PDBs. And of course I can back
it up separately as well! I hope I can do that with 12c as well but I don't see
exactly how at this stage. Early days though, so let's wait and see. I suspect
we'll see a "re-synch" utility/function to provide such flexibility and other
necessary global dictionary integrity.


> That Oracle couldn't think that far
> ahead ... well, I suppose the marketing department was getting a lot of
> grumpy email and feedback on the fact that 12c had been announced for quite a
> while but nothing was ever said to solidify WHEN it would be available.

Hmmm... Partly agreed. I rather think their R&D wasted too long pursuing the
confusion idiocy and let their cash cow fall behind in features. But of course
I'm a dinossaur dba 1.0, so what do I know of all those illuminated stratospheres?
Oh hang on, I'm also a paying customer!
Aw foggedaboudit, that counts for exactly squat...


> I
> would expect that in 12.2 the LGWR flaw will be corrected (once the early
> adopters find bucketloads of contention after they load up their containers
> with several fairly active databases each) as the hoi paloi will not stand
> for such an obvious and correctable performance problem (again, if Sybase,
> SQL Server and MySQL can prevent it why can't Oracle).


Like I said: let's wait and see. The latest info is that it'll be out somewhere
mid 2013. I suspect that will be slightly delayed once it percolates up there
are a couple of flies in the ointment.
I suspect the whole purpose of this early announcement is precisely to flush out
such flies and address them before release. But rest assured those doing the
flushing won't see a single atom of recognition of their support of the product
and their willingness to help improve it!...

joel garry

unread,
Oct 19, 2012, 7:00:01 PM10/19/12
to
On Oct 19, 2:16 am, Noons <wizofo...@yahoo.com.au> wrote:

> other necessary global dictionary integrity.

Meanwhile, in the redo zeitgeist:
http://www.pythian.com/news/36791/adaptive-log-file-sync-oracle-please-dont-do-that-again/

jg
--
@home.com is bogus.
http://www.reuters.com/article/2012/10/19/oraclecorp-notes-idUSL1E8LJ8JH20121019

Noons

unread,
Oct 20, 2012, 6:21:38 AM10/20/12
to
joel garry wrote,on my timestamp of 20/10/2012 10:00 AM:
> On Oct 19, 2:16 am, Noons <wizofo...@yahoo.com.au> wrote:
>
>> other necessary global dictionary integrity.
>
> Meanwhile, in the redo zeitgeist:
> http://www.pythian.com/news/36791/adaptive-log-file-sync-oracle-please-dont-do-that-again/

Interesting! Willhave to dig into this one. I've ben seeing some unique
messages in the logfiles since going to 11.2.0.3 across the board and this might
explain a few things.
Thaks for that, going to dig it up next week.

zifn...@gmail.com

unread,
Oct 21, 2012, 11:22:22 AM10/21/12
to wizo...@yahoo.com.au
> It's not just the contention. In MSSQL I can easily restore a PDB from a backup
>
> and do a PITR without affecting any of the other PDBs. And of course I can back
>
> it up separately as well! I hope I can do that with 12c as well but I don't see
>
> exactly how at this stage. Early days though, so let's wait and see. I suspect
>
> we'll see a "re-synch" utility/function to provide such flexibility and other
>
> necessary global dictionary integrity.
>
>

I asked this question at OOW and the answer was that you can backup and recover at the individual PDB level without affecting the other PDB's or do it at the CDB.

They said anything you can do now on a database you can do on a PDB. Fully backward compatible.

Mladen Gogala

unread,
Oct 22, 2012, 12:41:43 AM10/22/12
to
On Sat, 20 Oct 2012 21:21:38 +1100, Noons wrote:

> Interesting! Willhave to dig into this one. I've ben seeing some unique
> messages in the logfiles since going to 11.2.0.3 across the board and
this might
> explain a few things.
> Thaks for that, going to dig it up next week.

It would be interesting to see the difference in performance between the
old and new log file sync mechanism. Unfortunately, I have no RAC system
to test. I would be really grateful if you publish your results. Thanks in
advance.

Noons

unread,
Oct 22, 2012, 1:11:01 AM10/22/12
to
No RAC here either. The difference I'm seeing is definitely for the better.
That is (64bit):
from 10.2.0.3 on Aix 5.3
to 11.2.0.3 on Aix 7.1
The SAN is the same. So is the hardware - CPU (Power6) and memory.
Log file sync wait time dropped from around 2-3ms to under 1ms for 90% of the
time. CPU use dropped nearly 25% for same workload. I/O is similar, no major
alterations.
But I'm getting some alert messages that I don't understand. They are not
warnings, nor errors. Just "comments", I guess? (for what purpose?)
I'll get details in a coupla days time - currently on leave after many, many
working weekends...

Noons

unread,
Oct 22, 2012, 1:12:22 AM10/22/12
to
scott...@comcast.net wrote,on my timestamp of 22/10/2012 2:22 AM:


> I asked this question at OOW and the answer was that you can backup and
> recover at the individual PDB level without affecting the other PDB's or do
> it at the CDB.
>
> They said anything you can do now on a database you can do on a PDB. Fully
> backward compatible.
>


That is definitely a lot more encouraging! Thanks for the info.

John Hurley

unread,
Oct 22, 2012, 8:51:17 PM10/22/12
to
Nuno:

# The SAN is the same. So is the hardware - CPU (Power6) and memory.

... Log file sync wait time dropped from around 2-3ms to under 1ms for
90% of the time.  CPU use dropped nearly 25% for same workload. I/O is
similar, no major alterations.

Interesting but keep in mind that we are at the mercy of Oracle
instrumentation here at least for how they measure the log file sync
wait time. Lots of possible ways internally that Oracle might have
changed how they measure it between 10.2.0.3 ( well off some of the
final 10.2 code ) and 11.2.0.3.x




Noons

unread,
Oct 23, 2012, 1:46:18 AM10/23/12
to
John Hurley wrote,on my timestamp of 23/10/2012 11:51 AM:

> ... Log file sync wait time dropped from around 2-3ms to under 1ms for
> 90% of the time. CPU use dropped nearly 25% for same workload. I/O is
> similar, no major alterations.
>
> Interesting but keep in mind that we are at the mercy of Oracle
> instrumentation here at least for how they measure the log file sync
> wait time. Lots of possible ways internally that Oracle might have
> changed how they measure it between 10.2.0.3 ( well off some of the
> final 10.2 code ) and 11.2.0.3.x

Indeed, good point. Given they always recommend we use their metrics, we're at
their mercy on SQL-related counters.

But CPU and I/O above was measured through Unix, not Oracle. Backups for
example were considerably faster on wall clock time (30 minutes less on 3.5
hours) and the overnight batch run has significantly reduced its runtime as well.

One thing that I'm experimenting with at the moment is the new (11.2) RMAN
compression algorithms. The LOW compression setting gives me a backup that is
half the time (1.5h from 3), with considerably less CPU use and a backup file
size less than twice what it was: 250GB from 180GB. I/O has of course increased
but I've got oodles of bandwidth so it's not a problem.

I've now majorly reduced the CPU usage during the DW backup window, which is
excellent as that is the batch window for our JDE system and I can re-allocate
the CPU where it's needed.
One of the advantages of running db servers on vios inside Aix is that the OS
can dynamically (within defined constraints) re-apportion CPU, memory and I/O
channels and give them where it's needed. By saving on backup overhead, I've
freed resources that can immediately be used in other vio virtual boxes.

Man! I love this "private cloud" virtualization stuff!! ;-)
(mind you: we used to do exactly this in the mainframe and VM/370 days - the
more IT hardware/software evolves, the more it is the same...)

Noons

unread,
Oct 23, 2012, 5:18:49 PM10/23/12
to
On Oct 22, 4:11 pm, Noons <wizofo...@yahoo.com.au> wrote:

> But I'm getting some alert messages that I don't understand.  They are not
> warnings, nor errors.  Just "comments", I guess? (for what purpose?)


Some of the messages I'm seeing:
"Thread 1 cannot allocate new log, sequence 167
Private strand flush not complete"
This is on a db with 6 redo log of 1GB each.
Doing a search on that seems to indicate the message is relatively
harmless in 11gr2.
If it is, then why put it there in the first place?
And what does it REALLY mean and what is the remediation if any?

TheBoss

unread,
Oct 23, 2012, 6:20:04 PM10/23/12
to
Noons <wizo...@gmail.com> wrote in
news:8e1f23f4-18f4-4ca2...@r8g2000pbs.googlegroups.com:
Did you check MOS for this?
It has several notes related to this.
According to my notes, the most relevant are:

Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]
Manual Log Switching Causing "Thread 1 Cannot Allocate New Log" Message
in the Alert Log [ID 435887.1] Can Not Allocate Log [ID 1265962.1]

HTH

--
Jeroen

joel garry

unread,
Oct 23, 2012, 7:12:12 PM10/23/12
to
According to Alert Log Messages: Private Strand Flush Not Complete
[ID 372557.1] it's just telling you the consolidation of the private
redo is taking some time. I would guess the suggestion of adding db
writers to keep the cache clean is a bit of compulsiveness. Perhaps
Jonathan knows better.

jg
--
@home.com is bogus.
http://www.zdnet.com/sap-oracle-negotiations-its-complicated-7000006080/

Mladen Gogala

unread,
Oct 23, 2012, 7:56:21 PM10/23/12
to
On Tue, 23 Oct 2012 16:12:12 -0700, joel garry wrote:

> According to Alert Log Messages: Private Strand Flush Not Complete [ID
> 372557.1] it's just telling you the consolidation of the private redo is
> taking some time. I would guess the suggestion of adding db writers to
> keep the cache clean is a bit of compulsiveness. Perhaps Jonathan knows
> better.

Joel, I'm no Jonathan by any stretch of imagination, but it doesn't look
to me that adding DBWR would particularly helpful with REDO? How would
database block writer help LGWR consolidate the redo entries from the
private redo strands?
BTW, I met Jonathan in person and we don't look even remotely alike.

Noons

unread,
Oct 24, 2012, 6:12:34 AM10/24/12
to
TheBoss wrote,on my timestamp of 24/10/2012 9:20 AM:

>
> Did you check MOS for this?
> It has several notes related to this.
> According to my notes, the most relevant are:
>
> Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]
> Manual Log Switching Causing "Thread 1 Cannot Allocate New Log" Message
> in the Alert Log [ID 435887.1] Can Not Allocate Log [ID 1265962.1]

Yes, I have. And all of them indicate that it should be ignored in 11gr2.

Noons

unread,
Oct 24, 2012, 6:22:05 AM10/24/12
to
Not sure about the likeness. :)
But I do know that if I jack up the priority of the lgwr process the incidence
of this message is greatly reduced although not completely eliminated.

The issue I have with the "consolidation of the private redo is taking some
time" thing is simply this: which stat or wait tells me it is happening, and by
how much?
I'm fundamentally alergic to tuning by "some": either there is a numeric,
tallied base for an observation, or it's not worth the surface it's written on!
And of course: I'm not directing this at Joel! I've seen the same text in some
of the mentioned MOS docs and they indicate it means, essentially, nothing.

My gut feel is that someone forgot to remove a debug message from somewhere in
the 11gr2 code and now we have this thing showing up all over the place and
meaning essentially nothing!

And there are more. In fact, the 11gr2 alert file is NOTORIOUS for quite a few
comments with no WARNING or ERROR tag before them, or any ORA number. Which
makes it impossible to accurately make any sense of them, other than by coincidence!

Jonathan Lewis

unread,
Oct 24, 2012, 6:45:54 AM10/24/12
to




"Noons" <wizo...@yahoo.com.au> wrote in message
news:k68fgd$vj8$1...@dont-email.me...
| The issue I have with the "consolidation of the private redo is taking
some
| time" thing is simply this: which stat or wait tells me it is happening,
and by
| how much?

I think it would be wait event
"log file switch (private strand flush incomplete)"

|
| My gut feel is that someone forgot to remove a debug message from
somewhere in
| the 11gr2 code and now we have this thing showing up all over the place
and
| meaning essentially nothing!
|

I think I'd be inclined to agree, although it's also possible that it was
initially left in place on the grounds that:
"I think we've done the right thing, but if too many people complain
about seeing this debug message too often we must have missed an edge
case."

joel garry

unread,
Oct 24, 2012, 11:59:46 AM10/24/12
to
On Oct 23, 4:56 pm, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Tue, 23 Oct 2012 16:12:12 -0700, joel garry wrote:
> > According to       Alert Log Messages: Private Strand Flush Not Complete [ID
> > 372557.1] it's just telling you the consolidation of the private redo is
> > taking some time.  I would guess the suggestion of adding db writers to
> > keep the cache clean is a bit of compulsiveness.  Perhaps Jonathan knows
> > better.
>
> Joel, I'm no Jonathan by any stretch of imagination, but it doesn't look
> to me that adding DBWR would particularly helpful with REDO? How would
> database block writer help LGWR consolidate the redo entries from the
> private redo strands?

I'm guessing the general idea is that more dbwr would be kicking at
the lgwr to write things out more often, flushing private strands more
often, letting them build up less stuff to flush. As the concepts
guide puts it " If DBWn finds that some redo records have not been
written, it signals LGWR to write the records to disk and waits for
LGWR to complete before writing the data buffers to disk."

> BTW, I met Jonathan in person and we don't look even remotely alike.

I want to look like Ben Bernanke.

jg
--
@home.com is bogus.
RIP Paul Kurtz and I hope you don't find God laughing...
http://www.superscholar.org/interviews/paul-kurtz/

Mark D Powell

unread,
Oct 24, 2012, 2:16:51 PM10/24/12
to
On Thursday, October 18, 2012 10:08:37 AM UTC-4, ddf wrote:
> On Thursday, October 18, 2012 3:21:53 AM UTC-6, Jonathan Lewis wrote: > "Noons" <wizo...@yahoo.com.au> wrote in message > > news:k5ogqf$bds$1...@dont-email.me... > > | > > | Last bit of info I have indicates they got it completely wrong: all PDBs > > share > > | the same container redo log set! Talk about contention... > > | One of the *biggest* advantages MSSQL has in this field is that it is > > possible > > | to optimize I/O for each PDB log set. > > | With 12c if it stays with a global redo log? Ah yes, of course: buy > > Exadata! > > | That's gonna work really well... > > | > > > > > > That was such an obvious design flaw that I raised it at (I think) one of > > the Engineered Systems breakfast seminars. > > > > The point I made was in reply to the "you only need one of each background > > process for the whole system rather than one of each for each database." > > > > The follow-up answer to this was that you are able to define multiple log > > writers (not just I/O slaves for a single log writer). At that point I > > should have asked whether these multiple writers would behave like the > > multiple log writers you get in RAC, viz: separate log file groups that > > have to be resynchronized on recovery - but I didn't ask that question > > because it was such an obvious implementation detail that I didn't even > > think about thinking about it. > > > > Regards > > > > Jonathan Lewis > > http://jonathanlewis.wordpress.com/all_postings > > > > Author: Oracle Core (Apress 2011) > > http://www.apress.com/9781430239543 I agree that the single set of LGWR processes will be the Achiles' heel of the 'pluggable database' revolution. To be fair, though, MySQL got that idea from Sybase years before (the same place , I believe, Informix got it) so it's been around for a LONG, long time. As Noons mentioned SQL Server (and Sybase, if I remember correctly) have transaction logs for each database, not a single set for the entire server, so that contention between database transaction logging shouldn't happen. That Oracle couldn't think that far ahead ... well, I suppose the marketing department was getting a lot of grumpy email and feedback on the fact that 12c had been announced for quite a while but nothing was ever said to solidify WHEN it would be available. I would expect that in 12.2 the LGWR flaw will be corrected (once the early adopters find bucketloads of contention after they load up their containers with several fairly active databases each) as the hoi paloi will not stand for such an obvious and correctable performance problem (again, if Sybase, SQL Server and MySQL can prevent it why can't Oracle). David Fitzjarrell

>> single set of LGWR processes will be the Achiles' heel of the 'pluggable database' revolution <<

Won't that really depend on how your containers are allocated? Ignorning RAC you have one instance, one database, one log now. If you split the database schema into their own containers do you really have any more logging activity to be handled that what the log writer process is currently handling? In other words is this really an issue now?

Now if you have 10 RAC databases on one server like we do (mostly vendor products) that for security and upgrade reasons need to be separate if we put them each into a PDB we would be increasing the activty of the one 12c instance about 4X so them maybe the logging would be an issue. But I do not consider this type of consolidation to be the likely norm any time soon.

IMHO -- Mark D Powell --

Noons

unread,
Oct 24, 2012, 9:19:30 PM10/24/12
to
On Oct 24, 9:45 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:

> | time" thing is simply this: which stat or wait tells me it is happening,
> and by
> | how much?
>
> I think it would be wait event
> "log file switch (private strand flush incomplete)"

Thanks for that, Jonathan. Great! That's exactly what I was looking
for: something tangible for me to chase rather than (the usually
expensive...) CTD.

Jonathan Lewis

unread,
Oct 25, 2012, 5:00:51 AM10/25/12
to

| "joel garry" <joel-...@home.com> wrote in message
news:353e8f2b-2d9d-479b...@jj5g2000pbc.googlegroups.com...
|
| I'm guessing the general idea is that more dbwr would be kicking at
| the lgwr to write things out more often, flushing private strands more
| often, letting them build up less stuff to flush. As the concepts
| guide puts it " If DBWn finds that some redo records have not been
| written, it signals LGWR to write the records to disk and waits for
| LGWR to complete before writing the data buffers to disk."


DBWn wakeup every 3 seconds to see if any modified buffers need to be
copied to disc.
If you have more of them they share (pseudo-randomly, via the working data
sets) the same
number of buffers and are (probably) all be signalled at the same time, so
it's unlikely that
increasing the number will cause LWGR to get more signals.

I'm not certain of how the multiple DBWn interation with LGWR takes place,
but if I am the
only DBWR I'll send one signal if I see two buffers that need to be
written; if we are a pair of
DBWn who share that pair then one of us will send a signal for our one
buffer, and the other
should detect that LGWR has already been signalled.

Regardless, I don't think this comes into play at the log file switch where
the wait could be
seen as the "private redo thread" equivalent of the "LGWR wait for redo".
(In passing, either
may be an indication of levels of concurrency or CPU utilisation that is
causing delays between
a process being runnable and getting to the top of the runqueue).

Noons

unread,
Oct 25, 2012, 6:33:10 AM10/25/12
to
Mark D Powell wrote,on my timestamp of 25/10/2012 5:16 AM:

>
>>> single set of LGWR processes will be the Achiles' heel of the 'pluggable
>>> database' revolution <<
>
> Won't that really depend on how your containers are allocated? Ignorning RAC
> you have one instance, one database, one log now. If you split the database
> schema into their own containers do you really have any more logging activity
> to be handled that what the log writer process is currently handling? In
> other words is this really an issue now?

It is if one is consolidating, which is the whole reason for the PDBs.
Ie, not running one application per instance.
You run one per schema/PDB. And if you add sufficient load of them to a single
instance's redo logs, you got a serious contention problem right there.
MSSQL doesn't, because it uses discrete logs for each PDB.
Let's hope Oracle keeps it that way.

> Now if you have 10 RAC databases on one server like we do (mostly vendor
> products) that for security and upgrade reasons need to be separate if we put
> them each into a PDB we would be increasing the activty of the one 12c
> instance about 4X so them maybe the logging would be an issue. But I do not
> consider this type of consolidation to be the likely norm any time soon.

Surprisingly, it is. Not necessarily with RAC, mind you. In fact it's
beginning to look more and more like 12c PDB is just an extension of the
"single-node RAC" trials of 3-4 years ago.
0 new messages