Purging SYSAUX

In continuation to my previous post “SYSAUX Growing Rapidly” , here i wanted to present the second method of purging sysaux.

Basically i tried to perform the steps as mentioned in previous post and drop_snapshot_range was taking too long (> 24hrs) and still running on test db.Again WRH$_ACTIVE_SESSION_HISTORY was in top of the list occupying most of the SYSAUX space.

SYS01> EXEC dbms_workload_repository.drop_snapshot_range(25155,26155,3179571572);


From Another session after some time 

SYS01> @asw

USERNAME		      SID    SERIAL# SPID	EVENT			       LAST_CALL_ET  WAIT_TIME SECONDS_IN_WAIT STATE		   SQL_ID	 PLAN_HASH_VALUE
------------------------- ------- ---------- ---------- ------------------------------ ------------ ---------- --------------- ------------------- ------------- ---------------
SYS			     7654	8641 47879	db file sequential read 		 28	    -1		     0 WAITED SHORT TIME   fqq01wmb4hgt8       763705880

SYS01> @orax fqq01wmb4hgt8
old   7:       sql_id  = '&&1'
new   7:       sql_id  = 'fqq01wmb4hgt8'

SQL_ID
-------------
SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fqq01wmb4hgt8
delete from WRH$_FILESTATXS tab where (:beg_snap <= tab.snap_id and	    tab.snap_id = b.start_snap_id) and			  (tab.snap_id <= b.end_snap_id))

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fqq01wmb4hgt8',NULL,'typical +peeked_binds allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fqq01wmb4hgt8, child number 0
-------------------------------------
delete from WRH$_FILESTATXS tab where (:beg_snap <= tab.snap_id and
    tab.snap_id =
b.start_snap_id) and			      (tab.snap_id <=
b.end_snap_id))

Plan hash value: 763705880

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name 	      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT		 |		      |        |       |   325K(100)|	       |       |       |       |       |	  |
|   1 |  DELETE 			 | WRH$_FILESTATXS    |        |       |	    |	       |       |       |       |       |	  |
|*  2 |   FILTER			 |		      |        |       |	    |	       |       |       |       |       |	  |
|   3 |    MERGE JOIN ANTI		 |		      |    494M|    23G|   325K  (1)| 01:05:08 |       |       |       |       |	  |
|   4 |     PARTITION RANGE ITERATOR	 |		      |    494M|  8957M|   325K  (1)| 01:05:08 |   KEY |   KEY |       |       |	  |
|*  5 |      INDEX RANGE SCAN		 | WRH$_FILESTATXS_PK |    494M|  8957M|   325K  (1)| 01:05:08 |   KEY |   KEY |       |       |	  |
|*  6 |     FILTER			 |		      |        |       |	    |	       |       |       |       |       |	  |
|*  7 |      SORT JOIN			 |		      |      1 |    33 |     2	(50)| 00:00:01 |       |       | 73728 | 73728 |	  |
|*  8 |       TABLE ACCESS BY INDEX ROWID| WRM$_BASELINE      |      1 |    33 |     1	 (0)| 00:00:01 |       |       |       |       |	  |
|*  9 |        INDEX RANGE SCAN 	 | WRM$_BASELINE_PK   |      1 |       |     1	 (0)| 00:00:01 |       |       |       |       |	  |
---------------------------------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 0
   2 - (NUMBER): 95781
   3 - (NUMBER): 3179571572

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:BEG_SNAP=:BEG_SNAP AND "TAB"."SNAP_ID"="B"."START_SNAP_ID" AND "TAB"."SNAP_ID"=:BEG_SNAP AND "B"."START_SNAP_ID"<=:END_SNAP))
   9 - access("B"."DBID"=:DBID)

SYS01> col name format a10;
col VALUE_STRING format a30;
select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = '&sqlid';
SYS01>   2  Enter value for sqlid: fqq01wmb4hgt8
old   2: anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = '&sqlid'
new   2: anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = 'fqq01wmb4hgt8'

NAME	     POSITION DATATYPE_STRING						   WAS VALUE_STRING
---------- ---------- ------------------------------------------------------------ --- ------------------------------
ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
---------------------------------------------------------------------------
:BEG_SNAP	    1 NUMBER							   YES 0


:END_SNAP	    2 NUMBER							   YES 95781


:DBID		    3 NUMBER							   YES 3179571572

Interestingly, looking at the bind values shows value_string 0 and 95781 for BEG_SNAP and END_SNAP respectively, though the input range for drop snapshot was between 25155 and 26155.

The database was refreshed by client (so my session was no more) and so i thought not to take drop_snapshot_range approach. After going through few blogs and MOS documents, i thought we had 2 approaches :-

1. “Recreate the AWR tables as in the MOS note 782974.1” , which would basically drop all WRH$* table and then recreate. The AWR tables contains wealth of important performance data which can be very useful in performance tuning trend analysis and also in comparing performance between two separate periods of time.Hence recreating AWR,I believe should be the last resort. The activity needs to be done in startup restrict mode so requires downtime.

And if you plan to go forward with it, I would recommend to export the AWR snapshot data using @?/rdbms/admin/awrextr.sql and keep the dump. In future it can used by simply importing to some other repository db to get the AWR data.

2. Simply delete the Orphaned rows from WRH$_ACTIVE_SESSION_HISTORY table and perform shrink space cascade.

I went ahead with 2nd approach and performed the below steps (Note: – DB was a single instance db)


SYS01> SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

ORPHANED_ASH_ROWS
-----------------
        301206452

SYS01> alter table wrh$_active_session_history parallel 4;

Table altered.

SYS01> alter session force parallel dml;

Session altered.

SYS01> DELETE /*+ PARALLEL(a,4) */
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );


From Another session :-

SYS01> @asw

USERNAME		      SID    SERIAL# SPID	EVENT			       LAST_CALL_ET  WAIT_TIME SECONDS_IN_WAIT STATE
------------------------- ------- ---------- ---------- ------------------------------ ------------ ---------- --------------- -------------------
SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
SYS			      921	1329 107213	db file sequential read 		 60	     0		     0 WAITING
144bpj4qg68m1	   2217072169

SYS			     1227	 889 107215	db file sequential read 		 60	     0		     0 WAITING
144bpj4qg68m1	   2217072169

SYS			     9181	3277 107211	db file sequential read 		 60	     1		     0 WAITED KNOWN TIME
144bpj4qg68m1	   2217072169

SYS			     3370	 455 107727	SQL*Net message to client		  0	    -1		     0 WAITED SHORT TIME
8tfjp8cd2xtd1	    193683216

SYS			     1840	 809 107217	PX Deq Credit: need buffer		 60	     0		     0 WAITING
144bpj4qg68m1	   2217072169

SYS			     8875	3889 107209	db file sequential read 		 60	     1		     0 WAITED KNOWN TIME
144bpj4qg68m1	   2217072169

SYS			     8266	3139 90257	PX Deq: Execute Reply	 60	     0		    60 WAITING
144bpj4qg68m1	   2217072169


SYS01> @parallel_sess

Username     QC/Slave SlaveSet SID					Slave INS STATE    WAIT_EVENT			  QC SID QC INS Req. DOP Actual DOP
------------ -------- -------- ---------------------------------------- --------- -------- ------------------------------ ------ ------ -------- ----------
SYS	     QC 	       8266					1	  WAIT	   PX Deq: Execute Reply	  8266
 - p000      (Slave)  1        8875					1	  WAIT	   db file sequential read	  8266	 1	       4	  4
 - p003      (Slave)  1        1227					1	  WAIT	   db file sequential read	  8266	 1	       4	  4
 - p001      (Slave)  1        9181					1	  WAIT	   db file sequential read	  8266	 1	       4	  4
 - p002      (Slave)  1        921					1	  WAIT	   db file sequential read	  8266	 1	       4	  4
 - p004      (Slave)  2        1840					1	  WAIT	   PX Deq Credit: send blkd	  8266	 1	       4	  4
 - p007      (Slave)  2        2757					1	  WAIT	   PX Deq: Execution Msg	  8266	 1	       4	  4
 - p006      (Slave)  2        2450					1	  WAIT	   PX Deq: Execution Msg	  8266	 1	       4	  4
 - p005      (Slave)  2        2147					1	  WAIT	   PX Deq: Execution Msg	  8266	 1	       4	  4

SYS01> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('144bpj4qg68m1',NULL,'typical +peeked_binds allstats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	144bpj4qg68m1, child number 0
-------------------------------------
DELETE /*+ PARALLEL(a,4) */ FROM wrh$_active_session_history a WHERE
NOT EXISTS   (SELECT 1	 FROM wrm$_snapshot   WHERE snap_id	  =
a.snap_id   AND dbid		= a.dbid   AND instance_number =
a.instance_number   )

Plan hash value: 2217072169

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name 			  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |	TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT		 |				  |	   |	   |   106K(100)|	   |	   |	   |	    |	   |		|
|   1 |  PX COORDINATOR 		 |				  |	   |	   |		|	   |	   |	   |	    |	   |		|
|   2 |   PX SEND QC (RANDOM)		 | :TQ10001			  |    298M|	11G|   106K  (1)| 00:21:14 |	   |	   |  Q1,01 | P->S | QC (RAND)	|
|   3 |    DELETE			 | WRH$_ACTIVE_SESSION_HISTORY	  |	   |	   |		|	   |	   |	   |  Q1,01 | PCWP |		|
|   4 |     PX RECEIVE			 |				  |    298M|	11G|   106K  (1)| 00:21:14 |	   |	   |  Q1,01 | PCWP |		|
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10000			  |    298M|	11G|   106K  (1)| 00:21:14 |	   |	   |  Q1,00 | P->P | HASH (BLOCK|
|   6 |       NESTED LOOPS ANTI 	 |				  |    298M|	11G|   106K  (1)| 00:21:14 |	   |	   |  Q1,00 | PCWP |		|
|   7 |        PX PARTITION RANGE ALL	 |				  |    298M|  7404M|   106K  (1)| 00:21:14 |	 1 |	 3 |  Q1,00 | PCWC |		|
|   8 | 	INDEX FULL SCAN 	 | WRH$_ACTIVE_SESSION_HISTORY_PK |    298M|  7404M|   106K  (1)| 00:21:14 |	 1 |	 3 |  Q1,00 | PCWP |		|
|*  9 |        INDEX UNIQUE SCAN	 | WRM$_SNAPSHOT_PK		  |	 1 |	15 |	 0   (0)|	   |	   |	   |  Q1,00 | PCWP |		|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("DBID"="A"."DBID" AND "SNAP_ID"="A"."SNAP_ID" AND "INSTANCE_NUMBER"="A"."INSTANCE_NUMBER")

The deletion of rows (301206452 rows) completed with elapsed time of 12:59:38.44.


301206452 rows deleted.

Elapsed: 12:59:38.44

SYS01> alter table wrh$_active_session_history noparallel ;

Table altered.

SYS01> select degree from dba_tables where table_name=upper('wrh$_active_session_history');

DEGREE
----------
         1

SYS01> SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

ORPHANED_ASH_ROWS
-----------------
	   309984

SYS01> DELETE /*+ PARALLEL(a,4) */ FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );  

309984 rows deleted.

Elapsed: 00:00:19.08
SYS01> commit;

Commit complete.

Elapsed: 00:00:00.07

SYS01> SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

ORPHANED_ASH_ROWS
-----------------
		0

SYS01> alter table wrh$_active_session_history shrink space cascade;

Table altered.

Elapsed: 06:47:21.36

Before this activity SM/AWR was occupying 339Gb which reduced to 209Gb. Also had SM/OPTSTAT occupying 143Gb space and after confirmation from client purged the stats as it was test db.

SYS01> exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.54

SYS01> COLUMN "Item" FORMAT A25
 COLUMN "Space Used (GB)" FORMAT 999.99
 COLUMN "Schema" FORMAT A25
 COLUMN "Move Procedure" FORMAT A40

    SELECT  occupant_name "Item",
    space_usage_kbytes/1048576 "Space Used (GB)",
    schema_name "Schema",
    move_procedure "Move Procedure"
    FROM v$sysaux_occupants
    WHERE occupant_name in  ('SM/AWR','SM/OPTSTAT')
    ORDER BY 1
    /23:47:31 EMTSYS01> 

Item                      Space Used (GB) Schema                    Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
SM/AWR                             209.16 SYS
SM/OPTSTAT                          19.72 SYS

Saving in SYSAUX


TABLESPACE_NAME                  TSP_SIZE USED_SPACE FREE_SPACE   PCT_FREE
------------------------------ ---------- ---------- ---------- ----------
SYSAUX                             505856     496310       9546       1.89  --> Before Size

SYSAUX                             505856     237833     268023      52.98  --> After Size

Hope this helps 🙂

Advertisements

One thought on “Purging SYSAUX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s