Re: What is WRH$_SQL_BIND_METADATA used for?

From: Á¶µ¿¿í <ukja.dion_at_gmail.com>
Date: Sat, 16 Feb 2008 23:55:55 +0900
Message-ID: <43c2e3d60802160655wd2031ees8ea2db75dfde5fa0@mail.gmail.com>

v$sql_bind_metadata view exposes literally metadata for bind variables used in SQL statements. The metadata contains { postion of bind variable, name for bind variable, max size of bind variable, data type of bind variable } WRH$_SQL_BIND_METADATA table(or dba_hist_sql_bind_metadata view) is historical version of v$sql_bind_metadata.

It seems that Oracle has invoked purge for old stuffs from your AWR, and unluckily you had a quite large amount of v$sql_bind_metata data to be deleted. Hence you had a massive redo generation.

You might have large number of bind variables in your SQL statements, like

"insert into ... values(?,?,?,?,...........................,?)". The

notorious bind mismatch could have made this worse than we generally expected.

If this(the purge on AWR) is the case, you can periodically purge AWR yourself to alleviate the burden of automatic purge.

PS)
For usage of v$sql_bind_metadata see following example. OWI_at_joss> var i number;
OWI_at_joss> exec :i := 1;

OWI_at_joss> select /*+ meta_data */ * from t1 where id = :i;

        ID

---

         1

OWI_at_joss> select child_address from v$sql   2 where sql_text like 'select /*+ meta_data */%';

CHILD_ADDRESS

---

000000008B5416F0

OWI_at_joss> select * from v$sql_bind_metadata   2 where address = '000000008B5416F0';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN

---------------- ---------- ---------- ---------- ----------

BIND_NAME

---

000000008B5416F0 1 2 22 0 I

OWI_at_joss> var i varchar2(1);
OWI_at_joss> exec :i := '1';

OWI_at_joss> select /*+ meta_data */ * from t1 where id = :i;

        ID

---

         1

OWI_at_joss> select child_address from v$sql   2 where sql_text like 'select /*+ meta_data */%';

CHILD_ADDRESS

---

000000008B5416F0
000000008316FBE0 OWI_at_joss> select * from v$sql_bind_metadata   2 where address = '000000008316FBE0';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN

---------------- ---------- ---------- ---------- ----------

BIND_NAME

---

000000008316FBE0 1 1 32 0 I

2008/2/16, Weatherman, John <John.Weatherman_at_asurion.com>:
>
> All,
>
>
>
> This week I have suddenly seen what has been a very stable production
> database, which switches logs about once every 6 hours, suddenly start
> switch logs every minute for about 1 hour a day. These are 20M logs, so
> this is a lot of activity. No one will admit to having scheduled any new
> jobs or doing anything new. I've done some log mining to try to see just
> what objects are being modified. The biggest offender by far seems to be
> deletes on WRH$_SQL_BIND_METADATA.
>
>
>
> Does anyone know what this table is being used for, beyond just AWR? I
> can't find any details on just what is stored in it and I am far from an AWR
> internals expert. I am sure that a "real" process is just forcing it to be
> updated to reflect changes, but I would like to understand just what it is
> really used for. The second worst offender is an application table, so I am
> really trying to understand why changes to the application table might
> result in 3-5X as many changes to WRH$_SQL_BIND_METADATA.
>
>
>
> Any insights would be greatly appreciated.
>
>
>
> TIA,
>
>
>
> John
>
>
>
>
>
> John P Weatherman john.weatherman_at_asurion.com
>
> Sr Oracle DBA Desk: (615) 762-1376
>
> Asurion, Inc Mobile: (615) 517-2831
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 16 2008 - 08:55:55 CST