Oracle 12.1 CPU 100%

View overall load

CPU usage is high; Basically the overall CPU is over 80%;

At this time: physical reading 600mb/s,redo is not too high;

All for latch free, usually bug-prone

Check related SQL

Basic for System SQL:4B4WP0A8DVKF0,11BZV8FM87ZVR,3UQKKFU1CRW8Y,FRJD8ZFY2JFDQ,8MDZ49ZKAJHW3
Related bugs:
1 frequent execution of Recursive Sql Metrics Collector sql_id "FRJD8ZFY2JFDQ" in 12.1.0.2 (Doc ID 2059121.1)
Alter SYSETM set _optimizer_dynamic_sampling=0
2 Very Long Parse time for Queries in InMemory Database (Doc ID 2102106.1)
High "Latch free" Waits with contention on ' Result cache:rc Latch ' When result_cache_mode = MANUAL on Oracle 12c (Doc ID 2002089.1)
Alter system set "_optimizer_ads_use_result_cache" = FALSE

3 Bug 18511269:ora-600 [kksgagetnoalloc_int0] RUNNING WORKLOAD & ONLINE redef concurrently (Doc ID 2175884.1)
Symptoms

Following error is reported on the alert log and trace file may contain the following information:

Dump continued from file:/u01/app/oracle/diag/rdbms/flowr/flowr2/trace/flowr2_ora_31705.trc
[TOC00001]
Ora-00600:internal error code, arguments: [Kksgagetnoalloc_int0], [11], [11], [], [], [], [], [], [], [], [], []
-----current SQL Statement to this session (SQL_ID=4B4WP0A8DVKF0)-----
SELECT executions, End_of_fetch_count, Elapsed_time/px_servers elapsed_time, Cpu_time/px_servers cpu_time, Buffer_ Gets/executions buffer_gets from (SELECT sum (executions_delta) as executions, sum (case when Px_servers_execs_delta > 0 Then Px_servers_execs_delta else Executions_delta end) as Px_servers, sum (end_of_fetch_count_delta) as End_of_fetch_ Count, sum (Elapsed_time_delta) as Elapsed_time, sum (Cpu_time_delta) as Cpu_time, sum (buffer_gets_delta) as Buffer_gets From Dba_hist_sqlstat S, v$database D, dba_hist_snapshot sn WHERE s.dbid = d.dbid and Bitand (NVL (s.flag, 0), 1) = 0 and SN . end_interval_time > (select Systimestamp at time ZONE Dbtimezone from dual)-7 and s.sql_id =: 1 and s.snap_id = SN.S nap_id and S.instance_number = sn.instance_number and s.dbid = sn.dbid and Parsing_schema_name =: 2)
[Toc00004-end]

Client Details:
O/S info:user: , term:, ospid:31700
Machine: 3012 Program: [Email protected]***** (TNS v1-v3)
Application name:oracle Enterprise manager.metric Engine, hash value=2371535196

This bug can only be patched;

4 8mdz49zkajhw3,ora-4031 During Automatic report Flush Query execution after Upgrade to 12.1.0.2 (Doc ID 2330230.1)
To avoid the error with any of below alternatives:

1/increase the SGA size by running the sga_target_advisor and set a minimum size for shared_pool_size based on the Recomm Endation of the shared pool advisor.

• or-

2/use The workaround:

Turn off the "Automatic Report Flush" feature by setting:
SQL> alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */

Reference:
Troubleshooting ' Cursor:pin S wait on X ' waits. (Doc ID 1349387.1)
High Waits in real-time descriptor Latch from 12.1.0.1 (Doc ID 2382299.1)
Database hang on 12c with ' Cursor:pin S wait on X ', ' Row cache lock ' and ' Library cache Load Lock ' (Doc ID 2127483.1)
12c: ' Acknowledge over PGA limit ' Wait Event (Doc ID 2138882.1)
High wait time for ' Cursor:pin S Wait in X ' Event after Upgrade (Doc ID 1949691.1)

Oracle 12.1 CPU 100%