Re: 12c pluggable database shared SGA question

That comment did not come from me, however, I believe it was originally made in the context of database consolidation in a non-multi-tenant versus a multi-tenant environment.

If you consolidate two databases onto a single server in 11g, you are duplicating all of the instance components. If you consolidate two databases as pluggables in a multi-tenant environment, you are only adding additional resources to the existing instance for the additional database. In comparison, the additional memory required is very small.

Seth Miller

On Wed, Sep 10, 2014 at 3:18 PM, Paresh Yadav <yparesh_at_gmail.com> wrote:

> Seth,
>
> Can you expand what is meant by "....only *small increments* of memory
> are added as additional PDBs are added."? Please see my question below for
> details.
>
> Assuming current SGA size is already optimized for current data sets,
> won't additional PDBs with their own data to be cached need more buffer
> cache and more SQL statements need more memory in library cache? What is
> than meant by "....only *small increments* of memory are added as
> additional PDBs are added."? There is a fixed area in SGA which is quite
> small, is that what is being referred by savings in memory between PDBs?
>
>
> Thanks
> Paresh
> 416-688-1003
>
>
> On Wed, Sep 10, 2014 at 3:56 PM, Glenn Travis <Glenn.Travis_at_sas.com>
> wrote:
>
>> Seth, AHA! You just supported my argument.
>>
>>
>>
>> You would NOT put a heavy DSS type application in the same database as a
>> heavy OLTP application. And by application, you can assume ‘schema’.
>> These are two entirely different beasts with entirely different tuning
>> challenges/strategies. So, to follow, you would not put a DSS PDB in the
>> same CDB as a OTLP PDB. Correct?
>>
>>
>>
>> Yet, that is EXACTLY what Oracle is proposing with their promotional
>> materials, and NOWHERE does it explain the ‘sharing’ aspect of the
>> CDB/PDBs. You have to dive much deeper to get this info.
>>
>>
>>
>> Bad Oracle. Bad boy.
>>
>>
>>
>> *From:* rajendra.pande_at_ubs.com [mailto:rajendra.pande_at_ubs.com]
>> *Sent:* Wednesday, September 10, 2014 3:50 PM
>> *To:* sethmiller.sm_at_gmail.com
>> *Cc:* dmarc-noreply_at_freelists.org; Glenn Travis; Oracle-L_at_freelists.org
>> *Subject:* RE: 12c pluggable database shared SGA question
>>
>>
>>
>> I did not mean to plug in a 11g into 12
>>
>> I meant migrate a 11G into a 12c as a PDB
>>
>>
>>
>> Regards
>>
>>
>>
>> - Raj Pande
>>
>> UBS AG
>>
>> Platform Services - Operations
>>
>> Global Service Delivery (GSDM)
>>
>> 480 Washington Blvd. Jersey City, NJ 07310
>>
>> TEL# - External - +1 201 318 7597
>>
>> Internal - 19 436 7597
>>
>>
>>
>> *From:* Seth Miller [mailto:sethmiller.sm_at_gmail.com
>> <sethmiller.sm_at_gmail.com>]
>> *Sent:* Wednesday, September 10, 2014 3:48 PM
>> *To:* Pande, Rajendra
>> *Cc:* dmarc-noreply_at_freelists.org; Glenn.Travis_at_sas.com; Oracle-L
>> Freelists
>> *Subject:* Re: 12c pluggable database shared SGA question
>>
>>
>>
>> Glenn,
>>
>> Think of PDB's as schemas that are completely isolated and obscured from
>> one another. How would you manage the memory if you were adding another
>> schema to the database? Any database clients that have access to that new
>> schema have the potential of hogging all of the resources of the database,
>> including the memory. It is the DBA's and the developer's job to put into
>> place the controls that would prevent that from happening. The same goes
>> for a multi-tenant environment.
>>
>> Rajendra,
>>
>> I think the simple answer to your question is, you cannot plug an 11g
>> database into a 12c container. The more detailed explanation is that the
>> redo in a multi-tenant environment is tagged with the PDB information to
>> which it belongs. When a PDB datafile recovery takes place, any redo that
>> does not belong to that datafile is skipped.
>>
>> Seth Miller
>>
>>
>>
>> On Wed, Sep 10, 2014 at 2:32 PM, <rajendra.pande_at_ubs.com> wrote:
>>
>> Yes
>>
>> Redo being essentially a recovery structure is not that much of an issue
>> in my mind
>>
>>
>>
>> Where redo does become interesting I think is when recovery needs to be
>> done for a truly plugged database
>>
>> What I am thinking of is a PDB say taken from a 11G database and plugged
>> into a 12C database
>>
>> When you then have to do a PITR then it could get interesting because the
>> redo in the 12C are not applicable to that PDB.
>>
>> I wonder what happens in 12C. Also you will likely also have to do a PITR
>> in 11G (or the source database) and then replug it into 12C and then do a
>> recovery there…
>>
>> Need some play time to try this out J
>>
>>
>>
>> Have not checked or read enough on resource manager’s ability to deal
>> with capping at the PDB level
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Kevin Closson
>> *Sent:* Wednesday, September 10, 2014 2:54 PM
>> *To:* Glenn.Travis_at_sas.com; Oracle-L_at_freelists.org
>> *Subject:* Re: 12c pluggable database shared SGA question
>>
>>
>>
>> Shared REDO *should* be the end of the conversation, really.
>>
>>
>>
>>
>>
>>
>> ------------------------------
>>
>> *From:* Glenn Travis <Glenn.Travis_at_sas.com>
>> *To:* "Oracle-L_at_freelists.org" <Oracle-L_at_freelists.org>
>> *Sent:* Wednesday, September 10, 2014 9:11 AM
>> *Subject:* 12c pluggable database shared SGA question
>>
>>
>> 12c shared resources:
>> "PDBs share UNDO, REDO and control files."
>> "PDBs share common SGA and background processes."
>>
>> The selling point is that only small increments of memory are added as
>> additional PDBs are added. BUT
>>
>> A problem I have with the 12c 'pluggable' database, is that is shares an
>> SGA - that is library cache, and buffer cache. The diagrams used in all
>> the promo (and training) materials show an ERP, CRM and DW type databases
>> sharing the same memory. This seems counter intuitive to everything we as
>> DBAs have been taught for many years. Those databases have completely
>> different users and usage patterns/requirements. I realize the PDBs are
>> not sharing the buffers and statements between them, but they ARE sharing
>> the memory footprint, and there is only so much memory.
>>
>> See slides here :
>> [11g] http://goo.gl/wQ612C vs. [12c] http://goo.gl/eshQTA
>>
>> Obviously an ERP is queried (and tuned) differently (single transactions,
>> high volume, key values, shared sql) than a DW (multi, complex
>> transactions, low number of users, long running statements, full
>> table/index scans, low key value usage, and non-sharable sql).
>> Co-existence of these types of environments will not only be impossible to
>> tune in one SGA, but the cache will be useless. The DW will flush out all
>> the 'good' buffers/pages used by the ERP/OLTP application users, and the
>> non-sharable sql will flush out and fragment the library cache. So the ERP
>> will have nothing left in memory and constantly re-parse the 'sharable' sql
>> and go to disk for all the data. This just doesn't seem logical.
>>
>> What am I missing here? How can you possibly have this kind of shared
>> environment? I agree that 'same' type environments may work as pluggable
>> databases in a shared SGA, provided you have enough memory, but not
>> disparate databases like those in the examples.
>>
>> I have asked several people, including Oracle instructors the following
>> question, but have not yet received a definitive, convincing answer.
>> Comments?
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>> Please visit our website at
>> http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
>> for important disclosures and information about our e-mail
>> policies. For your protection, please do not transmit orders
>> or instructions by e-mail or include account numbers, Social
>> Security numbers, credit card numbers, passwords, or other
>> personal information.
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l