Re: 12c pluggable database shared SGA question

My bad, I should have directed the question to Debaditya. Your explanation/guess, comparing savings between multi tenant models makes sense for that statement. Thank you!

Thanks
Paresh
416-688-1003

On Wed, Sep 10, 2014 at 4:34 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:

> Paresh,
>
> 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