Skip to Main Content
  • Questions

  • AWR - call dbms_space.auto_space_advisor_job_proc ( )

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nikhil.

Asked: February 02, 2017 - 6:45 am UTC

Last updated: February 18, 2019 - 2:24 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Chris/Connor,

On Production (11g 11.2.0.4.0) there were delays in some application queries to execute.
While looking into AWR, encountered one entry "call dbms_space.auto_space_advisor_job_proc ()" and it took
%IO - 49.85
%CPU - 37.64
Elapsed Time (s) - 7,173.45
%Total - 3.03
CPU per Exec (s) - 2,699.98
Executions - 1
CPU Time (s) - 2,699.98

Will this be a problem?
Could you please provide any link related to what this auto_space_advisor_job_proc do.

and Connor said...

Its part of the automatic task infrastructure. From the docs:

"Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments."

You can alter the window in which the tasks run using DBMS_SCHEDULER, or you can disable it via the DBMS_AUTO_TASK_ADMIN package.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!!

Nikhil, February 02, 2017 - 5:31 pm UTC


Connor McDonald
February 03, 2017 - 7:21 pm UTC

glad we could help

A reader, February 17, 2019 - 1:30 pm UTC

I see the same "call dbms_space.auto_space_advisor_job_proc" in my AWR. Please answer the below.
When this gets triggered.? Is it an automated process?
Does this means, system crawling for additional spaces.?
Does this entry occurs mostly while performing DML's?
Connor McDonald
February 18, 2019 - 2:24 am UTC

It's part of the automatic task infrastructure. You can run:

select * from DBA_AUTOTASK_JOB_HISTORY;

to see when the tasks were run. One of them is "auto space advisor"

If you look in DBA_SCHEDULER_PROGRAMS, you'll see a program name AUTO_SPACE_ADVISOR_PROG which is a call to dbms_space.auto_space_advisor_job_proc
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library