Re: Missing DB time and enq:TM question;

Thanks Jonathan and Stefan for your reply.

2) Please clarify the question. Is the current insert waiting to acquire a TM lock (and in what mode), or is a session that you believe has only done inserts into a table causing other sessions to wait, if so in what mode is the holder holding, and in what mode are the requesters waiting ? If the state is still live, post the extract from v$lock that shows the holders and (a few of the) waiters

[A] This is while looking into an AWR/ADDM reports we figured out that on one of the AWRs the database was spending time in enq: TM. The ADDM report for the same duration had this recommendation (This db is an enterprise edition 11gR2). Unfortunately the retention period for snapshots is only 7 days so we don`t have any more data in Oracle. But in order to prevent this from happening again, I tried to see if there is any foreign key defined on LOG_ACTV table and there is none. So I am wondering why and insert on LOG_ACTV would cause the insert to wait on enq: TM.

Action

      Investigate the INSERT statement with SQL_ID "7c94npqhm045n" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 7c94npqhm045n.
         INSERT INTO LOG_ACTV (. . . .) values (. . . .)
   Rationale
      The SQL spent only 1% of its database time on CPU, I/O and Cluster
      waits. Therefore, the SQL Tuning Advisor is not applicable in this
case.
      Look at performance data for the SQL to find potential improvements.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "7c94npqhm045n" was executed 11557 times and
      had an average elapsed time of 0.58 seconds.
   Rationale
      Waiting for event "enq: TM - contention" in wait class "Application"
      accounted for 98% of the database time spent in processing the SQL
      statement with SQL_ID "7c94npqhm045n".

Regards,
Ashish

On Wed, Nov 29, 2017 at 4:43 PM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hello Ashish,
> as Jonathan pointed out - you need to fix the STATSPACK metrics for Oracle
> 12c as it is not adapted anymore by Oracle.
>
> Franck Pachot has written a blog post about it:
> https://blog.dbi-services.com/statspack-idle-events/
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Jonathan Lewis hat am 29. November 2017 um 09:35 geschrieben:
> >
> > 1)
> > A couple of comments:
> > It's statspack, not AWR, so there may be a couple of anomalies where an
> AWR report would give you different figures.
> > Example: The "LGWR worker group idle" is a background idling event, so
> shouldn't appear in the foreground active list.
> >
> > You have 20 Cores and 22 CPUs - that's very odd, CPUs is usually an
> integer multiple of Cores. Do you have an explicit setting for CPU_COUNT in
> your parameter file that is set for resource manager reasons ? Is it
> possible that you have hit an oddity with an accounting error because of
> this, or because of some CPU configuration anomaly at the O/S level.
> >
> > I would always cross-check the DB CPU figure with the OS Stats CPU
> information, and with the Instance Activity "CPU used by this session, CPU
> used when session started" figures - they may reveal an interesting
> inconsistency. I would also examine the headline details on SQL ordered by
> CPU to see if the amount of CPU was hugely inconsistent with the DB CPU
> reported.
> >
> > In this case (i.e. statspack) I would check the big numbers in the "idle
> waits" and "background waits" sections just in case a relevant foreground
> wait that made up the difference had been reported in the wrong category.
> >
> > 2) Please clarify the question. Is the current insert waiting to acquire
> a TM lock (and in what mode), or is a session that you believe has only
> done inserts into a table causing other sessions to wait, if so in what
> mode is the holder holding, and in what mode are the requesters waiting ?
> If the state is still live, post the extract from v$lock that shows the
> holders and (a few of the) waiters
> >
> > Regards
> > Jonathan Lewis
> >
> > ________________________________________
> > From: oracle-l-bounce_at_freelists.org on behalf of Ashish Lunawat
> > Sent: 29 November 2017 01:59
> > To: oracle-l_at_freelists.org
> > Subject: Missing DB time and enq:TM question;
> >
> > Hi,
> >
> > In this statspack report the top 5 event accounts only for about 55% of
> the DB time. Any clue where is the remaining time gone?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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