ORACLE CPU Utilization

Check out the history

SELECT snap_id, 
       begin_time||'-'||end_time AS TIME,
       metric_name, 
       metric_unit,
       ROUND(maxval) MAX, ROUND(AVERAGE) AVG
 FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE UPPER(metric_name ) LIKE '%CPU%' 
  AND end_TIME > SYSDATE -3
ORDER BY metric_name ASC, snap_id DESC

SNAP_ID TIME                    METRIC_NAME              METRIC_UNIT              MAX AVG

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

  27535 20162911 07:59-08:59 CPU Usage Per Sec    CentiSeconds Per Second      292 36

  27534 20162911 06:59-07:59 CPU Usage Per Sec    CentiSeconds Per Second       19 14

  27533 20162911 05:59-06:59 CPU Usage Per Sec    CentiSeconds Per Second       25 15

  27532 20162911 04:59-05:59 CPU Usage Per Sec    CentiSeconds Per Second       58 16

  27531 20162911 04:00-04:59 CPU Usage Per Sec    CentiSeconds Per Second      392 26

  27530 20162911 03:00-04:00 CPU Usage Per Sec    CentiSeconds Per Second     1375 74

  27529 20162911 02:00-03:00 CPU Usage Per Sec    CentiSeconds Per Second      105 84

  27528 20162911 01:00-02:00 CPU Usage Per Sec    CentiSeconds Per Second      797 37

  27527 20162911 00:00-01:00 CPU Usage Per Sec    CentiSeconds Per Second      177 19

  27535 20162911 07:59-08:59 CPU Usage Per Txn    CentiSeconds Per Txn          53  6

  27534 20162911 06:59-07:59 CPU Usage Per Txn    CentiSeconds Per Txn           4  3

  27533 20162911 05:59-06:59 CPU Usage Per Txn    CentiSeconds Per Txn           4  3

  27532 20162911 04:59-05:59 CPU Usage Per Txn    CentiSeconds Per Txn          12  3

  27531 20162911 04:00-04:59 CPU Usage Per Txn    CentiSeconds Per Txn          32  4

  27530 20162911 03:00-04:00 CPU Usage Per Txn    CentiSeconds Per Txn         274 14

  27529 20162911 02:00-03:00 CPU Usage Per Txn    CentiSeconds Per Txn          21 16

  27528 20162911 01:00-02:00 CPU Usage Per Txn    CentiSeconds Per Txn          87  4

  27527 20162911 00:00-01:00 CPU Usage Per Txn    CentiSeconds Per Txn          37  4

  27535 20162911 07:59-08:59 Database CPU Time Ratio   % Cpu/DB_Time            10  2

  27534 20162911 06:59-07:59 Database CPU Time Ratio   % Cpu/DB_Time             2  1

  27533 20162911 05:59-06:59 Database CPU Time Ratio   % Cpu/DB_Time             2  1

  27532 20162911 04:59-05:59 Database CPU Time Ratio   % Cpu/DB_Time             5  1

  27531 20162911 04:00-04:59 Database CPU Time Ratio   % Cpu/DB_Time             4  2

  27530 20162911 03:00-04:00 Database CPU Time Ratio   % Cpu/DB_Time             56 5

  27529 20162911 02:00-03:00 Database CPU Time Ratio   % Cpu/DB_Time              9 8

  27528 20162911 01:00-02:00 Database CPU Time Ratio   % Cpu/DB_Time             37 3

  27527 20162911 00:00-01:00 Database CPU Time Ratio   % Cpu/DB_Time              3 1

  27535 20162911 07:59-08:59 Host CPU Utilization (%)  % Busy/(Idle+Busy)      100 68

  27534 20162911 06:59-07:59 Host CPU Utilization (%)  % Busy/(Idle+Busy)       64 62

  27533 20162911 05:59-06:59 Host CPU Utilization (%)  % Busy/(Idle+Busy)      100 59

  27532 20162911 04:59-05:59 Host CPU Utilization (%)  % Busy/(Idle+Busy)       99 46

  27531 20162911 04:00-04:59 Host CPU Utilization (%)  % Busy/(Idle+Busy)       92 45

  27530 20162911 03:00-04:00 Host CPU Utilization (%)  % Busy/(Idle+Busy)       88 64

  27529 20162911 02:00-03:00 Host CPU Utilization (%)  % Busy/(Idle+Busy)      100 73

  27528 20162911 01:00-02:00 Host CPU Utilization (%)  % Busy/(Idle+Busy)       95 64  

  27527 20162911 00:00-01:00 Host CPU Utilization (%)  % Busy/(Idle+Busy)       82 58

SELECT snap_id, 

       begin_time||'-'||end_time AS TIME,

       metric_name, 

       metric_unit,

       ROUND(maxval) MAX, ROUND(AVERAGE) AVG

 FROM DBA_HIST_SYSMETRIC_SUMMARY

WHERE UPPER(metric_name ) LIKE '%PARSE%' 

  AND end_TIME > SYSDATE -3

ORDER BY metric_name ASC, snap_id DESC

SNAP_ID TIME                 METRIC_NAME                 METRIC_UNIT              MAX AVG

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

27535 20162911 07:59-08:59 Execute Without Parse Ratio %(ExecWOParse/TotalExec)   100  80

27534 20162911 06:59-07:59 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    86  84

27533 20162911 05:59-06:59 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    87  83

27532 20162911 04:59-05:59 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    87  84

27531 20162911 04:00-04:59 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    87  82

27530 20162911 03:00-04:00 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    99  86

27529 20162911 02:00-03:00 Execute Without Parse Ratio %(ExecWOParse/TotalExec)   100  87

27528 20162911 01:00-02:00 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    87  82

27527 20162911 00:00-01:00 Execute Without Parse Ratio %(ExecWOParse/TotalExec)    86  82

27535 20162911 07:59-08:59 Hard Parse Count Per Sec  Parses Per Second              6   0

27534 20162911 06:59-07:59 Hard Parse Count Per Sec  Parses Per Second              5   0

27533 20162911 05:59-06:59 Hard Parse Count Per Sec  Parses Per Second              7   0

27532 20162911 04:59-05:59 Hard Parse Count Per Sec  Parses Per Second              7   0

27531 20162911 04:00-04:59 Hard Parse Count Per Sec  Parses Per Second             53   2

27530 20162911 03:00-04:00 Hard Parse Count Per Sec  Parses Per Second             64   4

27529 20162911 02:00-03:00 Hard Parse Count Per Sec  Parses Per Second            188  20

27528 20162911 01:00-02:00 Hard Parse Count Per Sec  Parses Per Second             40 2

27527 20162911 00:00-01:00 Hard Parse Count Per Sec  Parses Per Second             50   1

27535 20162911 07:59-08:59 Hard Parse Count Per Txn  Parses Per Txn                 1   0

27534 20162911 06:59-07:59 Hard Parse Count Per Txn  Parses Per Txn                 1   0

27533 20162911 05:59-06:59 Hard Parse Count Per Txn  Parses Per Txn                 1   0

27532 20162911 04:59-05:59 Hard Parse Count Per Txn  Parses Per Txn                 1   0

27531 20162911 04:00-04:59 Hard Parse Count Per Txn  Parses Per Txn                 4   0

27530 20162911 03:00-04:00 Hard Parse Count Per Txn  Parses Per Txn                 4   0

27529 20162911 02:00-03:00 Hard Parse Count Per Txn  Parses Per Txn                 38  4

27528 20162911 01:00-02:00 Hard Parse Count Per Txn  Parses Per Txn                 3   0

27527 20162911 00:00-01:00 Hard Parse Count Per Txn  Parses Per Txn                 3   0

27535 20162911 07:59-08:59 Parse Failure Count Per Sec Parses Per Second     0   0

27534 20162911 06:59-07:59 Parse Failure Count Per Sec Parses Per Second     0   0

27533 20162911 05:59-06:59 Parse Failure Count Per Sec Parses Per Second     0   0

27532 20162911 04:59-05:59 Parse Failure Count Per Sec Parses Per Second     0   0

27531 20162911 04:00-04:59 Parse Failure Count Per Sec Parses Per Second    19   0

27530 20162911 03:00-04:00 Parse Failure Count Per Sec Parses Per Second    21 1

27529 20162911 02:00-03:00 Parse Failure Count Per Sec Parses Per Second     0 0

27528 20162911 01:00-02:00 Parse Failure Count Per Sec Parses Per Second    11   0

27527 20162911 00:00-01:00 Parse Failure Count Per Sec Parses Per Second    11   0

27535 20162911 07:59-08:59 Soft Parse Ratio        % SoftParses/TotalParses   100  99

27534 20162911 06:59-07:59 Soft Parse Ratio        % SoftParses/TotalParses   100 100

27533 20162911 05:59-06:59 Soft Parse Ratio        % SoftParses/TotalParses   100 100

27532 20162911 04:59-05:59 Soft Parse Ratio        % SoftParses/TotalParses   100 100

27531 20162911 04:00-04:59 Soft Parse Ratio          % SoftParses/TotalParses   100  98

27530 20162911 03:00-04:00 Soft Parse Ratio        % SoftParses/TotalParses   100  97

27529 20162911 02:00-03:00 Soft Parse Ratio        % SoftParses/TotalParses   100  96

27528 20162911 01:00-02:00 Soft Parse Ratio        % SoftParses/TotalParses   100  98

27527 20162911 00:00-01:00 Soft Parse Ratio        % SoftParses/TotalParses   100  99

27535 20162911 07:59-08:59 Total Parse Count Per Sec Parses Per Second   118  50

27534 20162911 06:59-07:59 Total Parse Count Per Sec Parses Per Second    59  31

27533 20162911 05:59-06:59 Total Parse Count Per Sec Parses Per Second   343  37

27532 20162911 04:59-05:59 Total Parse Count Per Sec Parses Per Second    74  29

27531 20162911 04:00-04:59 Total Parse Count Per Sec Parses Per Second   188  37

27530 20162911 03:00-04:00 Total Parse Count Per Sec Parses Per Second   369  70

27529 20162911 02:00-03:00 Total Parse Count Per Sec Parses Per Second  1532 156

27528 20162911 01:00-02:00 Total Parse Count Per Sec Parses Per Second   177  38

27527 20162911 00:00-01:00 Total Parse Count Per Sec Parses Per Second   273  35

27535 20162911 07:59-08:59 Total Parse Count Per Txn Parses Per Txn            22   9

27534 20162911 06:59-07:59 Total Parse Count Per Txn Parses Per Txn         9   6

27533 20162911 05:59-06:59 Total Parse Count Per Txn Parses Per Txn            55   7

27532 20162911 04:59-05:59 Total Parse Count Per Txn Parses Per Txn            14   6

27531 20162911 04:00-04:59 Total Parse Count Per Txn Parses Per Txn             35   7

27530 20162911 03:00-04:00 Total Parse Count Per Txn Parses Per Txn            38  12

27529 20162911 02:00-03:00 Total Parse Count Per Txn Parses Per Txn           294  30

27528 20162911 01:00-02:00 Total Parse Count Per Txn Parses Per Txn            30   6

27527 20162911 00:00-01:00 Total Parse Count Per Txn Parses Per Txn            18   6

Check on the Parse Wait Stats

http://www.databasejournal.com/features/oracle/article.php/10893_3341851_2/Parsing-in-Oracle.htm

Possible Solutions

Option one to reduce hard parse events:

1. SELECT name, value FROM V$PARAMETER WHERE name LIKE '%cursor%'

2. Change CURSOR SHARING from EXACT which is the default to SIMILAR

ALTER SYSTEM SET CURSOR_SHARING = 'SIMILAR'