Hi Expert,

I'm working with production Oracle DB,  which runs 24/7 basics, there are thousands of transaction running parallel everyday, i have seen many oracle jobs are running more than expect, i tried analyze them by "AWR" report  but unable to trace the bottleneck.

Could you please guide me steps to read "AWR" for getting the session bottleneck.

Your Advice always valuable.

Thanks in Advance.
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
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.

Geert GOracle dbaCommented:
what "AWR" ?
if you have an AWR report, then you should attach it

if you need some help on figuring it out, Tim has a beginners guide:
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
HI All,

Please find the attachment for html format of the AWR report .

Thank You!!
Geert GOracle dbaCommented:
One i recognize immediately:
In Foreground Wait Events:

6 times almost 2 minutes event enq: TM contention  (Total time = 666 seconds = 11 minutes)
enq: TM - contention      6      0      666      110947      0.00      0.09

i'm not used to working with AWR.
In my case, i'd click on the Enq TM Event, and it would display the querries causing that event, UPDATE or DELETE querry
then i'd run a @desc owner tablename and add the missing indexes
2 minutes work ...

the missing index will cause a full table lock on the child table during cascading delete
those "parallel" statements will run serially in that case
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

johnsoneSenior Oracle DBACommented:
First thing I always look at is the SQL section.  I would say you need to do quite a bit of tuning.  I have never seen so many forced full table scans and parallel query hints.  Things are probably slowing down because you have so many parallel query processes running.

I'm not going to go through them all, but there were certainly easy to find queries that have been run once and ran for many hours.  Look at those.

I found one with a forced full table scan that looked like it should be able to use an index.

There are also a lot of NVL and DECODE statements in where clauses.  Are they really necessary?  Can you put function based indexes to help?

It looks to me like you have people that believe full table scans in parallel is the answer to a slow query.  That isn't always the case.  This probably accounts for the massive amount of I/O you are doing.
Geert GOracle dbaCommented:
it looks like you have a developer who wants the optimizer not to be used
i bet it would all run way better if all those hints were switched off

or you can use 1 hint:
/*+ RULE */

beats all the other hints anytime anywhere ...
johnsoneSenior Oracle DBACommented:
Rules based optimizer hasn't been updated since Oracle 9 (maybe 8).  Assuming you are looking at a transactional system, the best hint would be FIRST_ROWS.  That one is the closest to the RULE hint in the cost based optimizer world.
Geert GOracle dbaCommented:
Johnsone, i know.
but RULE would probably work way better on this system instead of all those /*+ parallel */ hint rules
Geert GOracle dbaCommented:
and besides all that, the database was telling it's connected clients it wanted more work, cos it was idle

SQL*Net message from client      409,952      0      3,971,127      9687      0.08      

or you have a lot of connected clients which are reading the paper or having a very long coffee break, probably all night
johnsoneSenior Oracle DBACommented:
RULE would only work better if there is proper indexing.  Given the number of parallel hints, I would guess it probably isn't.  RULE also doesn't do a very good job if things are over-indexed.

Given the time of day, idle clients aren't surprising.  People can't be bothered to log out when they leave.
Mark GeerlingsDatabase AdministratorCommented:
I always start with the "Main Report" heading of an AWR report, then click on the "SQL Statistics" link.  In your report that shows two SQL statements that are responsible for 33% and 14% respectively of all of the work the database did in that time period.  These are SQL IDs: 6866vppm134gq  and 1q3184jkwq0wh.

I would start with those two statements and figure out why they are so slow.  Someone obviously put a lot of "parallel" hints in the SQL queries in your system.  Can you talk to whoever did that?  If not, you may have to investigate your server and storage hardware to see if it can benefit from those hints.  And, you will have to analyze your tables and indexes to see if these actually help.

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
alain bombledORACLE DBACommented:
 i can't read attached html report as an html file.
can you look this event  it's seem to be the fourth wait event  
C.3.127 resmgr:cpu quantum
The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.
Wait Time: The time the session waited to acquire a CPU quantum
 Location of the wait

Consumer Group ID
Consumer group ID of the session waiting. This value is from the CONSUMER_GROUP_ID column in the DBA_RSRC_CONSUMER_GROUPS view.
may be ressource manager parameters  was modified .
johnsoneSenior Oracle DBACommented:
Attachment is somehow missing the beginning <html> tag.  If you add that at the beginning of the file, it becomes readable.

The high CPU wait, is very highly likely due to the number of parallel query processes running.  If I am reading it correctly, there are 32 cores available, and the vast majority of running queries have a PARALLEL hint with at least 16 slave processes specified.  When you only have 32 cores and what looks like about 100 processes waiting for CPU, it isn't surprising.
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.