Oracle Enterprise Manager: What do 'Other' and 'Concurrency' mean?

We are using Oracle Enterprise Manager on an Oracle 11g database to identify bottlenecks in the execution of queries.  We have a query that can take as long as 2 minutes to execute the first time, and then around a second if executed again.

The Oracle Enterprise Manager has a multi-colored bar detailing how much time is taken up for different issues.  The longest parts of the bar are labeled 'Other' and 'Concurrency'.

Any ideas what could be included in Other and/or Concurrency?  I'm thinking that part of Other could be parsing of the query, but was wondering if there were other issues that could be causing the time sink?  All of the objects in our database are Materialized Views from another database on the same server.  These Materialized Views are updated about once every two hours.  I'm wondering if the Concurrency issues are related to the database waiting until the MV's are synced.

I've tried researching this, but one article leads to ten more, which each lead to ten more none of which provide much clarity.
koughdurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicIT ArchitectCommented:
Hi,

EM has multiple monitoring metrics which it gathers from the running database and then in order to have more clear view of the database they are grouped together. For example, the "Concurency" metrics group is sum of  times the database handles the locks, pins and any other metrics which relates to sharing of same resource i.e. to concurrent wait on resource.

If you click on the Concurrency ( colored in dark red)  you will then get more detail view and based on that view you can troubleshoot your database. Based on your mentioning that you have MV which are being build every two hours that can be the issue since the database must wait for the data to be written in order to server the newly written data to other user. If you post the screenshot of concurrency view i can give you more helpful answer this is just pure guessing.

The Other category hold basically any metric that is not listed in any of major groups. Any advice on why other is showing is based on pure guessing.
koughdurAuthor Commented:
Thanks for the feedback.  Unfortunately I am not the DBA and am not allowed access to Oracle Enterprise Manager.  I have limited access to DBA's for short periods of time.  Our DBA's do not appear to be thoroughly knowledgeable about OEM's capabilities so I am going to have to study it up myself and then have them bring it up and walk them through it.

I'm thinking now that providing them a bunch of V$ dynamic view queries that they can run while I execute my queries will be more useful as all of the details will be there and there will be no need for me to search through the OEM interface to get ambiguous answers.
slightwv (䄆 Netminder) Commented:
>>We have a query that can take as long as 2 minutes to execute the first time, and then around a second if executed again.

This makes perfect sense to me.  The first execution has to read the blocks of disk.  Subsequent executions are likely read from memory (consistent gets).  Even worse if you are using result caching.  Subsequent executions might be sub-second.

When tuning queries, you need to flush the result cache and buffer cache to make sure you execute the query with the worst possible setup.

>>for me to search through the OEM interface to get ambiguous answers.

My Oracle pre-dates OEM so I am biased towards command line and scripts.  I rarely install it.  At best I have dbConsole running.  I find a lot of what it provides is useless noise.

Even if you go with the older statspack and spreport, I never found much of what is in the report to be of much use.

Too many people use them as the bible they must follow instead of using them as guidelines.

The "book" says xyz needs to be less than 10% so I need to make it happen.  The truth is for that specific database a xyz of 30% is perfectly normal.  For the next database, 40% and so on.

>>I'm thinking now that providing them a bunch of V$ dynamic view queries that they can run while I execute my queries

If you need more than the execution plan:  Two words:  trace and tkprof.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
koughdurAuthor Commented:
It seems OEM is a dead end.  If I need to spend time looking into this further it is best to spend that time better understanding the V$ views rather than OEM.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.