SQL Execution Statistics -- 3 : Using STATISTICS_LEVEL

 In my previous posts here and here, I have shown how to use the GATHER_PLAN_STATISTICS Hint to collect additional information.  But what if you can't modify the SELECT query statement ?

You could set STATISTICS_LEVEL='ALL' at the session, run the query and then reset STATISTICS_LEVEL to the default  'TYPICAL'

SQL> alter session set tracefile_identifier='H2';

Session altered.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL> exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE);  -- just to get the SQL_ID in the trace file

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2  d.department_id, d.department_name, e.first_name, e.last_name
  3  from hr.departments d join hr.employees e
  4  on d.department_id=e.department_id
  5  order by 1,2,4,3
  6  /
....
....
....
106 rows selected.

SQL>
SQL> exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE);

PL/SQL procedure successfully completed.

SQL>
QL> select * from table(dbms_xplan.display_cursor('4aa2k5b98ybfr',format=>'ALLSTATS LAST'));  -- SQL_ID identified from the trace file

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4aa2k5b98ybfr, child number 0
-------------------------------------
select d.department_id, d.department_name, e.first_name, e.last_name
from hr.departments d join hr.employees e on
d.department_id=e.department_id order by 1,2,4,3

Plan hash value: 2209899241

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |    106 |00:00:00.10 |      10 |     14 |       |       |          |
|   1 |  SORT ORDER BY                |                   |      1 |    106 |    106 |00:00:00.10 |      10 |     14 | 11264 | 11264 |10240  (0)|
|   2 |   MERGE JOIN                  |                   |      1 |    106 |    106 |00:00:00.10 |      10 |     14 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |     27 |     12 |00:00:00.03 |       2 |      2 |       |       |          |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK        |      1 |     27 |     12 |00:00:00.03 |       1 |      1 |       |       |          |
|*  5 |    SORT JOIN                  |                   |     12 |    107 |    106 |00:00:00.06 |       8 |     12 |  9216 |  9216 | 8192  (0)|
|   6 |     VIEW                      | index$_join$_002  |      1 |    107 |    106 |00:00:00.06 |       8 |     12 |       |       |          |
|*  7 |      HASH JOIN                |                   |      1 |        |    106 |00:00:00.06 |       8 |     12 |  1610K|  1610K| 1658K (0)|
|   8 |       INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |      1 |    107 |    106 |00:00:00.06 |       4 |      6 |       |       |          |
|   9 |       INDEX FAST FULL SCAN    | EMP_NAME_IX       |      1 |    107 |    107 |00:00:00.01 |       4 |      6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   7 - access(ROWID=ROWID)


30 rows selected.

SQL>

Thus, I was able to get similar execution statistics without the Hint if I set STATISTICS_LEVEL='ALL'.

(Note : The top level operation here is a MERGE JOIN while that in the first example of the first post of this series on 27-January-22 was a HASH JOIN. The result here is 106 rows, not the 108 rows in the first example. The first example was in an HR schema in another database -- e.g. you would see DEPARTMENT_ID=280 'New Department' and Employee 'HEMANT CHITALE' in that listing.  These are two different HR schemas where I have modified some of the data.  Thus, you'll see a difference in the Execution Plan as well between the first example and this one. although the query is similar -- because the databases, patch levels,  possibly optimizer parameters, schema and data are different.)

Even a slight difference in data or parameters or patch levels can result in different execution plans !