Huge CPU load due to high %SYS usage: Oracle

Huge CPU load due to high %SYS usage: Oracle

Came across one of the interesting performance issues related to System CPU(%System) going very high< better to call Kernel CPU> on Oracle Db server box.After lot of hard work and tracing, 

It was realized that the Oracle as a part of system call calling "acpi_pm_read".

Oracle database relies  on gettimeofday(2) for timing everything from I/O calls to latch sleeps.The preferred clock source is the Time Stamp Counter (TSC) but in my case it was acpi_pm_read.  

Such context switches consumed lot of CPU and hence why the %Kernel CPU went high.

Just a brief: 

Multiprocessor systems such as NUMA or SMP have multiple instances of clock sources. The way clocks interact among themselves and the way they react to system events, such as CPU frequency scaling or entering energy economy modes, determine whether they are suitable clock sources for the real-time kernel. 

During boot time the kernel discovers the available clock sources and selects one to use. The preferred clock source is the Time Stamp Counter (TSC), but if it is not available the High Precision Event Timer (HPET) is the second best option. However, not all systems have HPET clocks and some HPET clocks can be unreliable. 

In the absence of TSC and HPET, other options include the ACPI Power Management Timer (ACPI_PM), the Programmable Interval Timer (PIT) and the Real Time Clock (RTC). 

The last two options are either costly to read or have a low resolution (time granularity), therefore they are sub-optimal for the real-time kernel. 

In my case as i mentioned the read call shows acpi_pm_read call which means it was using the non-preferred timer< ACPI Power Management Timer (ACPI_PM)>. 

Best of the lot is TSC and most of the preferred system uses the same.

You can always see what your hardware system is using by below command. Tsc is the likely outcome.

cat /sys/devices/system/clocksource/clocksource0/current_clocksource

Once set %Kernel CPU went back normal.

Note: This was my observation, It may not be same for everyone. So be careful.

V$SQL_BIND_CAPTURE Timestamp Datatype Binds are Not Captured

Being an Oracle performance expert you always be dealing with lot of queries. While doing SQL tuning of a query, it is quite common that we ask application team what binds are being used or else we capture binds from dictionary views.

But with timestamp as an bind data type most of us might have faced this issue where we see Oracle doesn’t capture TIMESTAMP data type binds variable but shows null; this is basically a bug Bug 6156624 which is supposed to get fix in 11.2 and later version.

The "bug" is that binds are not converted into a string for the value_string column.

Say for e.g. :d and :e is timestamp bind and below you can see the values are not captured with VALUE_STRING.

a99r5tq1dccym

--------------------------------------------------------

SELECT *

  FROM (SELECT   yt, xt

            FROM tab_defn

           WHERE (sy_name IN (:a, :,b :c))

             AND (effective_start_date < :d)

             AND (effective_end_date > :e)

        ORDER BY :SYS_B_0 ASC)

 WHERE ROWNUM <= :SYS_B_1

SELECT b.NAME,  b.datatype_string, b.value_string

  FROM v$sql_bind_capture b

WHERE sql_id = 'a99r5tq1dccym'

NAME                           DATATYPE_STRING                                      VALUE_STRING

------------------------------ -------------------------------------------  -------------------------------

:a                                    VARCHAR2(128)                                         X_9_XXXX

:b                                    VARCHAR2(128)                                         PP_P9_11_30

:c                                    VARCHAR2(128)                                         YYX_P9_000_31

:d                                    TIMESTAMP

:e                                    TIMESTAMP

:SYS_B_0                               NUMBER                                                1:

SYS_B_1                               NUMBER                                                30000

Now how to use it from Oracle perspective: ANYDATA.accesstimestamp (value_anydata) is the column which fetches such data. Please observe it.

/* Formatted on 2019/07/15 18:02 (Formatter Plus v4.8.8) */

SELECT b.NAME,  b.datatype_string, b.value_string,

       ANYDATA.accesstimestamp (value_anydata)

  FROM v$sql_bind_capture b

WHERE sql_id = 'a99r5tq1dccym';

NAME                           DATATYPE_STRING VALUE_STRING                   ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)

------------------------------ --------------- ------------------------------ ------------------------------------------

:a                 VARCHAR2(128)                 X_9_XXXX

:b                 VARCHAR2(128)                 PP_P9_11_30

:c                 VARCHAR2(128)                 YYX_P9_000_31

:d                 TIMESTAMP                                                           12-JUL-19 04.33.33.000000000 PM

:e                 TIMESTAMP                                                           12-JUL-19 04.33.33.000000000 PM

:SYS_B_0           NUMBER                       1

:SYS_B_1           NUMBER                       30000

So see above the timestamp got captured.

Enjoy Learning!!!!!