0

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)

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.

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

  2. 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>
1
  • Thanks, but same code query (select * from table(dbms_xplan.display(format=>'alias')); ) on my machine shows the aliases with a different syntax: 1 - SEL$25DAF526 3 - SEL$25DAF526 / A@SEL$1 4 - SEL$59A58C63 / VW_GBC_5@SEL$2495896A etc...
    – yossico
    Aug 13 '20 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.