Tag: purge

Database, Errors and Bugs, Performance, SQL | PLSQL

Purging a SQL Cursor in 10g

 , , ,

Hello all,
Having issues to purge SQL Cursor in a 10g database?

DBMS_SHARED_POOL.PURGE is available from 10.2.0.4 on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.

Example:

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.


sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> alter session set events '5614566 trace name context forever';

Session altered.

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         0
Database, Performance, SQL | PLSQL

Statistics not Being Auto Purged – Splitting Purge

 , , , ,

Hi all!
The post Purge SYSAUX Tablespace,  made on Fabruary 8this, is yet being high accessed. So, if  you’re interested, here it goes another post about:

Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.

The problem was very clear in alert.log, through the entry:

Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation

But it’s happening since 2012! How to address that?
More“Statistics not Being Auto Purged – Splitting Purge”

Database, SQL | PLSQL

Purge SYSAUX Tablespace

 , , ,

Your SYSAUX is bigger than the rest of database?
It’s not uncommon to “old” databases, usually bad administrated. Some databases configuration must cause this situation.

The general indication is to review stats and reports retention of objects and database.

But if you need to clean it now, how to do?
1) PURGE_STATS. It’s recommended to execute in smaller steps. Otherwise the RBS tablespace will be blown up.
2) Oracle is sometimes building new extents for SYSAUX stats table in other tablespaces. They will be moved back to the SYSAUX tablespace.
3) The Index rebuild will decrease the size of the indexes. They are mostly larger as the raw data.
4) The Indexes are partly function bases. Therefore it is imported in which order the index rebuild will be done. Otherwise you have to reexecute this steps again and again.

Going practical, I used the follow:

exec DBMS_STATS.PURGE_STATS(SYSDATE-180);
exec DBMS_STATS.PURGE_STATS(SYSDATE-160);
exec DBMS_STATS.PURGE_STATS(SYSDATE-140);
exec DBMS_STATS.PURGE_STATS(SYSDATE-120);
exec DBMS_STATS.PURGE_STATS(SYSDATE-100);
exec DBMS_STATS.PURGE_STATS(SYSDATE-80);
exec DBMS_STATS.PURGE_STATS(SYSDATE-60);
exec DBMS_STATS.PURGE_STATS(SYSDATE-40);
exec DBMS_STATS.PURGE_STATS(SYSDATE-20);
exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;
Alter index SYS.I_WRI$_OPTSTAT_IND_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_OPR_STIME rebuild TABLESPACE SYSAUX;

Matheus.