Re: SMO rowcache

From: Carol Dacko <dackoc_at_gmail.com>
Date: Fri, 25 Feb 2011 10:44:24 -0500
Message-ID: <AANLkTi=TB-FB+1uf3KZTY3Dyh3XGbQfLCUZoYF_DTtce_at_mail.gmail.com>

Looks like it is Space Management Operations. Jeff Holt, now with Method-R, did a presentation at NCOUG in 2000 that explains it well. Just Goggle SMO row cache and it should be on the second page of results. I would attach the URL but I have difficulties with that right now since I only have one hand to type with.

HTH!
Carol Dacko
University of Michigan

On Fri, Feb 25, 2011 at 10:11 AM, <Dominic.Brooks_at_barclayscapital.com>wrote:

> Just a bit more information:
>
>
>
> SQL> select * from v$version;
>
>
>
> BANNER
>
>
> --------------------------------------------------------------------------------
>
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
>
> PL/SQL Release 11.2.0.2.0 - Production
>
> CORE 11.2.0.2.0 Production
>
> TNS for Linux: Version 11.2.0.2.0 - Production
>
> NLSRTL Version 11.2.0.2.0 - Production
>
>
>
> SQL> select cache#, type, parameter, gets, getmisses from v$rowcache where
> cache#=25;
>
>
>
> CACHE# TYPE PARAMETER GETS
> GETMISSES
>
> ---------- ----------- -------------------------------- ----------
> ----------
>
> 25 PARENT SMO rowcache 4078042
> 4078042
>
>
>
> SQL>
>
>
>
> Background situation is that we're benchmarking an upgrade from 9.2.0.8 to
> 11.2.0.2.
>
>
>
> Plan was to capture 9i like plans into a baseline (yes there are other ways
> to capture 9i plans) using optimizer_features_enable set to 9.2.0 along with
> other parameters set to current settings.
>
>
>
> However, there are multiple processes running the same SQL, particularly
> row-by-row processing (yes, I know) and as a result of these loops, there
> are quite high executions of certain SQL lookups, etc.
>
>
>
> Everything is "running slowly"
>
>
>
> In ASH, I can see lots of row cache locks as per above.
>
>
>
> I can also see that the blocking session on these row cache locks is
> running the recursive SQL associated to SQL Plan Management, for example:
>
>
>
> MERGE INTO sqlobj$data
>
> USING dual ON (:1 IS NULL)
>
> WHEN MATCHED THEN
>
> UPDATE SET comp_data = :2 WHERE signature = :3 AND category = :4 AND
> obj_type = :5 AND plan_id = :6
>
> WHEN NOT MATCHED THEN
>
> INSERT (signature, category, obj_type, plan_id, comp_data, spare1,
> spare2) VALUES (:7, :8, :9, :10, :11, null, null)
>
>
>
> and
>
>
>
> MERGE INTO sqlobj$auxdata
>
> USING dual ON (:1 IS NULL)
>
> WHEN MATCHED THEN
>
> UPDATE
>
> WHEN NOT MATCHED THEN
>
> INSERT ....
>
>
>
>
>
> AWR also shows that 6 recursive statements related to SQL Plan Management
> account for 90% of all parsing and have each been executed 500,000 times in
> one hour.
>
>
>
> I'm still investigating.
>
>
>
> I would have thought that an Oracle Support ticket will come out of this
> (or I'll just abandon the plans for 9i baselines).
>
>
>
> But, bottom line is, does anyone know anything concrete about SMO Rowcache?
> Even just what SMO stands for?
>
>
>
>
>
> _______________________________________________
>
>
>
> This e-mail may contain information that is confidential, privileged or
> otherwise protected from disclosure. If you are not an intended recipient of
> this e-mail, do not duplicate or redistribute it by any means. Please delete
> it and any attachments and notify the sender that you have received it in
> error. Unless specifically indicated, this e-mail is not an offer to buy or
> sell or a solicitation to buy or sell any securities, investment products or
> other financial product or service, an official confirmation of any
> transaction, or an official statement of Barclays. Any views or opinions
> presented are solely those of the author and do not necessarily represent
> those of Barclays. This e-mail is subject to terms available at the
> following link: www.barcap.com/emaildisclaimer. By messaging with Barclays
> you consent to the foregoing. Barclays Capital is the investment banking
> division of Barclays Bank PLC, a company registered in England (number
> 1026167) with its registered office at 1 Churchill Place, London, E14 5HP.
> This email may relate to or be sent from other members of the Barclays
> Group.**
>
> _______________________________________________
>

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

Received on Fri Feb 25 2011 - 09:44:24 CST