RE: Quantum data in EM repository?

Comments back from our EM architect:

I personally have not had problems with the view, but there might be some kind of weird optimization bug going on with the CBO in the DB here: The query transform might be doing the WRONG thing here. I'm using it extensively too for things like Exadata and all the other fancy HW pieces to figure out relationships between targets, and so far, all my queries returned just fine...

Thing to watch out for the target association view though: - It does NOT take NYM targets (the not-yet managed targets) into account - So if you have a 'partial' RAC discovered, with the other half of the RAC found but NOT promoted yet, the association view is going to return NOTHING for RAC, even though associations are already in place in the repository...

Other thing to note - That view is getting deprecated - So better to use the GC$ASSOC_INSTANCES view instead...  

Pete

Pete Sharman
Database Architect, DBaaS / DBLM
Enterprise Manager Product Suite
33 Benson Crescent CALWELL ACT 2905 AUSTRALIA

Phone: HYPERLINK "tel:+61262924095"+61262924095 | | Fax: HYPERLINK "fax:+61262925183"+61262925183 | | Mobile: +61414443449 Email: HYPERLINK "mailto:pete.sharman_at_oracle.com"pete.sharman_at_oracle.com Twitter: @SharmanPete LinkedIn: au.linkedin.com/in/petesharman Website: petewhodidnottweet.com

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook  

"Oh no, it's not, it's much harder than that!"

Bruce Pihlamae, long term Oracle DBA

From: Herring, David [mailto:HerringD_at_DNB.com] Sent: Friday, September 25, 2015 9:25 AM To: Peter Sharman <pete.sharman_at_oracle.com>; oracle-l_at_freelists.org Subject: RE: Quantum data in EM repository?  

That's awesome! Thx Pete.  

Regards,  

Dave    

Dave Herring

DBA 103 JFK Parkway

Short Hills, New Jersey 07078

Mobile 630.441.4404  

HYPERLINK "http://www.dnb.com/"dnb.com  

http://www.facebook.com/DunBradstreethttp://twitter.com/dnbushttp://www.linkedin.com/company/dun-&-bradstreethttp://www.youtube.com/user/DunandBrad  

From: Peter Sharman [mailto:pete.sharman_at_oracle.com] Sent: Thursday, September 24, 2015 6:02 PM To: Herring, David; HYPERLINK "mailto:oracle-l_at_freelists.org"oracle-l_at_freelists.org Subject: RE: Quantum data in EM repository?  

Dave  

I've bounced this one up to our EM architect who knows these views far better than me (which is not difficult, I must admit!) He's travelling for work this week so it'll probably be next week before I get a response from him.  

Pete

Pete Sharman
Database Architect, DBaaS / DBLM
Enterprise Manager Product Suite
33 Benson Crescent CALWELL ACT 2905 AUSTRALIA

Phone: HYPERLINK "tel:+61262924095"+61262924095 | | Fax: HYPERLINK "fax:+61262925183"+61262925183 | | Mobile: +61414443449 Email: HYPERLINK "mailto:pete.sharman_at_oracle.com"pete.sharman_at_oracle.com Twitter: @SharmanPete LinkedIn: au.linkedin.com/in/petesharman Website: petewhodidnottweet.com

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook  

"Oh no, it's not, it's much harder than that!"

Bruce Pihlamae, long term Oracle DBA

From: Herring, David [mailto:HerringD_at_DNB.com] Sent: Friday, September 25, 2015 6:11 AM To: HYPERLINK "mailto:oracle-l_at_freelists.org"oracle-l_at_freelists.org Subject: Quantum data in EM repository?  

Folks,  

I've got a strange situation in querying data in MGMT$TARGET_ASSOCIATIONS in a 12.1.0.2 EM repository. We have 7 different EM repositories and I'm trying to pull data from all for a report, "Oracle Real Application Cluster (RAC) Database Topology". It's a great EM report but obviously it's only local. I thought it'd be great for management if I copied that report, created table versions of various MGMT$* views, then had a weekly job to pull from each repository into these C_MGMT$* tables.  

This all works EXCEPT a number of rows in MGMT$TARGET_ASSOCIATIONS are playing hide-and-seek. Within the report above, chart "Aggregated RAC Databases Nodes Distribution" uses a cursor against this view. When I noticed the consolidated data didn't look correct I checked one of the 12c repositories and ran the following as SYSMAN:  

SELECT COUNT(*) FROM sysman.mgmt$target_associations

WHERE SUBSTR(source_target_name, 1, 8) IN (<various database names>)

   AND assoc_def_name = 'contains';  

          COUNT(*)

                92

 

SELECT COUNT(*) FROM sysman.mgmt$target_associations

WHERE assoc_def_name = 'contains';  

          COUNT(*)

                 0

 

The 2 cursors use different plans which might hint at a wrong results issue. Since this cursor involves views of views . I checked the following:  

SELECT /*+ NO_QUERY_TRANSFORMATION */ COUNT(*) FROM sysman.mgmt$target_associations ta

WHERE assoc_def_name = 'contains';  

          COUNT(*)

              2506  

I'm not missing something incredibly obvious with EM data, am I?  

Regards,  

Dave

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