HOW TO PURGE BIND VARIABLES INFORMATION ORACLE
Bind variables are the nitrous booster for SQL execution speed because they help the application to reuse the cursor in library cache which aids parsing. There are ton of information about the use of bind variables but no resource available on how to maintain those bind variables in database. Lets see!
Check total bind record count. wrh$_SQL_BIND_METADATA is the history or snapshot table which store bind metadata. There are two scenarios.
- Purge bind from memory (wrh$_SQL_BIND_METADATA)
- Purge bind from disk (DBA_HIST_SQL_BIND_METADATA)
kIsH@x3z6zx9<^>select count(*) from wrh$_SQL_BIND_METADATA;
COUNT(*)
----------
1534
Check the first 6 records
kIsH@x3z6zx9<^>col NAME for a10
kIsH@x3z6zx9<^>col DATATYPE_STRING for a15
kIsH@x3z6zx9<^>select * from wrh$_SQL_BIND_METADATA where rownum < 6;
SNAP_ID DBID SQL_ID NAME POSITION DUP_POSITION DATATYPE DATATYPE_STRING CHARACTER_SID PRECISION SCALE MAX_LENGTH
---------- ---------- ------------- ---------- ---------- ------------ ---------- --------------- ------------- ---------- ---------- ----------
51 3942786352 13dctwg8htzkv :31 31 1 VARCHAR2(32) 873 32
51 3942786352 13dctwg8htzkv :32 32 1 VARCHAR2(32) 873 32
51 3942786352 13dctwg8htzkv :33 33 1 VARCHAR2(32) 873 32
51 3942786352 13dctwg8htzkv :34 34 1 VARCHAR2(32) 873 32
51 3942786352 13dctwg8htzkv :35 35 1 VARCHAR2(128) 873 128
Check the tablespace and corresponding index
kIsH@x3z6zx9<^>select table_name,index_name,tablespace_name from dba_indexes where index_name like '%BIND_METADATA%';
TABLE_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WRH$_SQL_BIND_METADATA WRH$_SQL_BIND_METADATA_PK SYSAUX
cursor_bind_capture_destination is a parameter which decides the place where bind should be stored, either memory or disk or both. It depends on the individuals choice. If the binds are stored in disk, then additional care and storage is needed.
kIsH@x3z6zx9<^>show parameter bind
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
Check the records which contribute the highest
kIsH@x3z6zx9<^>select name,count(*) bindcount from WRH$_SQL_BIND_METADATA group by name order by bindcount desc;
NAME BINDCOUNT
------------------------------ ----------
:1 131
:B1 99
:B2 76
:2 68
:3 54
:B4 52
:B3 51
:DBID 40
Check size of the binds
kIsH@x3z6zx9<^>select segment_name,bytes from dba_Segments where segment_name='WRH$_SQL_BIND_METADATA';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
WRH$_SQL_BIND_METADATA 131072
Move the table inside the tablespace to remove fragmentations
kIsH@x3z6zx9<^>alter table WRH$_SQL_BIND_METADATA move;
Table altered.
Elapsed: 00:00:00.34
kIsH@x3z6zx9<^>select segment_name,bytes from dba_Segments where segment_name='WRH$_SQL_BIND_METADATA';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
WRH$_SQL_BIND_METADATA 131072
Take a backup of the table to a new tablespace and truncate the table
kIsH@x3z6zx9<^>create table SQL_BIND_METADATA tablespace USERS as select * from WRH$_SQL_BIND_METADATA;
Table created.
Elapsed: 00:00:00.33
kIsH@x3z6zx9<^>select count(*) from SQL_BIND_METADATA;
COUNT(*)
----------
1567
Elapsed: 00:00:00.01
kIsH@x3z6zx9<^>truncate table WRH$_SQL_BIND_METADATA;
Table truncated.
Elapsed: 00:00:00.23
kIsH@x3z6zx9<^>select count(*) from WRH$_SQL_BIND_METADATA;
COUNT(*)
----------
0
Verify the size
kIsH@x3z6zx9<^>select segment_name,bytes from dba_Segments where segment_name='WRH$_SQL_BIND_METADATA';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
WRH$_SQL_BIND_METADATA 65536
Follow the same procedure for DBA_HIST_SQL_BIND_METADATA to clean them from disk
Reduce the value for hidden parameter ‘_awr_mmon_deep_purge_interval’ to increase the interval for MMON purge of expired snapshots. Consult before using the hidden ones.
kIsH@x3z6zx9<^>set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
col Desc for a20
select x.ksppinm as Hidden_parameter,
y.ksppstvl "It's Value",
x.ksppdesc "Desc"
from
x$ksppi x
inner join x$ksppcv y on (x.indx = y.indx)
kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^> 2 3 4 5 6 7 and
x.ksppinm like '%_awr_mmon_deep_purge_interval%'
order by x.ksppinm; 8 9
HIDDEN_PARAMETER It's Value Desc
-------------------- -------------------- --------------------
_awr_mmon_deep_purge 7 Set interval for dee
_interval p purge of AWR conte
nts
Default value is 7. Reduce it to 1 for frequent MMON purge activity.
kIsH@x3z6zx9<^>alter system set "_awr_mmon_deep_purge_interval"=1;
System altered.