Readable join for execution plan in Oracle

Question

I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan. Example: Consider this query:

SELECT f.year, f.geoid, a.age_band, sum(f.num_people), sum(f.num_insured)
FROM fact f
  JOIN age_bands a ON f.age_code = a.age_code
WHERE f.gender_code = 1
GROUP BY f.year, f.geoid, a.age_band

When I try to see the execution plan with this command:

SELECT s.sql_id, p.*
FROM v$sql s CROSS JOIN LATERAL (SELECT * FROM TABLE (
  dbms_xplan.display_cursor (s.sql_id, s.child_number, 'ALLSTATS LAST')
)) p
WHERE s.sql_text LIKE '%f.gender_code = 1%';

I get different values for the Predicate Information values (for different child numbers) for instance - for child number 0:

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ITEM_1"="A"."AGE_CODE")
   6 - filter("F"."GENDER_CODE"=1)

for child number 1 (and 2):

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("F"."AGE_CODE"="A"."AGE_CODE")
   4 - filter("F"."GENDER_CODE"=1)

My questions are

1. what is "ITEM_1" and why does it replace "F"."AGE_CODE" ?

2. is there a better way to understand what joins were made in the DB?

3. is there a simple way to convert the alias in the Predicate Information to the full table name? (for instance convert "A" to "AGE_BANDS" in the above example)

Answer 1

1. That is because of query transformations like for example Join Factorization.

Simply put, with JF, queries like this:

select t1.c1, t2.c2
    from t1, t2, t3
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2 
  union all
    select t1.c1, t2.c2
    from t1, t2, t4
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Can be automatically rewritten by the optimizer to something like this:

select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2
                   from t2, t3
                   where t2.c2 = t3.c2 and t2.c2 = 2                 
                 union all
                   select t2.c1 item_1, t2.c2 item_2
                   from t2, t4 
                   where t2.c3 = t4.c3) VW_JF_1
    where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

You can see a step called VW_JF_... in your plan whan this happens. There are other transformations as well that can cause this.

2. This is too generic and unclear what you want.

3. Not that I am aware of. You can however display an alias mapping with the format=>'alias' option:

SQL> explain plan for select count(*) from t1 a where a.object_id = 1;

Explained.

Default output:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - access("A"."OBJECT_ID"=1)

14 rows selected.

Same with alias mapping:

SQL> select * from table(dbms_xplan.display(format=>'alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

   1 - T1
   2 - T1 / A@T1

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

   2 - access("A"."OBJECT_ID"=1)

20 rows selected.

SQL>