Thursday, May 28, 2009

Scripts for showing execution plans via plain SQL and also in Oracle 9i

Tanel Poder has just put out some cool scripts that display execution plans, but unlike Oracle's, work with multiple Oracle versions and don't treat all bind variables as VARCHAR2.

Look really useful.

Tanel says:

    Why would you want to do this as there’s the DBMS_XPLAN.DISPLAY_CURSOR() function in Oracle 10g?

    Well, my scripts work also on Oracle 9.2. Also they give you better detail (and flexibility) compared to DBMS_XPLAN.DISPLAY_CURSOR.

    Of course in 9i there’s the DBMS_XPLAN.DISPLAY function which you can use in conjuction with EXPLAIN PLAN FOR command, but that approach has problems. EXPLAIN PLAN treats all bind variables as varchar2 datatype, possibly causing the reported execution plan to be different from reality. Also, if you have a long running report from last night and you run explain plan today morning, then if optimizer statistics have changed meanwhile, the explain plan command might come up with a different execution plan again, which is different from reality.

    So that’s why in 10g we have the DBMS_XPLAN.DISPLAY_CURSOR which goes directly to required library cache child cursor and extracts (unparses) the execution plan from there. The function uses V$SQL_PLAN% views as its data source. And guess what - these views are there in version 9.2 already! And thats’ where my scripts come in:

No comments:

Post a Comment