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.

Leave a Reply