New cursor_bind_capture_destination parameter in Oracle 11.2.0.2

I just noticed that there’s a new cursor_bind_capture_destination parameter in Oracle 11.2.0.2 (which is really more like Oracle 11gR3 version because of the large amount of new features in it, as opposed to just bugfixes).

This parameter allows you to save some SYSAUX tablespace disk space – if the occasionally captured bind variable values (from V$SQL_BIND_DATA) take too much space. Normally these bind values (in a packed RAW form) are visible in DBA_HIST_SQLSTAT.BIND_DATA column, which can take up to 2kB per statement in a snapshot – it’s stored as RAW(2000). Of course a more convenient way to query the actual bind values is to use DBA_HIST_SQLBIND (you can also use DBMS_SQLTUNE.EXTRACT_BIND function for translating the raw payload to meaningful values).

So, if you choose to capture a lot of SQL statements per AWR snapshot (it’s configurable) and don’t really care about the sampled bind variable values and want to save the disk space, then you can set cursor_bind_capture_destination = MEMORY or OFF (if you don’t want to capture bind variable values at all).

I’m using my pvalid.sql script for checking its valid values (it’s based on the X$ table underlying V$PARAMETER_VALID_VALUES view, so I could see undocumented parameter valid values too):

SQL> @pvalid cursor_bind_capture_destination
Display valid values for multioption parameters matching “cursor_bind_capture_destination”…

  PAR# PARAMETER                                                 ORD VALUE         
—— ————————————————– ———- —————
  2062 cursor_bind_capture_destination                             2 MEMORY
       cursor_bind_capture_destination                             3 MEMORY+DISK
       cursor_bind_capture_destination                             1 OFF

The default is MEMORY+DISK (this is essentially what you get before 11.2.0.2 and you can’t turn it off unless you turn off the AWR flushing of the whole SQLSTATS metrics).

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

6 Responses to New cursor_bind_capture_destination parameter in Oracle 11.2.0.2

  1. Greg Rahn says:

    “I’m using my pvalid.sql script for checking its valid values…”

    Or you could find it in the docs :)
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams041.htm

    Also listed in the New Features section of 11.2.0.2:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/whatsnew.htm#CEGBCJCB

  2. Tanel Poder says:

    @Greg Rahn

    Yeah, I first spotted this parameter in docs, actually. But it doesn’t explain what the hell is this parameter about really (you have to guess), then I thought that some research + blog entry would be useful – hopefully the next people wondering about this will save a bit of time thanks to this page…

  3. Tanel Poder says:

    To be fair (and to add to my last comment), the effect of this parameter was sort of documented, but not clearly enough… I want to know which tables (and in this case it’s about which column really)

  4. Vlado says:

    Another use case for this parameter is to address PCI compliance issues.
    You might have queries that use credit card numbers as bind variables, which would be stored in DBA_HIST_SQLSTAT unencrypted (against PCI rules). Tablespace encryption is not supported for SYSAUX so that is not an option.
    By setting this parameter to memory (for the applicable queries) the credit card numbers will not be stored thus circumventing the need to encrypt the bind variables on disk:)

  5. Júlio César Chaves says:

    Hi Tanel, thank you by explanation. I did not understand from oracle documentation what was it. In the meantime, could you post the pvalid.sql script again, or send me? The URL to download it does not work.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>