In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task. DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
We had situation demanding tuning of MV refresh and options like TUNE_MVIEW and Atomic_Refresh was explored . Hence Documenting materialized view sample commands and views in this blog . After migration if we are facing slowness in Materialized View refresh , we can explore to re-create Materialized View Since main topic for this blog was MV issues in 19c , posting that topic first in line below We also have Real-Time Materialized Views to explore . To Note : 1) We can create Mv on ON PREBUILT TABLE 2) We can create Index on Mv 3) We can gather stats on mv just like other table . 4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv REFRESH_METHOD can be Complete / FAST or FORCE . A FORCE attempts a FAST...
Comments
Post a Comment