New cursor_bind_capture_destination parameter in Oracle 11.2.0.2

January 19, 2011 by 6 Comments

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

Filed Under: Oracle Tagged With: , ,

Performance Stories from Exadata Migrations

January 11, 2011 by 19 Comments
Here are my UKOUG 2010 slides about Exadata migration performance, this is real life stuff, not repeating the marketing material:
View more presentations from tanelp.
Filed Under: Oracle Tagged With: , ,

Is this valid SQL syntax? :-)

January 10, 2011 by 21 Comments

I’m talking about this:

select-1from from dual;

Looks like invalid, right? Well, let’s run it:

SQL> select-1from from dual;

       ROM
----------
 -1.0E+000

This is because:

  1. Oracle doesn’t need whitespace for tokenizing the SQL statement (differences in character classes will do – as I’ve explained here)
  2. The first from “keyword” in the above statement is broken down to two tokens as an “F” right after a digit means that the preceding number is a FLOAT (and “D” means DOUBLE) and the tokenizer stops right there, knowing that whatever comes after this character (“ROM”) is a next token, which according to the Oracle SQL syntax rules will be assigned as the output column alias

The following funky-looking SQL statements are also valid:

SQL> select.1e2ffrom dual;

     .1E2F
----------
  1.0E+001

SQL> select.1e2fas"."from dual;

         .
----------
  1.0E+001

In the upper example, the “.1e2f” means number .1 * 10^2 (scientific notation) represented as a FLOAT internally and in the lower one I’ve just added a column alias with “AS” keyword just to make the SQL look a bit crazier.

:-)

Filed Under: Cool stuff, Oracle Tagged With:

Snapper 3.52 – With Oracle 9.2 support!

January 9, 2011 by 23 Comments

As I promised last year, I have 2 christmas gifts for you. I have already forgotten what the other one was supposed to be :), but the first one is Snapper v3.52 which has (the much requested) Oracle 9.2 support!

The syntax is the same, with Snapper you can now sample ASH-like data on Oracle 9.2 too. Instead of SQL_IDs it will display you SQL hash values:

SQL> @snapper ash,ash1=user+sql_id,ash2=sid+event 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

--------------------------------------------------
Active% | SQL_HASH_VAL | EVENT
--------------------------------------------------
    19% | 867131449    | db file scattered read
    19% | 1458866975   | db file scattered read
    13% | 1267657304   | db file scattered read
     6% | 884811952    | ON CPU
     6% | 581825634    | db file scattered read
     3% | 867131449    | ON CPU
     3% | 1267657304   | ON CPU
     3% | 1866659945   | ON CPU
     3% | 1671194465   | db file scattered read
     3% | 3021169464   | ON CPU

------------------------------------------------
Active% | USERNAME             | SQL_ID
------------------------------------------------
    23% | SYSTEM               | 867131449
    19% | SYSTEM               | 1458866975
    16% | SYSTEM               | 1267657304
     6% | SYSTEM               | 581825634
     6% | SYSTEM               | 884811952
     3% | SYSTEM               | 1558333473
     3% | SYSTEM               | 1671194465
     3% | SYSTEM               | 1866659945
     3% | SYSTEM               | 1927486197
     3% | SYSTEM               | 2700565926

--------------------------------------------
Active% |    SID | EVENT
--------------------------------------------
    77% |     18 | db file scattered read
    19% |     18 | ON CPU
     3% |     18 | db file sequential read

--  End of ASH snap 1, end=2011-01-10 03:02:58, seconds=5, samples_taken=31

PL/SQL procedure successfully completed.


Other than the 9i change, the rest of the snapper is pretty much the same as earlier, with some minor bugfixes and additions.

You can download it from here.

If you want to get the most out of snapper, read this article here (and make sure you look inside the script!)

Note: Big thanks to Marcus Mönnig who who wrote the additional 9i support code for Snapper first and Jamey Johnston for his additions (and myself for some final polishing fixes ;-)

Note2: I've agreed with Marcus that he could add Snapper into his free performance tool distribution in unchanged form, check out his Mumbai tool which could be useful for Oracle performance monitoring...

Filed Under: Uncategorized