| refresh asa_recommendation [message #440521] |
Mon, 25 January 2010 06:25  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
