Recent site activity

Copyright

PlanViz

About

PlanViz application is a spin-off prototype of a larger project going on at E2SN. It aims to make reading Oracle execution plan trees easier

Application URL

  • http://e2sn-planviz.appspot.com/
  • Note that if it takes really long (10+ seconds) for this URL to load, it's because Google Application Engine shuts down JVMs of applications that have been inactive for a while. Once the app loads, it should be fast!

How to use it

  1. Run your statement with GATHER_PLAN_STATISTICS hint or run "ALTER SESSION SET statistics_level = ALL" in your session before running your statement.
  2. Run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null, 'ALLSTATS LAST'))
  3. Paste output to http://e2sn-planviz.appspot.com/ and click Visualize
Now you will see the execution plan tree hierarchy visualized in a better way than DBMS_XPLAN does it. Also in the right hand you will see a treemap where the height of the box represents actual time spent inside that row source in execution plan (A-Time).

Make sure you move mouse over the colored treemap to see to which execution plan part (left side) the treemap box corresponds.

Note that JavaScript must be enabled. The visualization may not work due JavaScript problems in IE7, in which case use IE8 or Firefox until we work around the IE problems.

Example output


SQL> select /*+ gather_plan_statistics */ count(*) from dba_objects;

  COUNT(*)
----------
     73354

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  86pdr43g2p8fd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from dba_objects

Plan hash value: 2945320129

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |      1 |        |      1 |00:00:01.77 |    1828 |    919 |       |       |          |
|   1 |  SORT AGGREGATE                 |             |      1 |      1 |      1 |00:00:01.77 |    1828 |    919 |       |       |          |
|   2 |   VIEW                          | DBA_OBJECTS |      1 |  68514 |  73354 |00:00:01.74 |    1828 |    919 |       |       |          |
|   3 |    UNION-ALL                    |             |      1 |        |  73354 |00:00:01.67 |    1828 |    919 |       |       |          |
|*  4 |     FILTER                      |             |      1 |        |  73354 |00:00:01.53 |    1827 |    918 |       |       |          |
|*  5 |      HASH JOIN                  |             |      1 |  73560 |  74262 |00:00:01.31 |     927 |    908 |  1517K|  1517K| 1442K (0)|
|   6 |       INDEX FULL SCAN           | I_USER2     |      1 |     96 |     96 |00:00:00.01 |       1 |      0 |       |       |          |
|*  7 |       HASH JOIN                 |             |      1 |  73560 |  74262 |00:00:01.06 |     926 |    908 |  1180K|  1180K| 1227K (0)|
|   8 |        INDEX FULL SCAN          | I_USER2     |      1 |     96 |     96 |00:00:00.01 |       1 |      0 |       |       |          |
|*  9 |        TABLE ACCESS FULL        | OBJ$        |      1 |  73560 |  74262 |00:00:00.77 |     925 |    908 |       |       |          |
|* 10 |      TABLE ACCESS BY INDEX ROWID| IND$        |   4886 |      1 |   3983 |00:00:00.09 |     900 |     10 |       |       |          |
|* 11 |       INDEX UNIQUE SCAN         | I_IND1      |   4886 |      1 |   4886 |00:00:00.07 |     131 |     10 |       |       |          |
|  12 |      NESTED LOOPS               |             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 13 |       INDEX FULL SCAN           | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 14 |       INDEX RANGE SCAN          | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  15 |     NESTED LOOPS                |             |      1 |      1 |      0 |00:00:00.02 |       1 |      1 |       |       |          |
|  16 |      INDEX FULL SCAN            | I_LINK1     |      1 |      1 |      0 |00:00:00.02 |       1 |      1 |       |       |          |
|* 17 |      INDEX RANGE SCAN           | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
              "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13
              AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
              (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
              AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
   5 - access("O"."SPARE3"="U"."USER#")
   7 - access("O"."OWNER#"="U"."USER#")
   9 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
              BITAND("O"."FLAGS",128)=0))
  10 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
  11 - access("I"."OBJ#"=:B1)
  13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  17 - access("L"."OWNER#"="U"."USER#")


48 rows selected.





Now take the DBMS_XPLAN output (or you can even paste the above example) to http://e2sn-planviz.appspot.com/ and click Visualize, you should see output similar to the screenshot below:



That's it! Note that PlanViz is currently just a prototype, it doesn't have full functionality yet and is probably buggy too.



Comments