Oracle - Get statistics of a query

Question

I have a query with many left joins and I would like to see the stats of this query to understand if it could be done better. How can I do it? I tried with

SELECT * FROM
LEFT JOIN ...

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY);

Is this correct? Are there other ways? I also tried by doing

begin
DBMS_STATS.gather_table_stats(
  ownname => 'IWFM_LL', 
  objname => 'V_PX_LOG',
  organized => 7,
  force => FALSE);
end;

but oracle says the sql is not valid. Error:

Answer 1

Make sure you run your query with statistics. It's the best possible information you can get!

First of all, if you are in SQL*Navigator or similar tool, make sure you turn off DBMS_OUTPUT / server output. Then,

alter session set statistics_level = ALL;

... run your query here... (e.g., SELECT * FROM dba_objects where rownum <= 15;)

... make sure you fetch the entire result set!!!

Then,

SELECT *
FROM   TABLE (DBMS_XPLAN.display_cursor (NULL, NULL,
                                         'ALLSTATS LAST'));

Sample output:

SQL_ID  b2aspknw47k1k, child number 0
-------------------------------------
SELECT * FROM dba_objects where rownum <= 15

Plan hash value: 3172921843

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     15 |00:00:00.01 |      25 |       |       |          |
|*  1 |  COUNT STOPKEY                 |             |      1 |        |     15 |00:00:00.01 |      25 |       |       |          |
|   2 |   VIEW                         | DBA_OBJECTS |      1 |    334 |     15 |00:00:00.01 |      25 |       |       |          |
|   3 |    UNION-ALL                   |             |      1 |        |     15 |00:00:00.01 |      25 |       |       |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID| SUM$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |      INDEX UNIQUE SCAN         | I_SUM$_1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |     TABLE ACCESS BY INDEX ROWID| OBJ$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |      INDEX RANGE SCAN          | I_OBJ1      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |     FILTER                     |             |      1 |        |     15 |00:00:00.01 |      25 |       |       |          |
|   9 |      NESTED LOOPS              |             |      1 |    393 |     15 |00:00:00.01 |      25 |       |       |          |
|* 10 |       HASH JOIN                |             |      1 |     21 |     15 |00:00:00.01 |       5 |  1599K|  1599K| 1562K (0)|
|  11 |        INDEX FULL SCAN         | I_USER2     |      1 |    392 |    394 |00:00:00.01 |       1 |       |       |          |
|* 12 |        TABLE ACCESS FULL       | OBJ$        |      1 |     21 |     15 |00:00:00.01 |       4 |       |       |          |
|  13 |       TABLE ACCESS CLUSTER     | USER$       |     15 |     19 |     15 |00:00:00.01 |      20 |       |       |          |
|* 14 |        INDEX UNIQUE SCAN       | I_USER#     |     15 |      1 |     15 |00:00:00.01 |       4 |       |       |          |
|  15 |      NESTED LOOPS              |             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 16 |       INDEX SKIP SCAN          | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 17 |       INDEX RANGE SCAN         | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 18 |     HASH JOIN                  |             |      0 |     25 |      0 |00:00:00.01 |       0 |  1055K|  1055K|          |
|  19 |      TABLE ACCESS FULL         | LINK$       |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|  20 |      TABLE ACCESS FULL         | USER$       |      0 |    236 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=15)
   4 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   5 - access("S"."OBJ#"=:B1)
   7 - access("EO"."OBJ#"=:B1)
   8 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 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 (("U"."TYPE#"<>2 AND 
              SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND 
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL))))
  10 - access("O"."OWNER#"="U"."USER#")
  12 - filter((BITAND("O"."FLAGS",128)=0 AND "O"."TYPE#"<>10 AND "O"."LINKNAME" IS NULL AND "O"."NAME"<>'_NEXT_OBJECT' AND 
              "O"."NAME"<>'_default_auditing_options_'))
  14 - access("O"."SPARE3"="U"."USER#")
  16 - 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'))))
  17 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  18 - access("L"."OWNER#"="U"."USER#")