Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

"auto optimizer stats collection" task in Oracle 12cR1 takes very long time to complete and uses hig

KrishCMay 8 2017 — edited Feb 11 2018

How to address the issue "auto optimizer stats collection" task in Oracle 12cR1 takes very long time to complete and uses high (>98%) CPU? We are in the process of migrating from Oracle 11gR2 to "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit". The task is experiencing this issue only in Oracle 12cR1, but not in Oracle 11gR2.

The task in Oracle 12cR1 is taking 4+ hours and up to ~6 hours 30 minutes to complete. During weekdays (3 to 4 days), it times-out after 4 hours because the maintenance window by default is set to 4 hours and it's SYS process is using high (>98%) CPU until the task times-out. And during weekend days, the task successfully completes because the maintenance window by default is set to 20 hours. Following table shows some sample data of the task:

CLIENT_NAME

WINDOW_NAME

WINDOW_DURATION

JOB_NAME

JOB_STATUS

JOB_START_TIME

JOB_DURATION

JOB_INFO

auto optimizer stats collection

SATURDAY_WINDOW

+00 20:00:00.212149

ORA$AT_OS_OPT_SY_568

SUCCEEDED

5/6/2017 6:00:04.875350 AM
  +00:00

+00 06:35:03.000000

Tier1 team did not report high CPU usage issue

auto optimizer stats collection

SATURDAY_WINDOW

+00 20:00:00.205465

ORA$AT_OS_OPT_SY_531

SUCCEEDED

4/29/2017 6:00:03.370418 AM
  +00:00

+00 05:31:09.000000

Tier1 team did not report high CPU usage issue

auto optimizer stats collection

SATURDAY_WINDOW

+00 19:59:56.255947

ORA$AT_OS_OPT_SY_494

SUCCEEDED

4/22/2017 6:00:06.192118 AM
  +00:00

+00 04:38:51.000000

Tier1 team did not report high CPU usage issue

auto optimizer stats collection

FRIDAY_WINDOW

+00 04:00:00.025839

ORA$AT_OS_OPT_SY_565

STOPPED

5/5/2017 10:00:04.698943 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called because associated window was closed"

auto optimizer stats collection

THURSDAY_WINDOW

+00 03:59:59.934789

ORA$AT_OS_OPT_SY_562

STOPPED

5/4/2017 10:00:04.625673 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

TUESDAY_WINDOW

+00 04:00:00.035226

ORA$AT_OS_OPT_SY_556

STOPPED

5/2/2017 10:00:04.158322 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

WEDNESDAY_WINDOW

+00 04:00:00.000297

ORA$AT_OS_OPT_SY_559

STOPPED

5/3/2017 10:00:04.464612 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

WEDNESDAY_WINDOW

+00 04:00:00.178388

ORA$AT_OS_OPT_SY_522

STOPPED

4/26/2017 10:00:02.935923 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

WEDNESDAY_WINDOW

+00 04:00:00.047155

ORA$AT_OS_OPT_SY_485

STOPPED

4/19/2017 10:00:05.843111 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

THURSDAY_WINDOW

+00 04:00:00.020172

ORA$AT_OS_OPT_SY_525

STOPPED

4/27/2017 10:00:03.052138 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

FRIDAY_WINDOW

+00 04:00:00.044961

ORA$AT_OS_OPT_SY_528

STOPPED

4/28/2017 10:00:03.355112 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

THURSDAY_WINDOW

+00 04:00:00.020326

ORA$AT_OS_OPT_SY_488

STOPPED

4/20/2017 10:00:05.953047 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

auto optimizer stats collection

FRIDAY_WINDOW

+00 03:59:59.952418

ORA$AT_OS_OPT_SY_491

STOPPED

4/21/2017 10:00:06.122686 PM
  +00:00

+00 03:59:58.000000

REASON="Stop job called
  because associated window was closed"

This post has been answered by KrishC on Feb 3 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 3 2018
Added on May 8 2017
37 comments
6,515 views