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).
