Home » RDBMS Server » Server Administration » refresh asa_recommendation (Oracle 10g)
refresh asa_recommendation [message #440521] Mon, 25 January 2010 06:25 Go to next message
genfil
Messages: 1
Registered: January 2010
Junior Member
Hi everybody.
I'm new in Oracle and I have a huge (for me) problem.
I try reorganize the tablespaces by observing dbms_space.asa_recommendations. If there is any tablespace with recommendation "re-org" then I do DBMS_REDEFINITION. And my problem is after success reorganization. I don't know why, but in dbms_space.asa_recommendations there aren't any changes. The name of the table recommended to reorganize still appear as recommendation to reorg. When I do dbms_space.advisor also nothing is change. The Automatic Segment Advisor Job has a standard configuration.

I get information from asa_recomendations by:
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations());

I try many methods and now I send this question.

The same situation after shrink.

How can I refresh information in dbms_space.asa_recommendations() ? The DBMS_ADVISOR do nothing.

Catherine
Re: refresh asa_recommendation [message #467156 is a reply to message #440521] Thu, 22 July 2010 06:09 Go to previous message
jkli
Messages: 1
Registered: July 2010
Location: CZ
Junior Member
You need to analyze object you reorganized and then check parameters of this function to see that default it uses all (it means also old) runs of auto space advisor:

SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id

so use dbms_space.asa_recommendations('FALSE','FALSE','FALSE')
to see latest.

you can also manually invoke the run of advisor by submitting of DB job (under a SYSDBA account):

Declare
v_Job Integer;
Begin
dbms_job.submit(v_Job,
'Begin dbms_scheduler.run_job(''AUTO_SPACE_ADVISOR_JOB''); End;' );
commit;
end;
Previous Topic: Inrease SGA Parameter
Next Topic: MGMT_JOB
Goto Forum:
  


Current Time: Tue Sep 27 14:32:12 CDT 2016

Total time taken to generate the page: 0.12334 seconds