-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/&
-- # Name: Display cursor
-- ############################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ############################################################
-- #
-- # How to display more informations than explain 
-- # plan about the cursor: binds variables, 
-- #  estimated vs real rows (tuning with cardinalities)...
-- # 
-- # pre-requisite:
-- # - statistics_level=ALL
-- # - CBO used and tables analyzed
-- # - cursor_sharing=force (for "SERIAL PEEKED_BINDS IOSTATS LAST")
-- # - Better to have enough SGA to find old statements (1G)
-- # 
-- ############################################################


--      INPUTS (v$sql_area): 
--	&1 = SQL_ID (in lowercase)
--	&2 = child_number


-- Informations about the last SQL Statement:
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


-- Informations about another statement identified with sql_id and child_number:
SELECT * FROM TABLE(dbms_xplan.display_cursor('&1', &2, 'ALLSTATS LAST'));


-- To display the values of bind variables
select * from table(dbms_xplan.display_cursor('&1', &2, 'SERIAL PEEKED_BINDS IOSTATS LAST'));


-- To read the result:
--   "E-Rows" is the number of estimated rows that Oracle expects that step in the plan to return
--   "A-Rows" is the actual number. 
--   "Starts" column is the number of times that that step in the plan is "executed"
--    Note: "A-Rows" column is the cumulative count over all executions, 
--          "E-Rows" is the estimate for each execution of the step. 


Output example:


-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.02 |     196 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |     40 |    225 |00:00:00.02 |     196 |
|   3 |    NESTED LOOPS              |      |      1 |    225 |    241 |00:00:00.03 |     196 |
|*  4 |     TABLE ACCESS FULL        | T2   |      1 |     40 |     40 |00:00:00.01 |      45 |
|*  5 |     INDEX RANGE SCAN         | T_I1 |     10 |     40 |    400 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------------        

In this example, in line 5:  E-rows = 40 and A-rows = 400, because line 5 starts 10 times: 
so 400 actual rows = (10 starts) * (40 estimated rows per start).