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
-
what is "ITEM_1" and why does it replace "F"."AGE_CODE" ?
-
is there a better way to understand what joins were made in the DB?
-
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)
Best Answer
Simply put, with JF, queries like this:
Can be automatically rewritten by the optimizer to something like this:
You can see a step called
VW_JF_...
in your plan whan this happens. There are other transformations as well that can cause this.This is too generic and unclear what you want.
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:
Same with alias mapping: