Re: Missing DB time and enq:TM question;

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 29 Nov 2017 09:43:50 +0100 (CET)
Message-ID: <1312557637.292953.1511945030943_at_ox.hosteurope.de>


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
Received on Wed Nov 29 2017 - 09:43:50 CET

Original text of this message