Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: August 27, 2016 - 1:42 am UTC

Last updated: September 01, 2016 - 2:45 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Team,

We have a database in there we have observed every day morning 4 to 5 AM during this one hr application user not be able to insert data into the table. We seen that in application web page where red count high means not be able to insert that.
As a dba i monitor that same time on db level to check CPU load, Active Session, Inactive Session, Table Lock, Alert log but nothing is there to say it is related to db So, can you pls let me know
1. Anything more to check from db hand ?
2. What things need to check at this issue time ?
3. How to prove them it is not related to DB ?
4. What possibilities may be there ?
5. Which section needs to check in AWR Report to diagnose the problem ?
6. Any way to get the exact issue ?

Thanks

and Connor said...

If you can get a glimpse of v$active_session_history (ASH) for the impacted session during this time, that should give a definitive answer to this.

If they are blocked on something, or just running slowly, then they will most likely have an entry every second in there. (And as long as your sga is large enough, you dont have to see in this real time - you can check later in the morning and the samples should still be there).

If there are no entries for the session, then it is very likely the issue is not within the database. I say "very likely" because its *possible* that the session is doing lots of tiny small snippets of database work that are not being caught in ASH...but the probability of that (and having the user blocked) is pretty low.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, August 31, 2016 - 11:23 am UTC

Hi team,

Thanks for reply,

Now i have seen the wait_event like

SQL> select count(*),wait_class from v$session where type='USER' and status='ACTIVE' group by wait_class order by 2;

COUNT(*) WAIT_CLASS
---------------------------------------
11 Idle
1 Network
28 User I/O
So how to overcome user i/o and i have seen the sql_id which is used frequently and try to get recommendation like

sqltrpt.sql but no recommendations are there and in awr it is taking very much cpu so how to overcome with this.

Thanks
Connor McDonald
September 01, 2016 - 2:45 am UTC

I think you are taking 2 plus 2 and making 17.

We don't know if those active sessions are related to your app, or your web page - they could something (anything) else. Ideally you want to track from end-to-end, a slow web page, to its application server process, to its database connection(s) and *then* assess *those* sessions.

But let's *assume* that the SQL_ID you mentioned is indeed the culprit - ie, it is ranked very high in the AWR report.

Extract the SQL text and the sql plan, and take a look at tuning based on what the plan looks like.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.